advertisements
_____________________________________________________________________________________________________________________
One of the most frequent uses of hints is to force a particular access path to be selected, and typically this means forcing the use of a particular index.
The simplest hint for forcing an index is the rather appropriately named INDEX hint. Typically, the INDEX hint is used to force the use of a particular index in this manner
I have created one example_table from the dictionary view all_objects and I have created 2 indexes on this table. (example_tbl_idx and example_tbl_idx2)
SQL> DESC EXAMPLE_TBL
Name Null? Type
----------------------------------------- -------- -------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
SQL> CREATE INDEX EXAMPLE_TBL_IDX ON EXAMPLE_TBL(OWNER);
Index created.
SQL> CREATE INDEX EXAMPLE_TBL_IDX2 ON EXAMPLE_TBL(OBJECT_NAME);
Index created.
Here are the different methods to use INDEX hint.
Method 1
SQL> l
1 SELECT /*+ INDEX(EX, EXAMPLE_TBL_IDX) */ OBJECT_NAME
2 FROM EXAMPLE_TBL EX
3* WHERE OWNER='SCOTT'
SQL> /
Execution Plan
----------------------------------------------------------
Plan hash value: 651641215
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1905 | 59055 | 65 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EXAMPLE_TBL | 1905 | 59055 | 65 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EXAMPLE_TBL_IDX | 1905 | | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Method 2
You can also instruct the optimizer to choose between a subset of indexes by specifying multiple index names
SQL> SELECT /*+ INDEX(EX, EXAMPLE_TBL_IDX, EXAMPLE_TBL_IDX2) */ OBJECT_NAME
FROM EXAMPLE_TBL EX
WHERE OWNER='SCOTT'
AND OBJECT_NAME='EMP';
Execution Plan
----------------------------------------------------------
Plan hash value: 3522333813
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 5 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| EXAMPLE_TBL | 1 | 31 | 5 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EXAMPLE_TBL_IDX2 | 2 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SCOTT')
2 - access("OBJECT_NAME"='EMP')
Method 3
You can simply specify that you want an index to be used but leave it up to the optimizer to choose the appropriate index.
SQL> SELECT /*+ INDEX(EX) */ OBJECT_NAME
FROM EXAMPLE_TBL EX
WHERE OWNER='SCOTT' 2 3 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 651641215
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1905 | 59055 | 65 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EXAMPLE_TBL | 1905 | 59055 | 65 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EXAMPLE_TBL_IDX | 1905 | | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
Method 4
You can also specify that you would like multiple indexes to be merged using the AND_EQUAL hint (although most of the time, merging indexes suggests you are missing an appropriate concatenated index)
SQL> SELECT /*+ AND_EQUAL(EX, EXAMPLE_TBL_IDX, EXAMPLE_TBL_IDX2) */ OBJECT_NAME
FROM EXAMPLE_TBL EX
WHERE OWNER='SCOTT'
AND OBJECT_NAME='EMP' 2 3 4 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1444633027
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 8 (0)| 00:00:01 |
|* 1 | AND-EQUAL | | | | | |
|* 2 | INDEX RANGE SCAN| EXAMPLE_TBL_IDX2 | 2 | | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| EXAMPLE_TBL_IDX | 1905 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME"='EMP' AND "OWNER"='SCOTT')
2 - access("OBJECT_NAME"='EMP')
3 - access("OWNER"='SCOTT')
Method 4
By default Orcale scans indexes in ascending order. You can, however, specify that you wish the index to be scanned in descending order.
SQL> SELECT /*+ INDEX_desc(EX,EXAMPLE_TBL_IDX) */ OBJECT_NAME
FROM EXAMPLE_TBL EX
WHERE OWNER='SCOTT' 2 3 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 1564922202
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1905 | 59055 | 65 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID | EXAMPLE_TBL | 1905 | 59055 | 65 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN DESCENDING| EXAMPLE_TBL_IDX | 1905 | | 5 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SCOTT')
filter("OWNER"='SCOTT')
If you don’t want to use an index, you can use the FULL hint. You might want to do this if
· You are using rule-based optimization and the optimizer is selecting a very unselective index.
· You are using cost-based optimization and the optimizer is using an index that appears to be selective(e.g. has a large number of distinct values) but you happen to know that the particular alue being searched is not selective(for instance, you have a query that is using an index to get all people under 100 years of age)
Usage of FULL Hint:
SQL> SELECT /*+ FULL(EX) */ OBJECT_NAME
FROM EXAMPLE_TBL EX
WHERE OWNER='SCOTT' 2 3 ;
Execution Plan
----------------------------------------------------------
Plan hash value: 734479979
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1905 | 59055 | 325 (4)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| EXAMPLE_TBL | 1905 | 59055 | 325 (4)| 00:00:04 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OWNER"='SCOTT')
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment