我们再将原来表中的数据清除,使用下面的语句:
truncate table full_table_scan_demo drop storage;
insert into full_table_scan_demo select * from zk.cm_user where rownum < 281;
commit;
exec dbms_stats.gather_table_stats('CUIGB','FULL_TABLE_SCAN_DEMO',cascade => TRUE);
select blocks from user_tables where table_name='FULL_TABLE_SCAN_DEMO';
------
15
先看一下表的中的USER_TPYE字段值的情况:
SQL> select USER_TYPE,count(*) from full_table_scan_demo group by user_type;
USER_TYPE COUNT(*)
--------- ----------
0 28
1 28
2 28
3 28
4 28
5 28
6 28
7 28
8 28
9 28
我们看到表中user_type字段是分了10个等分,这个值在实际不可能出现,只是为了做测试我将这个值修改到这样的。
现在我们在使用user_type字段做个索引:语句如下:
create index idx_ftsd_user_type on full_table_scan_demo (user_type) tablespace ts_ob_1m_01;
再做统计值的收集工作:
exec dbms_stats.gather_table_stats('CUIGB','FULL_TABLE_SCAN_DEMO',cascade => TRUE);
我们执行下面SQL,看有什么结果呢?
SQL> select * from full_table_scan_demo where user_type in (1,2);
已选择56行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=56 Bytes=16240)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'FULL_TABLE_SCAN_DEMO' (Cost=5 Card=56 Bytes=16240)
3 2 INDEX (RANGE SCAN) OF 'IDX_FTSD_USER_TYPE' (NON-UNIQUE) (Cost=2 Card=56)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
11773 bytes sent via SQL*Net to client
257 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
56 rows processed
再与这个字段的全表扫描来比一下,我们使用FULL这个Hint:
SQL> select /*+ full (full_table_scan_demo) */ * from full_table_scan_demo where user_type in (1,2);
已选择56行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=56 Bytes=16240)
1 0 TABLE ACCESS (FULL) OF 'FULL_TABLE_SCAN_DEMO' (Cost=5 Card=56 Bytes=16240)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
21 consistent gets
0 physical reads
0 redo size
11786 bytes sent via SQL*Net to client
257 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
56 rows processed
从这两看执行我们看到了,他们的成本都一样,都是5,我们也大概可以从实际中总结出来,
Oracle的rows source result set(行源结果集,可以认为是返回记录行数)是占全表的20%左右,全
表扫描与使用索引基本上是一样的。
我们再将这个返回记录数提高的30%的情况,如下面的语句:
SQL> select /*+ full (full_table_scan_demo) */ * from full_table_scan_demo where user_type in (1,2,3);
已选择84行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=84 Bytes=24360)
1 0 TABLE ACCESS (FULL) OF 'FULL_TABLE_SCAN_DEMO' (Cost=5 Card=84 Bytes=24360)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
23 consistent gets
0 physical reads
0 redo size
17148 bytes sent via SQL*Net to client
270 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
84 rows processed
SQL> select * from full_table_scan_demo where user_type in (1,2,3);
已选择84行。
执行计划
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=84 Bytes=24360)
1 0 INLIST ITERATOR
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'FULL_TABLE_SCAN_DEMO' (Cost=7 Card=84 Bytes=24360)
3 2 INDEX (RANGE SCAN) OF 'IDX_FTSD_USER_TYPE' (NON-UNIQUE) (Cost=2 Card=84)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
17143 bytes sent via SQL*Net to client
270 bytes received via SQL*Net from client
7 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
84 rows processed
我们看到30%以上使用索引的成本要高于全表扫描的。
下面我们说一下什么时候Oracle会使用全表扫描:
第一、没有或是无效索引。 当使用的SQL语句中没有对就的字段做索引,或是索引字段顺序与where子句中的字段顺序不一致时,当使用非前导字段做谓词条件,还就是在where子句中使用已经有索引字段做函数操作,如:col1字段有一个单索引,在where子句中使用upper(col1)=:b,这都使用不到索引。
第二、大数据量,如果SQL语句将操作表中的绝大多数据块,那么SQL语句将使用全表扫描,既是该字段有索引。
第三、小表,也就是前面我们说到的,当HWM小于db_file_multiblock_read_count值时,会使用全表扫描。
第四、高并行度,当表或SQL指定了一个高的并行运行(parallel degree),Oracle会倾向于使用全表扫描。
第五、使用全表扫描的提示(hint),就面上面我们做的那个例一样。