Oracle SQL tuning: pocket reference by Mark Gurry

By Mark Gurry

I did not realize that this pocket reference used to be nonetheless in its first printing, from January of 2002. As such many of the content material is now out of date. particularly the writer devotes loads of area to evaluating the Rule-Based and Cost-Based Optimizers, and issues out the transparent superiority of the latter. This used to be vast information whilst Oracle 9i used to be approximately to be published, no longer quite a bit now.

As to the remaining, there are 22 pages (out of in simple terms 102) enumerating the numerous tricks you can still practice to SQL. there is not a lot the following that may not already on hand from Oracle documentation without delay.

Show description

Read Online or Download Oracle SQL tuning: pocket reference PDF

Best oracle books

Oracle PL/SQL Programming 4Th Edition

За последние 10 лет книга Oracle PL/SQL Programming издательства O'Reilly's стала бестселлером среди книг по PL/SQL, процедурному языку базы данных Oracle. Снабженная примерами и полезными рекомендациями книнга является незаменимой как новичкам, так и гуру, как разработчикам на Oracle varieties, так и администраторам - используйте PL/SQL на полную мощь.

Oracle Streams 11g Data Replication

Grasp Oracle Streams 11g Replication allow real-time info entry and knowledge sharing throughout your disbursed framework utilizing the specialist info during this Oracle Press consultant. Oracle Streams 11g info Replication explains find out how to manage and administer a unified firm facts sharing infrastructure.

Oracle Application Express 4 Recipes

Oracle software convey four Recipes presents an example-based method of studying program convey - the ground-breaking, speedy program improvement platform integrated with each Oracle Database license. The recipes layout is perfect for the quick-study who simply desires a superb instance or to kick commence their considering and get pointed within the correct path.

Extra resources for Oracle SQL tuning: pocket reference

Sample text

As a rule of thumb, if a query returns less than 10% of the rows from the tables involved, you should be using nested loops. Use hash joins or sort merges if 10% or more of the rows are being returned. To perform a hash join, a hash table is created in the memory of the smallest table, and then the other table is scanned. The rows from the second table are compared to the hash. A hash join will usually run faster than a merge join (involving a sort, then a merge) if memory is adequate to hold the entire table that is being hashed.

The new index was on (account_id, fin_yr, val). The result was that the index entirely satisfied the query, and the table did not need to be accessed. Another common problem I notice is that when tables are joined, the leading column of the index is not the column(s) that the tables are joined on. TRAN_TYPE = 'SALARY' In this situation, many sites will have an index on SURNAME for the ACCT table, and an index on TRAN_DATE and TRAN_TYPE for the TRANS table. To speed the query significantly, it is best to add the ACCT_NO join column as the leading column of the TRANS index.

In addition, merge joins will often run faster when all of the columns in the WHERE clause are presorted by being in an index. In this case, the rows are simply plucked from the tables using the ROWID in the index. With a merge join, all tables are sorted, unless all of the columns in the WHERE clause are contained within an index. This sort can be expensive, and it explains why a hash join will often run faster than a merge join. As with a hash join, the entire result set must be determined before a single row is returned to the user.

Download PDF sample

Rated 4.11 of 5 – based on 36 votes