Oracle optimized-leading tips and ordered tips and materialize tips

The following applies to Oracle 10.2.0.5 and above

One query is slow, and the original SQL is as follows:

1 Select 2A.*3    from(Select        4 Ssi.id,5          'Small_station_info'TB,6(SelectSbi.name7              fromScene_base_info SBI8            whereSbi.id=Ssi.antenna_selection) asAntenna_selection,9 Ssi.antenna_height,Ten Ssi.down_angle, One Ssi.azimuth_angle, A SSI. ITI_ID, - Sa.longitude, - Sa.latitude, the sa.attach_id -            fromConsolidation_demand CD -            Left JoinDemand_test_info DTI -              onCd.id=dti.cd_id +            Left Joindemand_plan_info dpi -              onDti.id=dpi.tdl_id +            Left Joinbuilding_plan_info BPI A              onDpi.id=bpi.dpi_id at            Left JoinNear_far_place_info NFPI -              onBpi.id=nfpi.bpi_id -            Left JoinSmall_station_info SSI -              onNfpi.id=ssi.nfpi_id -            Left Joinsite_attachment SA -              onTo_number (Sa.longitude) is  not NULL in             andTo_number (Sa.latitude)> 26.074423 -             andTo_number (Sa.latitude)< 26.077573 to             andTo_number (Sa.longitude)> 119.191148 +             andTo_number (Sa.longitude)< 119.197649 -             andSa.attach_name= the substr (SSI. Azimuth_angle_photo, *InStr (SSI. Azimuth_angle_photo,'/',-1)+ 1, $ Length (SSI. Azimuth_angle_photo))) aPanax Notoginseng  whereA.longitude is  not NULL

Tables are small and the implementation plan is as follows:

12 rows have been selected. Execution plans----------------------------------------------------------Plan hash value:1917963167------------------------ ---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |----------------------------------------------------------------------------------------------------------   -----| 0 |                         SELECT STATEMENT |     |   1 |   253 | 519 (2) |   00:00:07 | |  1 | TABLE ACCESS by INDEX ROWID |     Scene_base_info |    1 |     14 | 1 (0) | 00:00:01 | |   * 2 | INDEX UNIQUE SCAN |     SCENE_BASE_INFO_PK |       1 |     | 0 (0) |   00:00:01 | |  3 |                         VIEW |     |   1 |   253 | 519 (2) | 00:00:07 | |   * 4 |                         FILTER |       |       |            |       | ||    * 5 | HASH JOIN OUTER |     |   1 |   251 | 519 (2) | 00:00:07 | |     * 6 |                         HASH JOIN OUTER |    |  83 |   8134 | 505 (1) | 00:00:07 | |      * 7 |                         HASH JOIN OUTER |    |  83 |   7304 | 501 (1) | 00:00:07 | |       * 8 |                         HASH JOIN OUTER |    |  83 |   6391 | 493 (1) | 00:00:06 | |        * 9 |                         HASH JOIN OUTER |    |  83 |   5478 | 271 (1) |  00:00:04 | |         10 |                         MERGE JOIN Cartesian |    |  36 |    2052 | 21 (0) | 00:00:01 | |          * 11 | TABLE ACCESS by INDEX rowid|     site_attachment |    1 |    53 | 16 (0) | 00:00:01 | |           * 12 | INDEX RANGE SCAN |     IDX_SITE_ATTACHMENT_JWD |       1 |    | 15 (0) |  00:00:01 | |          13 |                         BUFFER SORT |  | 6725 |     26900 | 5 (0) |  00:00:01 | |           14 |INDEX FAST Full SCAN |  Pk_consolidation_demand | 6725 |     26900 | 5 (0) |  00:00:01 | |         15 | TABLE ACCESS Full | Demand_test_info |   15459 |   135k| 249 (1) |  00:00:03 | |        16 | TABLE ACCESS Full |  Demand_plan_info | 8787 |   96657 | 221 (1) |  00:00:03 | |       17 | TABLE ACCESS Full |  Building_plan_info | 3244 |     35684 | 8 (0) |  00:00:01 | |      18 | TABLE ACCESS Full |   Near_far_place_info |  389 |     3890 | 3 (0) |  00:00:01 | |     19 | TABLE ACCESS Full |   Small_station_info | 594 |    90882 | 13 (0) | 00:00:01 |------------------------------------------------------------------------------------------------------ ---------predicate information (identified by Operation ID):---------------------------------------------------2- Access ("SBI". " ID "=:B1" 4-filter ("SA". ") Attach_name "=substr (" SSI "). Azimuth_angle_photo ", INSTR (" SSI "."       Azimuth_angle_photo ", '       /', -1) +1,length ("SSI". ") Azimuth_angle_photo ")) 5-access (" NFPI "). ID "=" SSI "." nfpi_id "(+)) 6-access (" BPI "). ID "=" NFPI "." bpi_id "(+)) 7-access (" DPI ".") ID "=" BPI "." dpi_id "(+)) 8-access (" DTI "). ID "=" DPI "." tdl_id "(+)) 9-access (" CD ".") ID "=" DTI "." cd_id "(+)) 11-filter (" SA ").              Longitude "is not NULL" 12-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") I S not NULL and To_number ("LATITUDE") <26.077573 and To_number ("LATITUDE") >26.074423)

This execution plan looks incredibly normal and should be left join.

The key to the problem, however, is the 10 step-MERGE JOIN Cartesian. The sorting of the Cartesian product merges connections, which takes a long time.

Waiting for this result will take dozens of seconds, even longer!
How to solve such problems, there are several methods:

1. Re-collect statistics for each table-this has not been tested, but even then it may not work. But from this example, it is likely that this is the cause.
2. Enable leading hints, combined with other hints
3. Using Materialize Tips

Using Leading Tips

 1  select  /*  + no_merge (a) no_push_pred (a)  */ 2  a.*  3  from  (select  4  /*  + leading (CD DTI DPI BPI SSI)           */ 5   ...) A  6  where  a.longitude is  not  null  7  / 
 

Execution Plan

12 rows have been selected. Execution plans----------------------------------------------------------Plan hash value:1844304918------------------------ ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU) |    Time |---------------------------------------------------------------------------------------------------------| 0 |                         SELECT STATEMENT |     |   1 |   253 | 520 (2) |   00:00:07 | |  1 | TABLE ACCESS by INDEX ROWID |     Scene_base_info |    1 |     14 | 1 (0) | 00:00:01 | |   * 2 | INDEX UNIQUE SCAN |     SCENE_BASE_INFO_PK |       1 |     | 0 (0) |   00:00:01 | |  3 |                         VIEW |     |   1 |   253 | 520 (2) | 00:00:07 | |   * 4 |                         HASH JOIN |     |   1 |   251 | 520 (2) | 00:00:07 | |    * 5 | TABLE ACCESS by INDEX rowid| site_attachment |     1 |    53 | 16 (0) | 00:00:01 | |     * 6 | INDEX RANGE SCAN |     IDX_SITE_ATTACHMENT_JWD |       1 |    | 15 (0) | 00:00:01 | |    * 7 |                         HASH JOIN Right OUTER | |  23606 |   4564k| 503 (2) |   00:00:07 | |     8 | TABLE ACCESS Full |   Small_station_info | 594 |    90882 | 13 (0) | 00:00:01 | |     * 9 |                         HASH JOIN Right OUTER | |   15459 |   679k| 490 (2) |  00:00:06 | |      10 | TABLE ACCESS Full |   Near_far_place_info |  389 |     3890 | 3 (0) | 00:00:01 | |      * 11 |                         HASH JOIN Right OUTER | |   15459 |   528k| 486 (2) |  00:00:06 | |       12 | TABLE ACCESS Full |  Building_plan_info | 3244 |     35684 | 8 (0) | 00:00:01 | |       * 13 |                         HASH JOIN Right OUTER | |   15459 |   362k| 477 (1) |  00:00:06 | |        14 | TABLE ACCESS Full |  Demand_plan_info | 8787 |   96657 | 221 (1) | 00:00:03 | |        * 15 |HASH JOIN OUTER | |   15459 |   196k| 255 (1) |  00:00:04 | |         16 | INDEX FAST Full SCAN |  Pk_consolidation_demand | 6725 |     26900 | 5 (0) |  00:00:01 | |         17 | TABLE ACCESS Full | Demand_test_info |   15459 |   135k| 249 (1) | 00:00:03 |------------------------------------------------------------------------------------------------------ ---predicate information (identified by Operation ID):---------------------------------------------------2-access (" SBI "." ID "=:B1" 4-access ("SA". ") Attach_name "=substr (" SSI "). Azimuth_angle_photo ", INSTR (" SSI "." Azimuth_angle_ph OTO ", '/', -1) +1,length (" SSI "." Azimuth_angle_photo ")) 5-filter (" SA ").              Longitude "is not NULL" 6-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") I S not NULL and To_number ("LATITUDE") <26.077573 and To_number ("LATITUDE") >26.074423) 7-access ("NFPI". " ID "=" SSI "." nfpi_id "(+)) 9-access (" BPI "). ID "=" NFPI "." bpi_id "(+)) 11-access (" DPI ".") ID "=" BPI "." dpi_id "(+)) 13-access (" DTI "). ID "=" DPI "." tdl_id "(+)) 15-access (" CD ".") ID "=" DTI "." cd_id "(+))

There is no Cartesian merge join. Step 4 is also a hash join.
It'll take about 0.17 seconds.

Using Materialize Tips

1   with  as 2   (Select/**/3...    ) 4  Select A.*fromWHEREare notnull

Execution plan

12 rows have been selected. Execution plans----------------------------------------------------------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-ACC ESS ("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)

Also soon, about 0.19~0.2 about.
The reason is slow, mainly because to Mr. GT table sys_temp_0fd9d68a2_721ef047.

Summarize

1. It is best to collect statistics first

2. When collecting statistics is invalid, consider using the leading hint, followed by the materialize hint will also destroy some of the Oracle Optimizer's own sensible plans (the optimizer's shortcomings, Oracle has mentioned, this is the origin of hint)

3.DBA to optimize a library, it's not an easy thing to do and a lot of work needs to be done.

Oracle optimized-leading tips and ordered tips and materialize tips