連線條件是兩個欄位“or”關係的SQL優化
下午發現一條SQL，消耗大量IO。
SQL文字
(部分表名和欄位名做了替換)
SELECT "A3"."apptype",
"A3"."appno",
"A2"."edate",
"A3"."cno",
"A1"."rcode",
"A3"."chnl",
"A1"."pm",
(SELECT SUM("A5"."pm")
FROM "t_p" "A5"
WHERE "A5"."cno" = "A3"."cno"),
"A3"."pdate",
"A3"."sdate",
"A3"."acode"
FROM "t_c" "A3", "t_e" "A2", "t_p" "A1"
WHERE "A2"."etype" = 'CT'
AND "A2"."estate" = '0'
AND "A3"."cno" = "A1"."cno"
AND ("A3"."PRTNO" = "A2"."cno" OR "A3"."cno" = "A2"."cno")
AND "A2"."edate" >= TO_DATE(' 2014-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') - 90
AND "A2"."edate" <= TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A1"."ENDDATE" - "A3"."cvdate" > ADD_MONTHS("A3"."cvdate", 12) - "A3"."cvdate"
AND "A3"."CONTTYPE" = '1'
AND ("A2"."flag1" = '2' OR "A2"."flag1" IS NULL)
AND "A1"."pno" = "A1"."MAINpno"
AND ("A3"."chnl" = '1' OR "A3"."chnl" = '3')
AND EXISTS
(SELECT 1
FROM "t_ag" "A4"
WHERE "A4"."acode" = TRIM("A3"."acode")
AND ("A4"."depttype" = '4' OR "A4"."depttype" = '7'))
執行情況
IN SPID SID MACHINE OBJECT_NAME SQL_EXEC_START EVENT SQL_ID SQL_CHILD_NUMBER
-- --------- ---------- ------------------------- ------------------- ------------------- ------------------------ ------------- ----------------
2 20817 4255 xxxx t_e 2015-01-12 12:42:32 direct path read 0x7p8vx1hrmyn 0
PLAN_HASH_VALUE EXEC AVG_MIN AVG_BUFF_GET AVG_DF_READ_MB AVG_ROWS MIN BUFFER_GET DF_READ_MB SUM_ROWS
--------------- ---------- ---------- ------------ -------------- ---------- ---------- ---------- ---------- ----------
3632503727 1 107.27 462219484 3510012.76 0 107.27 462219484 3510012.76 0
由於連線條件是兩個欄位，or的關係。因此這個條件走了filter。
因此不得不在迴圈裡面對t_e做全表掃描，單次執行物理讀統計到的是3.5T（事後分析這條SQL應返回21萬行，因此實際物理讀遠不止3.5T）。
14:46:45 SQL> select * from table(dbms_xplan.display_cursor('0x7p8vx1hrmyn', null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0x7p8vx1hrmyn, child number 0
-------------------------------------
SELECT "A3"."apptype","A3"."appno","A2"."edate","A3"."CON
TNO","A1"."rcode","A3"."chnl","A1"."pm", (SELECT
SUM("A5"."pm") FROM "t_p" "A5" WHERE
"A5"."cno"="A3"."cno"),"A3"."pdate","A3"."sdate","A3"."A
GENTCODE" FROM "t_c" "A3","t_e" "A2","t_p" "A1" WHERE
"A2"."etype"='CT' AND "A2"."estate"='0' AND
"A3"."cno"="A1"."cno" AND ("A3"."PRTNO"="A2"."cno" OR
"A3"."cno"="A2"."cno") AND "A2"."edate">=TO_DATE('
2014-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')-90 AND
"A2"."edate"<=TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A1"."ENDDATE"-"A3"."cvdate">ADD_MONTHS("A3"."CVALID
ATE",12)-"A3"."cvdate" AND "A3"."CONTTYPE"='1' AND
("A2"."flag1"='2' OR "A2"."flag1" IS NULL) AND
"A1"."pno"="A1"."MAINpno" AND ("A3"."chnl"='1' OR
"A3"."chnl"='3') AND EXISTS (SELECT 1 FROM "t_ag" "A4" WHERE
"A4"."acode"=TRIM("A3"."acode") AND ("A4"."depttype"='4' OR
"A4"."BRA
Plan hash value: 3632503727
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2593K|
| 1 | SORT AGGREGATE | | 1 | 25 | |
| 2 | TABLE ACCESS BY INDEX ROWID | t_p | 2 | 50 | 5 |
|* 3 | INDEX RANGE SCAN | t_p_INDEX_7 | 2 | | 4 |
| 4 | NESTED LOOPS | | 1 | 237 | 2593K|
| 5 | NESTED LOOPS | | 1 | 197 | 2091K|
|* 6 | HASH JOIN | | 5 | 565 | 2091K|
| 7 | SORT UNIQUE | | 88397 | 1035K| 28177 |
|* 8 | VIEW | index$_join$_005 | 88397 | 1035K| 28177 |
|* 9 | HASH JOIN | | | | |
|* 10 | INDEX FAST FULL SCAN | IDX_t_ag_MNGCOM | 88397 | 1035K| 12889 |
| 11 | INDEX FAST FULL SCAN | PK_t_ag | 88397 | 1035K| 13026 |
|* 12 | TABLE ACCESS FULL | t_c | 13M| 1280M| 2062K|
|* 13 | TABLE ACCESS BY INDEX ROWID| t_p | 1 | 84 | 4 |
|* 14 | INDEX RANGE SCAN | t_p_INDEX_7 | 2 | | 3 |
|* 15 | TABLE ACCESS FULL | t_e | 1 | 40 | 502K|
------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('9.2.0')
DB_VERSION('11.2.0.3')
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$20FCF987")
OUTLINE_LEAF(@"SEL$8771BF6C")
UNNEST(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$3")
INDEX_JOIN(@"SEL$8771BF6C" "A4"@"SEL$3" ("t_ag"."MANAGECOM"
"t_ag"."depttype") ("t_ag"."acode"))
FULL(@"SEL$8771BF6C" "A3"@"SEL$1")
INDEX_RS_ASC(@"SEL$8771BF6C" "A1"@"SEL$1" ("t_p"."cno"))
FULL(@"SEL$8771BF6C" "A2"@"SEL$1")
LEADING(@"SEL$8771BF6C" "A4"@"SEL$3" "A3"@"SEL$1" "A1"@"SEL$1" "A2"@"SEL$1")
USE_HASH(@"SEL$8771BF6C" "A3"@"SEL$1")
USE_NL(@"SEL$8771BF6C" "A1"@"SEL$1")
USE_NL(@"SEL$8771BF6C" "A2"@"SEL$1")
INDEX_RS_ASC(@"SEL$2" "A5"@"SEL$2" ("t_p"."cno"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A5"."cno"=:B1)
6 - access("A4"."acode"=TRIM("A3"."acode"))
8 - filter(("A4"."depttype"='4' OR "A4"."depttype"='7'))
9 - access(ROWID=ROWID)
10 - filter(("A4"."depttype"='4' OR "A4"."depttype"='7'))
12 - filter(("A3"."CONTTYPE"='1' AND INTERNAL_FUNCTION("A3"."chnl")))
13 - filter(("A1"."ENDDATE"-"A3"."cvdate">ADD_MONTHS(INTERNAL_FUNCTION(
"A3"."cvdate"),12)-"A3"."cvdate" AND "A1"."pno"="A1"."MAINpno"))
14 - access("A3"."cno"="A1"."cno")
15 - filter(("A2"."etype"='CT' AND "A2"."estate"='0' AND
("A3"."PRTNO"="A2"."cno" OR "A3"."cno"="A2"."cno") AND
"A2"."edate">=TO_DATE(' 2014-07-03 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A2"."edate"<=TO_DATE(' 2014-10-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND ("A2"."flag1"='2' OR
"A2"."flag1" IS NULL)))
Note
-----
- cpu costing is off (consider enabling it)
調整方案
這裡可以做or expand，但是直接加hint不太好加。改為用with as將這兩張表抽出來。
38分鐘結束，物理讀260G
15:34:02 SQL> with x as
15:34:04 2 (
15:34:04 3 select /*+ no_merge no_unnest full(a2@sel$1) full(a2@sel$1_2) full(a3@sel$1) full(a3@sel$1_2)*/
15:34:04 4 "A3"."apptype",
15:34:04 5 "A3"."appno",
15:34:05 6 "A2"."edate",
15:34:05 7 "A3"."cno",
15:34:05 8 "A3"."chnl",
15:34:05 9 "A3"."pdate",
15:34:05 10 "A3"."sdate",
15:34:05 11 "A3"."acode",
15:34:05 12 a3.cvdate
15:34:05 13 from lis."t_c" "A3", lis."t_e" "A2"
15:34:05 14 where "A2"."etype" = 'CT'
15:34:05 15 AND "A2"."estate" = '0'
15:34:06 16 AND ("A3"."PRTNO" = "A2"."cno" OR "A3"."cno" = "A2"."cno")
15:34:06 17 AND "A2"."edate" >= TO_DATE(' 2014-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') - 90
15:34:06 18 AND "A2"."edate" <= TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
15:34:06 19 AND "A3"."CONTTYPE" = '1'
15:34:06 20 AND ("A2"."flag1" = '2' OR "A2"."flag1" IS NULL)
15:34:06 21 AND ("A3"."chnl" = '1' OR "A3"."chnl" = '3')
15:34:06 22 )
15:34:06 23 SELECT /*+ optimizer_features_enable('11.2.0.3') full(a4@sel$4)*/
15:34:06 24 "A3"."apptype",
15:34:07 25 "A3"."appno",
15:34:07 26 "A3"."edate",
15:34:07 27 "A3"."cno",
15:34:07 28 "A1"."rcode",
15:34:07 29 "A3"."chnl",
15:34:07 30 "A1"."pm",
15:34:07 31 (SELECT SUM("A5"."pm") FROM lis."t_p" "A5" WHERE "A5"."cno" = "A3"."cno"),
15:34:07 32 "A3"."pdate",
15:34:07 33 "A3"."sdate",
15:34:07 34 "A3"."acode"
15:34:08 35 FROM x a3, lis."t_p" "A1"
15:34:08 36 WHERE 1=1
15:34:08 37 AND "A3"."cno" = "A1"."cno"
15:34:08 38 AND "A1"."ENDDATE" - "A3"."cvdate" > ADD_MONTHS("A3"."cvdate", 12) - "A3"."cvdate"
15:34:08 39 AND "A1"."pno" = "A1"."MAINpno"
15:34:08 40 AND EXISTS
15:34:08 41 (SELECT 1
15:34:08 42 FROM lis."t_ag" "A4"
15:34:08 43 WHERE "A4"."acode" = TRIM("A3"."acode")
15:34:09 44 AND ("A4"."depttype" = '4' OR "A4"."depttype" = '7'))
15:34:09 45 ;
216169 rows selected.
Elapsed: 00:38:52.07
Execution Plan
----------------------------------------------------------
Plan hash value: 638620289
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 195 | | 9371K (1)| 31:14:18 |
| 1 | SORT AGGREGATE | | 1 | 25 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| t_p | 2 | 50 | | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | t_p_INDEX_7 | 2 | | | 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | | |
| 5 | NESTED LOOPS | | 1 | 195 | | 9371K (1)| 31:14:18 |
|* 6 | HASH JOIN RIGHT SEMI | | 1 | 111 | | 9371K (1)| 31:14:18 |
|* 7 | TABLE ACCESS FULL | t_ag | 80749 | 946K| | 51339 (1)| 00:10:17 |
| 8 | VIEW | | 617K| 58M| | 9320K (1)| 31:04:01 |
| 9 | CONCATENATION | | | | | | |
|* 10 | HASH JOIN | | 587K| 79M| 29M| 4660K (1)| 15:32:01 |
|* 11 | TABLE ACCESS FULL | t_e | 587K| 22M| | 896K (1)| 02:59:20 |
|* 12 | TABLE ACCESS FULL | t_c | 13M| 1262M| | 3691K (1)| 12:18:22 |
|* 13 | HASH JOIN | | 29702 | 4089K| 29M| 4660K (1)| 15:32:01 |
|* 14 | TABLE ACCESS FULL | t_e | 587K| 22M| | 896K (1)| 02:59:20 |
|* 15 | TABLE ACCESS FULL | t_c | 13M| 1262M| | 3691K (1)| 12:18:22 |
|* 16 | INDEX RANGE SCAN | t_p_INDEX_7 | 2 | | | 3 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID| t_p | 1 | 84 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A5"."cno"=:B1)
6 - access("A4"."acode"=TRIM("A3"."acode"))
7 - filter("A4"."depttype"='4' OR "A4"."depttype"='7')
10 - access("A3"."cno"="A2"."cno")
11 - filter("A2"."edate">=TO_DATE(' 2014-07-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A2"."etype"='CT' AND ("A2"."flag1" IS NULL OR "A2"."flag1"='2') AND
"A2"."edate"<=TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A2"."estate"='0')
12 - filter("A3"."CONTTYPE"='1' AND ("A3"."chnl"='1' OR "A3"."chnl"='3'))
13 - access("A3"."PRTNO"="A2"."cno")
filter(LNNVL("A3"."cno"="A2"."cno"))
14 - filter("A2"."edate">=TO_DATE(' 2014-07-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A2"."etype"='CT' AND ("A2"."flag1" IS NULL OR "A2"."flag1"='2') AND
"A2"."edate"<=TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A2"."estate"='0')
15 - filter("A3"."CONTTYPE"='1' AND ("A3"."chnl"='1' OR "A3"."chnl"='3'))
16 - access("A3"."cno"="A1"."cno")
17 - filter("A1"."pno"="A1"."MAINpno" AND
"A1"."ENDDATE"-"A3"."cvdate">ADD_MONTHS(INTERNAL_FUNCTION("A3"."cvdate"),12)-"A3"."CVALI
DATE")
Statistics
----------------------------------------------------------
1 recursive calls
11 db block gets
35805902 consistent gets
34360652 physical reads
0 redo size
21614365 bytes sent via SQL*Net to client
159045 bytes received via SQL*Net from client
14413 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
216169 rows processed
SQL文字
(部分表名和欄位名做了替換)
SELECT "A3"."apptype",
"A3"."appno",
"A2"."edate",
"A3"."cno",
"A1"."rcode",
"A3"."chnl",
"A1"."pm",
(SELECT SUM("A5"."pm")
FROM "t_p" "A5"
WHERE "A5"."cno" = "A3"."cno"),
"A3"."pdate",
"A3"."sdate",
"A3"."acode"
FROM "t_c" "A3", "t_e" "A2", "t_p" "A1"
WHERE "A2"."etype" = 'CT'
AND "A2"."estate" = '0'
AND "A3"."cno" = "A1"."cno"
AND ("A3"."PRTNO" = "A2"."cno" OR "A3"."cno" = "A2"."cno")
AND "A2"."edate" >= TO_DATE(' 2014-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') - 90
AND "A2"."edate" <= TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A1"."ENDDATE" - "A3"."cvdate" > ADD_MONTHS("A3"."cvdate", 12) - "A3"."cvdate"
AND "A3"."CONTTYPE" = '1'
AND ("A2"."flag1" = '2' OR "A2"."flag1" IS NULL)
AND "A1"."pno" = "A1"."MAINpno"
AND ("A3"."chnl" = '1' OR "A3"."chnl" = '3')
AND EXISTS
(SELECT 1
FROM "t_ag" "A4"
WHERE "A4"."acode" = TRIM("A3"."acode")
AND ("A4"."depttype" = '4' OR "A4"."depttype" = '7'))
執行情況
IN SPID SID MACHINE OBJECT_NAME SQL_EXEC_START EVENT SQL_ID SQL_CHILD_NUMBER
-- --------- ---------- ------------------------- ------------------- ------------------- ------------------------ ------------- ----------------
2 20817 4255 xxxx t_e 2015-01-12 12:42:32 direct path read 0x7p8vx1hrmyn 0
PLAN_HASH_VALUE EXEC AVG_MIN AVG_BUFF_GET AVG_DF_READ_MB AVG_ROWS MIN BUFFER_GET DF_READ_MB SUM_ROWS
--------------- ---------- ---------- ------------ -------------- ---------- ---------- ---------- ---------- ----------
3632503727 1 107.27 462219484 3510012.76 0 107.27 462219484 3510012.76 0
由於連線條件是兩個欄位，or的關係。因此這個條件走了filter。
因此不得不在迴圈裡面對t_e做全表掃描，單次執行物理讀統計到的是3.5T（事後分析這條SQL應返回21萬行，因此實際物理讀遠不止3.5T）。
14:46:45 SQL> select * from table(dbms_xplan.display_cursor('0x7p8vx1hrmyn', null, 'OUTLINE'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 0x7p8vx1hrmyn, child number 0
-------------------------------------
SELECT "A3"."apptype","A3"."appno","A2"."edate","A3"."CON
TNO","A1"."rcode","A3"."chnl","A1"."pm", (SELECT
SUM("A5"."pm") FROM "t_p" "A5" WHERE
"A5"."cno"="A3"."cno"),"A3"."pdate","A3"."sdate","A3"."A
GENTCODE" FROM "t_c" "A3","t_e" "A2","t_p" "A1" WHERE
"A2"."etype"='CT' AND "A2"."estate"='0' AND
"A3"."cno"="A1"."cno" AND ("A3"."PRTNO"="A2"."cno" OR
"A3"."cno"="A2"."cno") AND "A2"."edate">=TO_DATE('
2014-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')-90 AND
"A2"."edate"<=TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A1"."ENDDATE"-"A3"."cvdate">ADD_MONTHS("A3"."CVALID
ATE",12)-"A3"."cvdate" AND "A3"."CONTTYPE"='1' AND
("A2"."flag1"='2' OR "A2"."flag1" IS NULL) AND
"A1"."pno"="A1"."MAINpno" AND ("A3"."chnl"='1' OR
"A3"."chnl"='3') AND EXISTS (SELECT 1 FROM "t_ag" "A4" WHERE
"A4"."acode"=TRIM("A3"."acode") AND ("A4"."depttype"='4' OR
"A4"."BRA
Plan hash value: 3632503727
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2593K|
| 1 | SORT AGGREGATE | | 1 | 25 | |
| 2 | TABLE ACCESS BY INDEX ROWID | t_p | 2 | 50 | 5 |
|* 3 | INDEX RANGE SCAN | t_p_INDEX_7 | 2 | | 4 |
| 4 | NESTED LOOPS | | 1 | 237 | 2593K|
| 5 | NESTED LOOPS | | 1 | 197 | 2091K|
|* 6 | HASH JOIN | | 5 | 565 | 2091K|
| 7 | SORT UNIQUE | | 88397 | 1035K| 28177 |
|* 8 | VIEW | index$_join$_005 | 88397 | 1035K| 28177 |
|* 9 | HASH JOIN | | | | |
|* 10 | INDEX FAST FULL SCAN | IDX_t_ag_MNGCOM | 88397 | 1035K| 12889 |
| 11 | INDEX FAST FULL SCAN | PK_t_ag | 88397 | 1035K| 13026 |
|* 12 | TABLE ACCESS FULL | t_c | 13M| 1280M| 2062K|
|* 13 | TABLE ACCESS BY INDEX ROWID| t_p | 1 | 84 | 4 |
|* 14 | INDEX RANGE SCAN | t_p_INDEX_7 | 2 | | 3 |
|* 15 | TABLE ACCESS FULL | t_e | 1 | 40 | 502K|
------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('9.2.0')
DB_VERSION('11.2.0.3')
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$20FCF987")
OUTLINE_LEAF(@"SEL$8771BF6C")
UNNEST(@"SEL$3")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$3")
INDEX_JOIN(@"SEL$8771BF6C" "A4"@"SEL$3" ("t_ag"."MANAGECOM"
"t_ag"."depttype") ("t_ag"."acode"))
FULL(@"SEL$8771BF6C" "A3"@"SEL$1")
INDEX_RS_ASC(@"SEL$8771BF6C" "A1"@"SEL$1" ("t_p"."cno"))
FULL(@"SEL$8771BF6C" "A2"@"SEL$1")
LEADING(@"SEL$8771BF6C" "A4"@"SEL$3" "A3"@"SEL$1" "A1"@"SEL$1" "A2"@"SEL$1")
USE_HASH(@"SEL$8771BF6C" "A3"@"SEL$1")
USE_NL(@"SEL$8771BF6C" "A1"@"SEL$1")
USE_NL(@"SEL$8771BF6C" "A2"@"SEL$1")
INDEX_RS_ASC(@"SEL$2" "A5"@"SEL$2" ("t_p"."cno"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A5"."cno"=:B1)
6 - access("A4"."acode"=TRIM("A3"."acode"))
8 - filter(("A4"."depttype"='4' OR "A4"."depttype"='7'))
9 - access(ROWID=ROWID)
10 - filter(("A4"."depttype"='4' OR "A4"."depttype"='7'))
12 - filter(("A3"."CONTTYPE"='1' AND INTERNAL_FUNCTION("A3"."chnl")))
13 - filter(("A1"."ENDDATE"-"A3"."cvdate">ADD_MONTHS(INTERNAL_FUNCTION(
"A3"."cvdate"),12)-"A3"."cvdate" AND "A1"."pno"="A1"."MAINpno"))
14 - access("A3"."cno"="A1"."cno")
15 - filter(("A2"."etype"='CT' AND "A2"."estate"='0' AND
("A3"."PRTNO"="A2"."cno" OR "A3"."cno"="A2"."cno") AND
"A2"."edate">=TO_DATE(' 2014-07-03 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "A2"."edate"<=TO_DATE(' 2014-10-31 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND ("A2"."flag1"='2' OR
"A2"."flag1" IS NULL)))
Note
-----
- cpu costing is off (consider enabling it)
調整方案
這裡可以做or expand，但是直接加hint不太好加。改為用with as將這兩張表抽出來。
38分鐘結束，物理讀260G
15:34:02 SQL> with x as
15:34:04 2 (
15:34:04 3 select /*+ no_merge no_unnest full(a2@sel$1) full(a2@sel$1_2) full(a3@sel$1) full(a3@sel$1_2)*/
15:34:04 4 "A3"."apptype",
15:34:04 5 "A3"."appno",
15:34:05 6 "A2"."edate",
15:34:05 7 "A3"."cno",
15:34:05 8 "A3"."chnl",
15:34:05 9 "A3"."pdate",
15:34:05 10 "A3"."sdate",
15:34:05 11 "A3"."acode",
15:34:05 12 a3.cvdate
15:34:05 13 from lis."t_c" "A3", lis."t_e" "A2"
15:34:05 14 where "A2"."etype" = 'CT'
15:34:05 15 AND "A2"."estate" = '0'
15:34:06 16 AND ("A3"."PRTNO" = "A2"."cno" OR "A3"."cno" = "A2"."cno")
15:34:06 17 AND "A2"."edate" >= TO_DATE(' 2014-10-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') - 90
15:34:06 18 AND "A2"."edate" <= TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
15:34:06 19 AND "A3"."CONTTYPE" = '1'
15:34:06 20 AND ("A2"."flag1" = '2' OR "A2"."flag1" IS NULL)
15:34:06 21 AND ("A3"."chnl" = '1' OR "A3"."chnl" = '3')
15:34:06 22 )
15:34:06 23 SELECT /*+ optimizer_features_enable('11.2.0.3') full(a4@sel$4)*/
15:34:06 24 "A3"."apptype",
15:34:07 25 "A3"."appno",
15:34:07 26 "A3"."edate",
15:34:07 27 "A3"."cno",
15:34:07 28 "A1"."rcode",
15:34:07 29 "A3"."chnl",
15:34:07 30 "A1"."pm",
15:34:07 31 (SELECT SUM("A5"."pm") FROM lis."t_p" "A5" WHERE "A5"."cno" = "A3"."cno"),
15:34:07 32 "A3"."pdate",
15:34:07 33 "A3"."sdate",
15:34:07 34 "A3"."acode"
15:34:08 35 FROM x a3, lis."t_p" "A1"
15:34:08 36 WHERE 1=1
15:34:08 37 AND "A3"."cno" = "A1"."cno"
15:34:08 38 AND "A1"."ENDDATE" - "A3"."cvdate" > ADD_MONTHS("A3"."cvdate", 12) - "A3"."cvdate"
15:34:08 39 AND "A1"."pno" = "A1"."MAINpno"
15:34:08 40 AND EXISTS
15:34:08 41 (SELECT 1
15:34:08 42 FROM lis."t_ag" "A4"
15:34:08 43 WHERE "A4"."acode" = TRIM("A3"."acode")
15:34:09 44 AND ("A4"."depttype" = '4' OR "A4"."depttype" = '7'))
15:34:09 45 ;
216169 rows selected.
Elapsed: 00:38:52.07
Execution Plan
----------------------------------------------------------
Plan hash value: 638620289
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 195 | | 9371K (1)| 31:14:18 |
| 1 | SORT AGGREGATE | | 1 | 25 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| t_p | 2 | 50 | | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | t_p_INDEX_7 | 2 | | | 4 (0)| 00:00:01 |
| 4 | NESTED LOOPS | | | | | | |
| 5 | NESTED LOOPS | | 1 | 195 | | 9371K (1)| 31:14:18 |
|* 6 | HASH JOIN RIGHT SEMI | | 1 | 111 | | 9371K (1)| 31:14:18 |
|* 7 | TABLE ACCESS FULL | t_ag | 80749 | 946K| | 51339 (1)| 00:10:17 |
| 8 | VIEW | | 617K| 58M| | 9320K (1)| 31:04:01 |
| 9 | CONCATENATION | | | | | | |
|* 10 | HASH JOIN | | 587K| 79M| 29M| 4660K (1)| 15:32:01 |
|* 11 | TABLE ACCESS FULL | t_e | 587K| 22M| | 896K (1)| 02:59:20 |
|* 12 | TABLE ACCESS FULL | t_c | 13M| 1262M| | 3691K (1)| 12:18:22 |
|* 13 | HASH JOIN | | 29702 | 4089K| 29M| 4660K (1)| 15:32:01 |
|* 14 | TABLE ACCESS FULL | t_e | 587K| 22M| | 896K (1)| 02:59:20 |
|* 15 | TABLE ACCESS FULL | t_c | 13M| 1262M| | 3691K (1)| 12:18:22 |
|* 16 | INDEX RANGE SCAN | t_p_INDEX_7 | 2 | | | 3 (0)| 00:00:01 |
|* 17 | TABLE ACCESS BY INDEX ROWID| t_p | 1 | 84 | | 4 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A5"."cno"=:B1)
6 - access("A4"."acode"=TRIM("A3"."acode"))
7 - filter("A4"."depttype"='4' OR "A4"."depttype"='7')
10 - access("A3"."cno"="A2"."cno")
11 - filter("A2"."edate">=TO_DATE(' 2014-07-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A2"."etype"='CT' AND ("A2"."flag1" IS NULL OR "A2"."flag1"='2') AND
"A2"."edate"<=TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A2"."estate"='0')
12 - filter("A3"."CONTTYPE"='1' AND ("A3"."chnl"='1' OR "A3"."chnl"='3'))
13 - access("A3"."PRTNO"="A2"."cno")
filter(LNNVL("A3"."cno"="A2"."cno"))
14 - filter("A2"."edate">=TO_DATE(' 2014-07-03 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
AND "A2"."etype"='CT' AND ("A2"."flag1" IS NULL OR "A2"."flag1"='2') AND
"A2"."edate"<=TO_DATE(' 2014-10-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A2"."estate"='0')
15 - filter("A3"."CONTTYPE"='1' AND ("A3"."chnl"='1' OR "A3"."chnl"='3'))
16 - access("A3"."cno"="A1"."cno")
17 - filter("A1"."pno"="A1"."MAINpno" AND
"A1"."ENDDATE"-"A3"."cvdate">ADD_MONTHS(INTERNAL_FUNCTION("A3"."cvdate"),12)-"A3"."CVALI
DATE")
Statistics
----------------------------------------------------------
1 recursive calls
11 db block gets
35805902 consistent gets
34360652 physical reads
0 redo size
21614365 bytes sent via SQL*Net to client
159045 bytes received via SQL*Net from client
14413 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
216169 rows processed
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26239116/viewspace-1399756/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL優化--多表連線和走索引的關係SQL優化索引
- 外連線有 OR 關聯條件只能走 NL優化優化
- ORACLE SQL過濾條件是IS NULL or !=的優化OracleSQLNull優化
- 資料庫外連線，自然連線，內連線，條件連線，等值連線關係及詳解資料庫
- T-SQL——關於Join on的的連線條件和where的篩選條件的區分SQL
- 通過新增條件優化SQL優化SQL
- 查詢/刪除重複的資料(單個欄位和多個欄位條件)
- 關於外連線和where條件
- v$session與v$sql連線現在使用哪個欄位？SessionSQL
- SQL效能第1篇：關係優化SQL優化
- SQL最佳化-關於ORDERED-HASH中錯誤選擇連線欄位對效能的影響SQL
- 優化 JS 條件語句的 5 個技巧優化JS
- 同一欄位多個查詢條件時遇到的一個問題
- varchar or blob：欄位型別的儲存和溢位條件型別
- 【學習】= 概念 + 關係 + 關係發生的過程和條件
- RMAN連線與oracle連線模式的關係Oracle模式
- Laravel 一條 SQL 如何 count 多個欄位，Laravel 一條 sql 查詢每個分類的數量LaravelSQL
- 一個NOT EXISTS含有OR條件子查詢的優化優化
- 多表連線SQL優化如何處理SQL優化
- Java 條件表示式的優化Java優化
- 什麼是Socket連線？它與TCP連線有什麼關係TCP
- 【SQL 效能優化】表的三種連線方式SQL優化
- SQL優化34條SQL優化
- Gridview繫結資料庫的欄位，根據條件欄位顏色改變View資料庫
- [20170703]11g增加欄位與查詢條件的變化
- 關聯線探究，如何連線流程圖的兩個節點流程圖
- Oracle 是分割槽表，但條件不帶分割槽條件的SQLOracleSQL
- MySQL 針對 like 條件的優化MySql優化
- SQL優化--用各種hints優化一條SQLSQL優化
- 【Django drf】 序列化類常用欄位類和欄位引數 定製序列化欄位的兩種方式 關係表外來鍵欄位的反序列化儲存 序列化類繼承ModelSerializer 反序列化資料校驗原始碼分析Django繼承原始碼
- 一條sql的優化過程SQL優化
- 一條sql語句的優化SQL優化
- 表的連線是指在一個SQL語句中通過表與表之間的關連SQL
- Oracle 連線條件中帶有OR的測試Oracle
- Linq兩個from查詢條件
- 「大部分公司做AI教育思路是反的」,做好教育需要這兩個關鍵條件AI
- 【SQL】SQL中if條件的使用SQL
- 兩個類的關係，應該如何關聯？