Hi All

I have interesting query

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_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and
16 ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and
17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND
18 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND
19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND
20 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND
21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND
22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT )
23 AND
24 ( TPTFBDR_A.CODPTF = 'CROSB') )
25 ;

C NUMTRAFRT CODPTFOL NUMTRAFRTLNK C CODPTFNE CURDATE
- ---------- -------- ------------ - -------- ---------
A 16553901 WARTS 11199599 A CROSB 10-APR-03
A 19059202 WARTS 11199599 A CROSB 11-APR-03
A 19059202 WARTS 11898559 A CROSB 10-APR-03

Elapsed: 00:00:00.13

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 NESTED LOOPS
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
8 7 INDEX (UNIQUE SCAN) OF 'IPTFBDR2' (UNIQUE)
9 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
10 9 INDEX (RANGE SCAN) OF 'IPARCPTTIX2' (NON-UNI
QUE)

11 5 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX'
12 11 INDEX (RANGE SCAN) OF 'IPARGESTIX1' (UNIQUE)
13 4 INDEX (RANGE SCAN) OF 'PK_TTRALNKTIX' (UNIQUE)
14 3 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX'
15 14 INDEX (RANGE SCAN) OF 'IPARGESTIX2' (NON-UNIQUE)
16 2 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
17 16 INDEX (RANGE SCAN) OF 'IPARCPTTIX1' (UNIQUE)
18 1 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
19 18 INDEX (UNIQUE SCAN) OF 'IPTFBDR1' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
508 consistent gets
0 physical reads
0 redo size
1070 bytes sent via SQL*Net to client
554 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
3 rows processed

when i change the query to

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_A.IDFTRA = TPARGESTIX_A.IDFTRA ) and
16 ( TPARCPTTIX_B.IDFTRA = TPARGESTIX_B.IDFTRA ) and
17 ( ttralnktix.NUMTRAFRT = TPARGESTIX_A.NUMTRAFRT ) AND
18 ( TPARCPTTIX_A.CODTRSPTF = TPTFBDR_A.CODTRS ) AND
19 ( ttralnktix.NUMTRAFRTLNK = TPARGESTIX_B.NUMTRAFRT ) AND
20 ( TPARCPTTIX_B.CODTRSPTF = TPTFBDR_B.CODTRS ) AND
21 ( ttralnktix.CODAPP_OLD = TPARGESTIX_A.CODAPPFRT ) AND
22 ( ttralnktix.CODAPP_NEW = TPARGESTIX_B.CODAPPFRT )
23 AND
24 ( TPTFBDR_B.CODPTF = 'WARTS') ) ;


C NUMTRAFRT CODPTFOL NUMTRAFRTLNK C CODPTFNE CURDATE
- ---------- -------- ------------ - -------- ---------
A 16553901 WARTS 11199599 A CROSB 10-APR-03
A 16868993 WARTS 11199599 A ACTEU 10-APR-03
A 17778227 WARTS 11898558 A CBWAR 10-APR-03
A 19059202 WARTS 11199599 A CROSB 11-APR-03
A 19059202 WARTS 11898559 A CROSB 10-APR-03

Elapsed: 00:01:07.80

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 NESTED LOOPS
5 4 NESTED LOOPS
6 5 NESTED LOOPS
7 6 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
8 7 INDEX (UNIQUE SCAN) OF 'IPTFBDR2' (UNIQUE)
9 6 TABLE ACCESS (FULL) OF 'TPARGESTIX'
10 5 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
11 10 INDEX (RANGE SCAN) OF 'IPARCPTTIX2' (NON-UNIQU
E)

12 4 TABLE ACCESS (BY INDEX ROWID) OF 'TPARGESTIX'
13 12 INDEX (RANGE SCAN) OF 'IPARGESTIX1' (UNIQUE)
14 3 INDEX (RANGE SCAN) OF 'PK_TTRALNKTIX' (UNIQUE)
15 2 TABLE ACCESS (BY INDEX ROWID) OF 'TPARCPTTIX'
16 15 INDEX (RANGE SCAN) OF 'IPARCPTTIX1' (UNIQUE)
17 1 TABLE ACCESS (BY INDEX ROWID) OF 'TPTFBDR'
18 17 INDEX (UNIQUE SCAN) OF 'IPTFBDR1' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
11400369 consistent gets
0 physical reads
0 redo size
1292 bytes sent via SQL*Net to client
554 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

How can i tune this query ?

why is the plan getting changed ?

regards
Hrishy