树形查询的优化

最新推荐文章于 2024-04-09 14:04:14 发布

iteye_1485

最新推荐文章于 2024-04-09 14:04:14 发布

阅读量518

点赞数

一位哥们(之前的同事，某世界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