How To Force A Query To Used Index Hint With Stored Outline [ID 604022.1]

How To Force A Query To Used Index Hint With Stored Outline [ID 604022.1]

How To Force A Query To Used Index Hint With Stored Outline [ID 604022.1]

​​ 修改时间 16-JUN-2009 类型 HOWTO 状态 MODERATED In this Document Goal Solution References ________________________________________ This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process, and therefore has not been subject to an independent technical review. Applies to: Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4 Information in this document applies to any platform. Goal It might happen that, for a SQL statement due to some reason optimizer is opting for full table scan rather than index scan. Hints can be used to guide optimizer optimizer, but in some cases it is not possible to edit application SQL to add index hint. In this case stored outline can be used to select a desired plan. But text of the SQL should be same while using stored outline which will not be case after adding index hint. This document will help to create stored outline for a query after index hint. One query is without index hint & one is with index hint, so text of two SQL's are not identical. Solution 1. Create a stored outline for SQL without index hint (ORIGINALSQL) 2. Create temporary stored outline with index hint (HINTSQL) 3. Swap the hints of both the SQL's by updating OUTLN.OL$HINTS (SYSTEM.OL$HINTS in 9i) base table 4. Drop the temporary outline HINTSQL. Below is a simple testcase to achieve this. -- Create SCOTT user & assign necessary privileges. CONN /AS SYSDBA DROP USER SCOTT CASCADE; .@D:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/scott.sql CONN /AS SYSDBA GRANT CREATE ANY OUTLINE TO SCOTT; GRANT DROP ANY OUTLINE TO SCOTT; CONN SCOTT/TIGER CREATE TABLE OTLN_TEST AS SELECT * FROM EMP; INSERT INTO OTLN_TEST SELECT * FROM OTLN_TEST; INSERT INTO OTLN_TEST SELECT * FROM OTLN_TEST; INSERT INTO OTLN_TEST SELECT * FROM OTLN_TEST; INSERT INTO OTLN_TEST SELECT * FROM OTLN_TEST; COMMIT; CREATE INDEX IDX_TEST ON OTLN_TEST(EMPNO); EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'',TABNAME=>'OTLN_TEST', CASCADE=>TRUE); -- Check the plan EXPLAIN PLAN FOR SELECT EMPNO,ENAME FROM OTLN_TEST WHERE EMPNO > 7000; .@D:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/utlxpls.sql -- Create a public outline CREATE OR REPLACE OUTLINE ORIGINALSQL ON SELECT EMPNO,ENAME FROM OTLN_TEST WHERE EMPNO > 7000; -- Force index scan & check the plan using hint. EXPLAIN PLAN FOR SELECT /*+ INDEX(OTLN_TEST, IDX_TEST) */ EMPNO,ENAME FROM OTLN_TEST WHERE EMPNO > 7000; .@D:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/utlxpls.sql -- Create a public outline create or replace outline HINTSQL on SELECT /*+ INDEX(OTLN_TEST, IDX_TEST) */ EMPNO,ENAME FROM OTLN_TEST WHERE EMPNO > 7000; CONN /AS SYSDBA UPDATE OUTLN.OL$HINTS SET OL_NAME=DECODE(OL_NAME,'HINTSQL','ORIGINALSQL','ORIGINALSQL','HINTSQL') WHERE OL_NAME IN ('ORIGINALSQL','HINTSQL'); COMMIT; -- Flush the shared pool to force hard parsing for the original SQL ALTER SYSTEM FLUSH SHARED_POOL; -- Under SCOTT user -- Drop the temporary outline HINTSQL CONN SCOTT/TIGER DROP OUTLINE HINTSQL; ALTER SESSION SET USE_STORED_OUTLINES = TRUE; -- Check the plan for SQL without hint. EXPLAIN PLAN FOR SELECT EMPNO,ENAME FROM OTLN_TEST WHERE EMPNO > 7000; .@D:/oracle/product/10.2.0/db_1/RDBMS/ADMIN/utlxpls.sql References NOTE:144194.1 - Editing Stored Outlines in Oracle9i - an example NOTE:388433.1 - Outline Using Specific Index Does Not Work NOTE:454253.1 - Private Outline Not Stored in User Ol$ Or Ol$Hints Tables. ________________________________________ 相关的 ________________________________________ 产品 ________________________________________ • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition 关键字 ________________________________________ FULL TABLE SCAN; STORED OUTLINES; USE_STORED_OUTLINES; USING INDEX

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。