Params im ADOQuery nicht angenommen.

[Robert_G]

OffTopic:
Wie interpretiert die DB diese Statements (vorsicht sehr lang!)

DB: Oracle

Der dargestellte "explain plan" zeigt in welcher Reihenfolge welche Operation ausgeführt wurde.


Not Exists mit SELECT *

SQL-Code:

SELECT t.PkSeq,
       t.PkDTc,
       t.PkYN,
       t.PkHH,
       t.PkMM,
       t.PkDT,
       t.PkTM,
       t.SubKey
FROM   MM07.PKPROFLO_update t
WHERE  Not Exists (SELECT *
                   FROM   MM07.ENROLL_data e
                   WHERE  e.Subject_ID = t.Subject_ID)
ORDER  By t.PkSeq

Optimizer Plan:

Code:

                                             Cost  Cardinality  Optimizer       Options         Operation            Timestamp           Bytes    
SELECT STATEMENT, GOAL = CHOOSE                 3       1       CHOOSE                          SELECT STATEMENT     17.02.2004 15:53:50            101    
 SORT ORDER BY                                  3       1                       ORDER BY        SORT                 17.02.2004 15:53:50            101    
  FILTER                                                                                        FILTER               17.02.2004 15:53:50                
   TABLE ACCESS FULL   MM07   PKPROFLO_UPDATE   1       1       ANALYZED        FULL            TABLE ACCESS         17.02.2004 15:53:50            101   1 
   INDEX UNIQUE SCAN   MM07   ENROLL_DBIDX      1       1                       UNIQUE SCAN     INDEX                17.02.2004 15:53:50            3

__________________________________________________ __________________________________________________ _______________________

Not Exists mit SELECT 1 num. Spalte

SQL-Code:

SELECT t.PkSeq,
       t.PkDTc,
       t.PkYN,
       t.PkHH,
       t.PkMM,
       t.PkDT,
       t.PkTM,
       t.SubKey
FROM   MM07.PKPROFLO_update t
WHERE  Not Exists (SELECT e.Subject_ID
                   FROM   MM07.ENROLL_data e
                   WHERE  e.Subject_ID = t.Subject_ID)
ORDER  By t.PkSeq

Optimizer Plan:

Code:

SELECT STATEMENT, GOAL = CHOOSE                 3       1       CHOOSE                          SELECT STATEMENT     17.02.2004 15:54:08            101    
 SORT ORDER BY                                  3       1                       ORDER BY        SORT                 17.02.2004 15:54:08            101    
  FILTER                                                                                        FILTER               17.02.2004 15:54:08                
   TABLE ACCESS FULL   MM07   PKPROFLO_UPDATE   1       1       ANALYZED        FULL            TABLE ACCESS         17.02.2004 15:54:08            101   1 
   INDEX UNIQUE SCAN   MM07   ENROLL_DBIDX      1       1                       UNIQUE SCAN     INDEX                17.02.2004 15:54:08            3

__________________________________________________ _________________________________

Not in (SELECT...)

SQL-Code:

SELECT t.PkSeq,
       t.PkDTc,
       t.PkYN,
       t.PkHH,
       t.PkMM,
       t.PkDT,
       t.PkTM,
       t.SubKey
FROM   MM07.PKPROFLO_update t
WHERE  t.Subject_ID Not In (SELECT e.Subject_ID
                            FROM   MM07.ENROLL_data e)
ORDER  By t.PkSeq

Optimizer:

Code:

SELECT STATEMENT, GOAL = CHOOSE                 3       1       CHOOSE                          SELECT STATEMENT     17.02.2004 15:54:08            101    
 SORT ORDER BY                                  3       1                       ORDER BY        SORT                 17.02.2004 15:54:08            101    
  FILTER                                                                                        FILTER               17.02.2004 15:54:08                
   TABLE ACCESS FULL   MM07   PKPROFLO_UPDATE   1       1       ANALYZED        FULL            TABLE ACCESS         17.02.2004 15:54:08            101   1 
   INDEX UNIQUE SCAN   MM07   ENROLL_DBIDX      1       1                       UNIQUE SCAN     INDEX                17.02.2004 15:54:08            3