树形查询的优化

一位哥们(之前的同事,某世界500强,你懂的)问我,老外要他优化SQL,一共4个,全是树形查询的。前面3个跑半个多小时,后面一个跑1个小时,总共要跑 2个半小时。 问有啥方法可以优化。

因为SQL 类型基本上一样,这里指贴一个SQL

其实这些SQL 都是 insert into ... select ....

要想INSERT 快,就必须SELECT 块

SQL语句如下

 select rownum, adn, zdn, 'cable'
    from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
            from AGGR_1 t
           where t.tdl_operation <> 2
             and exists (select 1
                    from CABLE_1 a
                   where a.tdl_operation <> 2
                     and a.tdl_dn = t.tdl_z_dn)
           start with exists (select 1
                         from RESOURCE_FACING_SERVICE1_1 b
                        where b.tdl_operation <> 2
                          and t.tdl_a_dn = b.tdl_dn)
          connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn)

执行计划如下

SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 1439701716

-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                    | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                             |                            | 31125 |    59M|       | 36356   (1)| 00:07:17 |
|   1 |  COUNT                                       |                            |       |       |       |            |          |
|   2 |   VIEW                                       |                            | 31125 |    59M|       | 36356   (1)| 00:07:17 |
|   3 |    HASH UNIQUE                               |                            | 31125 |    59M|   334M| 36356   (1)| 00:07:17 |
|*  4 |     FILTER                                   |                            |       |       |       |            |          |
|*  5 |      CONNECT BY NO FILTERING WITH SW (UNIQUE)|                            |       |       |       |            |          |
|   6 |       TABLE ACCESS FULL                      | AGGR_1                     |   171K|  4353K|       |  2468   (1)| 00:00:30 |
|*  7 |       TABLE ACCESS FULL                      | RESOURCE_FACING_SERVICE1_1 |     1 |    18 |       |   137   (1)| 00:00:02 |
|*  8 |      TABLE ACCESS FULL                       | CABLE_1                    |     1 |    14 |       |   205   (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - filter("T"."TDL_OPERATION"<>2 AND  EXISTS (SELECT 0 FROM "CABLE_1" "A" WHERE "A"."TDL_DN"=:B1 AND
              "A"."TDL_OPERATION"<>2))
   5 - access("T"."TDL_A_DN"=PRIOR "T"."TDL_Z_DN")
       filter( EXISTS (SELECT 0 FROM "RESOURCE_FACING_SERVICE1_1" "B" WHERE "B"."TDL_DN"=:B1 AND "B"."TDL_OPERATION"<>2))
   7 - filter("B"."TDL_DN"=:B1 AND "B"."TDL_OPERATION"<>2)
   8 - filter("A"."TDL_DN"=:B1 AND "A"."TDL_OPERATION"<>2)

25 rows selected.


改写过后的SQL 如下

      
with a as (select /*+ materialize */ tdl_dn from CABLE_1 a where a.tdl_operation <> 2 ),
     b as (select /*+ materialize */ tdl_dn from  RESOURCE_FACING_SERVICE1_1 b where b.tdl_operation <> 2),
     t as (select /*+ materialize */ tdl_a_dn, tdl_z_dn,tdl_operation from AGGR_1 t )                       
 select rownum, adn, zdn, 'cable'
    from (select distinct connect_by_root(t.tdl_a_dn) adn, t.tdl_z_dn zdn
            from  t
           where t.tdl_operation <> 2
             and exists (select 1
                    from  a
                   where a.tdl_dn = t.tdl_z_dn)
           start with exists (select 1
                         from  b
                        where  t.tdl_a_dn = b.tdl_dn)
          connect by nocycle prior t.tdl_z_dn = t.tdl_a_dn)

执行计划如下

SQL> select * from table(DBMS_XPLAN.DISPLAY);
Plan hash value: 705757273

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                            | 31125 |    59M|       | 32045   (1)| 00:06:25 |
|   1 |  TEMP TABLE TRANSFORMATION              |                            |       |       |       |            |          |
|   2 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D6664_D65E0   |       |       |       |            |          |
|*  3 |    TABLE ACCESS FULL                    | CABLE_1                    | 12616 |   172K|       |   205   (1)| 00:00:03 |
|   4 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D6665_D65E0   |       |       |       |            |          |
|*  5 |    TABLE ACCESS FULL                    | RESOURCE_FACING_SERVICE1_1 | 10511 |   184K|       |   137   (1)| 00:00:02 |
|   6 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D6666_D65E0   |       |       |       |            |          |
|   7 |    TABLE ACCESS FULL                    | AGGR_1                     |   171K|  4353K|       |  2468   (1)| 00:00:30 |
|   8 |   COUNT                                 |                            |       |       |       |            |          |
|   9 |    VIEW                                 |                            | 31125 |    59M|       | 29236   (1)| 00:05:51 |
|  10 |     HASH UNIQUE                         |                            | 31125 |    59M|   140M| 29236   (1)| 00:05:51 |
|* 11 |      FILTER                             |                            |       |       |       |            |          |
|* 12 |       CONNECT BY WITH FILTERING (UNIQUE)|                            |       |       |       |            |          |
|* 13 |        HASH JOIN RIGHT SEMI             |                            | 22778 |    22M|       |   179   (3)| 00:00:03 |
|  14 |         VIEW                            |                            | 10511 |   164K|       |     9   (0)| 00:00:01 |
|  15 |          TABLE ACCESS FULL              | SYS_TEMP_0FD9D6665_D65E0   | 10511 |   164K|       |     9   (0)| 00:00:01 |
|  16 |         VIEW                            |                            |   171K|   168M|       |   168   (2)| 00:00:03 |
|  17 |          TABLE ACCESS FULL              | SYS_TEMP_0FD9D6666_D65E0   |   171K|  4353K|       |   168   (2)| 00:00:03 |
|* 18 |        HASH JOIN                        |                            | 49360 |    95M|    22M|  9874   (1)| 00:01:59 |
|  19 |         CONNECT BY PUMP                 |                            |       |       |       |            |          |
|  20 |         VIEW                            |                            |   171K|   168M|       |   168   (2)| 00:00:03 |
|  21 |          TABLE ACCESS FULL              | SYS_TEMP_0FD9D6666_D65E0   |   171K|  4353K|       |   168   (2)| 00:00:03 |
|* 22 |       VIEW                              |                            |     1 |  1002 |       |     1   (0)| 00:00:01 |
|  23 |        TABLE ACCESS FULL                | SYS_TEMP_0FD9D6664_D65E0   | 12616 |   147K|       |     8   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("A"."TDL_OPERATION"<>2)
   5 - filter("B"."TDL_OPERATION"<>2)
  11 - filter("T"."TDL_OPERATION"<>2 AND  EXISTS (SELECT 0 FROM  (SELECT /*+ CACHE_TEMP_TABLE ("T1") */ "C0" "TDL_DN"
              FROM "SYS"."SYS_TEMP_0FD9D6664_D65E0" "T1") "A" WHERE "A"."TDL_DN"=:B1))
  12 - access("T"."TDL_A_DN"=PRIOR "T"."TDL_Z_DN")
  13 - access("T"."TDL_A_DN"="B"."TDL_DN")
  18 - access("connect$_by$_pump$_011"."prior t.tdl_z_dn "="T"."TDL_A_DN")
  22 - filter("A"."TDL_DN"=:B1)

42 rows selected.


 

优化完毕之后,这些SQL,前3个能在 5分钟内跑完,最后一个能在10分钟内跑完, 最终一共耗时 25分钟左右,之前是2个班小时,他终于能给 澳大利亚鸟人交差了 嘎嘎。

 

有SQL 需要优化的 欢迎加入  QQ 群 220761024

 

posted @ 2013-04-27 20:04  javawebsoa  Views(428)  Comments(0Edit  收藏  举报