Table access by Index Row ID
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Table access by Index Row ID

Hybrid View

  1. #1
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865

    Table access by Index Row ID

    Hi,

    I have a query joining on two tables T1 (15.2 million rows) and T2 (285 rows). Please see the explain plan below.

    ------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1652 | 54516 | 160K (1)| 00:32:06 |
    | 1 | HASH GROUP BY | | 1652 | 54516 | 160K (1)| 00:32:06 |
    |* 2 | TABLE ACCESS BY INDEX ROWID| T1 | 1652 | 54516 | 160K (1)| 00:32:06 |
    |* 3 | INDEX RANGE SCAN | T1_INDX11 | 13M| | 2755 (1)| 00:00:34 |
    |* 4 | TABLE ACCESS FULL | T2 | 1 | 25 | 5 (0)| 00:00:01 |
    |* 5 | TABLE ACCESS FULL | T2 | 1 | 25 | 5 (0)| 00:00:01 |
    ------------------------------------------------------------------------------------------------
    Index range scan on T1 index is taking 34 seconds where as table access by index rowid T1 is taking 32 minutes. T1 is a big table with 250 columns and 15.2 million rows. Is that the reason table access by index rowid taking more time?

    Oracle version: 11.02
    OS - HPUX

    Please let me know if you need any more details to address this.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    adding to my earlier post ...

    If i pass the hints to do FTS and PARALLEL 6 the same query is taking 4 minutes only.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    How many rows from T1 are expected to be selected for processing?
    How selective is index T1_INDX11?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Hi Paul,

    nice to here back from you.

    Expected rows from this query is in few hundreds (less than 500).

    How selective is index T1_INDX11?
    Could you please expedite more on this? you would like to know the cardinality of the indexed column or uniqueness or ???

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Hi Vijay - glad to hear from you.

    Yes, I was referring to cardinality.
    As I can see query is doing a range scan on index T1_INDX11 ... 13M rows, too many rows when compared with the 500 rows that are expected to be retrieved.

    You may want to double-check indexing strategy, may be a new index more aligned with the queries predicate would help.
    Last edited by PAVB; 10-12-2010 at 12:52 PM. Reason: typo
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  6. #6
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    Thanks Paul.

    when doing more research on this issue, yesterday i found that there are no stats available for the index in issue. How did optimizer choose to pick the index when there is no stats available? Is this because number of output rows are very less?

    I am also looking into the indexing strategy.

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    When there are no statistics Oracle optimizer assumes normal distribution.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width