How can i force optimizer to use hash anit join

Guys...

I tried all sorts of hints but still oracle goes for filter and FTS on a temp table.. I dont want to create an index for this reason..

Is there any way to force Oracle to use Hash Aj in this case..

Below is the code and plan..

Code:

---

PRO> ed
Wrote file afiedt.buf

  1  explain plan set statement_id = 'ABHAY' for
  2  MERGE /*+ Ordered */ INTO  Shipment_Line X
  3  USING
  4  (
  5  select /*+ Ordered */ * from shipment_line sd
  6  where
  7    (
  8      Sd.SALES_ORGANIZATION_CD      ,
  9      Sd.REFERENCE_DOCUMENT_NO      ,
 10      Sd.REFRENCE_DOCUMENT_ITEM_NO  ,
 11      SUBSTR(sd.SOURCE_NM,-3,3)
 12    )
 13    not in (select /*+ Hash_Aj */
 14                      S.SALES_ORGANIZATION_CD      ,
 15                      S.SALES_ORDER_NO              ,
 16                      S.sales_order_item_no        ,
 17                      SUBSTR(s.SOURCE_NM,-3,3)
 18            from temp_slordt s) and
 19    sales_document_type <> '??' and rownum > 0 and
 20    exists (select null from temp_slsord s where
 21                      S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
 22                      S.SALES_ORDER_NO            =    Sd.REFERENCE_DOCUMENT_NO And
 23                      SUBSTR(s.SOURCE_NM,-3,3)    =    SUBSTR(sd.SOURCE_NM,-3,3))
 24  ) Y
 25  ON
 26  (
 27    X.SALES_ORGANIZATION_CD  =    Y.SALES_ORGANIZATION_CD      AND
 28    X.SHIPMENT_NO            =    Y.SHIPMENT_NO                AND
 29    X.SHIPMENT_ITEM_NO        =    Y.SHIPMENT_ITEM_NO          AND
 30    X.SOURCE_NM              =    Y.SOURCE_NM
 31  )
 32  WHEN MATCHED THEN
 33  UPDATE  SET
 34    x.sales_document_type = '??', x.reportable_flag = '?',
 35    x.sab101_flag = '?', x.country_cd = '?', x.Ship_To_Customer = '?',
 36    x.So_Entry_Date = '31-Dec-2999', x.SALES_ORDER_ITEM_SHORT_TX = '??'
 37  WHEN NOT MATCHED THEN
 38  INSERT
 39  (
 40    X.SALES_ORGANIZATION_CD          ,
 41    X.SHIPMENT_NO                    ,
 42    X.SHIPMENT_ITEM_NO              ,
 43    X.SOURCE_NM
 44  )
 45  VALUES
 46  (
 47    Y.SALES_ORGANIZATION_CD          ,
 48    Y.SHIPMENT_NO                    ,
 49    Y.SHIPMENT_ITEM_NO              ,
 50    Y.SOURCE_NM
 51*  )
PRO> /

Explained.

Elapsed: 00:00:01.39
PRO> @display_plan ABHAY

  Id  Par  Pos  Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
  0      ####        MERGE STATEMENT (choose)    Cost (4938,1,164)
  1    0    1          MERGE  WWCIW_DBA SHIPMENT_LINE
  2    1    1  12      VIEW  WWCIW_DBA
  3    2    1              NESTED LOOPS    (outer)  Cost (4938,1,303)
  4    3    1    7          VIEW  WWCIW_DBA  Cost (4937,1,177)
  5    4    1                  COUNT
  6    5    1                    FILTER
  7    6    1                      HASH JOIN    PARALLEL_TO_SERIAL Cost (4046,1,150)
  8    7    1                        SORT    (unique) PARALLEL_FROM_SERIAL
  9    8    1  10                    TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLSORD (full)  Cost (165,79119,1898856)
  10    7    2                        PARTITION RANGE    (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 10 Pt Range: 1 - 4
  11  10    1    8                    TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_TO_PARALLEL Pt id: 10 Pt Range: 1 - 4 Cost (3501,32155,4051530)
  12    6    2    9                TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLORDT (full)  Cost (891,2,58)
  13    3    2    2          TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (by global index rowid)  Pt id: 13 Pt Range: ROW LOCATION - ROW LOCATION Cost (3,1,126)
  14  13    1                  INDEX (analyzed) UNIQUE WWCIW_DBA UK_SHIPMENT_LINE (unique scan) (Columns 4  Cost (2,1,)

---

Thx
Abhay.

It is not so much a question of forcing the optimizer to do an anti-join as one of allowing it to.

Because of the logic of anti-join you must exclude the possibility of NULLs on either side of the join.

For columns or expressions not obviously NULL to the optimizer this is typically done by adding IS NOT NULL conditions to all columns or expressions referenced on BOTH sides of the in the NOT IN.

For example, the first query below does not guarantee that ename and loc are not null, hence hash anti-join is not an option. In the second query we explicitly state that ename and loc are NULL and anti-join results.

Code:

---

Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> SET AUTOTRACE ON EXPLAIN;
SQL> SELECT empno
  2  FROM  emp
  3  WHERE  ename NOT IN (
  4        SELECT loc
  5        FROM  dept);

    EMPNO
----------
      7369
      7499
      7521
      7566
      7654
      7698
      7782
      7788
      7839
      7844
      7876
      7900
      7902
      7934

14 rows selected.


Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=76 Card=13 Bytes 130)
  1    0  FILTER
  2    1    TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=55 Card=14 Bytes=140)
  3    1    TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Bytes=7)

SQL> SELECT empno
  2  FROM  emp
  3  WHERE  ename IS NOT NULL
  4  AND    ename NOT IN (
  5        SELECT loc
  6        FROM  dept
  7        WHERE  loc IS NOT NULL);

    EMPNO
----------
      7698
      7654
      7902
      7499
      7521
      7934
      7900
      7369
      7844
      7782
      7876
      7788
      7839
      7566

14 rows selected.

Execution Plan
----------------------------------------------------------
  0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=59 Card=10 Bytes=170)
  1    0  HASH JOIN (ANTI) (Cost=59 Card=10 Bytes=170)
  2    1    TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=55 Card=14 Bytes=140)
  3    1    TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=4 Bytes=28)

SQL>

---

Quote:

---

Originally posted by padders
Because of the logic of anti-join you must exclude the possibility of NULLs on either side of the join.

For columns or expressions not obviously NULL to the optimizer this is typically done by adding IS NOT NULL conditions to all columns or expressions referenced on BOTH sides of the in the NOT IN.

---

Its not the Q of "not null" at all, for the hash aj to be an option..

PS below.. the same query uses hash_aj if at all its run as query.. but when made as subqry in the update/merge statement it uses filter..

( here ofcourse i used not exists insead of not in, which should not make much difference ).. I tried with this not exists also in the merge statement still it goes for filter there....

Code:

---


PRO> explain plan set statement_id = 'ABHAY' for
  2  select * from shipment_line sd
  3  where
  4    not exists (select /*+ hash_aj */ null from temp_slordt s where
  5                      S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
  6                      S.SALES_ORDER_NO              =    Sd.REFERENCE_DOCUMENT_NO And
  7                      Sd.REFRENCE_DOCUMENT_ITEM_NO  =    S.sales_order_item_no And
  8                      SUBSTR(s.SOURCE_NM,-3,3)      =    SUBSTR(sd.SOURCE_NM,-3,3)) and
  9    sales_document_type <> '??' and --rownum > 0 and
 10    exists (select null from temp_slsord s where
 11                      S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
 12                      S.SALES_ORDER_NO            =    Sd.REFERENCE_DOCUMENT_NO And
 13                      SUBSTR(s.SOURCE_NM,-3,3)    =    SUBSTR(sd.SOURCE_NM,-3,3));

Explained.

Elapsed: 00:00:01.40
PRO> @display_plan ABHAY

  Id  Par  Pos  Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
  0      ####        SELECT STATEMENT (choose)    Cost (4743,6367,1139693)
  1    0    1          HASH JOIN    (anti) PARALLEL_TO_SERIAL Cost (4743,6367,1139693)
  2    1    1            HASH JOIN    (semi) PARALLEL_TO_PARALLEL Cost (3848,6431,964650)
  3    2    1              PARTITION RANGE    (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 3 Pt Range: 1 - 4
  4    3    1    1          TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_COMBINED_WITH_PARENT Pt id: 3 Pt Range: 1 - 4 Cost (3501,643102,81030852)
  5    2    2    3        TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLSORD (full) PARALLEL_FROM_SERIAL Cost (165,79119,1898856)
  6    1    2    2      TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLORDT (full) PARALLEL_FROM_SERIAL Cost (891,310268,8997772)

---

PS : In the table temp_slordt none of the columns are with "NOT NULL"..

Abhay.

> here ofcourse i used not exists insead of not in, which should not make much difference

Ah but it does. NOT IN and NOT EXISTS are not the same thing, and the difference revolves around NULLs.

Granted I do not have your tables or data but I can get your MERGE to go from FILTER to HASH_AJ simply by adding 8 IS NOT NULLs. Note that the FILTER plan is actually cheaper here because there is no data in the tables.

Code:

---

Personal Oracle Database 10g Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE TABLE shipment_line (
  2    sales_organization_cd NUMBER,
  3    reference_document_no NUMBER,
  4    reference_document_item_no NUMBER,
  5    source_nm VARCHAR2 (10),
  6    shipment_no NUMBER,
  7    shipment_item_no NUMBER,
  8    sales_document_type VARCHAR2 (2),
  9    reportable_flag VARCHAR2 (1),
 10    sab101_flag VARCHAR2 (1),
 11    country_cd VARCHAR2 (1),
 12    ship_to_customer VARCHAR2 (1),
 13    so_entry_date DATE,
 14    sales_order_item_short_tx VARCHAR2 (2));

Table created.

SQL> CREATE TABLE temp_slordt (
  2    sales_organization_cd NUMBER,
  3    sales_order_no NUMBER,
  4    sales_order_item_no NUMBER,
  5    source_nm VARCHAR2 (10));

Table created.

SQL> CREATE TABLE temp_slsord (
  2    sales_organization_cd NUMBER,
  3    sales_order_no NUMBER,
  4    sales_order_item_no NUMBER,
  5    source_nm VARCHAR2 (10));

Table created.

SQL> SET AUTOTRACE ON EXPLAIN;
SQL> MERGE INTO shipment_line x
  2  USING (SELECT *
  3        FROM  shipment_line sd
  4        WHERE (sd.sales_organization_cd,
  5                sd.reference_document_no,
  6                sd.reference_document_item_no,
  7                SUBSTR (sd.source_nm, -3, 3)) NOT IN (
  8                SELECT /*+ HASH_AJ */
  9                      s.sales_organization_cd, s.sales_order_no,
 10                      s.sales_order_item_no, SUBSTR (s.source_nm, -3, 3)
 11                FROM  temp_slordt s)
 12        AND    sales_document_type <> '??'
 13        AND    EXISTS (
 14                SELECT NULL
 15                FROM  temp_slsord s
 16                WHERE  s.sales_organization_cd = sd.sales_organization_cd
 17                AND    s.sales_order_no = sd.reference_document_no
 18                AND    SUBSTR (s.source_nm, -3, 3) = SUBSTR (sd.source_nm, -3,  3))) y
 19  ON  (x.sales_organization_cd = y.sales_organization_cd
 20  AND  x.shipment_no = y.shipment_no
 21  AND  x.shipment_item_no = y.shipment_item_no
 22  AND  x.source_nm = y.source_nm)
 23    WHEN MATCHED THEN UPDATE
 24          SET  x.sales_document_type = '??', x.reportable_flag = '?',
 25                x.sab101_flag = '?', x.country_cd = '?',
 26                x.ship_to_customer = '?', x.so_entry_date = '31-Dec-2999',
 27                x.sales_order_item_short_tx = '??'
 28    WHEN NOT MATCHED THEN INSERT (
 29                x.sales_organization_cd, x.shipment_no,
 30                x.shipment_item_no, x.source_nm)
 31    VALUES    (y.sales_organization_cd, y.shipment_no,
 32                y.shipment_item_no, y.source_nm);

0 rows merged.

Execution Plan
----------------------------------------------------------
  0      MERGE STATEMENT Optimizer=ALL_ROWS (Cost=9 Card=1 Bytes=133)
  1    0  MERGE OF 'SHIPMENT_LINE'
  2    1    VIEW
  3    2      FILTER
  4    3        HASH JOIN (SEMI) (Cost=7 Card=1 Bytes=230)
  5    4          HASH JOIN (OUTER) (Cost=5 Card=1 Bytes=197)
  6    5            TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=95)
  7    5            TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=102)
  8    4          TABLE ACCESS (FULL) OF 'TEMP_SLSORD' (TABLE) (Cost=2 Card=1 Bytes=33)
  9    3        TABLE ACCESS (FULL) OF 'TEMP_SLORDT' (TABLE) (Cost=2 Card=1 Bytes=46)

SQL> MERGE INTO shipment_line x
  2  USING (SELECT *
  3        FROM  shipment_line sd
  4        WHERE  sd.sales_organization_cd IS NOT NULL
  5        AND    sd.reference_document_no IS NOT NULL
  6        AND    sd.reference_document_item_no IS NOT NULL
  7        AND    sd.source_nm IS NOT NULL
  8        AND  (sd.sales_organization_cd,
  9                sd.reference_document_no,
 10                sd.reference_document_item_no,
 11                SUBSTR (sd.source_nm, -3, 3)) NOT IN (
 12                SELECT /*+ HASH_AJ */
 13                      s.sales_organization_cd, s.sales_order_no,
 14                      s.sales_order_item_no, SUBSTR (s.source_nm, -3, 3)
 15                FROM  temp_slordt s
 16              WHERE  s.sales_organization_cd IS NOT NULL
 17              AND    s.sales_order_no IS NOT NULL
 18              AND    s.sales_order_item_no IS NOT NULL
 19              AND    s.source_nm IS NOT NULL)
 20        AND    sales_document_type <> '??'
 21        AND    EXISTS (
 22                SELECT NULL
 23                FROM  temp_slsord s
 24                WHERE  s.sales_organization_cd = sd.sales_organization_cd
 25                AND    s.sales_order_no = sd.reference_document_no
 26                AND    SUBSTR (s.source_nm, -3, 3) = SUBSTR (sd.source_nm, -3,  3))) y
 27  ON  (x.sales_organization_cd = y.sales_organization_cd
 28  AND  x.shipment_no = y.shipment_no
 29  AND  x.shipment_item_no = y.shipment_item_no
 30  AND  x.source_nm = y.source_nm)
 31    WHEN MATCHED THEN UPDATE
 32          SET  x.sales_document_type = '??', x.reportable_flag = '?',
 33                x.sab101_flag = '?', x.country_cd = '?',
 34                x.ship_to_customer = '?', x.so_entry_date = '31-Dec-2999',
 35                x.sales_order_item_short_tx = '??'
 36    WHEN NOT MATCHED THEN INSERT (
 37                x.sales_organization_cd, x.shipment_no,
 38                x.shipment_item_no, x.source_nm)
 39    VALUES    (y.sales_organization_cd, y.shipment_no,
 40                y.shipment_item_no, y.source_nm);

0 rows merged.

Execution Plan
----------------------------------------------------------
  0      MERGE STATEMENT Optimizer=ALL_ROWS (Cost=10 Card=1 Bytes=141)
  1    0  MERGE OF 'SHIPMENT_LINE'
  2    1    VIEW
  3    2      HASH JOIN (ANTI) (Cost=10 Card=1 Bytes=276)
  4    3        HASH JOIN (SEMI) (Cost=7 Card=1 Bytes=230)
  5    4          HASH JOIN (OUTER) (Cost=5 Card=1 Bytes=197)
  6    5            TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=95)
  7    5            TABLE ACCESS (FULL) OF 'SHIPMENT_LINE' (TABLE) (Cost=2 Card=1 Bytes=102)
  8    4          TABLE ACCESS (FULL) OF 'TEMP_SLSORD' (TABLE) (Cost=2 Card=1 Bytes=33)
  9    3        TABLE ACCESS (FULL) OF 'TEMP_SLORDT' (TABLE) (Cost=2 Card=1 Bytes=46)

SQL>

---

Please do me the honour of posting your plan for MERGE statement with IS NOT NULLs added as above.

Yes indeed there is a difference in not in and not exists logically also.. NOT IN filters NULLS also but NOT EXISTS wont.

The merge is working with hash anti join after i put not null condition as you mentioned... Thanks a lot...

But I still dont get the point as to why Oracle didnt use Hash Aj when i used NOT EXISTS in merge statement.. but used hash aj if i just queried..

Abhay.

> Thanks a lot

You're welcome.

> why Oracle didnt use Hash Aj when i used NOT EXISTS in merge statement

Not 100% sure on that one. NOT EXISTS appears to use HASH_AJ when I try it. Perhaps it is related to your use of the /*+ ORDERED */ hint. Can you post a full example of that case.

Code:

---

explain plan set statement_id = 'ABHAY' for
MERGE INTO  Shipment_Line X
USING
(
select * from shipment_line sd
where
  not exists (select /*+ hash_aj */ null from temp_slordt s where
                    S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
                    S.SALES_ORDER_NO              =    Sd.REFERENCE_DOCUMENT_NO And
                    Sd.REFRENCE_DOCUMENT_ITEM_NO  =    S.sales_order_item_no And
                    SUBSTR(s.SOURCE_NM,-3,3)      =    SUBSTR(sd.SOURCE_NM,-3,3)) and
  sales_document_type <> '??' and --rownum > 0
  exists (select null from temp_slsord s where
                    S.SALES_ORGANIZATION_CD      =    Sd.SALES_ORGANIZATION_CD  And
                    S.SALES_ORDER_NO            =    Sd.REFERENCE_DOCUMENT_NO And
                    SUBSTR(s.SOURCE_NM,-3,3)    =    SUBSTR(sd.SOURCE_NM,-3,3))
) Y
ON
(
  X.SALES_ORGANIZATION_CD  =    Y.SALES_ORGANIZATION_CD      AND
  X.SHIPMENT_NO            =    Y.SHIPMENT_NO                AND
  X.SHIPMENT_ITEM_NO        =    Y.SHIPMENT_ITEM_NO          AND
  X.SOURCE_NM              =    Y.SOURCE_NM
)
WHEN MATCHED THEN
UPDATE  SET
  x.sales_document_type = '??', x.reportable_flag = '?',
  x.sab101_flag = '?', x.country_cd = '?', x.Ship_To_Customer = '?',
  x.So_Entry_Date = '31-Dec-2999', x.SALES_ORDER_ITEM_SHORT_TX = '??'
WHEN NOT MATCHED THEN
INSERT
 (
  X.SALES_ORGANIZATION_CD          ,
  X.SHIPMENT_NO                    ,
  X.SHIPMENT_ITEM_NO              ,
  X.SOURCE_NM
 )
VALUES
 (
  Y.SALES_ORGANIZATION_CD          ,
  Y.SHIPMENT_NO                    ,
  Y.SHIPMENT_ITEM_NO              ,
  Y.SOURCE_NM
 )

The plan as shown after explaining.

PRO> @display_plan ABHAY

  Id  Par  Pos  Ins Plan
---- ---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
  0      ####        MERGE STATEMENT (choose)    Cost (4976,24,3936)
  1    0    1          MERGE  WWCIW_DBA SHIPMENT_LINE
  2    1    1  12      VIEW  WWCIW_DBA  PARALLEL_TO_SERIAL
  3    2    1              NESTED LOOPS    (outer) PARALLEL_COMBINED_WITH_PARENT Cost (4976,24,7320)
  4    3    1                HASH JOIN    (anti) PARALLEL_COMBINED_WITH_PARENT Cost (4958,24,4296)
  5    4    1                  HASH JOIN    PARALLEL_TO_PARALLEL Cost (4066,25,3750)
  6    5    1                    SORT    (unique) PARALLEL_FROM_SERIAL
  7    6    1  10                TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLSORD (full)  Cost (165,79119,1898856)
  8    5    2                    PARTITION RANGE    (all) PARALLEL_COMBINED_WITH_PARENT Pt id: 8 Pt Range: 1 - 4
  9    8    1    8                TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (full) PARALLEL_COMBINED_WITH_PARENT Pt id: 8 Pt Range: 1 - 4 Cost (3501,643102,81030852)
  10    4    2    9            TABLE ACCESS (analyzed)  WWCIW_SAP TEMP_SLORDT (full) PARALLEL_FROM_SERIAL Cost (891,310268,8997772)
  11    3    2    2          TABLE ACCESS (analyzed)  WWCIW_DBA SHIPMENT_LINE (by global index rowid) PARALLEL_COMBINED_WITH_PARENT Pt id: 11 Pt Range: ROW LOCATION - ROW LOCATION Cost (3,1,126)
  12  11    1                  INDEX (analyzed) UNIQUE WWCIW_DBA UK_SHIPMENT_LINE (unique scan) (Columns 4 PARALLEL_COMBINED_WITH_PARENT Cost (2,1,)


Run time plan taken from v$sql_plan

PRO> @display_plan_by_address 1CCE821C

  Id  Par  Pos Plan
---- ---- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------
  0        0  MERGE STATEMENT (choose)  (Columns 0  Cost (290820,,)
  1    0    1    MERGE  (Columns 0
  2    1    1      VIEW  (Columns 0
  3    2    1        FILTER  (Columns 0
  4    3    1          NESTED LOOPS  (outer) (Columns 0 PARALLEL_TO_SERIAL Cost (3918,322,88872)
  5    4    1            HASH JOIN  (semi) (Columns 0 PARALLEL_COMBINED_WITH_PARENT Cost (3676,322,48300)
  6    5    1              PARTITION RANGE  (all) (Columns 0 PARALLEL_COMBINED_WITH_PARENT Pt id: 6 Pt Range: 1 - 4
  7    6    1                TABLE ACCESS (analyzed) WWCIW_DBA SHIPMENT_LINE (full) (Columns 0 PARALLEL_TO_PARALLEL Pt id: 6 Pt Range: 1 - 4 Cost (3501,32155,4051530)
  8    5    2              TABLE ACCESS (analyzed) WWCIW_SAP TEMP_SLSORD (full) (Columns 0 PARALLEL_FROM_SERIAL Cost (165,79119,1898856)
  9    4    2            TABLE ACCESS (analyzed) WWCIW_DBA SHIPMENT_LINE (by global index rowid) (Columns 0 PARALLEL_COMBINED_WITH_PARENT Pt id: 9 Pt Range: ROW L - ROW L Cost (3,1,126)
  10    9    1              INDEX (analyzed) WWCIW_DBA UK_SHIPMENT_LINE (unique scan) (Columns 4 PARALLEL_COMBINED_WITH_PARENT Cost (2,1,)
  11    3    2          TABLE ACCESS (analyzed) WWCIW_SAP TEMP_SLORDT (full) (Columns 0  Cost (891,1,29)

---

This is where i go crazy.. why on earth the run time plan is changing..


Abhay.

execution plan and run time execution plan can differ, it happened to me quite a few times , I think the real plan you can see in the tkprof file and v$sql_plan, what it happened to me was I had cursor_sharing set to FORCE

Yes i know... Run time plan is what the exact plan that oracle takes for execution... but my Q is Why does it change when we explain and at run time.. my cursor sharing is exact.. btw, i execute as SQL rather then in a PL/SQL block.

Abhay.

Pinging the thread once more.. if some one can throw logical input as to why Oracle behaves erratic..

Abhay.

Not sure to be honest. It *might* help to see TKPROF output for this SQL.

As far as I have observed, I have had experinces with such SQL going for Hash Aj, where as if put the same code in DML ( like Update and Merge ) it goes for filter.. Sounds like a bug or is that am missing some point..

Abhay.

have you tried raising a TAR?

Once you get past the numpty at 1st line support, might find someone intelligent who could help

Try it on 10.1.0.3, we had reports of plan wierdness in 10.1.0.2 (I think it was with merge cartesian joins though)

After you create those tables, analyze them, then does your merge consistently explain the same way?
This is a good one for Tom Kyte (asktom.oracle.com). He's got a big brain.

Quote:

---

Originally posted by davey23uk
have you tried raising a TAR?

Once you get past the numpty at 1st line support, might find someone intelligent who could help

---

Will raise a TAR for sure.. but before i do.. i wanted to make sure I didnt miss anything..

Abhay.

Quote:

---

Originally posted by KenEwald
After you create those tables, analyze them, then does your merge consistently explain the same way?
This is a good one for Tom Kyte (asktom.oracle.com). He's got a big brain.

---

All tables are analyzed.. with histograms too..

BTW, i never get a chnage to post in ASKTOM site .. always see the status as

Quote:

---

Sorry I have a large backlog right now, please ask a question later

---

Abhay.

Quote:

---

Originally posted by abhaysk
BTW, i never get a chnage to post in ASKTOM site .. always see the status as

"Sorry I have a large backlog right now, please ask a question later"


Abhay.

---

If any one gets a chance to post in Tom Site... can you post this one?

Rgds
Abhay.