Oracle: 9204
1. That and index information:
create global temporary table tmp_ic_atp_test1 (CTMPCORPID CHAR (4), CTMPCALBODYID CHAR (20), CINVENTORYID CHAR (20), VFREE1 VARCHAR2 (20), VFREE2 VARCHAR2 (20), VFREE3 VARCHAR2 (20), VFREE4 VARCHAR2 (20), VFREE5 VARCHAR2 (20), vfree7 varchar2 (20), vFree8 varcha2 (20), vfree9 varchar2 (20), vfree10 varchar2 (20), dplandate char (10), usableamount char (16), usableamountbyfree char (1 ), TS char (19)) on commit preserve rows;
CREATE INDEX I_BD_BD_PRODUCE1 On BD_Produce (pk_invmandoc, pk_calbody);
CREATE INEDX I_TMP_IC_ATP_TEST1 ON TMP_IC_ATP_TEST1 (CINVENTORYID, CTMPCALBODYID);
INSERT INTO TMP_IC_ATP_TEST1 (CTMPCORPID, CTMPCALBODYID, CINVENTORYID) VALUES ('1001', '1001AA100000000000015U', '0001AA1000000000200L');
SELECT Count (*) from bd_produve = 3178823size:
476.21M
2. SQL's original execution plan:
Analyze Table BD_Produce, Index I_BD_Produce1
select bd_produce.pk_corp, bd_produce.pk_calbody ccalbodyid, bd_produce.pk_invmandoccinventoryid, bd_produce.safetystocknum nsafenum, bd_produce.usableamount, bd_produce.usableamountbyfree from tmp_ic_atp_test1, bd_producewhere tmp_ic_atp_test1.cinventoryid = bd_produce.pk_invmandocand tmp_ic_atp_test1.ctmpcalbodyid = bd_produce.pk_calbody
We found that under CBO, the SQL execution time is very long. Observe the SQL execution plan and found a full table scan for Table BD_Produce:
SELECT STATEMENT, GOAL = CHOOSE 8511 1060318 HASH JOIN 8511 1060318 INDEX FAST FULL SCAN IND_TMP_IC_ATP_TEST1 4 719840 NON-UNIQUE TABLE ACCESS FULL BD_PRODUCE 5755 196450720 - where a large table full table scan attempts to rewrite the sql:
SELECT BD_PRODUCE.PK_CORP, BD_PRODUCE.PK_CALBODY CCALBODYID,
BD_PRODUCE.PK_INVMANDOC CINVENTORYID, BD_PRODUCE.SAFESTOCKNUM NSAFENUM,
BD_PRODUCE.USABLEAMOUNT, BD_PRODUCE.USABLEAMOUNTBYFREE
From bd_produce
WHERE (PK_INVMANDOC, PK_CALBODY)
in (SELECT TMP_IC_ATP_TEST1.CINVENTORYID, TMP_IC_ATP_TEST1.CTMPCALBODYID from TMP_IC_ATP_TEST1)
The implementation plan is still: Table Access Full BD_PRODUCE
Select Statement, Goal = Choose 8511 1060318
Hash join 8511 1060318
Sort unique
Index Fast Full Scan Ind_TMP_IC_ATP_TEST1 4 719840 NON-UNIQUE
Table Access Full BD_Produce 5755 196450720
In RBO:
Select Statement, Goal = Rule
Table Access by Index Rowid BD_PRODUCE
NESTED LOOPS
Table Access Full TMP_IC_ATP_TEST1
Index
Range
SCAN I_BD_PRODUCE1 NON-UNIQUE
It is INDEX access to Table BD_Produce, and the execution time is very fast.
3. Analyze the statistics of the temporary table
After analyzing the temporary table TMP_IC_ATP_TEST1, the implementation plan is finally normal:
Select Statement, Goal = Choose 4 106
Table Access by Index Rowid BD_Produce 3 62
Nested Loops 4 106
Index Full Scan Ind_TMP_IC_ATP_TEST1 44 NON-UNIQUE
Index
Range
SCAN I_BD_PRODUCE1 2 NON-UNIQUE
4. Remote SQL to boot Oracle to use INDEX:
Delete the statistics of the temporary table and rewrite SQL:
SELECT BD_PRODUCE.PK_CORP, BD_PRODUCE.PK_CALBODY CCALBODYID,
BD_PRODUCE.PK_INVMANDOC CINVENTORYID, BD_PRODUCE.SAFESTOCKNUM NSAFENUM,
BD_PRODUCE.USABLEAMOUNT, BD_PRODUCE.USABLEAMOUNTBYFREE from BD_Produce, TMP_IC_ATP_TEST1WHERE TMP_IC_ATP_TEST1.CINVENVENTORYID = BD_PRODUCE.PK_INVMANDOC
And TMP_IC_ATP_TEST1 .ctMpCalbodyid = BD_Produce.pk_calbody
And bd_produce.pk_invmandoc> chr (0) - Add a condition that makes Oracle use Index
Select Statement, Goal = Choose 1640 53000
Table Access by Index Rowid BD_Produce 3 62
Nested Loops 1640 53000
Index
Range
Scan Ind_TMP_IC_ATP_TEST1 2 35992 NON-UNIQUE
Index
Range
SCAN I_BD_PRODUCE1 2 NON-UNIQUE
