Oracle Database Discussions

Hi. I want to understand how index modifications are working here and why. I tried reseaching oficial documentation, but i feel like if I was swimming in a big ocean. Im starting as Oracle DBA, and I would like to understand this oracle behavior. Some clues will be appreciated:

I was dealing with a terrible designed query (and of course, no one wants to modificate the php application who builds it) which generates tons of db_sequential_reads in a huge table, and spends lots of time to execute. The only option that allows me is to handle with the oracle system and/or suggest some hints implement.
I'm not allowed to paste the whole real query, so i changed some names and removed unnecesary lines. DB is 9.2.0.2, all statistics are up to date, and query Its similar like this:

select *  
from	(  
			select 
				a.*, 
				ROWNUM rnum 
			from	(
					select  /*+ FIRST_ROWS (20) */
						ID,
						( select NAME from TABLE_B where TABLE_A.SENDERNAME_ID = TABLE_B.NAME_ID ) SENDERNAME,
						( select NAME from TABLE_B where TABLE_A.RECEIVERNAME_ID = TABLE_B.NAME_ID ) RECEIVERNAME,
					from TABLE_A
					where SENDERNAME_ID like 1234
					and ( select NAME from TABLE_B where TABLE_A.SENDERNAME_ID = TABLE_B.NAME_ID ) like 'Vendor_1234'
					order by ID
					)    
			where ROWNUM <= 20 
		)  
where rnum >= 1;

After analyzing traces and explain plans, I see that the optimizer use an index range scan in the column that is in the where clause.

STAT #1 id=1 cnt=20 pid=0 pos=1 obj=0 op='VIEW  '
STAT #1 id=2 cnt=20 pid=1 pos=1 obj=0 op='COUNT STOPKEY '
STAT #1 id=3 cnt=20 pid=2 pos=1 obj=0 op='VIEW  '
STAT #1 id=4 cnt=20 pid=3 pos=1 obj=0 op='SORT ORDER BY STOPKEY '
STAT #1 id=5 cnt=182388 pid=4 pos=1 obj=30578 op='TABLE ACCESS BY INDEX ROWID TABLE_A '
STAT #1 id=6 cnt=182388 pid=5 pos=1 obj=30665 op='INDEX RANGE SCAN TABLE_A-SENDERNAME_ID_IDX '
STAT #1 id=7 cnt=1 pid=6 pos=2 obj=30570 op='TABLE ACCESS BY INDEX ROWID TABLE_B '
STAT #1 id=8 cnt=1 pid=7 pos=1 obj=30571 op='INDEX UNIQUE SCAN PK_TABLE_B-NAME_ID '

Somehow I figure out (or I readed somewhere) that with large tables joined with small tables it could be worst the use of ranged scans index, so I force the optimizer to use a full scan descending in the PK with the hint INDEX_DESC, and the query speed improves from 309 secs to 0.24 secs. in every executions.

STAT #1 id=1 cnt=20 pid=0 pos=1 obj=0 op='VIEW  '
STAT #1 id=2 cnt=20 pid=1 pos=1 obj=0 op='COUNT STOPKEY '
STAT #1 id=3 cnt=20 pid=2 pos=1 obj=0 op='VIEW  '
STAT #1 id=4 cnt=20 pid=3 pos=1 obj=0 op='FILTER  '
STAT #1 id=5 cnt=20 pid=4 pos=1 obj=30578 op='TABLE ACCESS BY INDEX ROWID TABLE_A '
STAT #1 id=6 cnt=1288 pid=5 pos=1 obj=30579 op='INDEX FULL SCAN DESCENDING PK_TABLE_A-ID '
STAT #1 id=7 cnt=1 pid=4 pos=2 obj=30570 op='TABLE ACCESS BY INDEX ROWID TABLE_B '
STAT #1 id=8 cnt=1 pid=7 pos=1 obj=30571 op='INDEX UNIQUE SCAN PK_TABLE_B-NAME_ID '

Yes, it solves the problem, but I ask myself "why?",.. and I dont have a clue.
I want to have a better answer than "I had lucky and I did a wondering supposition which hit the spot!". I want to understand this for my professional future. Someone can teach me why this worked?

Thanks in advance

elvegaa_esp

Edited by: elvegaa_esp on 13-mar-2012 6:24

Edited by: elvegaa_esp on 13-mar-2012 6:34