大家好,执行计划中E-rows和A-rows差距很大,请问是什么原因引起的。
具体SQL和执行计划如下:
(一)SQL语句
SELECT *
FROM (
SELECT
"Distinct1"."C1" AS "C1",
"Distinct1"."ID2" AS "ID",
"Distinct1"."ID" AS "ID1",
"Distinct1"."PNR" AS "PNR",
"Distinct1"."ORDER_SERIAL_NUMBER" AS "ORDER_SERIAL_NUMBER",
"Distinct1"."SERIAL_NUMBER" AS "SERIAL_NUMBER",
"Distinct1"."TYPE" AS "TYPE",
"Distinct1"."ID1" AS "ID2",
"Distinct1"."TAKEOFF_TIME" AS "TAKEOFF_TIME",
"Distinct1"."FLIGHTNO" AS "FLIGHTNO",
"Distinct1"."DEPARTURE" AS "DEPARTURE",
"Distinct1"."ARRIVAL" AS "ARRIVAL",
"Distinct1"."TICKETNO1" AS "TICKETNO",
"Distinct1"."PASSENGER_NAME" AS "PASSENGER_NAME",
"Distinct1"."COMPANY_NAME" AS "COMPANY_NAME",
"Distinct1"."RECEIVE_NAME" AS "RECEIVE_NAME",
"Distinct1"."RECEIVE_PHONE" AS "RECEIVE_PHONE",
"Distinct1"."RECEIVE_ADDRESS" AS "RECEIVE_ADDRESS",
"Distinct1"."EXPRESS_TYPE" AS "EXPRESS_TYPE",
"Distinct1"."EXPRESS_NO" AS "EXPRESS_NO",
"Distinct1"."TICKETNO" AS "TICKETNO1",
"Distinct1"."CHECK_TIME" AS "CHECK_TIME",
"Distinct1"."CHECK_USER" AS "CHECK_USER",
"Distinct1"."STATUS" AS "STATUS",
"Distinct1"."ISINTERNATIONAL" AS "ISINTERNATIONAL",
"Distinct1"."AIRLINE" AS "AIRLINE",
"Distinct1"."AIRLINE_NAME" AS "AIRLINE_NAME"
FROM ( SELECT "Distinct1"."ID" AS "ID", "Distinct1"."TYPE" AS "TYPE", "Distinct1"."RECEIVE_NAME" AS "RECEIVE_NAME", "Distinct1"."RECEIVE_PHONE" AS "RECEIVE_PHONE", "Distinct1"."RECEIVE_ADDRESS" AS "RECEIVE_ADDRESS", "Distinct1"."COMPANY_NAME" AS "COMPANY_NAME", "Distinct1"."TICKETNO" AS "TICKETNO", "Distinct1"."STATUS" AS "STATUS", "Distinct1"."CHECK_USER" AS "CHECK_USER", "Distinct1"."CHECK_TIME" AS "CHECK_TIME", "Distinct1"."EXPRESS_TYPE" AS "EXPRESS_TYPE", "Distinct1"."EXPRESS_NO" AS "EXPRESS_NO", "Distinct1"."ORDER_SERIAL_NUMBER" AS "ORDER_SERIAL_NUMBER", "Distinct1"."ISINTERNATIONAL" AS "ISINTERNATIONAL", "Distinct1"."AIRLINE" AS "AIRLINE", "Distinct1"."PNR" AS "PNR", "Distinct1"."ID1" AS "ID1", "Distinct1"."DEPARTURE" AS "DEPARTURE", "Distinct1"."ARRIVAL" AS "ARRIVAL", "Distinct1"."TAKEOFF_TIME" AS "TAKEOFF_TIME", "Distinct1"."FLIGHTNO" AS "FLIGHTNO", "Distinct1"."ID2" AS "ID2", "Distinct1"."TICKETNO1" AS "TICKETNO1", "Distinct1"."PASSENGER_NAME" AS "PASSENGER_NAME", "Distinct1"."SERIAL_NUMBER" AS "SERIAL_NUMBER", "Distinct1"."AIRLINE_NAME" AS "AIRLINE_NAME", "Distinct1"."C1" AS "C1", row_number() OVER (ORDER BY "Distinct1"."ORDER_SERIAL_NUMBER" DESC, "Distinct1"."TICKETNO1" ASC) AS "row_number"
FROM ( SELECT DISTINCT
"Extent1"."ID" AS "ID",
"Extent1"."TYPE" AS "TYPE",
"Extent1"."RECEIVE_NAME" AS "RECEIVE_NAME",
"Extent1"."RECEIVE_PHONE" AS "RECEIVE_PHONE",
"Extent1"."RECEIVE_ADDRESS" AS "RECEIVE_ADDRESS",
"Extent1"."COMPANY_NAME" AS "COMPANY_NAME",
"Extent1"."TICKETNO" AS "TICKETNO",
"Extent1"."STATUS" AS "STATUS",
"Extent1"."CHECK_USER" AS "CHECK_USER",
"Extent1"."CHECK_TIME" AS "CHECK_TIME",
"Extent1"."EXPRESS_TYPE" AS "EXPRESS_TYPE",
"Extent1"."EXPRESS_NO" AS "EXPRESS_NO",
"Extent3"."ORDER_SERIAL_NUMBER" AS "ORDER_SERIAL_NUMBER",
"Extent3"."ISINTERNATIONAL" AS "ISINTERNATIONAL",
"Extent3"."AIRLINE" AS "AIRLINE",
"Extent4"."PNR" AS "PNR",
"Extent5"."ID" AS "ID1",
"Extent5"."DEPARTURE" AS "DEPARTURE",
"Extent5"."ARRIVAL" AS "ARRIVAL",
"Extent5"."TAKEOFF_TIME" AS "TAKEOFF_TIME",
"Extent5"."FLIGHTNO" AS "FLIGHTNO",
"Extent6"."ID" AS "ID2",
"Extent6"."TICKETNO" AS "TICKETNO1",
"Extent7"."PASSENGER_NAME" AS "PASSENGER_NAME",
"Extent9"."SERIAL_NUMBER" AS "SERIAL_NUMBER",
"Extent10"."AIRLINE_NAME" AS "AIRLINE_NAME",
1 AS "C1"
FROM "B2C_OW_DBUSER"."T_OD_EXPRESS" "Extent1"
INNER JOIN "B2C_OW_DBUSER"."T_OD_TICKET_ORDER_SUBORDER" "Extent2" ON "Extent1"."ID" = "Extent2"."SUBORDER_ID"
INNER JOIN "B2C_OW_DBUSER"."T_OD_TICKET_ORDER_INFO" "Extent3" ON "Extent2"."ORDERID" = "Extent3"."ID"
INNER JOIN "B2C_OW_DBUSER"."T_OD_PNR_INFO" "Extent4" ON "Extent3"."ID" = "Extent4"."ORDERID"
INNER JOIN "B2C_OW_DBUSER"."T_OD_PNR_LEG" "Extent5" ON "Extent4"."ID" = "Extent5"."PNRID"
INNER JOIN "B2C_OW_DBUSER"."T_OD_PNR_TICKET" "Extent6" ON "Extent5"."ID" = "Extent6"."LEGID"
INNER JOIN "B2C_OW_DBUSER"."T_OD_PNR_PASSENGER" "Extent7" ON "Extent6"."PASSENGERID" = "Extent7"."ID"
INNER JOIN "B2C_OW_DBUSER"."T_PY_ORDERPAY_RELATION" "Extent8" ON "Extent3"."ID" = "Extent8"."ORDERID"
INNER JOIN "B2C_OW_DBUSER"."T_PY_PAY_ORDER" "Extent9" ON "Extent8"."PAYID" = "Extent9"."ID"
INNER JOIN "B2C_OW_DBUSER"."T_BD_AIRLINES" "Extent10" ON "Extent3"."AIRLINE" = "Extent10"."AIRLINE_CODE"
WHERE ((N'ItineraryReceipt' = "Extent2"."SUBORDER_TYPE") AND (N'FinishTicket' = "Extent3"."STATUS") AND (('DR' = "Extent3"."AIRLINE") OR ('QD' = "Extent3"."AIRLINE")) AND ( NOT ((N'CP' = "Extent4"."TYPE") AND ("Extent4"."TYPE" IS NOT NULL))) AND ( NOT ((N'NC' = "Extent4"."TYPE") AND ("Extent4"."TYPE" IS NOT NULL))) AND (N'T' = "Extent6"."TICKET_STATUS") AND ("Extent7"."ISACTIVE" = 1) AND ('BookTicket' = "Extent8"."PAYTYPE_PURPOSE"))
) "Distinct1"
) "Distinct1"
WHERE ("Distinct1"."row_number" > 0)
ORDER BY "Distinct1"."ORDER_SERIAL_NUMBER" DESC, "Distinct1"."TICKETNO1" ASC
)
WHERE (ROWNUM <= (20) );
(二)执行计划
Plan hash value: 920920885
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 20 |00:00:01.67 | 177K| | | |
|* 1 | COUNT STOPKEY | | 1 | | 20 |00:00:01.67 | 177K| | | |
| 2 | VIEW | | 1 | 9785 | 20 |00:00:01.67 | 177K| | | |
|* 3 | VIEW | | 1 | 9785 | 20 |00:00:01.67 | 177K| | | |
| 4 | WINDOW SORT | | 1 | 9785 | 20 |00:00:01.67 | 177K| 6786K| 1045K| 6031K (0)|
| 5 | VIEW | | 1 | 9785 | 9785 |00:00:01.65 | 177K| | | |
| 6 | HASH UNIQUE | | 1 | 9785 | 9785 |00:00:01.65 | 177K| 18M| 3655K| 7056K (0)|
| 7 | NESTED LOOPS | | 1 | 26 | 9785 |00:00:01.61 | 177K| | | |
| 8 | NESTED LOOPS | | 1 | 26 | 9785 |00:00:01.57 | 149K| | | |
| 9 | NESTED LOOPS | | 1 | 26 | 9785 |00:00:01.53 | 121K| | | |
|* 10 | HASH JOIN | | 1 | 26 | 9785 |00:00:01.47 | 98335 | 797K| 797K| 539K (0)|
| 11 | INLIST ITERATOR | | 1 | | 2 |00:00:00.01 | 3 | | | |
| 12 | TABLE ACCESS BY INDEX ROWID | T_BD_AIRLINES | 2 | 2 | 2 |00:00:00.01 | 3 | | | |
|* 13 | INDEX UNIQUE SCAN | UN_AIRLINE_CODE | 2 | 2 | 2 |00:00:00.01 | 2 | | | |
| 14 | NESTED LOOPS | | 1 | 26 | 9785 |00:00:01.46 | 98332 | | | |
| 15 | NESTED LOOPS | | 1 | 26 | 9785 |00:00:01.45 | 87905 | | | |
| 16 | NESTED LOOPS | | 1 | 26 | 9785 |00:00:01.43 | 85691 | | | |
| 17 | NESTED LOOPS | | 1 | 1631 | 6126 |00:00:01.37 | 60662 | | | |
| 18 | NESTED LOOPS | | 1 | 1615 | 5730 |00:00:01.33 | 43873 | | | |
|* 19 | HASH JOIN | | 1 | 6358 | 5436 |00:00:01.28 | 27490 | 1753K| 1753K| 1543K (0)|
|* 20 | TABLE ACCESS FULL | T_OD_TICKET_ORDER_SUBORDER | 1 | 6358 | 6358 |00:00:00.01 | 45 | | | |
|* 21 | TABLE ACCESS FULL | T_OD_TICKET_ORDER_INFO | 1 | 17178 | 1329K|00:00:00.98 | 27445 | | | |
|* 22 | TABLE ACCESS BY INDEX ROWID| T_OD_PNR_INFO | 5436 | 1 | 5730 |00:00:00.04 | 16383 | | | |
|* 23 | INDEX RANGE SCAN | IDX_T_OD_PNR_INFO_ORDERID | 5436 | 1 | 5731 |00:00:00.02 | 10901 | | | |
| 24 | TABLE ACCESS BY INDEX ROWID | T_OD_PNR_LEG | 5730 | 1 | 6126 |00:00:00.04 | 16789 | | | |
|* 25 | INDEX RANGE SCAN | IDX_T_OD_PNR_LEG_PNRID | 5730 | 1 | 6126 |00:00:00.02 | 11478 | | | |
|* 26 | TABLE ACCESS BY INDEX ROWID | T_OD_PNR_TICKET | 6126 | 1 | 9785 |00:00:00.06 | 25029 | | | |
|* 27 | INDEX RANGE SCAN | IDX_T_OD_PNR_TICKET_LEGID | 6126 | 2 | 10551 |00:00:00.02 | 12298 | | | |
|* 28 | INDEX UNIQUE SCAN | PK_T_OD_EXPRESS | 9785 | 1 | 9785 |00:00:00.01 | 2214 | | | |
| 29 | TABLE ACCESS BY INDEX ROWID | T_OD_EXPRESS | 9785 | 1 | 9785 |00:00:00.01 | 10427 | | | |
|* 30 | TABLE ACCESS BY INDEX ROWID | T_PY_ORDERPAY_RELATION | 9785 | 1 | 9785 |00:00:00.06 | 23067 | | | |
|* 31 | INDEX RANGE SCAN | IDX_ORDERPAY_RELATION_ORDERID | 9785 | 1 | 9940 |00:00:00.03 | 18039 | | | |
| 32 | TABLE ACCESS BY INDEX ROWID | T_PY_PAY_ORDER | 9785 | 1 | 9785 |00:00:00.03 | 28327 | | | |
|* 33 | INDEX UNIQUE SCAN | PK_WEB_PAY_RECNO | 9785 | 1 | 9785 |00:00:00.02 | 18196 | | | |
|* 34 | TABLE ACCESS BY INDEX ROWID | T_OD_PNR_PASSENGER | 9785 | 1 | 9785 |00:00:00.04 | 28128 | | | |
|* 35 | INDEX UNIQUE SCAN | PK_T_OD_PNR_PASSENGER | 9785 | 1 | 9785 |00:00:00.02 | 18343 | | | |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - filter("Distinct1"."row_number">0)
10 - access(NVL("Extent3"."AIRLINE",'DR')="Extent10"."AIRLINE_CODE")
13 - access(("Extent10"."AIRLINE_CODE"='DR' OR "Extent10"."AIRLINE_CODE"='QD'))
19 - access("Extent2"."ORDERID"="Extent3"."ID")
20 - filter(SYS_OP_C2C("Extent2"."SUBORDER_TYPE")=U'ItineraryReceipt')
21 - filter((SYS_OP_C2C("Extent3"."STATUS")=U'FinishTicket' AND NVL("Extent3"."AIRLINE",'DR')))
22 - filter(((SYS_OP_C2C("Extent4"."TYPE")<>U'CP' OR "Extent4"."TYPE" IS NULL) AND (SYS_OP_C2C("Extent4"."TYPE")<>U'NC' OR "Extent4"."TYPE" IS NULL)))
23 - access("Extent3"."ID"="Extent4"."ORDERID")
25 - access("Extent4"."ID"="Extent5"."PNRID")
26 - filter(SYS_OP_C2C("Extent6"."TICKET_STATUS")=U'T')
27 - access("Extent5"."ID"="Extent6"."LEGID")
28 - access("Extent1"."ID"="Extent2"."SUBORDER_ID")
30 - filter("Extent8"."PAYTYPE_PURPOSE"='BookTicket')
31 - access("Extent3"."ID"="Extent8"."ORDERID")
33 - access("Extent8"."PAYID"="Extent9"."ID")
34 - filter("Extent7"."ISACTIVE"=1)
35 - access("Extent6"."PASSENGERID"="Extent7"."ID")
Note
-----
- cardinality feedback used for this statement
(三)问题表信息
TABLE_NAME NUM_ROWS PARTITIONED BLOCKS TABLE_SIZE_MB EMPTY_BLOCKS CHAIN_CNT AVG_ROW_LEN GLOBAL_STATS SAMPLE_SIZE TO_CHAR(T.LAST_ANALYZED,'MM-DD-YYYY')
------------------------------ ---------- ----------- ---------- ------------- ------------ ---------- ----------- ------------ ----------- -------------------------------------
T_OD_TICKET_ORDER_INFO 1720360 NO 27459 214.52 0 0 113 YES 344072 11-14-2019
Executed in 0.058 seconds
COLUMN_NAME DATA_TYPE NUM_DISTINCT DENSITY NUM_BUCKETS NUM_NULLS SAMPLE_SIZE TO_CHAR(T.LAST_ANALYZED,'MM-DD-YYYY')
------------------------------ --------------- ------------ ---------- ----------- ---------- ----------- -------------------------------------
LANGUAGECODE VARCHAR2 2 0.5 1 0 343039 11-14-2019
ORDER_TYPE VARCHAR2 1 1 1 1714225 2 11-14-2019
PLATFORM_ID NUMBER 2 0.5 1 1714235 5 11-14-2019
SENDMSGLIMIT DATE 1179245 8.48000203 1 528170 237405 11-14-2019
BOOKSYSTEM VARCHAR2 2 0.5 1 1658545 11330 11-14-2019
CANCEL_TYPE VARCHAR2 3 0.33333333 1 1436655 55708 11-14-2019
ISINTERNATIONAL NUMBER 2 2.91512043 2 0 343039 11-14-2019
IP VARCHAR2 16102 6.21040864 1 1048625 133314 11-14-2019
LAST_MODIFY_TIME DATE 1704575 5.90311909 254 0 343039 11-14-2019
AIRLINE VARCHAR2 2 2.91512043 2 0 343039 11-14-2019
ID NUMBER 1714235 5.83350590 1 0 343039 11-14-2019
ORDER_SERIAL_NUMBER VARCHAR2 1714235 5.83350590 254 0 343039 11-14-2019
USERID NUMBER 29808 0.00117647 254 0 343039 11-14-2019
CREATE_TIME DATE 1705770 5.89499508 254 0 343039 11-14-2019
STATUS VARCHAR2 3 2.90636843 3 0 344072 11-14-2019
SEGMENT_TYPE VARCHAR2 2 2.91512043 2 0 343039 11-14-2019
ADULT_NUMBER NUMBER 13 0.07692307 1 0 343039 11-14-2019
CHILD_NUMBER NUMBER 6 2.91512043 6 0 343039 11-14-2019
INFANT_NUMBER NUMBER 4 2.91512043 4 0 343039 11-14-2019
CHILDMODE VARCHAR2 2 0.5 1 0 343039 11-14-2019
TOTAL_PRICE NUMBER 2383 0.00041963 1 0 343039 11-14-2019
OPERATION_STATUS VARCHAR2 1 1 1 0 343039 11-14-2019
PLATFORM_TYPE VARCHAR2 16 2.91512043 16 0 343039 11-14-2019
23 rows selected
Executed in 0.151 seconds
INDEX_NAME BLEV LEAF_BLOCKS DISTINCT_KEYS NUM_ROWS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR TO_CHAR(T.LAST_ANALYZED,'MM-DD-YYYY')
------------------------------ ---------- ----------- ------------- ---------- ----------------------- ----------------------- ----------------- -------------------------------------
PK_T_OD_TICKET_ORDER_INFO 2 5825 1634070 1634070 1 1 1155110 11-14-2019
IDX_TKTOD_INFO_CREATE_USERID 2 8855 1724050 1746115 1 1 1242010 11-14-2019
Executed in 0.054 seconds
INDEX_NAME COLUMN_NAME COLUMN_POSITION COL
------------------------------ ------------------------------ --------------- ------------------------------
IDX_TKTOD_INFO_CREATE_USERID CREATE_TIME 1 DATE NOT NULL
IDX_TKTOD_INFO_CREATE_USERID USERID 2 NUMBER(22) NOT NULL
PK_T_OD_TICKET_ORDER_INFO ID 1 NUMBER(22) NOT NULL