oracle blevel降低,ORACLE-空块导致扫描慢的学习

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:以上均为个人学习之后的理解，如有错误，恳请指正。

学习来源：网络搜索