Effective Oracle by Design

Thomas Kyte

Mentioned 3

Tom Kyte of Oracle Magazine’s “Ask Tom” column has written the definitive guide to designing and building high-performance, scalable Oracle applications. The book covers schema design, SQL and PL/SQL, tables and indexes, and much more. From the exclusive publisher of Oracle Press books, this is a must-have resource for all Oracle developers and DBAs.

More on Amazon.com

Mentioned in questions and answers.

I am a very experienced MS Sql developer, and a few new job positions are coming my way where I will be working with Oracle on a more daily basis.

As with all the technologies I have learned, I want to know the best places and books to get started and up to speed with designing and developing with Oracle, but with pure C#.

What resources are there for us Microsoft guys to jump in and go with Oracle? I realize there is oracle.com and asktom.oracle.com, as well as the mass amount of documentation on Oracle, I am looking more for a quick primer (setting up a server, getting some sample data to play with, etc...) rather than in depth sql vs. oracle technology comparisons.

Thanks in advance.

Try getting Tom Kyte's books Expert one-on-one Oracle and Effective Oracle by Design. They're good intermediate-advanced level Oracle books that are well written by someone who knows the product well.

Additionally, get to know the data dictionary. If you have aspirations to be anything more than a 'tools guy' in the Oracle world the data dictionary is your friend. It's also much better than the one in SQL Server.

Finally, if you're moving to Oracle from a MS-Only world, get some background in unix and shell script programming. You will find this very useful when scripting automation infrastructure for Oracle systems, particularly if they sit on vanilla Unix installs. There is any amount of legacy Oracle/Unix kit still in production and vanilla installs of Solaris, AIX or HP/UX tend to be much more spartan than Linux. In the Unix/Oracle world you can't necessarily rely on being able to install Perl or Python.

When you do have access to Perl or Python, get to know these as well. If you're into python, the best Oracle database interface library is cx_Oracle. I can't really vouch for any particular Perl infrastructure as I have never had occasion to use it for this type of work. CPAN is the canonical resource for add-on modules for Perl.

In the (relatively unlikely) event that you're working on an Oracle/Windows environment(Oracle on Linux is more widely used in low-end Oracle deployments), good Windows distributions of both Perl and Python can be downloaded from Activestate.

How do you determine when to use table clusters? There are two types, index and hash, to use for different cases. In your experience, have the introduction and use of table clusters paid off?

If none of your tables are set up this way, modifying them to use table clusters would add to the complexity of the set up. But would the expected performance benefits outweight the cost of increased complexity in future maintenance work?

Do you have any favorite online references or books that describe table clustering well and give good implementation examples?

//Oracle tips greatly appreciated.

One can speak a lot about clustering, but I found that almost ultimate explanation about Oracle clusters (pros and cons, when to use and how to use) can be found in Tom Kyte's book - Effective Oracle by Design, also you can search asktom for some specific cluster usage examples (1, 2 etc). You should definitely take a look at this book if you haven't yet.

Some info you can also find here.

But the thing you should always do before creating complex schema structures is to try, to test, to benchmark and choose the one solution that best fits your needs :)

Hope this helps.

For someone new to SQL Tuning with Oracle, can people please provide pointers as well as informative URLs that can assist me with how to approach an SQL query as well as define indexes where necessary, when it comes to tuning SQL queries for tables holding large quantity of data, in excess of 50,000 rows?

What are the main things that I should be looking out for to speed up an SQL statement?

It's a big topic. Anything by Tom Kyte is good:

Jonathan Lewis Cost-Based Oracle Fundamentals is the best book on the market if you want to learn how Oracle "thinks" when selecting access paths, join mechanism, join order etcetera.

It takes a few readings to get the hang of it, but once it "clicks" you have a ridiculusly powerful tool in your box, because not only can you troubleshoot much better, but you start to design tables and indexes in a way that play to the strengths of the database.