一次sql执行效率的讨论

http://www.itpub.net/406784.html

在测试机上测试了一把
发现和楼主测试的结构有些不同
表，索引，索引字段已经分析过.
我感觉区别在
1 NL探测Inner table的次数和这个结果记录数.
2 子查询效率，INDEX FAST FULL SCAN 代价.

[@more@]

[PHP]

SQL>
SQL> select count(*) from track;

COUNT(*)
----------
12337495

Elapsed: 00:00:00.01
SQL> select count(*) from terminal_user;

COUNT(*)
----------
305

Elapsed: 00:00:00.00
SQL>
SQL> set autotrace traceonly
SQL>
SQL>
SQL> select tu.* from terminal_user tu
2 where tu.tu_id not in (select distinct tu_id from track);

36 rows selected.

Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 1944 | 614 (1)|
| 1 | NESTED LOOPS ANTI | | 27 | 1944 | 614 (1)|
| 2 | TABLE ACCESS FULL| TERMINAL_USER | 305 | 20740 | 3 (0)|
|* 3 | INDEX RANGE SCAN | TRACK_TUID_IDX | 11M| 42M| 2 (0)|
-----------------------------------------------------------------------------

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

3 - access("TU"."TU_ID"="TU_ID")

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
627 consistent gets
0 physical reads
80 redo size
4224 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

由于terminal_user 仅有305条记录，
而且track表相关字段有索引，INDEX RANGE SCAN使得探测发挥了很高的效率.因此这个NL效率很高.
我们看到整个sql执行的Cost仅为614。

再来看 outer jion + null condition
SQL>
SQL> select tu.*
2 from terminal_user tu,
( select distinct tu_id from track ) tk
where tu.tu_id = tk.tu_id(+)
and tk.tu_id is null; 3 4 5

36 rows selected.

Elapsed: 00:00:03.15

Execution Plan
----------------------------------------------------------

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 27 | 2187 | 10897 (16)|
|* 1 | HASH JOIN RIGHT ANTI | | 27 | 2187 | 10897 (16)|
| 2 | VIEW | | 278 | 3614 | 10894 (16)|
| 3 | HASH UNIQUE | | 278 | 1112 | 10894 (16)|
| 4 | INDEX FAST FULL SCAN| TRACK_TUID_IDX | 12M| 47M| 9365 (3)|
| 5 | TABLE ACCESS FULL | TERMINAL_USER | 305 | 20740 | 3 (0)|
----------------------------------------------------------------------------------

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

1 - access("TU"."TU_ID"="TK"."TU_ID")

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
42111 consistent gets
0 physical reads
0 redo size
4224 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
36 rows processed

我们可以看到 select distinct tu_id from track 的访问路径为 INDEX FAST FULL SCAN
而Cost就为9365.

SQL> select distinct tu_id from track;

278 rows selected.

Elapsed: 00:00:03.27

Execution Plan
----------------------------------------------------------

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 278 | 1112 | 10894 (16)|
| 1 | HASH UNIQUE | | 278 | 1112 | 10894 (16)|
| 2 | INDEX FAST FULL SCAN| TRACK_BK_TUID_IDX | 12M| 47M| 9365 (3)|
--------------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
42180 consistent gets
0 physical reads
124 redo size
5627 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
20 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
278 rows processed

SQL> select /*+ INDEX(track,TRACK_BK_TUID_IDX)*/ distinct tu_id from track
2 ;

278 rows selected.

Elapsed: 00:00:03.69

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 278 | 1112 | 43705 (5)|
| 1 | SORT UNIQUE NOSORT| | 278 | 1112 | 43705 (5)|
| 2 | INDEX FULL SCAN | TRACK_BK_TUID_IDX | 12M| 47M| 42176 (1)|
-----------------------------------------------------------------------------

Note
-----
- 'PLAN_TABLE' is old version

Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
41907 consistent gets
0 physical reads
132 redo size
5627 bytes sent via SQL*Net to client
667 bytes received via SQL*Net from client
20 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
278 rows processed

我发现select distinct tu_id from track无论是FFS还是IFS效率都不怎么样.

所以这里就产生一个问题.子查询的效率会受到数据量(影响到索引段的大小)的影响.
SQL> set autotrace off
SQL> analyze index TRACK_BK_TUID_IDX validate structure;

Index analyzed.

Elapsed: 00:00:15.65

SQL> select BLOCKS,USED_SPACE,DISTINCT_KEYS,LF_ROWS_LEN from index_stats;

BLOCKS USED_SPACE DISTINCT_KEYS LF_ROWS_LEN
---------- ---------- ------------- -----------
42752 198198860 278 197520787

[/PHP]

所以我感觉还是多做测试，用事实说话.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/76065/viewspace-838204/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/76065/viewspace-838204/