A common question that gets asked is does Oracle access the index itself or the parent table during an index rebuild to extract the necessary data for the index ? Thought it might be worth a blog post to discuss.
Now if the index is currently in an UNUSABLE state, then Oracle clearly can’t use the existing index during the index rebuild operation. So we’ll assume both table and index are hunky dory.
OK, to setup the first demo (using 11.2.0.1), we create and populate a table and index with the index being somewhat smaller than the parent table as is most common:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL> create table bowie (id number, code number, name1 varchar2(30), name2 varchar2(30), name3 varchar2(30), name4 varchar2(30), name5 varchar2(30), name6 varchar2(30), name7 varchar2(30), name8 varchar2(30), name9 varchar2(30), name10 varchar2(30));
Table created.SQL> insert into bowie select rownum, mod(rownum, 100), 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE','DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE' from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;Commit complete.SQL> create index bowie_code_i on bowie(code);
Index created.SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=>null, cascade=> true);
PL/SQL procedure successfully completed. |
If we look at the corresponding size of table and index:
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> select table_name, blocks from dba_tables where table_name = 'BOWIE';
TABLE_NAME BLOCKS------------------------------ ----------BOWIE 19277
SQL> select index_name, leaf_blocks from dba_indexes where index_name = 'BOWIE_CODE_I';
INDEX_NAME LEAF_BLOCKS------------------------------ -----------BOWIE_CODE_I 1948
|
As is common, the table is somewhat larger than the corresponding index.
Now in my first demo, I’m just going to perform a normal offline Index Rebuild. I’ll however trace the session to see what might be happening behind the scenes (the good old alter session set events ‘10046 trace name context forever, level 12′; still does the job). I’ll also flush the buffer cache as well to ensure the trace file shows me which blocks from which object get accessed.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> alter system flush buffer_cache;System altered.SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.SQL> alter index bowie_code_i rebuild;Index altered.SQL> alter session set events '10046 trace name context off';
Session altered. |
There’s lots of information of interest in the resultant trace file, well, for someone with an unhealthy interest in Oracle indexes anyways :) However, the portion that’s of direct interest in this discussion is to see which object Oracle accesses in order to read the necessary data for the index rebuild. The trace file will contain a relatively extensive section with the following wait events (the following is just a short sample):
WAIT #6: nam=’db file scattered read’ ela= 933 file#=4 block#=79339 blocks=5 obj#=75737 tim=20402099526
WAIT #6: nam=’db file scattered read’ ela= 1016 file#=4 block#=79344 blocks=8 obj#=75737 tim=20402102334
WAIT #6: nam=’db file scattered read’ ela= 978 file#=4 block#=79353 blocks=7 obj#=75737 tim=20402106904
WAIT #6: nam=’db file scattered read’ ela= 9519 file#=4 block#=80000 blocks=8 obj#=75737 tim=20402119605
WAIT #6: nam=’db file scattered read’ ela= 2800 file#=4 block#=80009 blocks=7 obj#=75737 tim=20402131869
….
If we query the database for the identity of object 75737:
|
1
2
3
4
5
6
|
SQL> select object_name from dba_objects where object_id = 75737;
OBJECT_NAME-----------------------BOWIE_CODE_I |
We can see that Oracle has accessed the data from the Index itself, using multi-block reads. As the index is the smallest segment that contains the necessary data, Oracle can very efficiently read all the required data (the expensive bit) from the index itself, perform a sort of all the data (as a multi-block read will not return the data in a sorted format) and complete the rebuild process relatively quickly. Note the table is locked throughout the entire index rebuild operation preventing DML operations on the table/index and so for an offline index rebuild, Oracle can access the Index segment without complication.
I’m going to repeat the same process but this time perform an Online index rebuild operation:
|
1
2
3
4
5
6
7
8
9
10
11
|
SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.SQL> alter index bowie_code_i rebuild online;Index altered.SQL> alter session set events '10046 trace name context off';
Session altered. |
We notice this time there are many more wait events than previously and that another object is referenced:
WAIT #5: nam=’db file scattered read’ ela= 8259 file#=4 block#=5635 blocks=5 obj#=75736 tim=4520179453
WAIT #5: nam=’db file scattered read’ ela= 1656 file#=4 block#=5640 blocks=8 obj#=75736 tim=4520181368
WAIT #5: nam=’db file scattered read’ ela= 891 file#=4 block#=5649 blocks=7 obj#=75736 tim=4520182459
WAIT #5: nam=’db file scattered read’ ela= 886 file#=4 block#=5656 blocks=8 obj#=75736 tim=4520183544
WAIT #5: nam=’db file scattered read’ ela= 827 file#=4 block#=5665 blocks=7 obj#=75736 tim=4520184579
…
|
1
2
3
4
5
6
|
SQL> select object_name from dba_objects where object_id = 75736;
OBJECT_NAME-------------------------BOWIE |
This time, the much larger BOWIE parent table has been accessed. So with an Online rebuild, Oracle is forced to use the parent table to access the data for the rebuild operation due to the concurrency issues associated with changes being permitted to the underlying table/index during the rebuild process. So although an online index rebuild has availability advantages, it comes at the cost of having to access the parent table which can result in much additional I/O operations. So if you don’t have availability concerns, an offline index rebuild is probably going to be the more efficient option.
In fact, Oracle can be quite clever in deciding which object to access with an offline rebuild …
In this next example, I’m going to create another table/index, only this time the index is somewhat larger than the parent table. This scenario is less common but certainly possible depending on circumstances:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
|
SQL> create table bowie2 (id number, code number, name varchar2(30));
Table created.SQL> insert into bowie2 select rownum, mod(rownum,100), 'DAVID BOWIE' from dual connect by level<= 1000000;
1000000 rows created.
SQL> commit;Commit complete.SQL> create index bowie2_code_i on bowie2(code) pctfree 90;
Index created.SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE2', estimate_percent=>null, cascade=> true);
PL/SQL procedure successfully completed.SQL> select table_name, blocks from dba_tables where table_name = 'BOWIE2';
TABLE_NAME BLOCKS------------------------------ ----------BOWIE2 3520
SQL> select index_name, leaf_blocks from dba_indexes where index_name = 'BOWIE2_CODE_I';
INDEX_NAME LEAF_BLOCKS------------------------------ -----------BOWIE2_CODE_I 21726
|
So the index is indeed much larger than the table. Which object will Oracle access now during an offline rebuild ?
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> alter system flush buffer_cache;System altered.SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.SQL> alter index bowie2_code_i rebuild;
Index altered.SQL> alter session set events '10046 trace name context off';
Session altered. |
A look at the trace file reveals:
WAIT #15: nam=’db file scattered read’ ela= 2278 file#=4 block#=81723 blocks=5 obj#=75744 tim=8570990574
WAIT #15: nam=’db file scattered read’ ela= 2733 file#=4 block#=81728 blocks=8 obj#=75744 tim=8570994765
WAIT #15: nam=’db file scattered read’ ela= 2398 file#=4 block#=81737 blocks=7 obj#=75744 tim=8570999057
WAIT #15: nam=’db file scattered read’ ela= 2661 file#=4 block#=81744 blocks=8 obj#=75744 tim=8571003369
WAIT #15: nam=’db file scattered read’ ela= 1918 file#=4 block#=81753 blocks=7 obj#=75744 tim=8571006709
…
|
1
2
3
4
5
6
|
SQL> select object_name from dba_objects where object_id = 75744;
OBJECT_NAME----------------------------BOWIE2
|
In this case, the smaller table segment is accessed. So during an offline rebuild, Oracle will access either the table or index, depending on which one is smaller and cheaper to read.
What if we now create another index that also contains the CODE column which is smaller than both the table and the existing index.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
SQL> create index bowie2_code_id_i on bowie2(code, id);
Index created.SQL> select index_name, leaf_blocks from dba_indexes where index_name = 'BOWIE2_CODE_ID_I';
INDEX_NAME LEAF_BLOCKS------------------------------ -----------BOWIE2_CODE_ID_I 2642
SQL> alter system flush buffer_cache;System altered.SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.SQL> alter index bowie2_code_i rebuild;
Index altered.SQL> alter session set events '10046 trace name context off';
Session altered. |
A look at the trace file reveals:
WAIT #6: nam=’db file scattered read’ ela= 2070 file#=4 block#=85179 blocks=5 obj#=75747 tim=8925949081
WAIT #6: nam=’db file scattered read’ ela= 2864 file#=4 block#=85184 blocks=8 obj#=75747 tim=8925957161
WAIT #6: nam=’db file scattered read’ ela= 2605 file#=4 block#=85193 blocks=7 obj#=75747 tim=8925969901
WAIT #6: nam=’db file scattered read’ ela= 10636 file#=4 block#=85536 blocks=8 obj#=75747 tim=8925989726
WAIT #6: nam=’db file scattered read’ ela= 2188 file#=4 block#=85545 blocks=7 obj#=75747 tim=8925996890
…
|
1
2
3
4
5
6
|
SQL> select object_name from dba_objects where object_id = 75747;
OBJECT_NAME------------------------------BOWIE2_CODE_ID_I
|
In this case, the smaller alterative index is actually accessed. So it might not be the table or the index being rebuilt that gets accessed, but the smallest segment that contains the data of interest which in this case is another index entirely.
My final little demo brings me back to the subject of secondary indexes on Index Organized Tables (IOTs) I’ve been recently discussing. In this example, I create an IOT and a much smaller secondary index:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
|
SQL> create table bowie3 (id number constraint bowie_pk primary key, code number, name1 varchar2(30), name2 varchar2(30), name3 varchar2(30), name4 varchar2(30), name5 varchar2 (30), name6 varchar2(30), name7 varchar2(30), name8 varchar2(30), name9 varchar2(30), name10 varchar2(30)) organization index;
Table created.SQL> insert into bowie3 select rownum, mod(rownum, 100), 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE', 'DAVID BOWIE','DAVID BOWIE','DAVID BOWIE', 'DAVID BOWIE' from dual connect by level <= 1000000;
1000000 rows created.
SQL> commit;Commit complete.SQL> create index bowie3_code_i on bowie3(code);
Index created.SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE3', estimate_percent=>null, cascade=> true);
PL/SQL procedure successfully completed.SQL> select index_name, leaf_blocks from dba_indexes where table_name = 'BOWIE3';
INDEX_NAME LEAF_BLOCKS------------------------------ -----------BOWIE_PK 16950
BOWIE3_CODE_I 2782
|
So the secondary index is much smaller. However, if I rebuild it offline:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL> alter system flush buffer_cache;System altered.SQL> alter session set events '10046 trace name context forever, level 12';
Session altered.SQL> alter index bowie3_code_i rebuild;
Index altered.SQL> alter session set events '10046 trace name context off';
Session altered. |
A look at the trace file reveals:
WAIT #5: nam=’db file scattered read’ ela= 13019 file#=4 block#=217856 blocks=4 obj#=75733 tim=8949436015
WAIT #5: nam=’db file scattered read’ ela= 1869 file#=4 block#=72915 blocks=5 obj#=75733 tim=8949438360
WAIT #5: nam=’db file scattered read’ ela= 3023 file#=4 block#=72920 blocks=8 obj#=75733 tim=8949442877
WAIT #5: nam=’db file scattered read’ ela= 2381 file#=4 block#=72929 blocks=7 obj#=75733 tim=8949448410
WAIT #5: nam=’db file scattered read’ ela= 2613 file#=4 block#=72936 blocks=8 obj#=75733 tim=8949453521
…
|
1
2
3
4
5
6
|
SQL> select object_name from dba_objects where object_id = 75733;
OBJECT_NAME---------------------------BOWIE_PK |
In this case, we see that the much larger IOT PK segment is accessed and not the smaller secondary index. When rebuilding the secondary index of an IOT, Oracle has no choice but to access the parent IOT PK segment itself as of course the secondary index doesn’t contain all the necessary information required for the index rebuild operation. The physical guess component within the secondary index might be stale and the only way for Oracle to determine the correct current address of all the rows is to access the IOT PK segment. This is another disadvantage of secondary indexes associated with IOTs, even offline index rebuilds must access the potentially much larger IOT PK segment in order to ensure the correctness of the physical guess components of the logical rowids.
So the general answer of whether an index rebuild accesses the table or index is that it depends and that it could very well be neither of them …
参考至:http://richardfoote.wordpress.com/2012/05/15/index-rebuild-does-it-use-the-index-or-the-table-nothing-touches-me/
如有错误，欢迎指正
邮箱:czmcj@163.com
相关推荐
本文将围绕“如何一次性利用生成的脚本rebuild所有table的index”这一主题展开，介绍相关知识点，并结合提供的`demobld.sql`文件进行说明。 首先，我们需要理解索引的作用。索引类似于书籍的目录，允许数据库系统...
根据提供的信息，我们可以总结出以下关于“索引重建（Index Rebuild）”的相关知识点： ### 索引重建概述 **索引重建**是数据库管理中的一个重要操作，它涉及删除现有的索引并重新创建它。这个过程可以帮助优化...
The only source of any storage location information is the sysindexes table, which keeps track of the address of the root page for every index, and the first IAM page for the index or table....
The SQL Server Maintenance Solution lets you intelligently rebuild or reorganize only the indexes that are fragmented. In the IndexOptimize procedure, you can define a preferred index maintenance ...
### Oracle更改表空间（table、index、lob） 在Oracle数据库管理中，更改表空间是一项重要的维护任务，尤其是在需要重新组织数据或优化存储时。本文将详细介绍如何通过SQL命令来更改表空间中的表(table)、索引...
本压缩包“SAP_very_useful_Oracle_SQL_cmd.zip_SAP_SAP rebuild index_Table”提供了与SAP系统相关的一系列Oracle数据库操作的实用工具，主要涉及表的管理和索引重建。 首先，解锁阴影实例（unlock shadow ...
However it is sometimes useful to set the device back to a totally erased state, particularly when making partition table changes or OTA app updates. To erase the entire flash, run idf.py erase_flash...
在SQL Server中，索引重建（Rebuild）是一项重要的维护任务，它有助于优化查询性能，通过重新组织数据页和消除碎片。然而，系统并不直接记录每个索引的最后一次重建时间。当你需要确认某个索引何时进行了重建时，...
### SAP Index索引及其对性能的影响 #### 一、SAP表中的索引(Indexing in SAP Tables) 在SAP系统中，索引是数据库表的一种副本，它仅包含少数几个字段，并始终以排序形式存在。索引的主要目的是提高数据访问速度，...
Theoretically a b+tree is O(N log k N) or log base k of N, now for the typical values of k which are above 200 for example the b+tree should outperform any binary tree because it will use less ...
1. 扫描方式差异：`REBUILD`通常使用`INDEX FAST FULL SCAN`或`TABLE FULL SCAN`，取决于统计信息的成本。`REBUILD ONLINE`则执行表扫描，两者均涉及排序操作。 2. `REBUILD`会阻塞DML操作，而`REBUILD ONLINE`不会...
IMPORTANT: If installing the registered version, please be sure to always re-install/rebuild the components (VCLZip and VCLUnZip) to the component pallette (or rebuild the design time package) so that...
=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~=~= ~ Import REConstructor v... - An editbox for entering the name of the API (MSDN-like when using Index) - Loader -...
A set of build scripts to automate the rebuilding of our components from source code for those who have the DXperience Enterprise or Universal installed. The scripts (DOS batch files) are in the ...
After you find a framework file you could pull it via adb pull /path/to/file or use a file manager application. After you have the file locally, pay attention to how Apktool installs it. The number ...
to the component pallette (or rebuild the design time package) so that the ThisVersion property and any other new properties will be properly updated. If your application still does not run without ...
"nexus index"指的是Nexus仓库中的索引，这个索引包含了仓库中所有组件的元数据，如版本信息、依赖关系等，方便开发者快速查找和下载所需的组件。 在默认情况下，Nexus会定期自动更新其索引，但这个过程可能比较...
Next, it shows you how to use the peripheral libraries in sbt to make common tasks simpler. Finally, it covers how to deploy software effectively. You’ll learn to appreciate how sbt improves the ...
• Save the file, rebuild the solution and start it. This will start a local instance of the application. • The solution can also be deployed into a Azure App service. Please use VisualStudio 2015's ...
Solar Board Rebuild on TI part4