Oracle取得表中总记录数最快的方法

由于ORACLE的缓存和共享池的机制，SQL语句逻辑读一般从第二次执行才稳定下来，出于篇幅的考虑，下面所有的SELECT COUNT(*) FROM

查询表中的记录总数的语法就是select count(*) from table_name。这可能是最经常使用的一类sql语句。
 
本文讨论怎样才能最快的得到这个记录数。本文纯粹主要是理论上的讨论，文章中很多内容（如常数索引）对实际的指导意义不大。
 
在具体描述之前，强调几个前提：
 
首先表中的记录数不能太少，否则讨论的意义就不大了，在我下面的例子中记录数是3万左右，其实这个数量级还是比较小，不过已经能够看出一些效果了。
 
根据执行时间的长短进行判断偶然性比较大，本文以没种方法逻辑读的多少来进行判断。由于包括查询重写（需要的相对较多的执行计划的分析）和索引压缩（属于cpu密集型，消耗cpu资源较多），仅仅用逻辑读来衡量各种方法的优劣肯定不会很准确，但是考虑到表中的数据量比较大，而且我们以sql的第二次执行结果为准，所以，其他方面的影响还是可以忽略的。
 
另外一个前提就是结果的准确性，查询user_tables的num_rows列等类似的方法不在本文讨论范畴之内。
 
最后，由于oracle的缓存和共享池的机制，sql语句逻辑读一般从第二次执行才稳定下来，出于篇幅的考虑，下面所有的select count(*) from t的结果都是该sql语句第二次执行的结果。
 
如果存在一个查询语句为select count(*)的物化视图，则最快的方式一定是扫描这张物化视图。
 
sql> create table t (id number not null, name varchar2(30), type varchar2(18));
 
表已创建。
 
sql> insert into t select rownum, object_name, object_type from dba_objects;
 
已创建30931行。
 
sql> commit;
 
提交完成。
 
sql> create materialized view log on t with rowid including new values;
 
实体化视图日志已创建。
 
sql> create materialized view mv_t refresh fast on commit enable query rewrite as
 2 select count(*) from t;
 
实体化视图已创建。
 
sql> alter session set query_rewrite_enabled = true;
 
会话已更改。
 
sql> exec dbms_stats.gather_table_stats(user, 't')
 
pl/sql 过程已成功完成。
 
sql> set autot on
 sql> select count(*) from t;
 
count(*)
 ----------
 30931
 
execution plan
 ----------------------------------------------------------
 0 select statement optimizer=choose (cost=2 card=82 bytes=1066)
 1 0 table access (full) of 'mv_t' (cost=2 card=82 bytes=1066)
 
statistics
 ----------------------------------------------------------
 0 recursive calls
 0 db block gets
 3 consistent gets
 0 physical reads
 0 redo size
 378 bytes sent via sql*net to client
 503 bytes received via sql*net from client
 2 sql*net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
 
根据上面的查询可以看出，扫描物化视图，只需3个逻辑读就可以了。但是，物化视图对系统的限制比较多。首先要创建物化视图日志，还要在system或session级设置参数，必须使用cbo等很多的条件，限制了物化视图的使用，而且最重要的是，一般情况下不会存在一个单纯查询全表记录数的物化视图，而一般建立的物化视图是为了加快一些更加复杂的表连接或聚集的查询的。因此，，即使存在物化视图，也不会直接得到结果，一般是对物化视图上的结果进行再次计算。
 
如果不考虑物化视图，那么得到记录总数的最快的方法一定是bitmap索引扫描。bitmap索引的机制使得bitmap索引回答count(*)之类的查询具有最快的响应速度和最小的逻辑读。至于bitmap索引的机制，这里就不重复描述了，还是看看bitmap索引的表现吧：
 
sql> drop materialized view mv_t;
 
实体化视图已删除。
 
sql> drop materialized view log on t;
 
实体化视图日志已删除。
 
sql> create bitmap index ind_b_t_type on t (type);
 
索引已创建。
 
sql> exec dbms_stats.gather_index_stats(user, 'ind_b_t_type')
 
pl/sql 过程已成功完成。
 
sql> select count(*) from t;
 
count(*)
 ----------
 30931
 
execution plan
 ----------------------------------------------------------
 0 select statement optimizer=choose (cost=2 card=1)
 1 0 sort (aggregate)
 2 1 bitmap conversion (count)
 3 2 bitmap index (fast full scan) of 'ind_b_t_type'
 
statistics
 ----------------------------------------------------------
 0 recursive calls
 0 db block gets
 5 consistent gets
 0 physical reads
 0 redo size
 378 bytes sent via sql*net to client
 503 bytes received via sql*net from client
 2 sql*net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
 
可以看到，bitmap索引的表现十分出色，只需5个逻辑读就可以得到结果。可惜的是，bitmap索引比较适合在数据仓库中使用，而对于oltp环境，bitmap索引的锁粒度将给整个系统带来严重的灾难。因此，对于oltp系统，bitmap索引也是不合适的。
 
不考虑bitmap索引，那么速度最快的应该是普通索引的快速全扫了，比如主键列。
 
sql> drop index ind_b_t_type;
 
索引已丢弃。
 
sql> alter table t add constraint pk_t primary key (id);
 
表已更改。
 
sql> select count(*) from t;
 
count(*)
 ----------
 30931
 
execution plan
 ----------------------------------------------------------
 0 select statement optimizer=choose (cost=4 card=1)
 1 0 sort (aggregate)
 2 1 index (fast full scan) of 'pk_t' (unique) (cost=4 card=30931)
 
statistics
 ----------------------------------------------------------
 0 recursive calls
 0 db block gets
 69 consistent gets
 0 physical reads
 0 redo size
 378 bytes sent via sql*net to client
 503 bytes received via sql*net from client
 2 sql*net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
 
主键的快速全扫只需69个逻辑读。但是由于主键这里用的是rownum，也就是说是主键的值是从1到30931，oracle存储这些number类型则需要2到4位不等。如果建立一个常数索引，则在存储空间上要节省一些。而在执行索引快速全扫时，就能减少一些逻辑读。
 
sql> create index ind_t_con on t(1);
 
索引已创建。
 
sql> select count(*) from t;
 
count(*)
 ----------
 30931
 
execution plan
 ----------------------------------------------------------
 0 select statement optimizer=choose (cost=4 card=1)
 1 0 sort (aggregate)
 2 1 index (fast full scan) of 'ind_t_con' (non-unique) (cost=4 card=30931)
 
statistics
 ----------------------------------------------------------
 0 recursive calls
 0 db block gets
 66 consistent gets
 0 physical reads
 0 redo size
 378 bytes sent via sql*net to client
 503 bytes received via sql*net from client
 2 sql*net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
 
果然，扫描常数索引比扫描主键的逻辑读更小一些。考虑到number类型中，1的存储需要两位，而0的存储只需一位，那么用0代替1创建常数索引，应该效果更好。
 
sql> create index ind_t_con_0 on t(0);
 
索引已创建。
 
sql> select /*+ index(t ind_t_con_0) */ count(*) from t;
 
count(*)
 ----------
 30931
 
execution plan
 ----------------------------------------------------------
 0 select statement optimizer=choose (cost=26 card=1)
 1 0 sort (aggregate)
 2 1 index (full scan) of 'ind_t_con_0' (non-unique) (cost=26 card=30931)
 
statistics
 ----------------------------------------------------------
 0 recursive calls
 0 db block gets
 58 consistent gets
 0 physical reads
 0 redo size
 378 bytes sent via sql*net to client
 503 bytes received via sql*net from client
 2 sql*net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
 
由于常数索引中所有节点值都相同，如果压缩一下的话，应该还能减少逻辑读。
 
sql> drop index ind_t_con_0;
 
索引已丢弃。
 
sql> create index ind_t_con_compress on t(0) compress;
 
索引已创建。
 
sql> select /*+ index(t ind_t_con_compress) */ count(*) from t;
 
count(*)
 ----------
 30931
 
execution plan
 ----------------------------------------------------------
 0 select statement optimizer=choose (cost=26 card=1)
 1 0 sort (aggregate)
 2 1 index (full scan) of 'ind_t_con_compress' (non-unique) (cost=26 card=30931)
 
statistics
 ----------------------------------------------------------
 0 recursive calls
 0 db block gets
 49 consistent gets
 0 physical reads
 0 redo size
 378 bytes sent via sql*net to client
 503 bytes received via sql*net from client
 2 sql*net roundtrips to/from client
 0 sorts (memory)
 0 sorts (disk)
 1 rows processed
 
和预计的一样，经过压缩，索引扫描的逻辑读进一步减少，现在和最初的主键扫描相比，逻辑读已经减少了30%。
 
如果只为了得到count(*)，那么压缩过的常数索引是最佳选择，不过这个索引对其他查询是没有任何帮助的，因此，实际中的用处不大。

魔术橡皮擦

智能擦除、填补背景内容

105

查看详情