1 WITH A AS 2 (select /*+MATERIALIZE */ 3 .....) 4 select a.* from A WHERE a.longitude is not null
执行计划
已选择 12 行。 执行计划 ---------------------------------------------------------- Plan hash value: 3536941173 -------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 23606 | 5832K| 700 (2)| 00:00:09 | | 1 | TEMP TABLE TRANSFORMATION | | | | | | | 2 | LOAD AS SELECT | | | | | | |* 3 | HASH JOIN RIGHT OUTER | | 23606 | 5786K| 520 (2)| 00:00:07 | | 4 | TABLE ACCESS BY INDEX ROWID| SITE_ATTACHMENT | 1 | 53 | 16 (0)| 00:00:01 | |* 5 | INDEX RANGE SCAN | IDX_SITE_ATTACHMENT_JWD | 1 | | 15 (0)| 00:00:01 | |* 6 | HASH JOIN RIGHT OUTER | | 23606 | 4564K| 503 (2)| 00:00:07 | | 7 | TABLE ACCESS FULL | SMALL_STATION_INFO | 594 | 90882 | 13 (0)| 00:00:01 | |* 8 | HASH JOIN RIGHT OUTER | | 15459 | 679K| 490 (2)| 00:00:06 | | 9 | TABLE ACCESS FULL | NEAR_FAR_PLACE_INFO | 389 | 3890 | 3 (0)| 00:00:01 | |* 10 | HASH JOIN RIGHT OUTER | | 15459 | 528K| 486 (2)| 00:00:06 | | 11 | TABLE ACCESS FULL | BUILDING_PLAN_INFO | 3244 | 35684 | 8 (0)| 00:00:01 | |* 12 | HASH JOIN RIGHT OUTER | | 15459 | 362K| 477 (1)| 00:00:06 | | 13 | TABLE ACCESS FULL | DEMAND_PLAN_INFO | 8787 | 96657 | 221 (1)| 00:00:03 | |* 14 | HASH JOIN OUTER | | 15459 | 196K| 255 (1)| 00:00:04 | | 15 | INDEX FAST FULL SCAN | PK_CONSOLIDATION_DEMAND | 6725 | 26900 | 5 (0)| 00:00:01 | | 16 | TABLE ACCESS FULL | DEMAND_TEST_INFO | 15459 | 135K| 249 (1)| 00:00:03 | |* 17 | VIEW | | 23606 | 5832K| 180 (2)| 00:00:03 | | 18 | TABLE ACCESS FULL | SYS_TEMP_0FD9D68A2_721EF047 | 23606 | 4103K| 180 (2)| 00:00:03 | -------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("SA"."ATTACH_NAME"(+)=SUBSTR("SSI"."AZIMUTH_ANGLE_PHOTO",INSTR("SSI"."AZIMUTH_ANGLE_PHOT O",'/',-1)+1,LENGTH("SSI"."AZIMUTH_ANGLE_PHOTO"))) 5 - access(TO_NUMBER("LONGITUDE"(+))>119.191148 AND TO_NUMBER("LATITUDE"(+))>26.074423 AND TO_NUMBER("LONGITUDE"(+))<119.197649 AND TO_NUMBER("LATITUDE"(+))<26.077573) filter(TO_NUMBER("LONGITUDE"(+)) IS NOT NULL AND TO_NUMBER("LATITUDE"(+))<26.077573 AND TO_NUMBER("LATITUDE"(+))>26.074423) 6 - access("NFPI"."ID"="SSI"."NFPI_ID"(+)) 8 - access("BPI"."ID"="NFPI"."BPI_ID"(+)) 10 - access("DPI"."ID"="BPI"."DPI_ID"(+)) 12 - access("DTI"."ID"="DPI"."TDL_ID"(+)) 14 - access("CD"."ID"="DTI"."CD_ID"(+)) 17 - filter("A"."LONGITUDE" IS NOT NULL)
也很快，大约0.19~0.2左右。
之所以慢，主要是因为要先生成gt表 SYS_TEMP_0FD9D68A2_721EF047。
总结
1.最好先收集统计数据
2.在收集统计数据无效的情况下，考虑使用leading提示，其次materialize提示也会破坏oracle优化器一些自以为明智的计划（优化器的不足，oracle已经提到了，这就是hint的由来)
3.dba要优化一个库，不是一个很容易的事情，需要做很多工作。