解决一个客户性能问题的时候，碰到一个有意思的SQL语句。






首先创建一个测试环境：


SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A, DBA_QUEUES B;


Table created.


SQL> ALTER TABLE T ADD CONSTRAINT PK_T PRIMARY KEY (ID);


Table altered.


SQL> CREATE INDEX IND_T_TYPE ON T(OBJECT_TYPE);


Index created.


SQL> CREATE TABLE T_TYPE (TYPE VARCHAR2(30) PRIMARY KEY, SUPERTYPE NUMBER);


Table created.


SQL> INSERT INTO T_TYPE SELECT OBJECT_TYPE, MOD(ROWNUM, 3)


 2  FROM (SELECT DISTINCT OBJECT_TYPE FROM T);


41 rows created.


SQL> CREATE INDEX IND_TYPE ON T_TYPE (SUPERTYPE);


Index created.


SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')


PL/SQL procedure successfully completed.


SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_TYPE')


PL/SQL procedure successfully completed.


SQL> SET TIMING ON


SQL> SET AUTOT TRACE


SQL> SELECT *


 2  FROM T T1, T T2


 3  WHERE T1.ID = 500


 4  AND T2.ID != 500


 5  AND (SELECT SUPERTYPE FROM T_TYPE WHERE TYPE = T1.OBJECT_TYPE)


 6   = (SELECT SUPERTYPE FROM T_TYPE WHERE TYPE = T2.OBJECT_TYPE)


 7  AND T1.OBJECT_TYPE IN (SELECT TYPE FROM T_TYPE WHERE SUPERTYPE = 2) ;


1167560 rows selected.


Elapsed: 00:00:38.32


Execution Plan


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


Plan hash value: 2153988938


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


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


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


|  0 | SELECT STATEMENT               |              |  2054K|  411M|  5006   (1)| 00:01:11 |


|* 1 |  FILTER                        |              |       |       |            |          |


|  2 |   NESTED LOOPS                 |              |  2054K|  411M|  5005   (1)| 00:01:11 |


|  3 |    NESTED LOOPS                |              |     1 |   111 |     4   (0)| 00:00:01 |


|  4 |     TABLE ACCESS BY INDEX ROWID| T            |     1 |    99 |     3   (0)| 00:00:01 |


|* 5 |      INDEX UNIQUE SCAN         | PK_T         |     1 |       |     2   (0)| 00:00:01 |


|* 6 |     TABLE ACCESS BY INDEX ROWID| T_TYPE       |    14 |   168 |     1   (0)| 00:00:01 |


|* 7 |      INDEX UNIQUE SCAN         | SYS_C0074670 |     1 |       |     0   (0)| 00:00:01 |


|* 8 |    TABLE ACCESS FULL           | T            |  2054K|  193M|  5001   (1)| 00:01:11 |


|  9 |   TABLE ACCESS BY INDEX ROWID  | T_TYPE       |     1 |    12 |     1   (0)| 00:00:01 |


|*10 |    INDEX UNIQUE SCAN           | SYS_C0074670 |     1 |       |     0   (0)| 00:00:01 |


| 11 |    TABLE ACCESS BY INDEX ROWID | T_TYPE       |     1 |    12 |     1   (0)| 00:00:01 |


|*12 |     INDEX UNIQUE SCAN          | SYS_C0074670 |     1 |       |     0   (0)| 00:00:01 |


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


Predicate Information (identified by operation id):


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


  1 - filter( (SELECT /*+ */ "SUPERTYPE" FROM "T_TYPE" "T_TYPE" WHERE "TYPE"=:B1)=


             (SELECT /*+ */ "SUPERTYPE" FROM "T_TYPE" "T_TYPE" WHERE "TYPE"=:B2))


  5 - access("T1"."ID"=500)


  6 - filter("SUPERTYPE"=2)


  7 - access("T1"."OBJECT_TYPE"="TYPE")


  8 - filter("T2"."ID"<>500)


 10 - access("TYPE"=:B1)


 12 - access("TYPE"=:B1)




Statistics


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


         0  recursive calls


         0  db block gets


     92598  consistent gets


         0  physical reads


         0  redo size


  54760599  bytes sent via SQL*Net to client


    856699  bytes received via SQL*Net from client


     77839  SQL*Net roundtrips to/from client


         0  sorts (memory)


         0  sorts (disk)


   1167560  rows processed


这个SQL不常见，将两个连接查询作为判断相等的条件，在执行计划中Oracle用FILTER执行计划实现了两个查询相等的判断。


按道理来说，这个查询的性能要比普通的管理慢，但是发现改写后使用管理的SQL并没有比这个SQL拥有更好的性能：


SQL> SELECT T1.*, T2.*


 2  FROM T T1, T T2, T_TYPE TY1, T_TYPE TY2


 3  WHERE T1.ID = 500


 4  AND T2.ID != 500


 5  AND T1.OBJECT_TYPE = TY1.TYPE


 6  AND T2.OBJECT_TYPE = TY2.TYPE


 7  AND TY1.SUPERTYPE = TY2.SUPERTYPE


 8  AND T1.OBJECT_TYPE IN (SELECT TYPE FROM T_TYPE WHERE SUPERTYPE = 2) ;


1167560 rows selected.


Elapsed: 00:00:37.85


Execution Plan


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


Plan hash value: 1952256050


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


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


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


|  0 | SELECT STATEMENT                |              |   686K|  153M|  5016   (2)| 00:01:11 |


|* 1 |  HASH JOIN                      |              |   686K|  153M|  5016   (2)| 00:01:11 |


|  2 |   NESTED LOOPS                  |              |    14 |  1890 |     5   (0)| 00:00:01 |


|  3 |    NESTED LOOPS                 |              |     1 |   123 |     4   (0)| 00:00:01 |


|  4 |     NESTED LOOPS                |              |     1 |   111 |     4   (0)| 00:00:01 |


|  5 |      TABLE ACCESS BY INDEX ROWID| T            |     1 |    99 |     3   (0)| 00:00:01 |


|* 6 |       INDEX UNIQUE SCAN         | PK_T         |     1 |       |     2   (0)| 00:00:01 |


|  7 |      TABLE ACCESS BY INDEX ROWID| T_TYPE       |    41 |   492 |     1   (0)| 00:00:01 |


|* 8 |       INDEX UNIQUE SCAN         | SYS_C0074670 |     1 |       |     0   (0)| 00:00:01 |


|* 9 |     TABLE ACCESS BY INDEX ROWID | T_TYPE       |    14 |   168 |     0   (0)| 00:00:01 |


|*10 |      INDEX UNIQUE SCAN          | SYS_C0074670 |     1 |       |     0   (0)| 00:00:01 |


| 11 |    TABLE ACCESS BY INDEX ROWID  | T_TYPE       |    14 |   168 |     1   (0)| 00:00:01 |


|*12 |     INDEX RANGE SCAN            | IND_TYPE     |    14 |       |     0   (0)| 00:00:01 |


|*13 |   TABLE ACCESS FULL             | T            |  2054K|  193M|  5001   (1)| 00:01:11 |


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


Predicate Information (identified by operation id):


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


  1 - access("T2"."OBJECT_TYPE"="TY2"."TYPE")


  6 - access("T1"."ID"=500)


  8 - access("T1"."OBJECT_TYPE"="TY1"."TYPE")


  9 - filter("SUPERTYPE"=2)


 10 - access("T1"."OBJECT_TYPE"="TYPE")


 12 - access("TY1"."SUPERTYPE"="TY2"."SUPERTYPE")


 13 - filter("T2"."ID"<>500)




Statistics


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


         0  recursive calls


         0  db block gets


     92228  consistent gets


         0  physical reads


         0  redo size


  54760599  bytes sent via SQL*Net to client


    856699  bytes received via SQL*Net from client


     77839  SQL*Net roundtrips to/from client


         0  sorts (memory)


         0  sorts (disk)


   1167560  rows processed


可以看到，虽然逻辑读和执行时间都有所提到，但是性能提高几乎可以忽略。


虽然这种写法很少见，但是Oracle生成的FILTER执行计划还是比较高效的，不过对于前面的SQL很难进行调整，因为如果不改写SQL的话，很难通过HINT来改变执行计划，来消除FILTER，虽然两个SQL是等价的，但是CBO对二者没有办法生成相同的执行计划，即使尝试HINT也无济于事。



oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html