1 2 Previous Next 19 Replies Latest reply: May 13, 2010 12:22 AM by Hemant K Chitale Go to original post RSS
      • 15. Re: Index not being used even with hint
        582987
        Could you do the explain plan for:

        explain plan for select count(distinct object_name) from myt1;

        You'll need to have an index on the object_name column for this test to be apples to apples with my example.

        I am curious why the Oracle optimizer is refusing to use the index when there are constants in the SQL query, as well as why the Optimizer hints are being ignored in the first place.
        • 16. Re: Index not being used even with hint
          Hemant K Chitale
          This is what I see in 10.2.0.4 :
          SQL> drop table t_stat purge;
          
          Table dropped.
          
          SQL> 
          SQL> create table t_stat as select * from dba_objects where object_id is not null;
          
          Table created.
          
          SQL> alter table t_stat modify (object_id not null);
          
          Table altered.
          
          SQL> create index t_ndx on t_stat(object_id);
          
          Index created.
          
          SQL> 
          SQL> exec dbms_stats.gather_table_stats('','T_STAT',estimate_percent=>100);
          
          PL/SQL procedure successfully completed.
          
          SQL> 
          SQL> 
          SQL> explain plan for select count(distinct(object_id)) from t_stat;
          
          Explained.
          
          SQL> select * from table(dbms_xplan.display);
          
          PLAN_TABLE_OUTPUT
          ------------------------------------------------------------------------------------------------------------------------------------
          Plan hash value: 881718575
          
          -------------------------------------------------------------------------------
          | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT      |       |     1 |     5 |    32   (0)| 00:00:01 |
          |   1 |  SORT GROUP BY        |       |     1 |     5 |            |          |
          |   2 |   INDEX FAST FULL SCAN| T_NDX | 50684 |   247K|    32   (0)| 00:00:01 |
          -------------------------------------------------------------------------------
          
          9 rows selected.
          
          SQL> 
          SQL> explain plan for select count(distinct(object_id)),'7' as ttt from t_stat;
          
          Explained.
          
          SQL> select * from table(dbms_xplan.display);
          
          PLAN_TABLE_OUTPUT
          ------------------------------------------------------------------------------------------------------------------------------------
          Plan hash value: 881718575
          
          -------------------------------------------------------------------------------
          | Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT      |       |     1 |     5 |    32   (0)| 00:00:01 |
          |   1 |  SORT GROUP BY        |       |     1 |     5 |            |          |
          |   2 |   INDEX FAST FULL SCAN| T_NDX | 50684 |   247K|    32   (0)| 00:00:01 |
          -------------------------------------------------------------------------------
          
          9 rows selected.
          
          SQL> 
          SQL> explain plan for select /*+ INDEX (t t_ndx) */
            2  count(distinct(object_id)),'7' as ttt from t_stat t;
          
          Explained.
          
          SQL> select * from table(dbms_xplan.display);
          
          PLAN_TABLE_OUTPUT
          ------------------------------------------------------------------------------------------------------------------------------------
          Plan hash value: 3343127530
          
          --------------------------------------------------------------------------
          | Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
          --------------------------------------------------------------------------
          |   0 | SELECT STATEMENT |       |     1 |     5 |   114   (1)| 00:00:02 |
          |   1 |  SORT GROUP BY   |       |     1 |     5 |            |          |
          |   2 |   INDEX FULL SCAN| T_NDX | 50684 |   247K|   114   (1)| 00:00:02 |
          --------------------------------------------------------------------------
          
          9 rows selected.
          
          SQL> 
          Oracle is behaving as expected. I need to go back to my earlier test case.
          The table here is larger. A FullTableScan is more "expensive" relative to an Index Full / Fast Full Scan.

          So, the CBO is doing a proper job.
          And all is well !

          Hemant K Chitale
          http://hemantoracledba.blogspot.com
          • 17. Re: Index not being used even with hint
            657203
            Hemant K Chitale wrote:
            Oracle is behaving as expected. I need to go back to my earlier test case.
            The table here is larger. A FullTableScan is more "expensive" relative to an Index Full / Fast Full Scan.

            So, the CBO is doing a proper job.
            And all is well !

            Hemant K Chitale
            http://hemantoracledba.blogspot.com
            Sorry, big table? You mean in this case?
            SQL> select count(*) from myt1;
            
              COUNT(*)
            ----------
                 69399
            
            SQL> insert into myt1 select * from myt1;
            
            69399 rows created.
            
            SQL> /
            
            138798 rows created.
            
            SQL> commit;
            
            Commit complete.
            
            SQL> exec dbms_stats.gather_table_stats('','MYT1', estimate_percent=>100);
            
            PL/SQL procedure successfully completed.
            
            SQL> explain plan for select 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: 1502943182
            
            -----------------------------------------------------------------------------------------
            | Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
            -----------------------------------------------------------------------------------------
            |   0 | SELECT STATEMENT     |          |     1 |    66 |       |  1979   (1)| 00:00:24 |
            |   1 |  SORT AGGREGATE      |          |     1 |    66 |       |            |          |
            |   2 |   VIEW               | VW_DAG_0 | 42042 |  2709K|       |  1979   (1)| 00:00:24 |
            |   3 |    HASH GROUP BY     |          | 42042 |  1026K|  8720K|  1979   (1)| 00:00:24 |
            |   4 |     TABLE ACCESS FULL| MYT1     |   277K|  6777K|       |  1072   (1)| 00:00:13 |
            -----------------------------------------------------------------------------------------
            
            11 rows selected.
            
            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 |       |  1979   (1)| 00:00:24 |
            |   1 |  SORT AGGREGATE      |          |     1 |    66 |       |            |          |
            |   2 |   VIEW               | VW_DAG_0 | 42042 |  2709K|       |  1979   (1)| 00:00:24 |
            |   3 |    HASH GROUP BY     |          | 42042 |  1026K|  8720K|  1979   (1)| 00:00:24 |
            |   4 |     TABLE ACCESS FULL| MYT1     |   277K|  6777K|       |  1072   (1)| 00:00:13 |
            -----------------------------------------------------------------------------------------
            
            11 rows selected.
            
            SQL> 
            • 18. Re: Index not being used even with hint
              ajallen
              Try this.
              1. alter session set events '10053 trace name context forever';
              2. run query w/o hint
              3. alter session set events '10053 trace name context off';
              4. look at the trace file in udump directory.

              5. connect to a new session and

              6. alter session set events '10053 trace name context forever';
              7. run query w/hint
              8. alter session set events '10053 trace name context off';
              9. look at trace file for this session

              Compare the costs for the two access paths. It may help you understand the choice Oracle made.
              • 19. Re: Index not being used even with hint
                Hemant K Chitale
                Sorry, big table?
                "big" in terms of Number of Rows AND RowSize (avg_row_len).

                If the table has only 3 columns and the rowsize (average row length) is very low then there is not a significant difference in size between the table and and index on one column.

                Hemant K Chitale
                1 2 Previous Next