Thursday, April 05, 2007
Oracle Beefs
I've got very few beefs with Oracle. It is extremely complicated and tough to learn compared to other relational databases, but that's partially offset by the tremendous documentation, and the huge Oracle community.
Don't get me wrong, I love Oracle. I'm regularly impressed by the sophisticated and often clever ways it handles the requirements of an RDBMS, and quick to recommend its use. But before making that recommendation, I make it clear that this advantage has a trade-off: expense.
Oracle is expensive by itself, both in money and in resources. Furthermore, it can be difficult to find people that know it well, or to train your own staff. You better be sure that you are planning on taking advantage of the benefits of using a more powerful, fast and reliable RDBMS before choosing Oracle. Too often I have seen software developed for Oracle, but seemingly going to great pains to actually AVOID using the packages, features and facilities Oracle has built-in to maintain data integrity and speed up data access.
My other beefs with Oracle are mostly just pet peeves. It still boggles me how they can refuse to introduce a Boolean type because we don't NEED it, and yet introduce all sorts of convoluted features (like DBMS_ADVANCED_REWRITE, another pet peeve). I also wish that constraints on views could be enforced.
Speaking of pet peeves, there has been a whole discussion on AskTom over the past couple of years. Here are my top ten most interesting:
1. The "table or view does not exist" error message should include the name of the invalid table/view.
2. A lot of people mentioned wanting better formatting in SQL*Plus
3. Niall Litchfield has a good rumination about binds that started a bit of a discussion:
I really don't see why "select cols from tab where pk = 10;" should be radically worse than "select cols from tab where pk = :b1;" I know why it is, I just wish it wasn't.
4. Look for Alberto Dell'Era's desire for better instrumentation. I find Oracle's instrumentation far superior to others RDBMS, but I agree there's always room for improvement.
5. Customized errors messages for constraint violations, like in Sybase.
Example: alter table widgets add constraint cost_chk check (cost < 100) error "Widget cost must be less than $100";
6. There were some who wanted to be able to disable implicit type conversion
7. Check out Connor McDonald's 19 suggestions (what, he couldn't think of 1 more to make a round number?)
8. Better support for business rules in the database. I interpret this as being able to support multi-table check constraints without restoring to triggers: I'd love that. ANSI has it.
9. Stop treating empty strings as NULL (I'm not touching this one, I'm just saying it's interesting).
10. Having an auto-incrementing identity column, without using sequences and such.
Incidentally, don't expect Tom to defend Oracle on every point. He himself has a few beefs I've heard over the years: autonomous transactions, "WHEN OTHERS" exception handling, triggers, and others.
Final point: please check out and enjoy some blogs that are new among my links: Don Seiler and Coskan Gundogar.
Don't get me wrong, I love Oracle. I'm regularly impressed by the sophisticated and often clever ways it handles the requirements of an RDBMS, and quick to recommend its use. But before making that recommendation, I make it clear that this advantage has a trade-off: expense.
Oracle is expensive by itself, both in money and in resources. Furthermore, it can be difficult to find people that know it well, or to train your own staff. You better be sure that you are planning on taking advantage of the benefits of using a more powerful, fast and reliable RDBMS before choosing Oracle. Too often I have seen software developed for Oracle, but seemingly going to great pains to actually AVOID using the packages, features and facilities Oracle has built-in to maintain data integrity and speed up data access.
My other beefs with Oracle are mostly just pet peeves. It still boggles me how they can refuse to introduce a Boolean type because we don't NEED it, and yet introduce all sorts of convoluted features (like DBMS_ADVANCED_REWRITE, another pet peeve). I also wish that constraints on views could be enforced.
Speaking of pet peeves, there has been a whole discussion on AskTom over the past couple of years. Here are my top ten most interesting:
1. The "table or view does not exist" error message should include the name of the invalid table/view.
2. A lot of people mentioned wanting better formatting in SQL*Plus
3. Niall Litchfield has a good rumination about binds that started a bit of a discussion:
I really don't see why "select cols from tab where pk = 10;" should be radically worse than "select cols from tab where pk = :b1;" I know why it is, I just wish it wasn't.
4. Look for Alberto Dell'Era's desire for better instrumentation. I find Oracle's instrumentation far superior to others RDBMS, but I agree there's always room for improvement.
5. Customized errors messages for constraint violations, like in Sybase.
Example: alter table widgets add constraint cost_chk check (cost < 100) error "Widget cost must be less than $100";
6. There were some who wanted to be able to disable implicit type conversion
7. Check out Connor McDonald's 19 suggestions (what, he couldn't think of 1 more to make a round number?)
8. Better support for business rules in the database. I interpret this as being able to support multi-table check constraints without restoring to triggers: I'd love that. ANSI has it.
9. Stop treating empty strings as NULL (I'm not touching this one, I'm just saying it's interesting).
10. Having an auto-incrementing identity column, without using sequences and such.
Incidentally, don't expect Tom to defend Oracle on every point. He himself has a few beefs I've heard over the years: autonomous transactions, "WHEN OTHERS" exception handling, triggers, and others.
Final point: please check out and enjoy some blogs that are new among my links: Don Seiler and Coskan Gundogar.
Comments:
<< Home
I'm with you on the issue of dozens of obscure DBMS packages appearing when everyday features seem to be crying out for implementation (or fixing), although I suppose different people have their own ideas of what is obscure and what is life-changing. I can only imagine there must be a lot of sites out there saying "DBMS_PCLXUTIL - at last!" while presumably not having much use for FORALL and object types which have been broken for years. The campaign to fix DBMS_OUTPUT took 10 years so I'm not holding my breath.
Regarding Booleans in SQL, I agree it is frustrating that there is no built-in way to define a Yes/No indicator, and as a result applications end up with a random assortment of Y/N, 1/0 etc. However Booleans would be a huge change to the language and require a correspondingly huge change to client interfaces. Tools like SQL*Plus would have to be able to interpret them, presumably using 'TRUE'/'FALSE' rather than translating them into NLS_LANGUAGE ('VRAI'/'FAUX'?), along with the corresponding middleware products like OCI and JDBC. We would need an IS [NOT] TRUE operator, but should we also allow a Boolean to stand alone as a WHERE predicate or in a CASE expression? Should we be able to use expressions like "sal > 1000" in a SELECT list and have them implicitly cast to Boolean, or should there be additional functions, perhaps
BOOLEAN(sal > 1000)
or just
CAST(sal > 1000 AS BOOLEAN)
That would open up the way for some funky constructions like
SELECT ename, (sal > 1000) = (mgr IS NULL)
FROM emp
WHERE is_permanent;
I'd certainly be interested to see this sort of thing, but I can understand Oracle's reluctance to make such a huge change to the language and the related products (i.e. all of them).
Post a Comment
BOOLEAN(sal > 1000)
or just
CAST(sal > 1000 AS BOOLEAN)
That would open up the way for some funky constructions like
SELECT ename, (sal > 1000) = (mgr IS NULL)
FROM emp
WHERE is_permanent;
I'd certainly be interested to see this sort of thing, but I can understand Oracle's reluctance to make such a huge change to the language and the related products (i.e. all of them).
<< Home
