By Mike Ault
Read Online or Download Using Oracle Sql Stored Outlines & Optimizer Plan Stability (Oracle In-Focus Series) PDF
Similar databases books
This e-book brings the entire parts of database layout jointly in one quantity, saving the reader the time and rate of creating a number of purchases. It consolidates either introductory and complex issues, thereby overlaying the gamut of database layout method ? from ER and UML suggestions, to conceptual info modeling and desk transformation, to storing XML and querying relocating items databases.
Oracle Call Interface. Programmer's Guide
The Oracle name Interface (OCI) is an program programming interface (API) that enables functions written in С or C++ to engage with a number of Oracle database servers. OCI supplies your courses the potential to accomplish the total diversity of database operations which are attainable with an Oracle database server, together with SQL assertion processing and item manipulation.
Oracle Warehouse Builder 11g: Getting Started
This easy-to-understand instructional covers Oracle Warehouse Builder from the floor up, and faucets into the author's extensive adventure as a software program and database engineer. Written in a peaceful kind with step by step reasons, plenty of screenshots are supplied during the publication. there are lots of suggestions and worthy tricks all through that aren't present in the unique documentation.
Extra resources for Using Oracle Sql Stored Outlines & Optimizer Plan Stability (Oracle In-Focus Series)
Example text
Let's call the same SQL statement with hints 'HINTSQL' 2. Create the OUTLINE for ORIGINALSQL 3. Create the OUTLINE for HINTSQL 4. Exchange the OUTLINE plan between the two OUTLINES 5. Drop the OUTLINE for HINTSQL 6. Now the OUTLINE plan for ORIGINALSQL is the same as the execution plan of HINTSQL which uses HINTs. Only the point 5 (Exchange the OUTLINE plan between the two OUTLINEs) is detailed below. The rest of the steps are covered in the bulk of the paper. OL$HINTS SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL') WHERE OL_NAME IN ('HINTSQL','ORIGINALSQL'); Commit; Example Let’s look at an example using the SCOTT schema using the example schema objects.
All rights reserved. 0 - Production PAGE 27 COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED. 06 via conventional path import done in US7ASCII character set and US7ASCII NCHAR character set import server uses WE8ISO8859P9 character set (possible charset conversion) . importing OUTLN's objects into OUTLN . importing table "OL$" 2 rows imported . importing table "OL$HINTS" 17 rows imported Import terminated successfully without warnings. Of course you could have also imported the tables into a different user and then used an INSERT using a SELECT to add the outlines if you wanted to retain the outlines already in production.
OL$HINTS SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL') WHERE OL_NAME IN ('ORIGINALSQL','HINTSQL'); PAGE 23 COPYRIGHT © 2003 RAMPANT TECHPRESS. ALL RIGHTS RESERVED. ROBO BOOKS MONOGRAPH STABILIZING EXECUTION PLANS IN ORACLE commmit; Please note that the outline names used (ORIGINALSQL and HINTSQL) must be unique. Now under the SCOTT user, drop the temporary OUTLINE HINTSQL. DNAME; The plan for this SQL is now the same access plan as the SQL with HINTS. 0 1 2 3 4 5 0 1 2 3 2 SELECT STATEMENT Optimizer=CHOOSE SORT (GROUP BY) NESTED LOOPS (OUTER) TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' INDEX (RANGE SCAN) OF 'I_DEPT$LOC' (NON-UNIQUE) TABLE ACCESS (FULL) OF 'EMP' Moving OUTLINES from One DB to Another A user may want to copy OUTLINEs from one database to another, for example, to copy the outlines of an application from a test database to a production database.