Does this secondary suffer from the above problem?check the plan, if it just uses the secondary index and never goes to the table, it would not 'suffer' from the above problem.
ops$tkyte%ORA11GR2> create table a ( aid int primary key, data varchar2(1) );
Table created.
ops$tkyte%ORA11GR2> create table b ( bid int primary key, data varchar2(1) );
Table created.
ops$tkyte%ORA11GR2> create table a_b ( aid references a, bid references b, constraint a_b_pk primary key(aid,bid) ) organization index;
Table created.
ops$tkyte%ORA11GR2> create unique index a_b_idx on a_b(bid,aid);
Index created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from a, a_b, b where a.aid = 5 and a.aid = a_b.aid and a_b.bid = b.bid;
Execution Plan
----------------------------------------------------------
Plan hash value: 1908560623
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 56 | 3 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 30 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| A | 1 | 15 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | SYS_C0016268 | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | B | 1 | 15 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | A_B_IDX | 1 | 26 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."AID"=5)
6 - access("A_B"."BID"="B"."BID" AND "A_B"."AID"=5)
Note
-----
- dynamic sampling used for this statement (level=2)
ops$tkyte%ORA11GR2> select * from a, a_b, b where b.bid = 5 and a.aid = a_b.aid and a_b.bid = b.bid;
Execution Plan
----------------------------------------------------------
Plan hash value: 2364633749
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 56 | 2 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 56 | 2 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 41 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| B | 1 | 15 | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0016269 | 1 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | A_B_IDX | 1 | 26 | 0 (0)| 00:00:01 |
|* 7 | INDEX UNIQUE SCAN | SYS_C0016268 | 1 | | 0 (0)| 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | A | 1 | 15 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("B"."BID"=5)
6 - access("A_B"."BID"=5)
7 - access("A"."AID"="A_B"."AID")
Note
-----
- dynamic sampling used for this statement (level=2)
ops$tkyte%ORA11GR2> set autotrace off
No reference to the table A_B, so the rowid hint is not being used.