在上一篇关于ora-1652的一点总结中，有部分内容并未给出一个最终的结论，详见如下连接：
关于ORA-1652的一点简单总结
在本文中，对上篇文章中的疑问进行的详细的实验说明和解释，实验过程如下：
SQL> conn roger/roger
Connected.
SQL> create table ht1 as select * from sys.dba_objects where rownum <1000;
Table created.
SQL> create index idx_ht1 on ht1(object_id) tablespace roger;
Index created.
SQL> select dump(object_id)
2 from ht1
3 where object_id <20 order by object_id;
DUMP(OBJECT_ID)
--------------------------
Typ=2 Len=2: 193,3
Typ=2 Len=2: 193,4
Typ=2 Len=2: 193,5
Typ=2 Len=2: 193,6
Typ=2 Len=2: 193,7
Typ=2 Len=2: 193,8
Typ=2 Len=2: 193,9
Typ=2 Len=2: 193,10
Typ=2 Len=2: 193,11
Typ=2 Len=2: 193,12
Typ=2 Len=2: 193,13
Typ=2 Len=2: 193,14
Typ=2 Len=2: 193,15
Typ=2 Len=2: 193,16
Typ=2 Len=2: 193,17
Typ=2 Len=2: 193,18
Typ=2 Len=2: 193,19
Typ=2 Len=2: 193,20
18 rows selected.
SQL> select dump(object_id)
2 from ht1
3 where object_id > 500
4 and object_id < 510
5 order by object_id;
DUMP(OBJECT_ID)
----------------------------------------
Typ=2 Len=3: 194,6,2
Typ=2 Len=3: 194,6,3
Typ=2 Len=3: 194,6,4
Typ=2 Len=3: 194,6,5
Typ=2 Len=3: 194,6,6
Typ=2 Len=3: 194,6,7
Typ=2 Len=3: 194,6,8
Typ=2 Len=3: 194,6,9
Typ=2 Len=3: 194,6,10
9 rows selected.
我们可以发现，该字段object_id有些是2个字节，有些是3个字节，那么我们应该以2还是3为准呢？
我想应该以该列的平均长度为准，通过分析该表，得到该列的平均长度。
SQL> analyze table ht1 compute statistics for table for all indexes for all columns;
Table analyzed.
SQL> select table_name,COLUMN_NAME,DATA_LENGTH,AVG_COL_LEN,CHAR_LENGTH,CHAR_USED
2 from user_tab_columns
3 where table_name='HT1';
TABLE_NAME COLUMN_NAME DATA_LENGTH AVG_COL_LEN CHAR_LENGTH C
------------- ----------------- ----------- ----------- ----------- -
HT1 OWNER 30 4 30 B
HT1 OBJECT_NAME 128 14 128 B
HT1 SUBOBJECT_NAME 30 1 30 B
HT1 OBJECT_ID 22 3 0
HT1 DATA_OBJECT_ID 22 3 0
HT1 OBJECT_TYPE 19 6 19 B
HT1 CREATED 7 7 0
HT1 LAST_DDL_TIME 7 7 0
HT1 TIMESTAMP 19 19 19 B
HT1 STATUS 7 5 7 B
HT1 TEMPORARY 1 1 1 B
HT1 GENERATED 1 1 1 B
HT1 SECONDARY 1 1 1 B
13 rows selected.
###### 从这里得到object_id平均列长度为3 ######
++++++ 下面再来看rowid是占据多少个字节 ++++++
SQL> select rowid ,
2 substr(rowid,1,6) "OBJECT",
3 substr(rowid,7,3) "FILE",
4 substr(rowid,10,6) "BLOCK",
5 substr(rowid,16,3) "ROW"
6 from ht1
7 where object_id <20 order by object_id;
ROWID OBJECT FILE BLOCK ROW
------------------ ------------ ------ ------------ ------
AAAMpRAAFAAAAAUAAt AAAMpR AAF AAAAAU AAt
AAAMpRAAFAAAAAUAAF AAAMpR AAF AAAAAU AAF
AAAMpRAAFAAAAAUAAu AAAMpR AAF AAAAAU AAu
AAAMpRAAFAAAAAUAAZ AAAMpR AAF AAAAAU AAZ
AAAMpRAAFAAAAAUAAU AAAMpR AAF AAAAAU AAU
AAAMpRAAFAAAAAUAAQ AAAMpR AAF AAAAAU AAQ
AAAMpRAAFAAAAAUAAh AAAMpR AAF AAAAAU AAh
AAAMpRAAFAAAAAUAAM AAAMpR AAF AAAAAU AAM
AAAMpRAAFAAAAAUAAi AAAMpR AAF AAAAAU AAi
AAAMpRAAFAAAAAUAA1 AAAMpR AAF AAAAAU AA1
AAAMpRAAFAAAAAUAAl AAAMpR AAF AAAAAU AAl
AAAMpRAAFAAAAAUAAL AAAMpR AAF AAAAAU AAL
AAAMpRAAFAAAAAUAAT AAAMpR AAF AAAAAU AAT
AAAMpRAAFAAAAAUAAD AAAMpR AAF AAAAAU AAD
AAAMpRAAFAAAAAUAAg AAAMpR AAF AAAAAU AAg
AAAMpRAAFAAAAAUAAK AAAMpR AAF AAAAAU AAK
AAAMpRAAFAAAAAUAAr AAAMpR AAF AAAAAU AAr
AAAMpRAAFAAAAAUAAS AAAMpR AAF AAAAAU AAS
18 rows selected.
SQL> select owner,object_id
2 from dba_objects
3 where object_name='IDX_HT1';
OWNER OBJECT_ID
------------------------------ ----------
ROGER 51794
SQL> alter session set events 'immediate trace name treedump level 51794';
Session altered.
++++++ begin tree dump ++++++
branch: 0x1400024 20971556 (0: nrow: 3, level: 1)
leaf: 0x1400025 20971557 (-1: nrow: 485 rrow: 485)
leaf: 0x1400026 20971558 (0: nrow: 479 rrow: 479)
leaf: 0x1400027 20971559 (1: nrow: 35 rrow: 35)
++++++ end tree dump ++++++
SQL> SELECT DISTINCT t.*
2 FROM (SELECT DBMS_ROWID.rowid_relative_fno (ROWID) file_id,
3 DBMS_ROWID.rowid_block_number (ROWID) block_id
4 FROM ht1) t;
FILE_ID BLOCK_ID
---------- ----------
5 24
5 28
5 21
5 27
5 25
5 29
5 31
5 20
5 23
5 22
5 26
5 30
12 rows selected.
SQL> SELECT DBMS_UTILITY.data_block_address_file (20971557) file_id,
2 DBMS_UTILITY.data_block_address_block (20971557) block_number
3 FROM DUAL;
FILE_ID BLOCK_NUMBER
---------- ------------
5 37
###### dump file 5 block 37，信息如下 ######
*** 2011-10-02 06:59:47.502
Start dump data blocks tsn: 6 file#: 5 minblk 37 maxblk 37
buffer tsn: 6 rdba: 0x01400025 (5/37)
scn: 0x0000.00067c52 seq: 0x02 flg: 0x04 tail: 0x7c520602
frmt: 0x02 chkval: 0x4e1b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB732B800 to 0xB732D800
B732B800 0000A206 01400025 00067C52 04020000 [....%.@.R|......]
.............
B732D7D0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
B732D7F0 00000000 00000000 00000000 7C520602 [..............R|]
Block header dump: 0x01400025
Object id on Block? Y
seg/obj: 0xca52 csc: 0x00.67c50 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1400021 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.00067c50
Leaf block dump
===============
header address 3073554532=0xb732b864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1830=0x726
kdxcoavs 824
kdxlespl 0
kdxlende 0
kdxlenxt 20971558=0x1400026
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 40 00 14 00 2d
row#1[8008] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 40 00 14 00 05
... ... ...
... ... ...
... ... ...
SQL> col dump for a45
SQL> set lines 160
SQL> select dump(rowid) dump,
2 dbms_rowid.rowid_object(rowid) object_number,
3 dbms_rowid.rowid_relative_fno(rowid) file_number,
4 dbms_rowid.rowid_block_number(rowid) block_number,
5 dbms_rowid.rowid_row_number(rowid) row_number
6 from ht1
7 where object_id < 20
8 order by object_id;
DUMP OBJECT_NUMBER FILE_NUMBER BLOCK_NUMBER ROW_NUMBER
--------------------------------------------- ------------- ----------- ------------ ----------
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,45 51793 5 20 45
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,5 51793 5 20 5
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,46 51793 5 20 46
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,25 51793 5 20 25
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,20 51793 5 20 20
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,16 51793 5 20 16
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,33 51793 5 20 33
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,12 51793 5 20 12
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,34 51793 5 20 34
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,53 51793 5 20 53
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,37 51793 5 20 37
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,11 51793 5 20 11
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,19 51793 5 20 19
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,3 51793 5 20 3
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,32 51793 5 20 32
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,10 51793 5 20 10
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,43 51793 5 20 43
Typ=69 Len=10: 0,0,202,81,1,64,0,20,0,18 51793 5 20 18
18 rows selected.
我们可以看到rowid是10个字节。
我们还需要知道index header占据多数字节，那么如何才能知道呢？当然用bbed来看是最方便的了，如下：
BBED> set file 5 block 37
FILE# 5
BLOCK# 37
BBED> map /v
File: /home/ora10g/oradata/roger/roger01.dbf (5)
Block: 37 Dba:0x01400025
------------------------------------------------------------
KTB Data Block (Index Leaf)
struct kcbh, 20 bytes @0
ub1 type_kcbh @0
ub1 frmt_kcbh @1
ub1 spare1_kcbh @2
ub1 spare2_kcbh @3
ub4 rdba_kcbh @4
ub4 bas_kcbh @8
ub2 wrp_kcbh @12
ub1 seq_kcbh @14
ub1 flg_kcbh @15
ub2 chkval_kcbh @16
ub2 spare3_kcbh @18
struct ktbbh, 72 bytes @20
ub1 ktbbhtyp @20
union ktbbhsid, 4 bytes @24
struct ktbbhcsc, 8 bytes @28
b2 ktbbhict @36
ub1 ktbbhflg @38
ub1 ktbbhfsl @39
ub4 ktbbhfnx @40
struct ktbbhitl[2], 48 bytes @44
struct kdxle, 32 bytes @100
struct kdxlexco, 16 bytes @100
b2 kdxlespl @116
sb2 kdxlende @118
ub4 kdxlenxt @120
ub4 kdxleprv @124
ub1 kdxledsz @128
ub1 kdxleunuse @129
b2 kd_off[485] @132
ub1 freespace[824] @1102
ub1 rowdata[6202] @1926
ub4 tailchk @8188
BBED> p kdxlexco
struct kdxlexco, 16 bytes @100
ub1 kdxcolev @100 0x00
ub1 kdxcolok @101 0x00
ub1 kdxcoopc @102 0x80
ub1 kdxconco @103 0x02
ub4 kdxcosdc @104 0x00000000
sb2 kdxconro @108 485
b2 kdxcofbo @110 1006
b2 kdxcofeo @112 1830
b2 kdxcoavs @114 824
这里可以看到是16个字节，但是这并不是活index entry 长度就是16个字节，还要加上2.
为什么要加2呢？因为我们还要算上行头。
SQL> select INDEX_NAME,PCT_THRESHOLD,PCT_FREE,BLEVEL,LEAF_BLOCKS,NUM_ROWS
2 from dba_indexes
3 where table_name='HT1';
INDEX_NAME PCT_THRESHOLD PCT_FREE BLEVEL LEAF_BLOCKS NUM_ROWS
------------------------------ ------------- ---------- ---------- ----------- ----------
IDX_HT1 10 1 3 999
SQL> select 8192*0.9-20 from dual;
8192*0.9-20
-----------
7352.8
SQL> select 7352/18 from dual;
7352/18
----------
408.444444
SQL> select count(*) from ht1;
COUNT(*)
----------
999
SQL> select 999/408 from dual;
999/408
----------
2.44852941
也就是说一个block最多存放408个索引条目，为表ht1创建index(object_id)那么需要3个index block。
那么我们来看看我们创建的idx_ht1 索引是不是使用了3个block呢？
SQL> select blocks from dba_segments where segment_name='IDX_HT1';
BLOCKS
----------
8
SQL> select blocks,INITIAL_EXTENT,EXTENTS from dba_segments where segment_name='IDX_HT1';
BLOCKS INITIAL_EXTENT EXTENTS
---------- -------------- ----------
8 65536 1
这里至于说为什么创建该index只需要3个block即可，为啥却占据了8个block呢？
很简单，因为初始化extent 为65536大小，即为8个block。换句话说创建一个索引，
最小分配初始化extent大小的空间。
SQL> analyze index idx_ht1 validate structure;
Index analyzed.
SQL> set heading off
SQL> col name newline
SQL> col headsep newline
SQL> col height newline
SQL> col blocks newline
SQL> col lf_rows newline
SQL> col lf_blks newline
SQL> col lf_rows_len newline
SQL> col lf_blk_len newline
SQL> col br_rows newline
SQL> col br_blks newline
SQL> col br_rows_len newline
SQL> col br_blk_len newline
SQL> col del_lf_rows newline
SQL> col del_lf_rows_len newline
SQL> col distinct_keys newline
SQL> col most_repeated_key newline
SQL> col btree_space newline
SQL> col used_space newline
SQL> col pct_used newline
SQL> col rows_per_key newline
SQL> col blks_gets_per_access newline
SQL> select
2 name,
3 '----------------------------------------------------------' headsep,
4 'height '||to_char(height, '999,999,990') height,
5 'blocks '||to_char(blocks, '999,999,990') blocks,
6 'del_lf_rows '||to_char(del_lf_rows,'999,999,990') del_lf_rows,
7 'del_lf_rows_len '||to_char(del_lf_rows_len,'999,999,990') del_lf_rows_len,
8 'distinct_keys '||to_char(distinct_keys,'999,999,990') distinct_keys,
9 'most_repeated_key '||to_char(most_repeated_key,'999,999,990') most_repeated_key,
10 'btree_space '||to_char(btree_space,'999,999,990') btree_space,
11 'used_space '||to_char(used_space,'999,999,990') used_space,
12 'pct_used '||to_char(pct_used,'990') pct_used,
13 'rows_per_key '||to_char(rows_per_key,'999,999,990') rows_per_key,
14 'blks_gets_per_access '||to_char(blks_gets_per_access,'999,999,990') blks_gets_per_access,
15 'lf_rows '||to_char(lf_rows, '999,999,990')||' '||+
16 'br_rows '||to_char(br_rows, '999,999,990') br_rows,
17 'lf_blks '||to_char(lf_blks, '999,999,990')||' '||+
18 'br_blks '||to_char(br_blks, '999,999,990') br_blks,
19 'lf_rows_len '||to_char(lf_rows_len,'999,999,990')||' '||+
20 'br_rows_len '||to_char(br_rows_len,'999,999,990') br_rows_len,
21 'lf_blk_len '||to_char(lf_blk_len, '999,999,990')||' '||+
22 'br_blk_len '||to_char(br_blk_len, '999,999,990') br_blk_len
23 from
24 index_stats
25 /
set verify on
IDX_HT1
----------------------------------------------------------
height 2
blocks 8
del_lf_rows 0
del_lf_rows_len 0
distinct_keys 999
most_repeated_key 1
btree_space 32,016
used_space 14,899
pct_used 47
rows_per_key 1
blks_gets_per_access 3
lf_rows 999 br_rows 2
lf_blks 3 br_blks 1
lf_rows_len 14,877 br_rows_len 22
lf_blk_len 7,996 br_blk_len 8,028
我们可以发现是3个block，下面我们继续来看看上次的问题，关于排序空间？
SQL> explain plan for
2 create index idx_ht1 on ht1(object_id) tablespace roger;
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3209147535
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 999 | 2997 | 8 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX_HT1 | | | | |
| 2 | SORT CREATE INDEX | | 999 | 2997 | | |
| 3 | INDEX FAST FULL SCAN| IDX_HT1 | | | | |
----------------------------------------------------------------------------------
Note
-----
- estimated index size: 65536 bytes
14 rows selected.
SQL> select 2997/1024 from dual;
2.92675781
我们可以发现该操作需要排序空间3k大小。但是这个2997到底是如何计算出来的呢？
SQL> select 999*3 from dual;
2997
从这样来看，对于单列 index，就是avg_col_len*row_tables
那么对于符合索引呢？其实也很简单，道理一样的，如下：
SQL> explain plan for
2 create index idx_owner on ht1(owner,object_id) tablespace roger;
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 4167866385
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 999 | 6993 | 8 (0)| 00:00:01 |
| 1 | INDEX BUILD NON UNIQUE| IDX_OWNER | | | | |
| 2 | SORT CREATE INDEX | | 999 | 6993 | | |
| 3 | TABLE ACCESS FULL | HT1 | 999 | 6993 | 6 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Note
-----
- estimated index size: 65536 bytes
14 rows selected.
SQL> select table_name,
2 COLUMN_NAME,
3 DATA_LENGTH,
4 AVG_COL_LEN,
5 CHAR_LENGTH,
6 CHAR_USED
7 from user_tab_columns
8 where table_name = 'HT1'
9 and column_name in ('OWNER', 'OBJECT_ID');
TABLE_NAME COLUMN_NAME DATA_LENGTH AVG_COL_LEN CHAR_LENGTH C
------------- ----------------- ----------- ----------- ----------- -
HT1 OWNER 30 4 30 B
HT1 OBJECT_ID 22 3 0
SQL> select (4+3)*999 from dual;
6993
最好再回到上次文章中的为什么是42M？同样很简单，如下：
SQL> select count(*) from ht1;
COUNT(*)
----------
5003900
SQL> select table_name,column_name,avg_col_len
2 from user_tab_columns
3 where table_name='HT1';
TABLE_NAME COLUMN_NAME AVG_COL_LEN
------------------------------ ------------------------------ -----------
HT1 OWNER 5
HT1 OBJECT_NAME 24
HT1 SUBOBJECT_NAME 2
HT1 OBJECT_ID 4
HT1 DATA_OBJECT_ID 2
......
HT1 SECONDARY 1
13 rows selected.
SQL> select 5003900*(5+4)/1024/1024 from dual;
42.9488182
发表回复