Table access by Index Row ID

[vnktummala]

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,

[vnktummala]

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,

[PAVB]

How many rows from T1 are expected to be selected for processing?
How selective is index T1_INDX11?

[vnktummala]

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,

[PAVB]

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.

[vnktummala]

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,

[PAVB]

When there are no statistics Oracle optimizer assumes normal distribution.