Good morning all,

We have 2 identical databases siting on the same Unix HP box, having same environment settings, I will expect to have same execution plan and same query result. Yet the execution plan turns out to be different. The database that used indexes scan runs faster than the one used full table scan. With large data records coming from client soon, will significantly slow down the run time especially on the database that uses full table scan.

Here is the query that runs on both databases,

SELECT pay, app, refer, enter_date,
DECODE(ssn, 11,'USA', 22,'NA', 33,'SD',
44,'RA', ssn) ssn,
DECODE(SUBSTR(pay,2,1), ssn||SUBSTR(refer,3,1),app),
amt
FROM payment
WHERE refer IN ('TEP','AAP','ST') AND
((app LIKE 'MAI%') OR (app LIKE 'CUT%'))
ORDER BY 1,DECODE(ssn, 11,'USA', 22,'NA', 33,'SD',
44,'RA', ssn) ssn,
DECODE(SUBSTR(pay,2,1), ssn||SUBSTR(refer,3,1),app);

The execution plan for database 1 is:

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=120 Card=77 Bytes=42
35)

1 0 SORT (ORDER BY) (Cost=120 Card=77 Bytes=4235)
2 1 TABLE ACCESS (FULL) OF 'PAYMENT' (Cost=118 Card=77 Byte
s=4235)


Statistics
----------------------------------------------------------
9 recursive calls
7 db block gets
1934 consistent gets
660 physical reads
0 redo size
23924 bytes sent via SQL*Net to client
2788 bytes received via SQL*Net from client
32 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
459 rows processed


The execution plan for database 2 is,
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=69 Card=77 Bytes=908
6)

1 0 SORT (ORDER BY) (Cost=69 Card=77 Bytes=9086)
2 1 INLIST ITERATOR
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'PAYMENT' (Cost=66 C
ard=77 Bytes=9086)

4 3 INDEX (RANGE SCAN) OF 'CONT_INDEX' (NON-UNIQUE) (C
ost=3 Card=77)


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1634 consistent gets
0 physical reads
0 redo size
23891 bytes sent via SQL*Net to client
2788 bytes received via SQL*Net from client
32 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
459 rows processed

I placed a hint for index on the query before it was running on database 2, but the same execution plan was generated. Can you explain why 2 different plans are generated and how to use index on database 2 to speed up the query performance? The query results in both databases are also different in the order. The sorting of the query is critical. Can anyone suggest a solution of how to have the same ordering on the results?


Thanks!