最近发现有一条sql要执行几百秒，是执行计划走错了，为什么走错呢？来看一下：
SELECT *
FROM (SELECT *FROM (SELECT INNER_TABLE.*,ROWNUM OUTER_TABLE_ROWNUM
FROM (SELECT DISTINCT PRO.PROJECT_ID,
PRO.PROJECT_NAME,
PRO.PROJECT_CODE,
PRO.PROJECT_CHARGER,
PRO.EXE_DEPT,
PRO.EXE_DEPT_CODE,
PRO.CONS_DEPT,
PRO.CONS_DEPT_CODE,
PRO.PROJECT_ACCESS_TYPE,
PROJECT_AMOUNT,
PRO.PROJECT_TYPE_CODE,
VER.SANCTIFIED_AMOUNT,
VER.BUDGET_AMOUNT,
PRO.APPLY_YEAR,
PRO.PROJECT_PROPERTY,
PRO.BUILD_TYPE,
PRO.PROJECT_TYPE,
PRO.PLAN_START_DATE,
PRO.TECH_PROJECT_CODE,
PRO.PLAN_COMPLETE_DATE,
PRO.LOCAL_CODE,
PRO.DATA_AREA DATA_AREA,
PRO.PROJECT_KIND,
PRO.PROJECT_ATTRIBUTE,
PRO.BELONGS_CATEGORY BELONGS_CATEGORY,
PRO.FUND_SOURCE FUND_SOURCE,
PRO.PROJECT_CHARGER_ID PROJECT_CHARGER_ID,
V_BGG.TODO_TASK_ID AS TODO_TASK_ID,
VER.FBS_VERSION_ID,
NULL AS DONE_TASK_ID,
VER.LEGAL_MARK,
VER.AUDIT_STATUS,
VER.PROCESS_INSTANCE_ID,
V_BGG.READ_FLAG,
VER.FBS_CLASS,
V_BGG.BACK_FLAG AS BACK_FLAG
FROM GG_PROJECT PRO,
GG_FBS_VERSION VER,
V_GG_BGG_TODOTASK V_BGG
WHERE PRO.PROJECT_ID = VER.PROJECT_ID
AND (VER.VERSION_ID = '1' OR VER.FBS_CLASS = '2')
AND V_BGG.MAIN_PROCESS_INS_ID =
VER.PROCESS_INSTANCE_ID
AND V_BGG.VERSION = 8
AND VER.AUDIT_STATUS != '3'
AND V_BGG.TRANS_ACTOR_ID = 'DFA179F838A14CAFB96D065948F46D86'
AND V_BGG.CUR_NODE_ID = 'Task_5'
AND VER.PROJECT_TYPE_CODE = PRO.PROJECT_TYPE_CODE
AND PRO.PROJECT_STATUS < 40
AND PRO.PROJECT_TYPE_CODE = '4') INNER_TABLE)
WHERE OUTER_TABLE_ROWNUM <= 10) OUTER_TABLE
WHERE OUTER_TABLE_ROWNUM > 0 ;
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1071 | 5101 | 00:01:02 |
| * 1 | VIEW | | 1 | 1071 | 5101 | 00:01:02 |
| 2 | COUNT | | | | | |
| 3 | NESTED LOOPS | | 1 | 580 | 5101 | 00:01:02 |
| 4 | NESTED LOOPS | | 256 | 580 | 5101 | 00:01:02 |
| 5 | MERGE JOIN CARTESIAN | | 32 | 16704 | 5012 | 00:01:01 |
| * 6 | TABLE ACCESS BY INDEX ROWID | GG_RU_TODO_TASK_GG | 1 | 121 | 5 | 00:00:01 |
| * 7 | INDEX RANGE SCAN | TODO_TASK_GG_I_2 | 3 | | 3 | 00:00:01 |
| 8 | BUFFER SORT | | 9223 | 3698423 | 5007 | 00:01:01 |
| 9 | PARTITION LIST SINGLE | | 9223 | 3698423 | 5007 | 00:01:01 |
| * 10 | TABLE ACCESS FULL | GG_PROJECT | 9223 | 3698423 | 5007 | 00:01:01 |
| * 11 | INDEX RANGE SCAN | FBS_PROJECT_ID_TEMP1231 | 8 | | 2 | 00:00:01 |
| * 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_FBS_VERSION | 1 | 58 | 4 | 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10)
* 6 - filter("BRTT"."VERSION"=8 AND "BRTT"."CUR_NODE_ID"='Task_5')
* 7 - access("BRTT"."TRANS_ACTOR_ID"='F2D29C42D4904336B0CDEBEC6BC0B68A')
* 10 - filter(TO_NUMBER("PRO"."PROJECT_STATUS")<40)
* 11 - access("PRO"."PROJECT_ID"="PROJECT_ID")
* 12 - filter("VER"."PROCESS_INSTANCE_ID" IS NOT NULL AND "VER"."AUDIT_STATUS"<>3 AND ("VER"."VERSION_ID"='1' OR "VER"."FBS_CLASS"=2) AND "VER"."PROJECT_TYPE_CODE"='4' AND
"BRTT"."MAIN_PROCESS_INS_ID"="VER"."PROCESS_INSTANCE_ID")
exec dbms_stats.gather_table_stats(user,'GG_RU_TODO_TASK_GG',cascade => true,degree => 4,method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY FOR COLUMNS (TRANS_ACTOR_ID,CUR_NODE_ID,version)',no_invalidate=>FALSE);
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1071 | 6096 | 00:01:14 |
| * 1 | VIEW | | 1 | 1071 | 6096 | 00:01:14 |
| 2 | COUNT | | | | | |
| 3 | NESTED LOOPS | | 1 | 580 | 6096 | 00:01:14 |
| 4 | NESTED LOOPS | | 1 | 580 | 6096 | 00:01:14 |
| * 5 | HASH JOIN | | 1 | 179 | 6094 | 00:01:14 |
| * 6 | TABLE ACCESS BY INDEX ROWID | BGG_RU_TODO_TASK_GG | 3 | 363| 5 | 00:00:01 | | * 7 | INDEX RANGE SCAN | TODO_TASK_GG_I_2 | 3 | | 3 | 00:00:01 | | 8 | PARTITION LIST SINGLE | | 27586 | 1599988 | 6089 | 00:01:14 | | 9 | PARTITION LIST ALL | | 27586 | 1599988 | 6089 | 00:01:14 | | * 10 | TABLE ACCESS FULL | GG_FBS_VERSION | 27586 | 1599988 | 6089 | 00:01:14 | | * 11 | INDEX UNIQUE SCAN | PK_PROJECT_ID_T | 1 | | 1 | 00:00:01 | | * 12 | TABLE ACCESS BY GLOBAL INDEX ROWID | GG_PROJECT | 1 | 401 | 2 | 00:00:01 | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ------------------------------------------ * 1 - filter("from$_subquery$_002"."OUTER_TABLE_ROWNUM">0 AND "OUTER_TABLE_ROWNUM"<=10) * 5 - access("BRTT"."MAIN_PROCESS_INS_ID"="VER"."PROCESS_INSTANCE_ID") * 6 - filter("BRTT"."VERSION"=8 AND "BRTT"."CUR_NODE_ID"='Task_1') * 7 - access("BRTT"."TRANS_ACTOR_ID"='1F624F50BCAD4231B165BA246E582243') * 10 - filter("VER"."PROCESS_INSTANCE_ID" IS NOT NULL AND "VER"."AUDIT_STATUS"<>3 AND ("VER"."VERSION_ID"='1' OR "VER"."FBS_CLASS"=2)) * 11 - access("PRO"."PROJECT_ID"="VER"."PROJECT_ID") * 12 - filter(TO_NUMBER("PRO"."PROJECT_STATUS")<40 AND "PRO"."PROJECT_TYPE_CODE"='4')
