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.
Read Online or Download Oracle SQL tuning: pocket reference PDF
Best oracle books
За последние 10 лет книга Oracle PL/SQL Programming издательства O'Reilly's стала бестселлером среди книг по PL/SQL, процедурному языку базы данных Oracle. Снабженная примерами и полезными рекомендациями книнга является незаменимой как новичкам, так и гуру, как разработчикам на Oracle varieties, так и администраторам - используйте PL/SQL на полную мощь.
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 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.
- Pro Oracle Spatial, 1st Edition
- Oracle Fusion Middleware patterns : real-world composite applications using SOA, BPM, Enterprise 2.0, Business Intelligence, Identity Management, and Application Infrastructure
- Oracle NoSQL database : real-time big data management for the enterprise, 1st Edition
- OCP: Oracle9i Performance Tuning Study Guide with CDROM
- Oracle9i RAC: Oracle Real Application Clusters Configuration and Internals (Oracle In-Focus series)
Extra resources for Oracle SQL tuning: pocket reference
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.