程序博客网 > 台式机品牌 知乎

有关全表扫描的讨论

来源：互联网 发布：台式机品牌 知乎 编辑：程序博客网 时间：2024/05/14 05:48

       我想这个大家都是知道的，不就是全表扫描嘛，但是我想问一下大家，什么情况下做全表扫描会比较好，它有什么有关系呢？

 　小表是使用全表扫描比较好，但是怎么来确定全表扫描比较小呢？在oracle的定义是当表中的HWM小于
db_file_multiblock_read_count中的设置值，这才算小表，这种情况下使用全表扫描要比使用索引会更快。
　下面我们做个测试(init.ora文件中:optimizer_mode=first_rows,db_file_multiblock_read_count=8,
也就是说我们必须使用CBO的优化器方式)，首先我们建立一个demo的表，表中的serv_id为唯一值，建表语句如下：
　 create table full_table_scan_demo tablespace TS_OB_1M_01 as select * from zk.cm_user where rownum < 11;
　 第二步、在这个上面建立索引：
　 create index idx_ftsd_serv_id on full_table_scan_demo (serv_id) tablespace TS_OB_1M_01 pctfree 10;
　 第三步、我们收集统计值：
　 exec dbms_stats.gather_table_stats('CUIGB','FULL_TABLE_SCAN_DEMO',cascade=>TRUE);
　 第四步、我们做个查询语句的执行计划：(使用sqlplus，set autotrace traceonly)
SQL> select * from full_table_scan_demo where serv_id=2000004461;


执行计划
----------------------------------------------------------
　 0　　　SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=2 Bytes=524)
　 1　　0　 TABLE ACCESS (BY INDEX ROWID) OF 'FULL_TABLE_SCAN_DEMO' (Cost=3 Card=2 Bytes=524)
　 2　　1　　 INDEX (RANGE SCAN) OF 'IDX_FTSD_SERV_ID' (NON-UNIQUE) (Cost=2 Card=2)

统计信息
----------------------------------------------------------
　　　　　0　recursive calls
　　　　　0　db block gets
　　　　　4　consistent gets
　　　　　0　physical reads
　　　　　0　redo size
　　　 1552　bytes sent via SQL*Net to client
　　　　236　bytes received via SQL*Net from client
　　　　　2　SQL*Net roundtrips to/from client
　　　　　0　sorts (memory)
　　　　　0　sorts (disk)
　　　　　2　rows processed
　　第五步，我再执行一个SQL（没有创建索引的）
SQL>　select * from full_table_scan_demo where cust_id=1111195039;
执行计划
----------------------------------------------------------
　 0　　　SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=2 Bytes=524)
　 1　　0　 TABLE ACCESS (FULL) OF 'FULL_TABLE_SCAN_DEMO' (Cost=3 Card=2 Bytes=524)

统计信息
----------------------------------------------------------
　　　　　0　recursive calls
　　　　　0　db block gets
　　　　　5　consistent gets
　　　　　0　physical reads
　　　　　0　redo size
　　　 1641　bytes sent via SQL*Net to client
　　　　236　bytes received via SQL*Net from client
　　　　　2　SQL*Net roundtrips to/from client
　　　　　0　sorts (memory)
　　　　　0　sorts (disk)
　　　　　2　rows processed
　　第六步：我们查看user_tables数据字典中的结果：
SQL> select table_name,blocks from user_tables where table_name='FULL_TABLE_SCAN_DEMO';

TABLE_NAME　　　　　　　　　　　　 BLOCKS
------------------------------ ----------
FULL_TABLE_SCAN_DEMO　　　　　　　　　　2
　 我们看到使用索引与不使用索引在2个数据块的情况下是CBO的cost是一样的都是3.
　 我们再加数据块来看。首先我们使用truncate将原来的数据清除，再插入：
insert into full_table_scan_demo select * from zk.cm_user where rownum < 121;
　 再做统计值的收集
　 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';
　 -------
　 5
　
　 我们对下面做执行计划的生成（有索引）：
select * from full_table_scan_demo where serv_id=2000004461;　
　 执行计划
----------------------------------------------------------
　 0　　　SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1 Bytes=288)
　 1　　0　 TABLE ACCESS (BY INDEX ROWID) OF 'FULL_TABLE_SCAN_DEMO' (Cost=3 Card=1 Bytes=288)
　 2　　1　　 INDEX (RANGE SCAN) OF 'IDX_FTSD_SERV_ID' (NON-UNIQUE) (Cost=2 Card=1)

统计信息
----------------------------------------------------------
　　　　　0　recursive calls
　　　　　0　db block gets
　　　　　3　consistent gets
　　　　　0　physical reads
　　　　　0　redo size
　　　 1535　bytes sent via SQL*Net to client
　　　　236　bytes received via SQL*Net from client
　　　　　2　SQL*Net roundtrips to/from client
　　　　　0　sorts (memory)
　　　　　0　sorts (disk)
　　　　　1　rows processed
　　再对没有索引的字段（cust_id）做执行计划
SQL> select * from full_table_scan_demo where cust_id=1111195039;
执行计划
----------------------------------------------------------
　 0　　　SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1 Bytes=288)
　 1　　0　 TABLE ACCESS (FULL) OF 'FULL_TABLE_SCAN_DEMO' (Cost=3 Card=1 Bytes=288)

统计信息
----------------------------------------------------------
　　　　　0　recursive calls
　　　　　0　db block gets
　　　　　8　consistent gets
　　　　　0　physical reads
　　　　　0　redo size
　　　 1624　bytes sent via SQL*Net to client
　　　　236　bytes received via SQL*Net from client
　　　　　2　SQL*Net roundtrips to/from client
　　　　　0　sorts (memory)
　　　　　0　sorts (disk)
　　　　　1　rows processed
　我们对比较他们的成本还是一样，也都是3,但这有个不同，这就是使用索引要存取2个数据块，全表扫描的要访问5个数据块，
但我们知道，db_file_multiblock_read_count=8，这样Oracle就会在一个IO操作中完成，所有问题两个块的成本与5个数据块
的成本基本上没有什么差别，所以我们看到cost都为3。

我们再加大数据块的个数，或说提高HWM，我的操作如下面的SQL：
truncate table full_table_scan_demo drop storage;
insert into full_table_scan_demo select * from zk.cm_user where rownum < 181;
　select blocks from user_tables where table_name='FULL_TABLE_SCAN_DEMO';
　----------
　10
这样我们再做有索引的SQL
SQL> select * from full_table_scan_demo where serv_id=2000004461;　
执行计划
----------------------------------------------------------
　 0　　　SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=3 Card=1 Bytes=289)
　 1　　0　 TABLE ACCESS (BY INDEX ROWID) OF 'FULL_TABLE_SCAN_DEMO' (Cost=3 Card=1 Bytes=289)
　 2　　1　　 INDEX (RANGE SCAN) OF 'IDX_FTSD_SERV_ID' (NON-UNIQUE) (Cost=2 Card=1)

统计信息
----------------------------------------------------------
　　　　　0　recursive calls
　　　　　0　db block gets
　　　　　3　consistent gets
　　　　　0　physical reads
　　　　　0　redo size
　　　 1535　bytes sent via SQL*Net to client
　　　　236　bytes received via SQL*Net from client
　　　　　2　SQL*Net roundtrips to/from client
　　　　　0　sorts (memory)
　　　　　0　sorts (disk)
　　　　　1　rows processed
　执行不使用索引的SQL：　
SQL> select * from full_table_scan_demo where cust_id=1111195039;
执行计划
----------------------------------------------------------
　 0　　　SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=1 Bytes=289)
　 1　　0　 TABLE ACCESS (FULL) OF 'FULL_TABLE_SCAN_DEMO' (Cost=4 Card=1 Bytes=289)

统计信息
----------------------------------------------------------
　　　　　0　recursive calls
　　　　　0　db block gets
　　　　 13　consistent gets
　　　　　0　physical reads
　　　　　0　redo size
　　　 1624　bytes sent via SQL*Net to client
　　　　236　bytes received via SQL*Net from client
　　　　　2　SQL*Net roundtrips to/from client
　　　　　0　sorts (memory)
　　　　　0　sorts (disk)
　　　　　1　rows processed
　这样我们看到如果表中的数据大于db_file_multiblock_read_count=8后，没有使用索引的全表扫描，成本已经变为4了，
使用到索引的成本还是3。
　通过上面的我们就能看到全表扫描成本要高与索引的扫描，所以我们建议表再小（就是我们通常说的小表）都要使用索引，
但这个是有一个先决条件的，那就是建立索引的那个字段，必须有很高的数据分散性，如果数据值的分散性不好那就不能
使用，下面我们再来说明这个。

我们再将原来表中的数据清除，使用下面的语句：
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%以上使用索引的成本要高于全表扫描的。

　 我看到当SQL语句操作的数据量要大于原集（是表，或是视图等）的20%以上Oracle在使用索引时可能就会比全表扫描要差，在Oracle9i中Oracle认为使用索引的最佳操作数据量是小于全表15%，当然使用数据量是不准确的一个方法，准确的是每个SQL在执行过程中操作的数据块的个与全表数据的比例是多少，总的来说就是一个原则，就是：每个SQL语句必须在操作尽可能少的数据块的情况下完成操作，达到效果。
　 下面我们说一下什么时候Oracle会使用全表扫描：
　 第一、没有或是无效索引。 当使用的SQL语句中没有对就的字段做索引，或是索引字段顺序与where子句中的字段顺序不一致时，当使用非前导字段做谓词条件，还就是在where子句中使用已经有索引字段做函数操作，如：col1字段有一个单索引，在where子句中使用upper(col1)=:b，这都使用不到索引。
　 第二、大数据量，如果SQL语句将操作表中的绝大多数据块，那么SQL语句将使用全表扫描，既是该字段有索引。
　 第三、小表，也就是前面我们说到的，当HWM小于db_file_multiblock_read_count值时，会使用全表扫描。
　 第四、高并行度，当表或SQL指定了一个高的并行运行(parallel degree)，Oracle会倾向于使用全表扫描。
　 第五、使用全表扫描的提示（hint），就面上面我们做的那个例一样。

台式机品牌 知乎 台式机品牌 知乎
原创粉丝点击
热门IT博客
imovie windowsimovie windows
海康域名注册平台海康域名注册平台
java官网jdk
java 控制日志级别
处理器优化
java微信发送模板消息
老舍诺贝尔文学奖 知乎
下载打印机软件
程序编程培训班
excel筛选重复数据函数
聚合数据什么梗
apache没有MySQL
彩票大赢家软件
电视机看电视软件
ps mac 破解版
数据库原理 微盘
淘宝客服网上哪个页面
node sass 命令行
登山机 知乎
阿里云万网域名注册
热门问题 老师的惩罚 人脸识别 我在镇武司摸鱼那些年 重生之率土为王 我在大康的咸鱼生活 盘龙之生命进化 天生仙种 凡人之先天五行 春回大明朝 姑娘不必设防，我是瞎子 m发际线男士发型图 际遇什么意思 风云际遇 十三岁的际遇 际遇是什么意思 际遇的意思 席慕容际遇 曾有些荆棘的际遇 总有些惊奇的际遇是什么歌 总有些惊奇的际遇 曾有些荆棘的际遇是什么歌 总有一些惊奇的际遇 陆 陆小姐又美又娇 陆一 陆一伟传奇 陆一语和霍予沉 陆一语霍予沉 2个帅老公陆一炎子涛 腹黑双胞胎陆一琛 陆一语霍予沉全文免费阅读 霍予沉陆一语免费阅读 程海安和陆一琛195章免费阅读 霍予沉与陆一语 霍少的闪婚暖妻陆一语全文 霍少的闪婚媛妻陆一语 霍少的闪婚暖妻陆一言 霍少的闪婚暖妻陆一语 陆丰 海陆丰 陆丰市 陆丰x7 广东陆丰 陆丰x8 陆丰汽车 陆丰邮编 陆丰x5 x6陆丰 陆丰在线 陆丰在线网 陆丰核电站

程序博客网，程序员的互联网技术博客家园。csdn论坛精品 msdn技术资料都在这里