Monday, September 12, 2005

20 Oracle Lessons

I started using Oracle with version 8 in 1999. After a few years I changed companies to a Sybase/SQL-Server shop. But the past year has found me back working with Oracle, this time version 8, and 9.

It has been an interesting time getting myself back into "game shape" with Oracle, and digging into version 9. If you've been reading this blog, you've been able to follow along with me in my adventures.

I decided this was as good a time as any to pause and reflect on some of the lessons I've learned in this past year.

1. Oracle is very complex.
I always thought "a database is a database" but Oracle is about 4 times as complex as Sybase/MS-SQL.

2. Fortunately Oracle is well-documented.
I have fallen in love with Oracle documentation. Clear, well-written, comprehensive and lots of examples.

3. There are many on-line Oracle sites and forums to find help.
The primary benefit of a popular product is that whatever your mistake is, you're probably not the first person to experience it. There is a huge on-line Oracle community, and so many places to search for help. My favourite links and blogs are kept current on this site.

4. It's quick, free and very easy to set up a personal Oracle database on your Windows PC for testing purposes.

5. Build proper test cases, and test everything you read before your implement.
This is part of my personal style that I apply to all my work, regardless of technology. But I feel it is especially true of a database as complex as Oracle. Especially with all the different versions out there.

6. Burleson Consulting makes a lot of mistakes.
So test those even more carefully. But remember, even the stellar Tom Kyte makes mistakes.

Coding/Modelling Practises:
7. Document your code.
It makes it easier to reuse good code, and fix bad code. Again, this is my personal style that I apply to all project, regardless of technology. Do the best I can, and explain what I'm doing.

8. Specify column/parameter names when writing queries or making stored procedure calls.

9. NULLs are very special
This is one of my favourite topics. Casual database programmers might not be aware of all the special cases related to NULLs, making them a common cause of honest (but costly) mistakes.

10. Data integrity is best accomplished in the database layer (as opposed to procedure or application layers).
Why use a sophisticated database like Oracle if you're just going to use it as a data store? Use Oracle's ability to protect your data's integrity, and then you can fear badly written applications a little bit less.

11. Triggers can be used as a form of "advanced constraint" for data integrity.

12. Views are very useful in solving complex queries without affecting your data model (among many other uses!)

13. A great way to improve performance is to archive/partition data.

14. You can often achieve common table types and procedure parameters by defining user types, using %TYPE and referencing foreign keys. Otherwise, use an advanced SQL modeller to develop your PL/SQL.

15. Choose carefully between using natural and synthetic keys when designing your tables.

Useful Oracle (other than REGEXP, in Version 10):

16. DECODE is incredibly useful (CASE WHEN can also be used).
I am a huge fan of DECODE, I can't imagine working without it. It is perhaps poorly named.

17. CONNECT BY is useful when you need hierarchical, string aggregation (stragg).

18. GROUP BY has a lot of relatives to help write queries for complex analytic functions: RANK, GROUPING SETS, GROUPING_ID, ROLLUP

19. PL/SQL supports OOP (Object-Oriented Programming)

20. Oracle has a lot of handy toolkits
For example UTL_HTTP can be used to achieve very simple screen-scraping (among other things)

One more thing that didn't make the list (because it would push the total from an even 20 to an odd 21):
When you're having trouble writing a very clever query or procedure, take a step back and look at your data model. It might be inappropriate for how you're using it.

Thanks for joining me on this stroll down memory lane!

21st point is the best one!
Sometimes Oracle doucmentation is clear and useful.
But how much use are these flow charts of the SQL syntax,
which IMO are fairly typical of Oracle docs?

Personally I feel that the terse and partially (or non-publically) documented nature of Oracle
makes for an entire range of publications which would not otherwise be there.
... to another anonymous who wrote on timestampt 13 September 2005

But how much use are these flow charts of the SQL syntax,
which IMO are fairly typical of Oracle docs?

I find them very useful. If you happen to have a vague knowledge of the syntax,
it really helps to get the little bits right, like what is the correct order
of the syntactical elements or which part is optional etc.

On the downside, if you're about to enter a new field like e.g. XML DB, I frequently
find it very hard to gather all the necessary bits that get you started and
let you understand the interaction of the components involved.

This comment has been removed by a blog administrator.
As a designer for webdesign leeds I come across a lot of coding not just for sites but for software and applications, I personally have never used this and find it extreemly interesting.
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?