This discussion is archived
7 Replies Latest reply: Dec 3, 2013 2:12 AM by Vite DBA RSS

Reg: Index usage -

ranit B Expert
Currently Being Moderated

Hi,

 

I'm trying to execute the below, but my index is not getting used for table "my_tables".

 

Can you please provide me some pointers?

 

--- Initial setup

CREATE TABLE my_tables AS

SELECT dba_tables.*

FROM dba_tables;

 

CREATE TABLE my_indexes AS

SELECT dba_indexes.*

FROM dba_tables, dba_indexes

WHERE dba_tables.owner = dba_indexes.table_owner

AND dba_tables.table_name = dba_indexes.table_name;

 

--- Creating Indexes

CREATE INDEX idx_1
ON my_indexes(table_owner, table_name);

 

CREATE INDEX idx_2
ON my_tables(owner, table_name /*, tablespace_name*/ );

 

--- Gathering statistics

BEGIN

    Dbms_Stats.gather_table_stats('ARLT_DEV', 'MY_TABLES');

    Dbms_Stats.gather_index_stats('ARLT_DEV', 'IDX_1');

    Dbms_Stats.gather_table_stats('ARLT_DEV', 'MY_INDEXES');

    Dbms_Stats.gather_index_stats('ARLT_DEV', 'IDX_2');

END;

 

--- Query to execute

SELECT DISTINCT

    my_tables.owner,

    my_tables.table_name,

    my_tables.tablespace_name

FROM my_tables, my_indexes

WHERE my_tables.owner = my_indexes.table_owner

AND my_tables.table_name = my_indexes.table_name;

 

--- Explain Plan

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------

| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT       |           | 13246 |   879K|       |   376   (1)|

|   1 |  HASH UNIQUE           |           | 13246 |   879K|  1000K|   376   (1)|

|   2 |   HASH JOIN            |           | 13246 |   879K|       |   160   (1)|

|   3 |    INDEX FAST FULL SCAN| IDX_1     | 14018 |   383K|       |    22   (0)|

|   4 |    TABLE ACCESS FULL   | MY_TABLES | 13246 |   517K|       |   137   (0)|

---------------------------------------------------------------------------------

 

Note

-----

   - 'PLAN_TABLE' is old version

 

Help much appreciated.

 

( Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production )

 

-- Ranit

  • 1. Re: Reg: Index usage -
    Vite DBA Pro
    Currently Being Moderated

    Hi,

     

    What makes you think the index should be getting used? Your query has to return data from every row in the table.

     

    Andre

  • 2. Re: Reg: Index usage -
    Martin Preiss Expert
    Currently Being Moderated

    the use of the index would not be beneficial and the optimizer is aware of this. If you create the covering index on  my_tables(owner, table_name, tablespace_name) an INDEX FAST FULL SCAN becomes an option: the index can be used as a smaller replacement for the table and be read by multiblock I/O. If you force the optimizer to use the index on my_tables(owner, table_name) by adding an index hint you may get an INDEX FULL SCAN: an ordered read of the complete index with single block I/O - and this will be quite slow.

  • 3. Re: Reg: Index usage -
    ranit B Expert
    Currently Being Moderated

     

    What makes you think the index should be getting used? Your query has to return data from every row in the table.

    Both my columns used in the WHERE clause are present in index "idx_2" on "my_tables".

    Then, why is it not getting used?

     

    I understand FFS happens when the columns are also present in my index, but atleast in my case it should get used, right?

     

    Please let me know if I'm missing any fundamental concept.

     

    -- Ranit

  • 4. Re: Reg: Index usage -
    Martin Preiss Expert
    Currently Being Moderated

    if you want to use an index efficiently you need a filtering condition. If there is only the join condition to limit the result then the engine has to read the first set completely before the join condition can be used to filter the second set (and discard not matching results from the first set). Furthermore the use of the HASH JOIN prevents even the use of the index for the match lookups since in this case a hash function is used to determine the matching rows. In your case you could get an index access with some hints:

    explain plan for

    SELECT /*+ leading (my_indexes my_tables) use_nl(my_indexes my_tables) */

        DISTINCT

        my_tables.owner,

        my_tables.table_name,

        my_tables.tablespace_name

    FROM my_tables, my_indexes

    WHERE my_tables.owner = my_indexes.table_owner

    AND my_tables.table_name = my_indexes.table_name

     

    -------------------------------------------------------------------------------------------

    | Id  | Operation                     | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

    -------------------------------------------------------------------------------------------

    |   0 | SELECT STATEMENT              |           |  3873 |   238K|  7749   (1)| 00:00:39 |

    |   1 |  HASH UNIQUE                  |           |  3873 |   238K|  7749   (1)| 00:00:39 |

    |   2 |   NESTED LOOPS                |           |       |       |            |          |

    |   3 |    NESTED LOOPS               |           |  3873 |   238K|  7748   (0)| 00:00:39 |

    |   4 |     INDEX FAST FULL SCAN      | IDX_1     |  3873 |   109K|     2   (0)| 00:00:01 |

    |*  5 |     INDEX RANGE SCAN          | IDX_2     |     1 |       |     1   (0)| 00:00:01 |

    |   6 |    TABLE ACCESS BY INDEX ROWID| MY_TABLES |     1 |    34 |     2   (0)| 00:00:01 |

    -------------------------------------------------------------------------------------------

     

    Predicate Information (identified by operation id):

    ---------------------------------------------------

       5 - access("MY_TABLES"."OWNER"="MY_INDEXES"."TABLE_OWNER" AND

                  "MY_TABLES"."TABLE_NAME"="MY_INDEXES"."TABLE_NAME")

    The leading hint tells the optimizer to start with my_indexes and the use_nl-hint forces the NESTED LOOPS join. So the engine would read all the rows from IDX_1 (or from the table if there was no covering index) and do the index lookup on idx_2. But again: this is not a good idea and almost certainly slower than the HASH JOIN.

  • 5. Re: Reg: Index usage -
    Karthick_Arp Guru
    Currently Being Moderated

    There is no predicate filter that filters for a specific value in your query. Also the relation between both the table are more like 1 to 1. So oracle has to visit all the rows in both the tables. So use of index (range scan) can be more expensive because it may result in more I/O.

     

    Oracle uses Index fast full scan on IDX_1 because you are looking for TABLE_OWNER and TABLE_NAME from MY_INDEXES table. And these fields are already available in the index IDX_1. So oracle treats IDX_1 as skinny version of MY_INDEXES table. This helps oracle to reduce I/O to a considerable amount.

     

    Different joining methods like HASH JOIN, NESTED LOOP etc are used to address specific set problems based on how the data is in the table. This is well documented under Join Methods please go over it.

  • 6. Re: Reg: Index usage -
    ranit B Expert
    Currently Being Moderated
    Oracle uses Index fast full scan on IDX_1 because you are looking for TABLE_OWNER and TABLE_NAME from MY_INDEXES table. And these fields are already available in the index IDX_1. So oracle treats IDX_1 as skinny version of MY_INDEXES table. This helps oracle to reduce I/O to a considerable amount.

    1- I got this, but what is the reason for not using IDX_2?

     

    2- Is it mandatory to have a Filter clause (not the JOINing condition) to enable indexes?

     

    3- Say there is some column referenced in the query which is not present in the Index, for sure Index FFS is not going to happen.

    But since the indexed columns are used in JOIN, can't it go for a Index Full Scan?

     

    AFAIK, Index Full Scan (not Index "Fast" Full Scan) mandates all the columns used in query to be indexed. Is this true?

    Please rectify me, if getting wrong.

     

    -- Ranit

  • 7. Re: Reg: Index usage -
    Vite DBA Pro
    Currently Being Moderated

    ranitB wrote:

     

     

    What makes you think the index should be getting used? Your query has to return data from every row in the table.

    Both my columns used in the WHERE clause are present in index "idx_2" on "my_tables".

    Then, why is it not getting used?

     

    I understand FFS happens when the columns are also present in my index, but atleast in my case it should get used, right?

     

    Please let me know if I'm missing any fundamental concept.

     

    -- Ranit

    Hi,

     

    yes you are missing something. The query has to visit every row in the table to get the data you are asking for, why would it waste time doing a full fast scan of the index just to lookup a little bit more data in the table.

     

    Regards

    Andre

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points