Oracle中rownum对表的扫描方式性能上的影响深入探究

最新推荐文章于 2024-05-17 16:32:57 发布

cong0516

最新推荐文章于 2024-05-17 16:32:57 发布

阅读量1k

点赞数

文章标签：数据库操作系统

本文通过实验详细探讨了Oracle数据库中rownum对表扫描方式的影响，展示了不同设置下对数据读取效率的差异，揭示了rownum在数据库操作中的性能表现，并分析了相关优化策略。

摘要由CSDN通过智能技术生成

前言：

偶然发现rownum上加限定条件后，执行计划虽然是全表扫描，但是实际上却是只扫描了满足条件的行所在的数据块，也就是没有做全表扫描。

问题可以假设性描述为一条select语句的全表扫描，结果是1000行，加上rownum<=500之后，oracle是扫描了前500行所在的块，还是扫描了1000行后取前500行（前者性能要更好）。

构造数据进行探究。探究过程中影响的因素有

1.参数db_file_multiblock_read_count，关于该参详情可以读阅 http://blog.itpub.net/30174570/viewspace-2140241/ 。

2.动态采样。

操作系统信息：

[oracle@oracle ~]$ uname -a
Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
[oracle@oracle ~]$ lsb_release -a
LSB Version: :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
Distributor ID: RedHatEnterpriseServer
Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
Release: 6.5
Codename: Santiago

数据库版本：

SYS@proc> select * from v$version where rownum<=1;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

构造测试数据：

SYS@proc> drop table test purge;
Table dropped.
SYS@proc> create table test tablespace test as select * from dba_objects where rownum<=1500;
Table created.
SYS@proc> select dbms_rowid.rowid_row_number(rowid) row# from test where rownum<=10;
ROW#
----------
0
1
2
3
4
5
6
7
8
9
10 rows selected.
SYS@proc> select dbms_rowid.rowid_block_number(rowid) block#,min(rownum),max(rownum) from test group by dbms_rowid.rowid_block_number(rowid) order by dbms_rowid.rowid_block_number(rowid);
BLOCK# MIN(ROWNUM) MAX(ROWNUM)
---------- ----------- -----------
139 1 88
140 89 171
141 172 251
142 252 329
143 330 407
144 408 487
145 488 567
146 568 646
147 647 724
148 725 798
149 799 873
BLOCK# MIN(ROWNUM) MAX(ROWNUM)
---------- ----------- -----------
150 874 946
151 947 1022
161 1023 1104
162 1105 1179
163 1180 1261
164 1262 1342
165 1343 1418
166 1419 1496
167 1497 1500
20 rows selected.
SYS@proc> select EXTENT_ID,FILE_ID,BLOCK_ID,BLOCKS from dba_extents where owner='SYS' and SEGMENT_NAME='TEST';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
---------- ---------- ---------- ----------
0 6 136 8 --136 137 138 139 140 141 142 143
1 6 144 8 --144 145 146 147 148 149 150 151
2 6 160 8 --160 161 162 163 164 165 166 167

实验 过程：
说明：由于该过程是做的全表扫描下对rownum加限定词后，扫描表的块是否发生减少，为了避免db_file_multiblock_read_count的影响，将其值设置为1。

SYS@proc> alter system flush buffer_cache;
System altered.
SYS@proc> alter system flush shared_pool;
System altered.
SYS@proc> select file#,dbablk,state from x$bh where obj=(select data_object_id from dba_objects where owner='SYS' and object_name='TEST') and state<>0 order by dbablk;
no rows selected
SYS@proc> select count(*) from test where rownum<=171; --只读前两个块
COUNT(*)
----------
171
SYS@proc> set pagesize 9999
SYS@proc> select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID 5h6qpq1adpkh8, child number 0
-------------------------------------
select count(*) from test where rownum<=171
Plan hash value: 827909369
--------------------------------------------------------------------
| Id | Operation |

关注