select存在rownum的条件下,为什么读取的块比较多,并且返回结果的速度较慢?
最近有人问我为什么这个语句 select * from table_name where rownum <= 10查询条件却很慢,我思考了下,只有单表全表扫的情况,并且只返回前10行,怎么会慢呢?带着疑问进行了学习。
实验证明之前我先补充一个最近学习的知识点,因为在创建语句中会使用。
PCTFREE:表示一个数据块block中保留可用空间的百分比,一般默认为10,也就是10%,在一个假定一个数据块能保存100行数据的情况下,就要保留10%也就是10行的数据,当此块已经插入到90行数据时,就会从FREELIST上移走此块,确保不会再被写入数据,剩余的10%的空间是留给后续修改可用空间。
PCTUSED:表示一个数据块block剩余空间低于指定百分比时才能够继续使用,一般默认为40,也就是40%,在一个假定一个数据块能够保存100行数据的情况下,并且PCTFREE为10的情况下,此时已经使用到90%了,开始删除数据,删除到剩余40行时,这时并不会加入FREELIST,因为此时40行等于设定的40%,必须得低于40%,也就是再删除一行,才能够加入FREELIST重新提供数据的插入功能。
在表空间开启了ASMM也就是表空间段自动管理的情况下,只有PCTFREE生效,PCTUSED不生效。
这里可以看到表空间段自动管理开启的情况下,修改PCTUSED是无效的。
实验1-select中存在rownum很慢
create tablespace zwy_test datafile size 100m Segment Space Management Manual;
创建一个段自动管理的表空间。
然后创建一个属性pctfree为70,pctused为30的ZWY_TEST表,这样设定是为了尽量一行数据就占据一个数据块,后续查看执行计划时更简单明了。
现在这个表共有10000行,总共使用了10240个块,可以近似的看做1行数据占据1个数据块.
这样的一个语句,读取的块应该是很少的就是第一行的数据块,结果也应该是快速返回的。
本次select结果显示确实如此,但是有没有其他情况呢?
发现这里结果只返回了一行但是扫描了505个块,也就是扫描了前500个空行的数据块。
现在回滚,删除500行以后的数据进行测试。
所以这里可以得到一个结论是:当执行计划走全表扫描时,如果前面的空块很多,取特定的少量的数据时比如rownum是可能出现很慢的情况,因为需要扫描的块很多。
实验2-INDEX RANGE SCAN (MIN/MAX)很慢
这个是在搜索第一个问题时,类似的扫描过程中也很慢的问题。
当我们使用select min(values) from table或者select max(values) from table的时候,若该字段上存在能使用的索引,执行计划会使用index full scan(min/max)直接获取该值。
create table zwy_test1 as select * from dba_objects;
create index idx_test on zwy_test1(owner,object_id);
analyze table zwy_test1 estimate statistics;
查询最大值和最小值时都显示只消耗了2个逻辑读。
现在将所有数据全删除,进行查询。
现在可以发现111的逻辑读远远比2要大了,如果表的数量基数再大一点,那么执行计划走这个肯定是会很慢的,这里慢的原因就是因为存在很多空块。
INDEX RANGE SCAN (MIN/MAX)的原理:从根节点root一直走最左边的 或者最右边的branch block->leaf block叶子节点,根据索引树的深度读取所需要扫描的块数。
blevel为1的树,说明树高为1+1=2,也对应了开始的时候正常扫描的2个逻辑读。但是如果中间出现了很多空块,它会扫描范围之内所有的空块。
PS:以上均为个人学习之后的理解,如有错误,恳请指正。
学习来源:网络搜索