Thursday, 2 April 2020

PT - OPTIMIZER Hints using Table and Index Access

Table and Index Access Path Hints

  • Table and Index Access Path
  1. Full
  2. Index
  3. Index_FFS
  4. Index_SS
  5. No_Index

SET LINESIZE 180
SET AUTOTRACE ON ( PLUSTRACE ALREADY ENABLED)

  • FULL Tables Scan Hints


SQL> SELECT /*+ FULL(D) */ DEPT_NO, DNAME FROM DEPT D WHERE DEPT_NO=10;

    DEPT_NO DNAME
---------- ----------------------
        10 Administration
Execution Plan
----------------------------------------------------------
Plan hash value: 3383998547

 --------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    13 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DEPT |     1 |    13 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

 Predicate Information (identified by operation id):
---------------------------------------------------

    1 - filter("DEPT_NO"=10)
Statistics
----------------------------------------------------------
         80  recursive calls
          2  db block gets
         81  consistent gets
          0  physical reads
          0  redo size
        623  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

 SQL> SELECT /*+ FULL(DEPT) */ DEPT_NO, DNAME FROM DEPT D WHERE DEPT_NO=10;

    DEPT_NO DNAME
---------- ----------------------
        10 Administration
Execution Plan
----------------------------------------------------------
Plan hash value: 602043285

 ---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |    13 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    13 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

 Predicate Information (identified by operation id):
---------------------------------------------------

    2 - access("DEPT_NO"=10)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        483  bytes sent via SQL*Net to client
        597  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

 SQL>

SQL>
NOTE : Do not use Alias dept d instead give table name


SQL> SELECT /*+ FULL(E) */ EMP_NO, ENAME, SALARY  FROM EMP E WHERE EMP_NO=635;

    EMP_NO ENAME                    SALARY
---------- -------------------- ----------
       635 FNTAMOSA GKSDYNDFH        60200


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    24 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    24 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMP_NO"=635)


Statistics
----------------------------------------------------------
        200  recursive calls
          9  db block gets
        393  consistent gets
          0  physical reads
       1076  redo size
        699  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         36  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT /*+ FULL(EMP) */ EMP_NO, ENAME, SALARY  FROM EMP E WHERE EMP_NO=635;

    EMP_NO ENAME                    SALARY
---------- -------------------- ----------
       635 FNTAMOSA GKSDYNDFH        60200


Execution Plan
----------------------------------------------------------
Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    24 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP_NO"=635)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          3  physical reads
          0  redo size
        699  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

  • Index Hints 


SQL> SELECT /*+ INDEX(E INDEX_PK) */ EMP_NO, ENAME, SALARY  FROM EMP E WHERE EMP_NO=635;

    EMP_NO ENAME                    SALARY
---------- -------------------- ----------
       635 FNTAMOSA GKSDYNDFH        60200


Execution Plan
----------------------------------------------------------
Plan hash value: 4024650034

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    24 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    24 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP_NO"=635)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        699  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

( This is the example using multiple Index)


SQL> SELECT /*+ INDEX (E EMP_PK EMP_DEPTNO_IX ) */ EMP_NO, ENAME, SALARY  FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

    EMP_NO ENAME                    SALARY
---------- -------------------- ----------
       600 AHYVFGNLRS AOVXOCDJH      56700
       601 GBUKTVDY TVTQ             78300
       602 RQHW SECLVR               80900
       603 BFBCIPA LNKORQ            72200
       604 GTKGSMJURW VQIFED         55800
       605 QBGOCE NQJDIEFXD          81000
       606 XXNEFJPPQ SAHGX           61800
       607 KTIWMDWCIZ GBBB           61300
       608 AQWFHXZ WGQRDQQM          62800
       609 UAWDGPRRSO PHXT           72500
       610 JNECDUASZ GPBWBE          78700

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2288396553

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     8 |   216 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |     8 |   216 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_PK |    10 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPT_NO"=60)
   2 - access("EMP_NO">=600 AND "EMP_NO"<=610)


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
       1034  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL>

  • Index_FFS ( Fast Full Scan hints)


SQL> CREATE INDEX EMP_ENAME_JC_IX ON EMP(ENAME, JOB_CODE) COMPUTE STATISTICS;

Index created.

SQL> SELECT /*+ INDEX_FFS(E EMP_ENAME_JC_IX) */ ENAME, JOB_CODE FROM EMP E WHERE ENAME LIKE 'Z%';

ENAME                JOB_
-------------------- ----
ZAPSU SSRLIG         SL02
ZATDCTVSSA TUWQY     SL02
ZAYEY MQKDN          SL02
ZBOTTAPH CESYWCQB    SL02
ZDCYJZRIE GSGDKQF    SL02
ZDFLKXK SGIPR        MG03
ZDMIWBZZN ABZMWMOCR  SL02
ZDUT UEQTZ           SL02
ZDVDBEMSA MOAZKC     SL02
ZDZYA ROKMN          SL02
ZELDVW JXIMCUJ       SL02

ENAME                JOB_
-------------------- ----
ZERDPOLO RTVDO       SL02
ZGBYOSAW GZYQB       HR02
ZGDEYEFD NXMAQDBD    SL02
ZGGKHUS URMPHH       SL02
ZHPH XWSWJH          SL02
ZHWIIXH QJQKKYS      IT05
ZILVN QQBCCDMN       MG02
ZIUBBOMPE MZUQWAO    AD06
ZIUOQP PSUJZ         SL02
ZIVXT CPIR           SL02
ZJSGY ZJELUQ         SL02

ENAME                JOB_
-------------------- ----
ZKCH OACWGHOGY       ST02
ZKTTV KFXDFAL        SL02
ZKXXQADJL BASDCVQT   SL02
ZLJAVNC GHGUUO       SL02
ZLXCPCPLW WNRPZT     SL02
ZNII KDGQWLXB        SL02
ZOAKRCS MYOHKZP      SL02
ZOLIEFVJ JOHEUHXMK   SL02
ZQCES OGEKAQZ        SL02
ZQGNJLDAK ALKPGIG    SL02
ZRNQRSDNVH BSFTIWHPQ SL02

ENAME                JOB_
-------------------- ----
ZRPLO WIIJCEE        SL02
ZTHXNXRPM BSBB       FN03
ZUZQJO PSRIWT        SL02
ZVMTJUWTNO KQLSJRMN  SL02
ZWHBK WQNWNTI        SL02
ZWKTN WVPAQBW        AD06
ZWXRXB VSVVEJED      SL02
ZXATQK DYDNVFKT      SL02
ZXHTHFIAR PJNVYW     SL02
ZYCWR UAQGDWND       SL02
ZYIGXJV BZLJO        SL02

ENAME                JOB_
-------------------- ----
ZZROO ILREINIMQ      SL02

45 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1191882812

----------------------------------------------------------------------------------------
| Id  | Operation            | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |                 |    42 |   882 |     3   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| EMP_ENAME_JC_IX |    42 |   882 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ENAME" LIKE 'Z%')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          4  physical reads
          0  redo size
       2064  bytes sent via SQL*Net to client
        630  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         45  rows processed

SQL>

SQL> SELECT /*+ INDEX_FFS(E EMP_ENAME_JC_IX) */ ENAME, JOB_CODE FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610;

ENAME                JOB_
-------------------- ----
AHYVFGNLRS AOVXOCDJH SL02
GBUKTVDY TVTQ        SL02
RQHW SECLVR          SL02
BFBCIPA LNKORQ       SL02
GTKGSMJURW VQIFED    SL02
QBGOCE NQJDIEFXD     SL02
XXNEFJPPQ SAHGX      SL02
KTIWMDWCIZ GBBB      SL02
AQWFHXZ WGQRDQQM     SL02
UAWDGPRRSO PHXT      SL02
JNECDUASZ GPBWBE     SL02

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2288396553

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |    10 |   250 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |    10 |   250 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_PK |    10 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMP_NO">=600 AND "EMP_NO"<=610)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        936  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

  • Index_SS (Skip Scan hints)


SQL> SELECT /* INDEX_SS ( C CUST_CLASS_EMAIL_IX ) */ * FROM CUSTOMERS C WHERE CUST_EMAIL = 'lyndia.bangle@oracle.com';

CUSTOMER_ID CUST_FIRST_NAME                          CUST_LAST_NAME                           NLS NLS_TERRITORY                  CREDIT_LIMIT
----------- ---------------------------------------- ---------------------------------------- --- ------------------------------ ------------
CUST_EMAIL                                                                                           ACCOUNT_MGR_ID CUSTOMER_
---------------------------------------------------------------------------------------------------- -------------- ---------
CUSTOMER_CLASS                           SUGGESTIONS                              DOB       M P PREFERRED_ADDRESS PREFERRED_CARD
---------------------------------------- ---------------------------------------- --------- - - ----------------- --------------
      45550 lyndia                                   bangle                                   th  THAILAND                               1392
lyndia.bangle@oracle.com                                                                                154 02-OCT-18
Occasional                               Music                                    06-NOV-71 Y N          55550     55550



Execution Plan
----------------------------------------------------------
Plan hash value: 3336747554

-----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |               |     1 |   114 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| CUSTOMERS     |     1 |   114 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | CUST_EMAIL_IX |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CUST_EMAIL"='lyndia.bangle@oracle.com')


Statistics
----------------------------------------------------------
         61  recursive calls
         52  db block gets
        124  consistent gets
          1  physical reads
       9804  redo size
       1770  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
         14  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

  • No_Index Hints


SQL> SELECT /*+ NO_INDEX (E EMP_PK ) */ EMP_NO, ENAME, SALARY  FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

    EMP_NO ENAME                    SALARY
---------- -------------------- ----------
       600 AHYVFGNLRS AOVXOCDJH      56700
       601 GBUKTVDY TVTQ             78300
       602 RQHW SECLVR               80900
       603 BFBCIPA LNKORQ            72200
       604 GTKGSMJURW VQIFED         55800
       605 QBGOCE NQJDIEFXD          81000
       606 XXNEFJPPQ SAHGX           61800
       607 KTIWMDWCIZ GBBB           61300
       608 AQWFHXZ WGQRDQQM          62800
       609 UAWDGPRRSO PHXT           72500
       610 JNECDUASZ GPBWBE          78700

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     8 |   216 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |     8 |   216 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMP_NO">=600 AND "EMP_NO"<=610 AND "DEPT_NO"=60)


Statistics
----------------------------------------------------------
          1  recursive calls
          2  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
       1034  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

SQL> SELECT EMP_NO, ENAME, SALARY  FROM EMP E WHERE EMP_NO BETWEEN 600 AND 610 AND DEPT_NO=60;

    EMP_NO ENAME                    SALARY
---------- -------------------- ----------
       600 AHYVFGNLRS AOVXOCDJH      56700
       601 GBUKTVDY TVTQ             78300
       602 RQHW SECLVR               80900
       603 BFBCIPA LNKORQ            72200
       604 GTKGSMJURW VQIFED         55800
       605 QBGOCE NQJDIEFXD          81000
       606 XXNEFJPPQ SAHGX           61800
       607 KTIWMDWCIZ GBBB           61300
       608 AQWFHXZ WGQRDQQM          62800
       609 UAWDGPRRSO PHXT           72500
       610 JNECDUASZ GPBWBE          78700

11 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2288396553

----------------------------------------------------------------------------------------------
| Id  | Operation                           | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |        |     8 |   216 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP    |     8 |   216 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_PK |    10 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPT_NO"=60)
   2 - access("EMP_NO">=600 AND "EMP_NO"<=610)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
       1034  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed


Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)