Oracle索引组织表学习


索引组织表

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

  • 1
  • 2
  • 3
  • 4
  • 下一页

相关内容