经查看执行计划，发现执行select count(id) from SWD_BILLDETAIL;使用全表扫描，执行完用了大约1.50分钟(4次执行取平均，每次分别为1.45 1.51 2.00 1.46)。而执行select count(id) from SWD_BILLDETAIL where cn <'6';却用了2个小时还没有执行完，经分析该语句使用了cn列上的索引，然后利用查询出的rowid再从表中查询数据。我为什么不使用select count(cn) from SWD_BILLDETAIL where cn <'6';呢？后面在分析执行路径的索引扫描时时会给出说明。
下面就是基于规则的优化器使用的执行路径与各个路径对应的等级： RBO Path 1: Single Row by Rowid(等级最高) RBO Path 2: Single Row by Cluster Join RBO Path 3: Single Row by Hash Cluster Key with Unique or Primary Key RBO Path 4: Single Row by Unique or Primary Key RBO Path 5: Clustered Join RBO Path 6: Hash Cluster Key RBO Path 7: Indexed Cluster Key RBO Path 8: Composite Index RBO Path 9: Single-Column Indexes RBO Path 10: Bounded Range Search on Indexed Columns RBO Path 11: Unbounded Range Search on Indexed Columns RBO Path 12: Sort Merge Join RBO Path 13: MAX or MIN of Indexed Column RBO Path 14: ORDER BY on Indexed Column RBO Path 15: Full Table Scan(等级最低)
精品推荐