Part2 - Problem porting PL/SQL code from Oracle 9.0.1.3.1 to Oracle 9.2.0.4.0 (code attached)

Hi

This is a continuation and a restatement of problem: http://www.experts-exchange.com/Databases/Oracle/Q_21242048.html Points from both questions will be combined (350 points)

After some research I have found my problem in Part1 one is that I am running the same code against the same tables in two different databases and getting different results.

Database1
---------------
Intel Windows NT
Oracle9i Enterprise Edition Release 9.0.1.3.1 - Production
With the Partitioning option
JServer Release 9.0.1.3.0 - Production

Database2
------------
Intel RedHat Enterprise Server Linux 2.4.21-20.ELsmp
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

According to the explain plan, the cause is there is a difference in the execution path between the two databases.

In database1 (incorrect results) the joins are done first and then the filter conditions are applied. In database2 (correct results) the filter conditions (select from dual) are done sooner, before the final joins, and I get the results I want.

1) Is there a way for me to control the query execution path?
2) What factors influence the execution path?
2) Is there a way to modify my code so it will run the same on any Oracle 9.* database?
3) Does anyone have some reference recommendations regarding SQL optimization?

Thanks and take care,
Shayne

Code
-----
SELECT ... FROM (
      (TX_ACCTS TA INNER JOIN TX_TACS_LEGALS TTL ON TA.ID = TTL.ACC_ID) LEFT OUTER JOIN
       (
        (TX_RELTY_ACCT_LINC_RELTN TRALR INNER JOIN TX_LINCS TL ON TRALR.LIN_NUM = TL.NUM) INNER JOIN
        (
         (TX_LINC_SHORT_LEGL_RELTNS TLSLR INNER JOIN TX_SHORT_LEGALS TSL ON TLSLR.SHL_ID = TSL.ID)
        ) ON TL.NUM = TLSLR.LIN_NUM
       ) ON TA.ID = TRALR.ACC_ID
     )
WHERE (  
       (UPPER(TA.ACCOUNT_TYPE) = 'R')
   AND (UPPER(TA.STATUS) = UPPER('A'))
   AND ((TA.LAST_ASSESSMENT_YEAR) IS NULL)
       AND (TTL.EFFECTIVE_DATE <= (SELECT SYSDATE FROM DUAL))  
   AND (  
             (TTL.EXPIRY_DATE IS NULL)
     OR (TTL.EXPIRY_DATE >= (SELECT SYSDATE FROM DUAL))
       )  
        AND (TRALR.EFFECTIVE_DATE <= (SELECT SYSDATE FROM DUAL))
   AND (  
            (TRALR.EXPIRY_DATE IS NULL)
     OR (TRALR.EXPIRY_DATE >= (SELECT SYSDATE FROM DUAL))
        )
        AND (TL.EFFECTIVE_DATE <= (SELECT SYSDATE FROM DUAL))
   AND (                                                
             (TL.EXPIRY_DATE IS NULL)                        
     OR (TL.EXPIRY_DATE >= (SELECT SYSDATE FROM DUAL))
        )                                                
      )

End Code
-----------

Database1 Explain Plan
---------------------------
23) SELECT STATEMENT, GOAL = CHOOSE                  Cost=12      Cardinality=9      Bytes=2304
22) FILTER                              
15)  NESTED LOOPS OUTER                  Cost=12      Cardinality=9      Bytes=2304
 4)   NESTED LOOPS                  Cost=10      Cardinality=7      Bytes=441
 1)    TABLE ACCESS FULL      Object owner=TAS_PMO      Object name=TX_ACCTS      Cost=9      Cardinality=7      Bytes=84
 3)    TABLE ACCESS BY INDEX ROWID      Object owner=TAS_PMO      Object name=TX_TACS_LEGALS      Cost=1      Cardinality=3357      Bytes=171207
 2)     INDEX RANGE SCAN      Object owner=TAS_PMO      Object name=TX_TLG_ACC_ID_IX            Cardinality=3357      
14)   VIEW PUSHED PREDICATE      Object owner=SYS                  Cardinality=84708      Bytes=16348644
13)    NESTED LOOPS                  Cost=5      Cardinality=1      Bytes=198
10)     NESTED LOOPS                  Cost=4      Cardinality=1      Bytes=154
 8)      NESTED LOOPS                  Cost=3      Cardinality=1      Bytes=137
 5)       INDEX RANGE SCAN      Object owner=TAS_PMO      Object name=TX_RLP_PK_IX      Cost=2      Cardinality=1      Bytes=17
 7)       TABLE ACCESS BY INDEX ROWID      Object owner=TAS_PMO      Object name=TX_LINCS      Cost=1      Cardinality=84430      Bytes=10131600
 6)        INDEX UNIQUE SCAN      Object owner=TAS_PMO      Object name=TX_LIN_NUM_IX            Cardinality=84430      
 9)      INDEX RANGE SCAN      Object owner=TAS_PMO      Object name=TX_LSL_PK_IX      Cost=1      Cardinality=84442      Bytes=1435514
12)     TABLE ACCESS BY INDEX ROWID      Object owner=TAS_PMO      Object name=TX_SHORT_LEGALS      Cost=1      Cardinality=81181      Bytes=3571964
11)      INDEX UNIQUE SCAN      Object owner=TAS_PMO      Object name=TX_SHL_ID_IX            Cardinality=81181      
16)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=16      Cardinality=8168      
17)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=16      Cardinality=8168      
18)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=16      Cardinality=8168      
19)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=16      Cardinality=8168      
20)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=16      Cardinality=8168      
21)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=16      Cardinality=8168      

End Database1 Explain Plan
--------------------------------

Database2 Explain Plan
---------------------------
23) SELECT STATEMENT, GOAL = CHOOSE                  Cost=56      Cardinality=9      Bytes=2439
22) FILTER                              
17)  NESTED LOOPS OUTER                  Cost=46      Cardinality=9      Bytes=2439
 6)   NESTED LOOPS                  Cost=32      Cardinality=7      Bytes=441
 1)    TABLE ACCESS FULL      Object owner=TAS_PMO      Object name=TX_ACCTS      Cost=18      Cardinality=7      Bytes=84
 5)    TABLE ACCESS BY INDEX ROWID      Object owner=TAS_PMO      Object name=TX_TACS_LEGALS      Cost=2      Cardinality=1      Bytes=51
 2)     INDEX RANGE SCAN      Object owner=TAS_PMO      Object name=TX_TLG_ACC_ID_IX      Cost=1      Cardinality=1      
 3)     TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=5      Cardinality=16360      
 4)     TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=5      Cardinality=16360      
16)   VIEW PUSHED PREDICATE      Object owner=SYS            Cost=2      Cardinality=1      Bytes=208
15)    NESTED LOOPS                  Cost=6      Cardinality=1      Bytes=208
12)     NESTED LOOPS                  Cost=5      Cardinality=1      Bytes=164
11)      NESTED LOOPS                  Cost=4      Cardinality=1      Bytes=147
 8)       TABLE ACCESS BY INDEX ROWID      Object owner=TAS_PMO      Object name=TX_RELTY_ACCT_LINC_RELTN      Cost=3      Cardinality=1      Bytes=27
 7)        INDEX RANGE SCAN      Object owner=TAS_PMO      Object name=TX_RLP_PK_IX      Cost=2      Cardinality=1      
10)       TABLE ACCESS BY INDEX ROWID      Object owner=TAS_PMO      Object name=TX_LINCS      Cost=1      Cardinality=1      Bytes=120
 9)        INDEX UNIQUE SCAN      Object owner=TAS_PMO      Object name=TX_LIN_NUM_IX            Cardinality=1      
12)      INDEX RANGE SCAN      Object owner=TAS_PMO      Object name=TX_LSL_PK_IX      Cost=1      Cardinality=1      Bytes=17
14)     TABLE ACCESS BY INDEX ROWID      Object owner=TAS_PMO      Object name=TX_SHORT_LEGALS      Cost=1      Cardinality=1      Bytes=44
13)      INDEX UNIQUE SCAN      Object owner=TAS_PMO      Object name=TX_SHL_ID_IX            Cardinality=1      
18)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=5      Cardinality=16360      
19)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=5      Cardinality=16360      
20)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=5      Cardinality=16360      
21)  TABLE ACCESS FULL      Object owner=SYS      Object name=DUAL      Cost=5      Cardinality=16360      

End Database2 Explain Plan
--------------------------------Start Free Trial