索引组织表
索引组织表的存储结构是按照主键的 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