-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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>
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
> 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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
> 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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Pinging the thread once more.. if some one can throw logical input as to why Oracle behaves erratic..
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|