How To Force A Query To Used Index Hint With Stored Outline [ID 604022.1] -优快云博客

本文介绍了一种方法，即通过创建存储的轮廓来确保查询使用指定的索引提示，即使无法直接修改应用程序的SQL语句也能实现。此过程分为四步：创建原始SQL的存储轮廓、创建带索引提示的临时存储轮廓、交换两个SQL语句的提示并删除临时轮廓。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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

http://inthirties.com:90/thread-1421-1-1.html

________________________________________
修改时间 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