OracleBlog

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.

Oracle:
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.

http://thinkoracle.blogspot.com/2005/07/oracle-docs.html

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.

Testing:
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.

http://thinkoracle.blogspot.com/2005/06/expert-one-on-one.html

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.

http://thinkoracle.blogspot.com/2005/07/specifying-insert-columns.html

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.

http://thinkoracle.blogspot.com/2005/05/null-vs-nothing.html
http://thinkoracle.blogspot.com/2005/06/nulls-in-oracle.html
http://thinkoracle.blogspot.com/2005/09/nulls-in-count.html

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.

http://thinkoracle.blogspot.com/2005/07/use-constraints.html

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

http://thinkoracle.blogspot.com/2005/07/use-views.html

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

http://thinkoracle.blogspot.com/2005/08/keeping-tables-small.html

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.

http://thinkoracle.blogspot.com/2005/06/common-table-column-types.html

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

http://thinkoracle.blogspot.com/2005/06/natural-vs-synthetic-keys.html

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.

http://thinkoracle.blogspot.com/2005/06/decode.html

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

http://thinkoracle.blogspot.com/2005/06/connect-by.html

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

http://thinkoracle.blogspot.com/2005/08/compute.html

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

http://thinkoracle.blogspot.com/2005/06/oop-in-plsql-yep.html

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

http://thinkoracle.blogspot.com/2005/08/utlhttp.html

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!