1 2 3 Previous Next 35 Replies Latest reply: Jun 24, 2012 9:46 AM by Paulie RSS

    Index Usage

    788793
      Hi All,

      Using Oracle 11.2.0.3 on a TEST server
      create index emp_job_sal_idx on emp(job,sal) ;
      
      SQL> create or replace view test1 as select d.deptno, d.dname, e.empno, e.job, e.sal from dept d, emp e where d.deptno=e.deptno and
      (e.job='CLERK' and e.sal > 1000) OR
      (e.job='ANALYST' and e.sal >= 3000) OR
      (e.job='SALESMAN' and e.sal >=1500);
      
      SQL> set autotrace on explain
      SQL> select * from test1;
      
      18 rows selected.
      
      
      Execution Plan
      ----------------------------------------------------------
      Plan hash value: 4192419542
      
      ---------------------------------------------------------------------------
      | Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time       |
      ---------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |       |     1 |    67 |    10   (0)| 00:00:01 |
      |   1 |  NESTED LOOPS        |       |     1 |    67 |    10   (0)| 00:00:01 |
      |   2 |   TABLE ACCESS FULL| DEPT |     4 |    88 |     3   (0)| 00:00:01 |
      |*  3 |   TABLE ACCESS FULL| EMP  |     1 |    45 |     2   (0)| 00:00:01 |
      ---------------------------------------------------------------------------
      The index is not used, mostly due to a OR condition. How can we make the index use without using hints ?
        • 1. Re: Index Usage
          Oviwan
          Hi

          you can fix the plan using sql plan management:

          check this http://url4u.co/15FTGn

          hth
          • 2. Re: Index Usage
            Fran
            Just use a "WHERE" condition on the query for example.
            • 3. Re: Index Usage
              Hemant K Chitale
              Given that the tables are very small, Oracle figures that a FullTableScan is the fastest way to join the two tables. Fetching 4 rows from DEPT shouldn't require very many blocks to be read. Then, for each each, a FullTableScan of EMP also requires very few block reads. Look at the estimated COST for each operation -- it is very low.

              To test indexes, work with much larger tables.

              Hemant K Chitale

              Edited by: Hemant K Chitale on Jun 19, 2012 5:19 PM
              • 4. Re: Index Usage
                944213
                Even if after providing the hints oracle may not be able to use the index. As suggested by hemant, tables are so small so there is no use of index.

                Also you can try with sql profile and see if statement can use the plan somehow. but I don't think that is a right option.


                Cheers,
                lkm
                • 5. Re: Index Usage
                  788793
                  Hi Hemant,

                  This is a simulated query for a very large query with tables over 20 million ..but the OR construct is same

                  Table x(20 million) joined with table (y) 25 million.. Output 7000 records.

                  There are multiple OR constructs and getting a FTS..

                  Does OR bypass a index..How to approach the issue?

                  Edited by: user13355115 on Jun 19, 2012 5:42 PM
                  • 6. Re: Index Usage
                    sybrand_b
                    WHICH issue?
                    You seem to subscribe to the MYTH using an index is always better.
                    It isn't!!
                    You didn't show any information about the real query, so you are just hunting for : How do I turn Oracle 11 into Oracle 5.
                    That is not going to work ever.
                    Your question is way too generic, and consequently doesn't make sense, as you suffer from Compulsive Index Use Disorder.

                    -----------
                    Sybrand Bakker
                    Senior Oracle DBA
                    • 7. Re: Index Usage
                      Nikolay Savvinov
                      Hi,

                      If you are you are using OR in WHERE clause then possibility to use an index depends on optimizer's ability to transform OR into UNION ALL. You can make things easier for the optimizer by doing this transform yourself.

                      Best regards,
                      Nikolay
                      • 8. Re: Index Usage
                        788793
                        sybrand_b wrote:
                        WHICH issue?
                        You seem to subscribe to the MYTH using an index is always better.
                        It isn't!!
                        I know index is not always better. Depends on the CBO to choose the best plan.
                        But if my joined set = 27 million + and resultset is just 6000 records, Indexing should be the proper go. Its less than 0.5% of the records.
                        Also it depends on the data distribution.

                        As a matter of fact the same query with same indexes is running in < 1min in sqlserver, In Oracle 11.2.0.1 its taking 8 mins.
                        In sqlserver its using the indexes.
                        You didn't show any information about the real query, so you are just hunting for : How do I turn Oracle 11 into Oracle 5.
                        That is not going to work ever.
                        Here we go;
                        Indexed on (NAME, PP_NO) 
                        SELECT  OL_BASE.ENT_TRAN.EID, OL_BASE.ENT_MAST.RID, OL_BASE.ENT_MAST.ACC_BAL, OL_BASE.ENT_MAST.ACC_NO, 
                        OL_BASE.ENT_MAST.ACC_OPEN_DATE, OL_BASE.ENT_MAST.ACC_OPEN_STATE_ID, OL_BASE.ENT_MAST.ACC_STATUS_ID, OL_BASE.ENT_MAST.ACC_TYPE_ID, 
                        OL_BASE.ENT_MAST.CDS_NO, OL_BASE.ENT_MAST.CHECKER_ID, OL_BASE.ENT_MAST.CLAIM_NO, OL_BASE.ENT_MAST.CLASSIFICATION, 
                        OL_BASE.ENT_MAST.CTR_CODE, OL_BASE.ENT_MAST.CTR_PARTY
                          FROM OL_BASE.ENT_MAST,OL_BASE.ENT_TRAN
                          WHERE  (( OL_BASE.ENT_TRAN.NAME = 'XXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXXXXXXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXXXXXXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXXXX') OR
                          ( OL_BASE.ENT_TRAN.NAME = 'XXXXXXXXX' AND  OL_BASE.ENT_TRAN.PP_NO = 'XXXXXX') OR
                           AND  ENT_MAST.ID = ENT_TRAN.RID;
                        Your question is way too generic, and consequently doesn't make sense, as you suffer from Compulsive Index Use Disorder.

                        -----------
                        Sybrand Bakker
                        Senior Oracle DBA
                        • 9. Re: Index Usage
                          sb92075
                          Thread: HOW TO: Post a SQL statement tuning request - template posting
                          HOW TO: Post a SQL statement tuning request - template posting


                          post EXPLAIN PLAN for problem SQL
                          • 10. Re: Index Usage
                            788793
                            Hemant K Chitale wrote:
                            Given that the tables are very small, Oracle figures that a FullTableScan is the fastest way to join the two tables. Fetching 4 rows from DEPT shouldn't require very many blocks to be read. Then, for each each, a FullTableScan of EMP also requires very few block reads. Look at the estimated COST for each operation -- it is very low.

                            To test indexes, work with much larger tables.
                            Ok if I change my sql on the same small tables;
                            SQL> create or replace view TEST2 as    
                              2  select d.deptno, d.dname, e.empno, e.job, e.sal from dept d, emp e
                              3  where d.deptno=e.deptno and
                              4  (e.job='CLERK' and e.sal > 1000) and 
                              5  (e.job='CLERK' and e.hiredate = '23-JAN-82');
                            
                            View created.
                            
                            SQL> set autotrace on explain
                            SQL> select * from test2;
                            
                                DEPTNO DNAME            EMPNO JOB           SAL
                            ---------- -------------- ---------- --------- ----------
                                 10 ACCOUNTING          7934 CLERK          1300
                            
                            
                            Execution Plan
                            ----------------------------------------------------------
                            Plan hash value: 637676659
                            
                            -------------------------------------------------------------------------------------------------
                            | Id  | Operation                | Name          | Rows     | Bytes | Cost (%CPU)| Time     |
                            -------------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT           |           |     1 |    49 |     3   (0)| 00:00:01 |
                            |   1 |  NESTED LOOPS                |           |     |     |          |          |
                            |   2 |   NESTED LOOPS                |           |     1 |    49 |     3   (0)| 00:00:01 |
                            |*  3 |    TABLE ACCESS BY INDEX ROWID| EMP          |     1 |    27 |     2   (0)| 00:00:01 |
                            |*  4 |     INDEX RANGE SCAN           | EMP_JOB_SAL_IDX |     3 |     |     1   (0)| 00:00:01 |
                            |*  5 |    INDEX UNIQUE SCAN           | PK_DEPT      |     1 |     |     0   (0)| 00:00:01 |
                            |   6 |   TABLE ACCESS BY INDEX ROWID | DEPT          |     1 |    22 |     1   (0)| 00:00:01 |
                            -------------------------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                            
                               3 - filter("E"."HIREDATE"='23-JAN-82')
                               4 - access("E"."JOB"='CLERK' AND "E"."SAL">1000 AND "E"."SAL" IS NOT NULL)
                               5 - access("D"."DEPTNO"="E"."DEPTNO")
                            
                            Note
                            -----
                               - dynamic sampling used for this statement (level=2)
                            • 11. Re: Index Usage
                              788793
                              sb92075 wrote:
                              post EXPLAIN PLAN for problem SQL
                              Execution Plan
                              ----------------------------------------------------------
                              Plan hash value: 3466901445
                              
                              ----------------------------------------------------------------------------------
                              | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
                              ----------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT   |             |     1 |   331 |   383K  (7)| 01:16:40 |
                              |*  1 |  HASH JOIN         |             |     1 |   331 |   383K  (7)| 01:16:40 |
                              |*  2 |   TABLE ACCESS FULL| ENT_TRAN|     1 |   165 |   205K  (9)| 00:41:07 |
                              |   3 |   TABLE ACCESS FULL| ENT_MAST         |    19M|  3111M|   177K  (3)| 00:35:31 |
                              ----------------------------------------------------------------------------------
                              
                               1 - access(NLSSORT("ENT_MAST"."ID",'nls_sort=''BINARY_CI''')=NLSSORT(
                                            "ENT_TRAN"."RID",'nls_sort=''BINARY_CI'''))
                                 2 - filter(NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_CI'''
                                            )=HEXTORAW('616D616E616820736168616D206E6173696F6E616C2062657268616400')
                                            AND NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('343
                                            73435377600')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_C
                                            I''')=HEXTORAW('74656E616761206E6173696F6E616C2062657268616400')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3230303
                                            836367700')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_CI'
                                            '')=HEXTORAW('74656C656B6F6D206D616C61797369612062657268616400')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3132383
                                            734307000')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_CI'
                                            '')=HEXTORAW('6167726F2062616E6B00')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3831313
                                            831307500')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_CI'
                                            '')=HEXTORAW('6272756365206C656520796565206C616D00')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3530333
                                            333373100')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_CI'
                                            '')=HEXTORAW('616B6261722074726164696E6700')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3030313
                                            438383037397500')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINA
                                            RY_CI''')=HEXTORAW('372D656C6576656E206D616C61797369612073646E2062686400')
                                              AND NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3
                                            132303936327000')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINA
                                            RY_CI''')=HEXTORAW('6368616E20796F6B65206368656E6700')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3637303
                                            532372D30362D3534393600')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sor
                                            t=''BINARY_CI''')=HEXTORAW('646B7368206D616C61797369612073646E2062686400')
                                              AND NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3
                                            030343437367500')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINA
                                            RY_CI''')=HEXTORAW('626574746572207B267D206265737420656E746572707269736500
                                            ')  AND NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW(
                                            '3030313632353132347000')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sor
                                            t=''BINARY_CI''')=HEXTORAW('6C696D20656E672073696F65206A756E6B657400')
                                            AND NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('743
                                            5353138393300')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY
                                            _CI''')=HEXTORAW('712E712E206A756E6B657400')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('7331373
                                            233363200')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_CI'
                                            '')=HEXTORAW('6572206B696D206B656F6E6700')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3539303
                                            932302D31302D3538333700')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sor
                                            t=''BINARY_CI''')=HEXTORAW('726963686C616E64206C6569737572652067726F757000
                                            ')  AND NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW(
                                            '3538303532382D30312D3539383500')  OR
                                            NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('74
                                            69656E2079756E20656E74657270726973652073646E2E206268642E00')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3637303
                                            332382D31302D3534363700')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sor
                                            t=''BINARY_CI''')=HEXTORAW('73686172696B617420686F636B2068696E00')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3030303
                                            234323136397000')  OR NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINA
                                            RY_CI''')=HEXTORAW('6D7964696E206D6F68616D656420686F6C64696E67732062686420
                                            28666F726D65726C79206B6E6F776E206173206D7964696E206D6F68616D656420686F6C64
                                            696E67732073646E206268642900')  AND NLSSORT("ENT_TRAN"."PP_NO",'nls_so
                                            rt=''BINARY_CI''')=HEXTORAW('3232313434386100')  OR
                                            NLSSORT("ENT_TRAN"."NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('65
                                            7665726973652076656E74757265732073646E2062686400')  AND
                                            NLSSORT("ENT_TRAN"."PP_NO",'nls_sort=''BINARY_CI''')=HEXTORAW('3239353
                                            137377000')  OR NLSSORT("EN)
                              
                              Statistics
                              ----------------------------------------------------------
                                        1  recursive calls
                                        0  db block gets
                                  1353887  consistent gets
                                  1353339  physical reads
                                        0  redo size
                                  1204784  bytes sent via SQL*Net to client
                                     4989  bytes received via SQL*Net from client
                                      408  SQL*Net roundtrips to/from client
                                        0  sorts (memory)
                                        0  sorts (disk)
                                     6092  rows processed
                              {code}                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                              • 12. Re: Index Usage
                                sb92075
                                post EXPLAIN PLAN for problem SQL
                                Execution Plan
                                ----------------------------------------------------------
                                Plan hash value: 3466901445
                                
                                ----------------------------------------------------------------------------------
                                | Id  | Operation          | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
                                ----------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT   |             |     1 |   331 |   383K  (7)| 01:16:40 |
                                |*  1 |  HASH JOIN         |             |     1 |   331 |   383K  (7)| 01:16:40 |
                                |*  2 |   TABLE ACCESS FULL| ENT_TRAN|     1 |   165 |   205K  (9)| 00:41:07 |
                                |   3 |   TABLE ACCESS FULL| ENT_MAST         |    19M|  3111M|   177K  (3)| 00:35:31 |
                                ----------------------------------------------------------------------------------
                                CBO thinks tables only have *ONE* row each so FTS is reasonable choice
                                
                                Edited by: sb92075 on Jun 19, 2012 8:34 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                                • 13. Re: Index Usage
                                  788793
                                  Hi SB,

                                  Good Clue. But why the CBO thinks like that..tables have over 27 million records.

                                  Do I need to collect DBMS_STATS (SCHEMA, TABLE, SYSTEM, FIXED_OBJECTS) ? to make the CBO understand better.
                                  • 14. Re: Index Usage
                                    sb92075
                                    user13355115 wrote:
                                    Hi SB,

                                    Good Clue. But why the CBO thinks like that..tables have over 27 million records.

                                    Do I need to collect DBMS_STATS (SCHEMA, TABLE, SYSTEM, FIXED_OBJECTS) ? to make the CBO understand better.
                                    select table_name, num_rows from all_tables where table_name in ('ENT_TRAN', 'ENT_MAST');
                                    1 2 3 Previous Next