文章详情

  • 游戏榜单
  • 软件榜单
关闭导航
热搜榜
热门下载
热门标签
php爱好者> php文档>查询作为条件的SQL

查询作为条件的SQL

时间:2011-01-04  来源:yangtingkun

解决一个客户性能问题的时候,碰到一个有意思的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也无济于事。

相关阅读 更多 +
排行榜 更多 +
辰域智控app

辰域智控app

系统工具 下载
网医联盟app

网医联盟app

运动健身 下载
汇丰汇选App

汇丰汇选App

金融理财 下载