-
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.
-
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.
-
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.
-
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.
-
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.
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|