使用with as 优化SQL

在一个SQL语句中,如果某个表需要被访问多次,而且每次访问的时候限制条件基本上一致的话,我们就可以利用with as来优化

下面举个例子:

 

 

SQL> CREATE TABLE t1 (id number, name varchar2(10));

Table created.

SQL> begin

  2      FOR i IN 1 .. 1000 LOOP

  3          INSERT INTO t1 VALUES(i,'fuck');

  4      END loop;

  5      commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

 

SQL> CREATE INDEX i_t1_id ON t1(id);

Index created.

 

 

SQL> CREATE TABLE t2(id number, name varchar2(100), sex VARCHAR2(1));

Table created.

SQL> begin

  2      FOR i IN 1 .. 1000 LOOP

  3          INSERT INTO t2 VALUES(i,'motherfucker',CASE MOD(i,2) WHEN 1 THEN 'M' ELSE 'F' END);

  4      END loop;

  5      commit;

  6  end;

  7  /

PL/SQL procedure successfully completed.

SQL> CREATE INDEX i_t2_id ON t2(id);

Index created.

 

SQL> EXEC dbms_stats.gather_table_stats('ROBINSON','T1',cascade=>true);

PL/SQL procedure successfully completed.

SQL> EXEC dbms_stats.gather_table_stats('ROBINSON','T2',cascade=>true);

PL/SQL procedure successfully completed.

 

 

比如有如下SQL语句:

 

SELECT a.sex, b.*

FROM (SELECT * FROM t2 WHERE sex='M') a inner join (SELECT * FROM t1 WHERE id<100) b ON a.id=b.id

UNION ALL

SELECT a.name, b.*

FROM (SELECT * FROM t2 WHERE sex='F') a inner join (SELECT * FROM t1 WHERE id<100) b ON a.id=b.id;

我们看他执行计划:

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 606540077

 

-----------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |    98 |  1960 |    13  (54)| 00:00:01 |

|   1 |  UNION-ALL                    |         |       |       |            |          |

|*  2 |   HASH JOIN                   |         |    49 |   686 |     7  (15)| 00:00:01 |

|*  3 |    TABLE ACCESS BY INDEX ROWID| T2      |    50 |   300 |     3   (0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | I_T2_ID |    99 |       |     2   (0)| 00:00:01 |

|   5 |    TABLE ACCESS BY INDEX ROWID| T1      |    99 |   792 |     3   (0)| 00:00:01 |

|*  6 |     INDEX RANGE SCAN          | I_T1_ID |    99 |       |     2   (0)| 00:00:01 |

|*  7 |   HASH JOIN                   |         |    49 |  1274 |     7  (15)| 00:00:01 |

|*  8 |    TABLE ACCESS BY INDEX ROWID| T2      |    50 |   900 |     3   (0)| 00:00:01 |

|*  9 |     INDEX RANGE SCAN          | I_T2_ID |    99 |       |     2   (0)| 00:00:01 |

|  10 |    TABLE ACCESS BY INDEX ROWID| T1      |    99 |   792 |     3   (0)| 00:00:01 |

|* 11 |     INDEX RANGE SCAN          | I_T1_ID |    99 |       |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - access("T2"."ID"="T1"."ID")

   3 - filter("SEX"='M')

   4 - access("T2"."ID"<100)

   6 - access("ID"<100)

   7 - access("T2"."ID"="T1"."ID")

   8 - filter("SEX"='F')

   9 - access("T2"."ID"<100)

  11 - access("ID"<100)

 

 

注意观察上面的SQL语句,t1,t2表都被访问了2次,而且t1表2次访问都是同一条SQL语句(子查询),t2表也被访问了2次,但是2次的SQL结构基本一样,所以这里可以用with as优化该SQL

 

WITH b AS (SELECT * FROM t1 WHERE id<100),

a as (select * from t2 where id<100)

SELECT a.sex, b.*

FROM a inner join b ON a.id=b.id and a.sex='M'

UNION ALL

SELECT a.name, b.*

FROM a inner join b ON a.id=b.id and a.sex='F';

经过优化的SQL的执行计划:

 

SQL> select * from table(dbms_xplan.display);

 

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------------------------------

Plan hash value: 2605773608

 

-----------------------------------------------------------------------------------------------------------

| Id  | Operation                     | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |                           |    20 |  1220 |     9  (56)| 00:00:01 |

|   1 |  TEMP TABLE TRANSFORMATION    |                           |       |       |            |       |

|   2 |   LOAD AS SELECT              |                           |       |       |            |       |

|   3 |    TABLE ACCESS BY INDEX ROWID| T1                        |    99 |   792 |     3   (0)| 00:00:01 |

|*  4 |     INDEX RANGE SCAN          | I_T1_ID                   |    99 |       |     2   (0)| 00:00:01 |

|   5 |   LOAD AS SELECT              |                           |       |       |            |       |

|   6 |    TABLE ACCESS BY INDEX ROWID| T2                        |    99 |  1782 |     3   (0)| 00:00:01 |

|*  7 |     INDEX RANGE SCAN          | I_T2_ID                   |    99 |       |     2   (0)| 00:00:01 |

|   8 |   UNION-ALL                   |                           |       |       |            |       |

|*  9 |    HASH JOIN                  |                           |    10 |   350 |     5  (20)| 00:00:01 |

|* 10 |     VIEW                      |                           |    99 |  1485 |     2   (0)| 00:00:01 |

|  11 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6611_E78EA1 |    99 |  1782 |     2   (0)| 00:00:01 |

|  12 |     VIEW                      |                           |    99 |  1980 |     2   (0)| 00:00:01 |

|  13 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6610_E78EA1 |    99 |   792 |     2   (0)| 00:00:01 |

|* 14 |    HASH JOIN                  |                           |    10 |   870 |     5  (20)| 00:00:01 |

|* 15 |     VIEW                      |                           |    99 |  6633 |     2   (0)| 00:00:01 |

|  16 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6611_E78EA1 |    99 |  1782 |     2   (0)| 00:00:01 |

|  17 |     VIEW                      |                           |    99 |  1980 |     2   (0)| 00:00:01 |

|  18 |      TABLE ACCESS FULL        | SYS_TEMP_0FD9D6610_E78EA1 |    99 |   792 |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   4 - access("ID"<100)

   7 - access("ID"<100)

   9 - access("A"."ID"="B"."ID")

  10 - filter("A"."SEX"='M')

  14 - access("A"."ID"="B"."ID")

  15 - filter("A"."SEX"='F')

 

35 rows selected.

 

 

 

 

 

 

 

阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/robinson1988/article/details/5646105
个人分类: SQL TUNING
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭