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.
