一次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]

所以我感覺還是多做測試，用事實說話.