索引组织表
索引组织表的存储结构是按照主键的 B-tree 结构搭建的。不象普通的表（堆 积表——数据的存储是无序进行的），索引组织表中的数据是按照主键的 B-tree 结构排序后保存的。包括保存索引组织表行的主键字段值在内，B-tree 中的每一 个索引项还保存了非键字段的值。
组织索引表实际上就是索引的表化
为什么要引进组织索引表
create table org_index_table
( object_id int primary key,
oname varchar2(30),
owner varchar2(30),
status varchar2(30))
organization index;
create table heap_table
( object_id int primary key,
oname varchar2(30),
owner varchar2(30),
status varchar2(30)
)
alter table org_index_table nologging;
alter table heap_table nologging;
create table s_table as
select object_id, object_name, owner,status
from all_objects
insert into heap_table select * from s_table
call count cpu elapsed disk query current rows
------- ------ -------- -------------------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 1 0 0
Execute 1 0.48 0.62 90 2301 11134 70231
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- -------------------- ---------- ---------- ----------
total 2 0.50 0.63 90 2302 11134 70231
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 114
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=2575 pr=90 pw=90 time=0 us)
70231 TABLE ACCESS FULL S_TABLE (cr=450 pr=88pw=88 time=1380 us cost=128 size=4239300 card=81525)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 16 0.08 0.14
db file sequential read 8 0.01 0.02
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
Insert into org_index_table select* from s_table
call count cpu elapsed disk query current rows
------- ------ -------- -------------------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 1 0 0
Execute 1 0.39 0.57 0 2389 10939 70231
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- -------------------- ---------- ---------- ----------
total 2 0.39 0.57 0 2390 10939 70231
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD TABLE CONVENTIONAL (cr=2535 pr=0 pw=0 time=0 us)
70231 TABLE ACCESS FULL S_TABLE (cr=450 pr=0 pw=0time=1079 us cost=128 size=4239300 card=81525)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
log buffer space 1 0.14 0.14
log file sync 1 0.02 0.02
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 0.00 0.00
selectindex_name,table_name
fromuser_indexes
wheretable_name in( 'HEAP_TABLE','ORG_INDEX_TABLE')
|
INDEX_NAME |
TABLE_NAME |
|
SYS_C0016433 |
HEAP_TABLE |
|
SYS_IOT_TOP_84235 |
ORG_INDEX_TABLE |
begin
scott.show_space(p_segname =>'HEAP_TABLE');
end;
Unformatted Blocks .....................0
FS1 Blocks (0-25) ......................0
FS2 Blocks (25-50) .....................0
FS3 Blocks (50-75) .....................1
FS4 Blocks (75-100).....................52
Full Blocks ............................443
Total Blocks............................512
Total Bytes.............................4194304
Total MBytes............................4
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................4
Last Used Ext BlockId...................441609
Last Used Block.........................128
select * from user_tables where table_name in( 'HEAP_TABLE','ORG_INDEX_TABLE')
我们看不到块的个数
begin
scott.show_space(p_segname =>'ORG_INDEX_TABLE');
end;
出现错误
怎么看着个表的大小？
select * from user_segments where segment_name='ORG_INDEX_TABLE'
也看不到数据
analyze index SYS_IOT_TOP_84235 validate structure
|
HEIGHT |
2 |
|
BLOCKS |
512 |
|
NAME |
SYS_IOT_TOP_84235 |
|
PARTITION_NAME |
|
|
LF_ROWS |
70231 |
|
LF_BLKS |
440 |
|
LF_ROWS_LEN |
3350295 |
|
LF_BLK_LEN |
8000 |
|
BR_ROWS |
439 |
|
BR_BLKS |
1 |
|
BR_ROWS_LEN |
4757 |
|
BR_BLK_LEN |
8032 |
|
DEL_LF_ROWS |
0 |
|
DEL_LF_ROWS_LEN |
0 |
|
DISTINCT_KEYS |
70231 |
|
MOST_REPEATED_KEY |
1 |
|
BTREE_SPACE |
3528032 |
|
USED_SPACE |
3355052 |
|
PCT_USED |
96 |
|
ROWS_PER_KEY |
1 |
|
BLKS_GETS_PER_ACCESS |
3 |
|
PRE_ROWS |
0 |
|
PRE_ROWS_LEN |
0 |
|
OPT_CMPR_COUNT |
0 |
|
OPT_CMPR_PCTSAVE |
0 |
analyze index SYS_C0016433 validate structure
|
HEIGHT |
2 |
|
BLOCKS |
256 |
|
NAME |
SYS_C0016433 |
|
PARTITION_NAME |
|
|
LF_ROWS |
70231 |
|
LF_BLKS |
243 |
|
LF_ROWS_LEN |
1043578 |
|
LF_BLK_LEN |
8000 |
|
BR_ROWS |
242 |
|
BR_BLKS |
1 |
|
BR_ROWS_LEN |
2612 |
|
BR_BLK_LEN |
8032 |
|
DEL_LF_ROWS |
0 |
|
DEL_LF_ROWS_LEN |
0 |
|
DISTINCT_KEYS |
70231 |
|
MOST_REPEATED_KEY |
1 |
|
BTREE_SPACE |
1952032 |
|
USED_SPACE |
1046190 |
|
PCT_USED |
54 |
|
ROWS_PER_KEY |
1 |
|
BLKS_GETS_PER_ACCESS |
3 |
|
PRE_ROWS |
0 |
|
PRE_ROWS_LEN |
0 |
|
OPT_CMPR_COUNT |
0 |
|
OPT_CMPR_PCTSAVE |
0 |
我们看看执行计划
explain plan for
select * from ORG_INDEX_TABLE t
where t.object_id=30
select * from table(dbms_xplan.display())
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN|SYS_IOT_TOP_84235 | 1 | 46 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
我们并没有看到TABLE ACCESS BY INDEX ROWID，说明其他字段信息存放在SYS_IOT_TOP_84235
下面一个执行计划继续说明我们的结论
explain plan for
select * from ORG_INDEX_TABLE t
where t.oname='xxxx'
select * from table(dbms_xplan.display())
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 702 | 32292 | 122 (1)| 00:00:02 |
|* 1 | INDEX FASTFULL SCAN| SYS_IOT_TOP_84235 | 702 | 32292 | 122 (1)| 00:00:02 |
------------------------------------------------------------------------------------------
)
我们查找的字段并不是主键，然而还是走索引了，只是走的是全索引扫描。
explain plan for
select * from HEAP_TABLE t
where t.object_id=30
select * from table(dbms_xplan.display())
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 44 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID|HEAP_TABLE | 1 | 44 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0016433 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
对比而言，我们看到组织索引表在查找某些数据（根据主键），没有出现TABLE ACCESS BY INDEX ROWID， 因此提高了查询效率。
从以上分析可以看出，索引组织表有些象普通表与单独索引组成的配置，只不过 没有维护两个单独的存储结构，数据库仅维护一个单独的 B-tree 索引。因此， 每一个索引项包含了<primary_key_value,non_primary_key_column_values>。
使用索引组织表的好处。
索引组织表通过主键或可以成为主键合法前缀的任何键提供了对表数据的 更快访问，在 B-tree 叶块中出现的非键字段避免了额外的数据块访问。同时， 由于数据行是按照主键的顺序进行存储的，对主键或合法前缀的范围扫描只涉及 到最小数据块的访问。
我们看到组织索引表示一个全新类型的表，在查询时候，我们发现由于没有TABLE ACCESS BY INDEX ROWID ，从而提高了查询的性能。oracle数据库不会提供一个全方面全天候的优化技术。了解组织索引表以及适合应用场所，就显得很有必要。
组织索引表的基本存储
我们删除一部分数据，然后再插入这部分数据，观察数据存储
|
HEIGHT |
2 |
2 |
2 |
|
BLOCKS |
512 |
512 |
512 |
|
NAME |
SYS_IOT_TOP_71372 |
SYS_IOT_TOP_71372 |
SYS_IOT_TOP_71372 |
|
PARTITION_NAME |
|||
|
LF_ROWS |
68261 |
68261 |
68261 |
|
LF_BLKS |
417 |
417 |
417 |
|
LF_ROWS_LEN |
3271826 |
3271826 |
3271826 |
|
LF_BLK_LEN |
8000 |
8000 |
8000 |
|
BR_ROWS |
416 |
416 |
416 |
|
BR_BLKS |
1 |
1 |
1 |
|
BR_ROWS_LEN |
4517 |
4517 |
4517 |
|
BR_BLK_LEN |
8032 |
8032 |
8032 |
|
DEL_LF_ROWS |
0 |
34110 |
0 |
|
DEL_LF_ROWS_LEN |
0 |
1643446 |
0 |
|
DISTINCT_KEYS |
68261 |
68261 |
68261 |
|
MOST_REPEATED_KEY |
1 |
1 |
1 |
|
BTREE_SPACE |
3344032 |
3344032 |
3344032 |
|
USED_SPACE |
3276343 |
3276343 |
3276343 |
|
PCT_USED |
98 |
98 |
98 |
|
ROWS_PER_KEY |
1 |
1 |
1 |
|
BLKS_GETS_PER_ACCESS |
3 |
3 |
3 |
|
PRE_ROWS |
0 |
0 |
0 |
|
PRE_ROWS_LEN |
0 |
0 |
0 |
|
OPT_CMPR_COUNT |
0 |
0 |
0 |
|
OPT_CMPR_PCTSAVE |
0 |
0 |
0 |
1、我们隔行删除一半数据记录
delete from ORG_INDEX_TABLE where mod(object_id,2) = 1;
analyze index SYS_IOT_TOP_84235 validate structure;
select * fromindex_stats
alter table ORG_INDEX_TABLE move online;
analyze index SYS_IOT_TOP_84235 validate structure;
select *from index_stats
|
HEIGHT |
2 |
2 |
2 |
|
BLOCKS |
512 |
512 |
256 |
|
NAME |
SYS_IOT_T… |
SYS_IOT… |
SYS_IOT_... |
|
PARTITION_NAME |
|||
|
LF_ROWS |
70231 |
70231 |
35124 |
|
LF_BLKS |
440 |
440 |
233 |
|
LF_ROWS_LEN |
3350295 |
3350295 |
1667079 |
|
LF_BLK_LEN |
8000 |
8000 |
8000 |
|
BR_ROWS |
439 |
439 |
232 |
|
BR_BLKS |
1 |
1 |
1 |
|
BR_ROWS_LEN |
4757 |
4757 |
2521 |
|
BR_BLK_LEN |
8032 |
8032 |
8032 |
|
DEL_LF_ROWS |
0 |
35107 |
0 |
|
DEL_LF_ROWS_LEN |
0 |
1683216 |
0 |
|
DISTINCT_KEYS |
70231 |
70231 |
35124 |
|
MOST_REPEATED_KEY |
1 |
1 |
1 |
|
BTREE_SPACE |
3528032 |
3528032 |
1872032 |
|
USED_SPACE |
3355052 |
3355052 |
1669600 |
|
PCT_USED |
96 |
96 |
90 |
|
ROWS_PER_KEY |
1 |
1 |
1 |
|
BLKS_GETS_PER_ACCESS |
3 |
3 |
3 |
|
PRE_ROWS |
0 |
0 |
0 |
|
PRE_ROWS_LEN |
0 |
0 |
0 |
|
OPT_CMPR_COUNT |
0 |
0 |
0 |
|
OPT_CMPR_PCTSAVE |
0 |
0 |
0 |
Shrink 与 rebuild又会怎样？
alter index SYS_IOT_TOP_84235 rebuild
ERROR at line 1:
ORA-28650: Primary index on an IOT cannot be rebuilt
我们看到不能通过move 二只能用 rebuild index来重新组织数据，从而减少空行，或者空数据块的目的。
组织索引表的ROWID
为了实现对最长访问字段的最快访问，可以使用 rowoverflow 存储选项（稍后会祥述 ），将不常被访问的字段从 B-tree 叶块中取出放到一个可选的 （heap-organized）存储区域，从而限制了真正保存在 B-tree 叶块中的数据行 的大小与内容，并可导致在较小的 B-tree 中保存更多的数据行。不象 heap-organized 配置的情况（表的主键在表和索引中都要保存），索引 组织表没有这种重复存储的情况发生，因为主键字段的值仅保存在
B-tree 索引 中。
由于数据行按照主键的顺序进行保存，通过键压缩特性可以节省出来的大量 空间。
基于逻辑 rowid 的主键的使用（与物理 rowid 相对应），在索引组织表中的 第二个索引还提供了高可用性。这是因为，rowid 的本身逻辑特性——即使在进行了引起基表数据行移动的表重做操作之后，第二索引也不会变得不可用。与此 同时，通过在逻辑 rowid 的物理推测的使用。
select object_id,
rowidfrom ORG_INDEX_TABLE where object_id=281
281 *BAEAGSUDwgNS/g
delete from ORG_INDEX_TABLE where object_id=281
insert into ORG_INDEX_TABLE
select * from s_table whereobject_id=281
select object_id, rowid from ORG_INDEX_TABLE
whereobject_id=281
281 *BAEAGSUDwgNS/g
看到插入数据后，rowid并没有发生变化。
组织索引表的rowid是逻辑rowid，在堆栈表(正常的heap table)中，rowid没有*, 我们不能根据组织索引表的rowid来推算当前数据所在的数据文件，块号，以及行
运行以下的sql,会引起以下错误信息：
ORA-01410: 无效的 ROWID
ORA-06512: 在 "SYS.DBMS_ROWID", line 114
ORA-06512: 在 line 1
我们已经知道组织索引表本质上就是一个索引，而索引是一个比表复杂的多的数据结构，维护索引产生比维护堆栈表产生更多的redo
truncate tableorg_index_table;
truncate table heap_table;
select a.sid,a.statistic#,a.value
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
b.NAME like 'redo size' and
sid=( select sid fromv$mystat where rownum=1)
22304700
insert intoorg_index_table
select * from s_table;
select a.sid,a.statistic#,a.value
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
b.NAME like 'redo size' and
sid=( select sid fromv$mystat where rownum=1)
33991716
select 33991716- 22304700 from dual
11687016
insert into heap_table
select * from s_table;
select a.sid,a.statistic#,a.value
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
b.NAME like 'redo size' and
sid=( select sid fromv$mystat where rownum=1)
43551060
select 43551060-33991716 fromdual;
9559344
观察到插入组织索引表产生的redo要比插入堆栈表并维护一个主键产生的redo还要多。
我们在察看直径路径加载是否对组织索引表有效
truncate tableorg_index_table;
truncate table heap_table;
select a.sid,a.statistic#,a.value
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
b.NAME like 'redo size' and
sid=( select sid fromv$mystat where rownum=1)
43666448
insert /*+append*/ intoorg_index_table
select * from s_table;
select a.sid,a.statistic#,a.value
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
b.NAME like 'redo size' and
sid=( select sid fromv$mystat where rownum=1)
55338096
select 55338096- 43666448 from dual
11671648
11687016(没有/*+append*/提示的普通加载)
insert /*+append*/ intoheap_table
select * from s_table;
select a.sid,a.statistic#,a.value
from v$sesstat a, v$statname b
where a.STATISTIC#=b.STATISTIC# and
b.NAME like 'redo size' and
sid=( select sid fromv$mystat where rownum=1)
59308224
select 59308224-55338096 fromdual;
3970128
9559344(没有/*+append*/提示的普通加载)
我们观察到，对组织索引表直径路径加载并没有减少redo的产生量。
我们对比插入组织索引表与堆栈表所产生的拴与锁的对比，还能看到插入组织索引表需要更多的资源。实际上对表进行DML操作都会引起更大的资源消耗。
这也说明了 组织索引表的一些使用场所。
Index-Organized Tableswith Row Overflow Area
B-tree 索引项通常是比较小，因为每一个索引项的组成仅是键值和 rowid。 但是，在索引组织表，B-tree 索引项可能会变得比较大，因为其中包含了整个数
应用程序操控索引组织表的方式与操控普通表相同——使用 SQL 命令。但
是，数据库系统的通过操控对应的 B-tree 索引来执行所有的操作。
下表显示了索引组织表与普通表的不同。
|
普通表 |
索引组织表 |
|
rowid 唯一地识别每一行，主键是可选的设定项目。 |
主键唯一地识别每一行，主键必须被设定。 |
|
ROWID 隐含字段中的物理 rowid 可以用于创建第二个索引 |
ROWID 隐含字段中的逻辑 rowid 可以用于创建第二个索引 |
|
基于 rowid 进行数据访问 |
基于逻辑 rowid 进行数据访问 |
|
连续扫描返回所有的行 |
全索引扫描返回所有的行 |
|
可以与其他表一起保存在簇中 |
不能够保存在簇中 |
|
可以包含数据类型位 LONG 和 LOB 的字段 |
可以包含数据类型位 LOB 的字段，但是不 能包含数据类型位 LONG 的字段 |
组合索引表还有3个重要的参数
select dbms_metadata.get_ddl('TABLE','ORG_INDEX_TABLE')
from dual
CREATE TABLE"TIWEN"."ORG_INDEX_TABLE"
( "OBJECT_ID"NUMBER(38,0),
"ONAME" VARCHAR2(30),
"OWNER" VARCHAR2(30),
"STATUS" VARCHAR2(30),
PRIMARY KEY ("OBJECT_ID") ENABLE
) ORGANIZATION INDEX NOCOMPRESSPCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELISTGROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
PCTTHRESHOLD 50
主键总是要存在索引叶结点块上
在一个8k的数据块上，PCTTHRESHOLD 50表示，如果一行数据如果字节数>4k 则这个行的一部分数据存在另一个非索引块上（溢出块），至于哪一些数据存储在块上，还要察看其它的一些参数。
Including 行中的第一列到including指定的列存放在索引块上，剩余部分存放在溢出块。
CREATE TABLE "TIWEN"."ORG_INDEX_TABLE"
( OBJECT_IDNUMBER(38,0),
ONAME VARCHAR2(30),
OWNER VARCHAR2(30),
STATUS VARCHAR2(30),
PRIMARY KEY ("OBJECT_ID") ENABLE
)
Organization index
Include ONAME overflow