1 2 Previous Next 19 Replies Latest reply: May 13, 2010 12:22 AM by Hemant K Chitale RSS

    Index not being used even with hint

    582987
      For this query, I'm getting a full table scan even when specifying a hint:

      SELECT /*+ index_ffs (d ix_dunsno_dnb_store_data ) */7 as s_no, 'Number of DNB Records' as name, count(distinct( dnb_duns_number)) as value
      FROM dnb_store_data d

      However, this query uses an index fast full scan, even without the hint:

      select count(distinct(dnb_duns_number)) as value from dnb_store_data

      Anyone have any ideas??? I'm on oracle 10.2.0.4
        • 1. Re: Index not being used even with hint
          657203
          Pls, post explain plan for both of these queries.

          Regards,
          Teymur Hajiyev
          Oracle 10g Certified Master
          http://teymur-hajiyev.blogspot.com
          http://dba.az
          • 2. Re: Index not being used even with hint
            Pavan Kumar
            Hi,

            As you stated it's going for FTS.

            If you check the query correct its a count(*) across the table, where the Optimizer had to fetch upto the HWM.
            I say that FTS is better instead of index, as Optimizer is going ahead - inturn that depends on statistics of the table and distinctness of the index columns. Is it possible to post the index column stats and table stats and explain plan.
            SELECT /*+ index_ffs (d ix_dunsno_dnb_store_data ) */7 as s_no, 'Number of DNB Records' as name, count(distinct( dnb_duns_number)) as value
            FROM dnb_store_data d
            the below query you stated that it's using an index. can you check the index which oracle Optimizer is choosen or used is same the which you specified across in the above query index_ffs (d ix_dunsno_dnb_store_data ) . I doubt that it might not the same one. If yes, then the index which you hinted - needed more number of fetches (blocks at segment level) and cost would be more, so optimizer might choosen the FTS which would be better.
            select count(distinct(dnb_duns_number)) as value from dnb_store_data
            - Pavan Kumar N
            - ORACLE OCP - 9i/10g
            https://www.oracleinternals.blogspot.com
            • 3. Re: Index not being used even with hint
              Hemant K Chitale
              The presence of "7 as s_no, 'Number of DNB Records' as name" in your SELECT list is driving Oracle to read the table.


              This would be worthy of testing in 11.2 as well.


              Hemant K Chitale
              • 4. Re: Index not being used even with hint
                657203
                SQL> create table myt1 as select * from dba_objects;
                
                Table created.
                
                SQL> create index myt1$objname on myt1(object_name);
                
                Index created.
                
                SQL> explain plan for select /*+ FULL(myt1) */ count(distinct object_name) from myt1;
                
                Explained.
                
                SQL> SET LINESIZE 130
                SQL> SET PAGESIZE 0
                SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                Plan hash value: 1763129885
                
                ---------------------------------------------------------------------------
                | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                ---------------------------------------------------------------------------
                |   0 | SELECT STATEMENT   |      |     1 |    66 |    49   (0)| 00:00:01 |
                |   1 |  SORT GROUP BY     |      |     1 |    66 |            |          |
                |   2 |   TABLE ACCESS FULL| MYT1 | 11020 |   710K|    49   (0)| 00:00:01 |
                ---------------------------------------------------------------------------
                
                Note
                -----
                   - dynamic sampling used for this statement
                
                13 rows selected.
                
                SQL> explain plan for select /*+ INDEX_FFS( MYT1$OBJNAME myt1) */ count(distinct object_name) from myt1;
                
                Explained.
                
                SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                Plan hash value: 2781289525
                
                --------------------------------------------------------------------------------------
                | Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                --------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT      |              |     1 |    66 |    18   (0)| 00:00:01 |
                |   1 |  SORT GROUP BY        |              |     1 |    66 |            |          |
                |   2 |   INDEX FAST FULL SCAN| MYT1$OBJNAME | 11020 |   710K|    18   (0)| 00:00:01 |
                --------------------------------------------------------------------------------------
                
                Note
                -----
                   - dynamic sampling used for this statement
                
                13 rows selected.
                
                SQL> explain plan for select /*+ INDEX_FFS( MYT1$OBJNAME myt1) */ 7 as ttt, count(distinct object_name) from myt1;
                
                Explained.
                
                SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                Plan hash value: 1763129885
                
                ---------------------------------------------------------------------------
                | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                ---------------------------------------------------------------------------
                |   0 | SELECT STATEMENT   |      |     1 |    66 |    49   (0)| 00:00:01 |
                |   1 |  SORT GROUP BY     |      |     1 |    66 |            |          |
                |   2 |   TABLE ACCESS FULL| MYT1 | 11020 |   710K|    49   (0)| 00:00:01 |
                ---------------------------------------------------------------------------
                
                Note
                -----
                   - dynamic sampling used for this statement
                
                13 rows selected.
                
                SQL> explain plan for select 7 as ttt, ct from ( select count(distinct object_name) ct from myt1);
                
                Explained.
                
                SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                Plan hash value: 4000407046
                
                ---------------------------------------------------------------------------------------
                | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                ---------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT       |              |     1 |    13 |    18   (0)| 00:00:01 |
                |   1 |  VIEW                  |              |     1 |    13 |    18   (0)| 00:00:01 |
                |   2 |   SORT GROUP BY        |              |     1 |    66 |            |          |
                |   3 |    INDEX FAST FULL SCAN| MYT1$OBJNAME | 11020 |   710K|    18   (0)| 00:00:01 |
                ---------------------------------------------------------------------------------------
                
                Note
                -----
                   - dynamic sampling used for this statement
                
                14 rows selected.
                So, problem is with '7 as ttt'.
                • 5. Re: Index not being used even with hint
                  Pavan Kumar
                  Hi Hemanth,

                  Guide me.. !!

                  *7 as s_no, 'Number of DNB Records' as name* - does not influence on the query as they are literals in select clause
                  when compared to columns of table.

                  driving Oracle to read the table - I think it depends on the criteria of where condition which gives the preference
                  across the table.

                  - Pavan Kumar N
                  - ORACLE OCP - 9i/10g
                  https://www.oracleinternals.blogspot.com
                  • 6. Re: Index not being used even with hint
                    Hemant K Chitale
                    Could you also do a GATHER_TABLE_STATS and try again -- so that we eliminate dynamic sampling as well.

                    Hemant K Chitale
                    • 7. Re: Index not being used even with hint
                      Pavan Kumar
                      Hi Teymur Hajiyev.

                      That's was nice demo, provided picture how it's working and some clarity.

                      Hemanth,

                      What could be the reason from Optimizer working perspective that providing the hint with respect to literal force for FTS, why not given the priority or say an Option going for leading column index. Any guess in this case.

                      - Pavan Kumar N
                      - ORACLE OCP - 9i/10g
                      https://www.oracleinternals.blogspot.com
                      • 8. Re: Index not being used even with hint
                        657203
                        Hemant K Chitale wrote:
                        Could you also do a GATHER_TABLE_STATS and try again -- so that we eliminate dynamic sampling as well.

                        Hemant K Chitale
                        Dear Hemant.
                        Pls see:
                        SQL> exec DBMS_STATS.gather_table_stats('DBUSER', 'MYT1');
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> explain plan for select 7 as ttt, count(distinct object_name) from myt1;
                        
                        Explained.
                        
                        SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                        Plan hash value: 1763129885
                        
                        ---------------------------------------------------------------------------
                        | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                        ---------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT   |      |     1 |    19 |    49   (0)| 00:00:01 |
                        |   1 |  SORT GROUP BY     |      |     1 |    19 |            |          |
                        |   2 |   TABLE ACCESS FULL| MYT1 | 10111 |   187K|    49   (0)| 00:00:01 |
                        ---------------------------------------------------------------------------
                        
                        9 rows selected.
                        
                        SQL>  EXEC DBMS_STATS.gather_index_stats('DBUSER, 'MYT1$OBJNAME');
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> explain plan for select 7 as ttt, count(distinct object_name) from myt1;
                        
                        Explained.
                        
                        SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                        Plan hash value: 1763129885
                        
                        ---------------------------------------------------------------------------
                        | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
                        ---------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT   |      |     1 |    19 |    49   (0)| 00:00:01 |
                        |   1 |  SORT GROUP BY     |      |     1 |    19 |            |          |
                        |   2 |   TABLE ACCESS FULL| MYT1 | 10111 |   187K|    49   (0)| 00:00:01 |
                        ---------------------------------------------------------------------------
                        
                        9 rows selected.
                        
                        SQL> explain plan for select 7 as ttt, ct from ( select count(distinct object_name) ct from myt1);
                        
                        Explained.
                        
                        SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                        Plan hash value: 4000407046
                        
                        ---------------------------------------------------------------------------------------
                        | Id  | Operation              | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
                        ---------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT       |              |     1 |    13 |    18   (0)| 00:00:01 |
                        |   1 |  VIEW                  |              |     1 |    13 |    18   (0)| 00:00:01 |
                        |   2 |   SORT GROUP BY        |              |     1 |    19 |            |          |
                        |   3 |    INDEX FAST FULL SCAN| MYT1$OBJNAME | 10111 |   187K|    18   (0)| 00:00:01 |
                        ---------------------------------------------------------------------------------------
                        
                        10 rows selected.
                        • 9. Re: Index not being used even with hint
                          Hemant K Chitale
                          Pavan,

                          All of us know that "7 as s_no, 'Number of DNB Records' as name" are constants. However, Oracle seems to need to go to the table.

                          I had tested a similar setup using "SUM" instead of "COUNT(DISTINCT)" -- the SUM should have been much easier for Oracle to execute off the index, yet it goes to the table if I add the "7 as s_no".

                          SQL> explain plan for select /*+ index (t T_STAT_NDX) */ count(s_no) as value, 7 from t_stat t;
                          
                          Explained.
                          
                          SQL> select * from table(dbms_xplan.display);
                          
                          PLAN_TABLE_OUTPUT
                          ------------------------------------------------------------------------------------------------------------------------------------
                          Plan hash value: 3819792971
                          
                          -----------------------------------------------------------------------------
                          | Id  | Operation          | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
                          -----------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT   |        |     1 |     5 |    47   (0)| 00:00:02 |
                          |   1 |  SORT AGGREGATE    |        |     1 |     5 |            |          |
                          |   2 |   TABLE ACCESS FULL| T_STAT | 45084 |   220K|    47   (0)| 00:00:02 |
                          -----------------------------------------------------------------------------
                          
                          9 rows selected.
                          
                          SQL> explain plan for select count(s_no) as value from t_stat t;
                          
                          Explained.
                          
                          SQL> select * from table(dbms_xplan.display);
                          
                          PLAN_TABLE_OUTPUT
                          ------------------------------------------------------------------------------------------------------------------------------------
                          Plan hash value: 3388010658
                          
                          ------------------------------------------------------------------------------------
                          | Id  | Operation             | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
                          ------------------------------------------------------------------------------------
                          |   0 | SELECT STATEMENT      |            |     1 |     5 |    16   (0)| 00:00:01 |
                          |   1 |  SORT AGGREGATE       |            |     1 |     5 |            |          |
                          |   2 |   INDEX FAST FULL SCAN| T_STAT_NDX | 45084 |   220K|    16   (0)| 00:00:01 |
                          ------------------------------------------------------------------------------------
                          
                          9 rows selected.
                          
                          SQL>
                          Teymur has run a similar test in 11.2 as well now.

                          Hemant K Chitale
                          http://hemantoracledba.blogspot.com

                          Edited by: Hemant K Chitale on May 12, 2010 2:43 PM
                          • 10. Re: Index not being used even with hint
                            657203
                            Hemant, I tested it in 10.2.0.4.
                            • 11. Re: Index not being used even with hint
                              Hemant K Chitale
                              Oh. I thought that you had seen my comment "This would be worthy of testing in 11.2 as well."


                              We'll see if someone comes around with an 11.2 environment to test on.


                              Hemant K Chitale
                              • 12. Re: Index not being used even with hint
                                657203
                                In 11g R2 Linux:
                                SQL> explain plan for select 7 as ttt, count(distinct object_name) from myt1;
                                
                                Explained.
                                
                                SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                                Plan hash value: 1502943182
                                
                                -----------------------------------------------------------------------------------------
                                | Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                -----------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT     |          |     1 |    66 |       |   643   (1)| 00:00:08 |
                                |   1 |  SORT AGGREGATE      |          |     1 |    66 |       |            |          |
                                |   2 |   VIEW               | VW_DAG_0 | 41500 |  2674K|       |   643   (1)| 00:00:08 |
                                |   3 |    HASH GROUP BY     |          | 41500 |  1013K|  2192K|   643   (1)| 00:00:08 |
                                |   4 |     TABLE ACCESS FULL| MYT1     | 69399 |  1694K|       |   271   (1)| 00:00:04 |
                                -----------------------------------------------------------------------------------------
                                
                                11 rows selected.
                                
                                SQL> explain plan for select 7 as ttt, ct from ( select count(distinct object_name) ct from myt1);
                                
                                Explained.
                                
                                SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                                Plan hash value: 1213218914
                                
                                ------------------------------------------------------------------------------------------
                                | Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                ------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT      |          |     1 |    13 |       |   643   (1)| 00:00:08 |
                                |   1 |  VIEW                 |          |     1 |    13 |       |   643   (1)| 00:00:08 |
                                |   2 |   SORT AGGREGATE      |          |     1 |    66 |       |            |          |
                                |   3 |    VIEW               | VW_DAG_0 | 41500 |  2674K|       |   643   (1)| 00:00:08 |
                                |   4 |     HASH GROUP BY     |          | 41500 |  1013K|  2192K|   643   (1)| 00:00:08 |
                                |   5 |      TABLE ACCESS FULL| MYT1     | 69399 |  1694K|       |   271   (1)| 00:00:04 |
                                ------------------------------------------------------------------------------------------
                                
                                12 rows selected.
                                
                                SQL> explain plan for select 7 as ttt, ct from ( select /*+ INDEX_FFS( MYT1$OBJNAME myt1) */ count(distinct object_name) ct from myt1);
                                
                                Explained.
                                
                                SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);
                                Plan hash value: 1213218914
                                
                                ------------------------------------------------------------------------------------------
                                | Id  | Operation             | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                                ------------------------------------------------------------------------------------------
                                |   0 | SELECT STATEMENT      |          |     1 |    13 |       |   643   (1)| 00:00:08 |
                                |   1 |  VIEW                 |          |     1 |    13 |       |   643   (1)| 00:00:08 |
                                |   2 |   SORT AGGREGATE      |          |     1 |    66 |       |            |          |
                                |   3 |    VIEW               | VW_DAG_0 | 41500 |  2674K|       |   643   (1)| 00:00:08 |
                                |   4 |     HASH GROUP BY     |          | 41500 |  1013K|  2192K|   643   (1)| 00:00:08 |
                                |   5 |      TABLE ACCESS FULL| MYT1     | 69399 |  1694K|       |   271   (1)| 00:00:04 |
                                ------------------------------------------------------------------------------------------
                                
                                12 rows selected.
                                
                                SQL> 
                                • 13. Re: Index not being used even with hint
                                  582987
                                  SELECT count(distinct( dnb_duns_number)) as value
                                  FROM dnb_store_data

                                  Plan
                                  SELECT STATEMENT ALL_ROWS Cost: 4,690 Bytes: 10 Cardinality: 1           
                                       2 SORT GROUP BY Bytes: 10 Cardinality: 1      
                                            1 INDEX FAST FULL SCAN INDEX MCM3.IX_DUNSNO_DNB_STORE_DATA Cost: 4,690 Bytes: 71,416,680 Cardinality: 7,141,668


                                  SELECT /*+ index_ffs (ix_dunsno_dnb_store_data ) */7 as s_no, 'Number of DNB Records' as name, count(distinct( dnb_duns_number)) as value
                                  FROM dnb_store_data

                                  Plan
                                  SELECT STATEMENT ALL_ROWS Cost: 126,897 Bytes: 10 Cardinality: 1           
                                       2 SORT GROUP BY Bytes: 10 Cardinality: 1      
                                            1 TABLE ACCESS FULL TABLE MCM3.DNB_STORE_DATA Cost: 126,897 Bytes: 71,416,680 Cardinality: 7,141,668
                                  • 14. Re: Index not being used even with hint
                                    582987
                                    The issues here are two-fold:

                                    1) Why is the index not being used even when I am telling Oracle to do so with a hint?

                                    2) Why is a hint even necessary as the cost of getting the distinct count is much less with an index scan vs. a full table scan. This is the cost as determined by the CBO itself. I have also empircally tested both queries and the time taken to return with results is in-line with the cost determined by the CBO from the explain plan.

                                    I am running on 10.2.0.4. I am curious if this is a bug of some kind for which there is a patch, or if there is a misunderstanding I have of Oracle functionality.

                                    Thank you.
                                    1 2 Previous Next