Originally posted by hrishy
Hi

I was just wundering how come by creating a index on ttralnktix.NUMTRAFRTLNK

how come the plan changed so much..increasing the response time to fraction of a second..

regards
Hrishy
Hrishy :

Its jus my thinking.. i may be wrong..

check the bold part of the where clause.

Code:
SQL> SELECT 
ttralnktix.CODAPP_OLD, 
2 ttralnktix.NUMTRAFRT, 
3 TPTFBDR_B.CODPTF codptfold , 
4 ttralnktix.NUMTRAFRTLNK, 
5 ttralnktix.CODAPP_NEW, 
6 TPTFBDR_A.CODPTF codptfnew, 
7 ttralnktix.CURDATE 
8 FROM ttralnktix, 
9 TPTFBDR TPTFBDR_A, 
10 TPARCPTTIX TPARCPTTIX_A, 
11 TPARGESTIX TPARGESTIX_A, 
12 TPTFBDR TPTFBDR_B, 
13 TPARCPTTIX TPARCPTTIX_B, 
14 TPARGESTIX TPARGESTIX_B 
15 WHERE ( ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and 
16 ( TPARCPTTIX_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and 
17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND 
18 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND 
19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND 
20 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND 
21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND 
22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT ) 
23 AND 
24 ( TPTFBDR_B.CODPTF = 'WARTS') ) ;
Has this column ttralnktix.NUMTRAFRT index?

If yes, then ur first query went smoothly coz u restricted the table TPTFBDR with _A alias and the access order seems good.

If no, i am compeltely wrong.

Now column ttralnktix.NUMTRAFRTLNK was not indexed and with most restrictive statement on table TPTFBDR was with _B alias joined to this indirectly..

so its joining table also went for toss..for FTS.


Mind this is my feeling..may be wrong..just trying to analyze query :-)

Abhay.