Oracle heap 表的主键 dump 分析

1.
创建heap 表:

 create table t1  (id char(10) primary key,a1 char(10),a2 char(10),a3 char(10));  

 SQL> select  OBJECT_NAME,OBJECT_TYPE from user_objects;

OBJECT_NAME		       OBJECT_TYPE
------------------------------ -------------------
T1			       TABLE
SYS_C0021515		       INDEX


SQL> show user;
USER 为 "TEST"
SQL>  select owner,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from dba_segments where owner='TEST';

未选定行

SQL> select * from user_segments;

未选定行


可以看到表没有数据,不出现在user_segments


SQL>  select  TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME, IOT_NAME from user_tables;

TABLE_NAME	     TABLESPACE_NAME	  CLUSTER_NAME			 IOT_NAME
-------------------- -------------------- ------------------------------ --------------------
T1		     USERS

SQL> 

/***开始填充数据:

begin 
for i in 1 .. 50 
loop 
insert into t1 values(i,i,i,'a'||i); 
end loop 
; 
commit; 
end; 

insert into t1 values(51,51,51,'a51');
insert into t1 values(52,52,52,'a52');

insert into t1 values(81,81,81,'a81');
insert into t1 values(61,61,61,'a61');

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'TEST',
                                tabname          => 'T1',
                                estimate_percent => 100,
                                method_opt       => 'for all columns size repeat',
                                no_invalidate    => FALSE,
                                degree           => 8,
                                cascade          => TRUE);
END;
/ 



SQL>  select id from t1;

已选择52行。



执行计划
----------------------------------------------------------
Plan hash value: 2566532982

-------------------------------------------------------------------------------------
| Id  | Operation	     | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		    |	 52 |	624 |	  2   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| SYS_C0021515 |	 52 |	624 |	  2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
	  4  recursive calls
	  0  db block gets
	 17  consistent gets
	  0  physical reads
	  0  redo size
       1812  bytes sent via SQL*Net to client
	553  bytes received via SQL*Net from client
	  5  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 52  rows processed

SQL> select id from t1 order by id;

已选择52行。


执行计划
----------------------------------------------------------
Plan hash value: 1686489781

---------------------------------------------------------------------------------
| Id  | Operation	 | Name 	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |		|    52 |   624 |     2   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | SYS_C0021515 |    52 |   624 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)


统计信息
----------------------------------------------------------
	  4  recursive calls
	  0  db block gets
	 14  consistent gets
	  0  physical reads
	  0  redo size
       1812  bytes sent via SQL*Net to client
	553  bytes received via SQL*Net from client
	  5  SQL*Net roundtrips to/from client
	  0  sorts (memory)
	  0  sorts (disk)
	 52  rows processed



Index fast full scan :多块读,无序

Index full scan:它扫描的结果是有序的,因为索引是有序的。


INDEX FAST FULL SCAN使用多块读方式读取索引块,为无序读,效率高
INDEX FULL SCAN使用单块读方式有序读取索引块,为顺序读,效率低一些。


select /*+ INDEX_FFS(t1 SYS_C0021515) */ id  from t1;
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2566532982

-------------------------------------------------------------------------------------
| Id  | Operation	     | Name	    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |		    |	 52 |	572 |	  2   (0)| 00:00:01 |
|   1 |  INDEX FAST FULL SCAN| SYS_C0021515 |	 52 |	572 |	  2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

已选择8行。


SQL> explain plan for select id from t1;

已解释。

SQL>  select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1686489781

---------------------------------------------------------------------------------
| Id  | Operation	 | Name 	| Rows	| Bytes | Cost (%CPU)| Time	|
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |		|    52 |   572 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | SYS_C0021515 |    52 |   572 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

已选择8行。


1.
得到这个index的object_id:

SQL> select object_name,object_id from user_objects;

OBJECT_NAME															  OBJECT_ID
-------------------------------------------------------------------------------------------------------------------------------- ----------
T1																     101443
SYS_C0021515		                                                                                                             101444

begin 
for i in 100 .. 2000 
loop 
insert into t1 values(i,i,i,'a'||i); 
end loop 
; 
commit; 
end;



2.将索引dump到trace文件中
SQL>  select value from v$diag_info where name='Default Trace File';

VALUE
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/oadb/oadb/trace/oadb_ora_29729.trc

SQL> alter session set events 'immediate trace name treedump level 101444';

会话已更改。


trace信息:

/**************************************************************

*** 2016-06-14 20:25:41.838
*** SESSION ID:(609.16765) 2016-06-14 20:25:41.838
*** CLIENT ID:() 2016-06-14 20:25:41.838
*** SERVICE NAME:(SYS$USERS) 2016-06-14 20:25:41.838
*** MODULE NAME:(SQL*Plus) 2016-06-14 20:25:41.838
*** ACTION NAME:() 2016-06-14 20:25:41.838

----- begin tree dump
leaf: 0x10000b3 16777395 (0: nrow: 54 rrow: 54)
----- end tree dump

*** 2016-06-14 20:28:02.625
----- begin tree dump
branch: 0x10000b3 16777395 (0: nrow: 10, level: 1)
   leaf: 0x10000b5 16777397 (-1: nrow: 190 rrow: 190)
   leaf: 0x10000c2 16777410 (0: nrow: 189 rrow: 189)
   leaf: 0x10000c3 16777411 (1: nrow: 189 rrow: 189)
   leaf: 0x10000c4 16777412 (2: nrow: 188 rrow: 188)
   leaf: 0x10000c5 16777413 (3: nrow: 189 rrow: 189)
   leaf: 0x10000c6 16777414 (4: nrow: 245 rrow: 245)
   leaf: 0x10000b6 16777398 (5: nrow: 189 rrow: 189)
   leaf: 0x10000b7 16777399 (6: nrow: 192 rrow: 192)
   leaf: 0x10000b4 16777396 (7: nrow: 380 rrow: 380)
   leaf: 0x10000c1 16777409 (8: nrow: 4 rrow: 4)
----- end tree dump


0x10000b3  16进制转换为10进制16777395
/*************************************************

branch 表示的是 branch block ,它后面跟了一个十六进制表示的DBA(data block address),以及用10进制表示的DBA 
DBA 之后表示在同一层次的相对位置(root 从0开始,branch 以及leaf从 -1开始)  
nrow  表示块中包含了多少条目(包括delete的条目)  
rrow  表示块中包含的实际条目(不包括delete的条目)  
level 表示从该block到leaf的深度(leaf没有 level)

这个 branch block 的 level 为1,也就是说 从这个branch block 到 leaf block 的深度为1,根据前面的查询,这个索引的Blevel为1


SQL> select index_name, PREFIX_LENGTH, BLEVEL, LEAF_BLOCKS from user_indexes where index_name='SYS_C0021515';

INDEX_NAME		       PREFIX_LENGTH	 BLEVEL LEAF_BLOCKS
------------------------------ ------------- ---------- -----------
SYS_C0021515					      0 	  1


可以看到高度为0,leaf block为1

BLEVEL* NUMBER   B*-Tree level: depth of the index from its root block to its leaf blocks. A depth of 0 indicates that the root block and leaf block are the

same.


现在我来验证一下branch: 0x10000b3 16777395 (0: nrow: 10, level: 1)是不是 root block , 我查询这个 branch 的 DBA

SQL>  select dbms_utility.data_block_address_file('16777395') FILE_ID,
       dbms_utility.data_block_address_block('16777395') BLOCK_ID
  from dual;   2    3  

   FILE_ID   BLOCK_ID
---------- ----------
	 4	  179


Btree 索引的 root block总是segment header+1,所以我查询该索引的段头  

SQL>  select header_file,header_block from dba_segments where segment_name='SYS_C0021515';

HEADER_FILE HEADER_BLOCK
----------- ------------
	  4	     178


证明branch: 0x10000b3 16777395 (0: nrow: 10, level: 1) 就是root块,其实 treedump第一个 branch block 就是 root block

到此时为止,已经从dump信息得出了索引的高度和第一个branch block就是root block的结论 

----- begin tree dump
branch: 0x10000b3 16777395 (0: nrow: 10, level: 1)
   leaf: 0x10000b5 16777397 (-1: nrow: 190 rrow: 190)
   leaf: 0x10000c2 16777410 (0: nrow: 189 rrow: 189)
   leaf: 0x10000c3 16777411 (1: nrow: 189 rrow: 189)
   leaf: 0x10000c4 16777412 (2: nrow: 188 rrow: 188)
   leaf: 0x10000c5 16777413 (3: nrow: 189 rrow: 189)
   leaf: 0x10000c6 16777414 (4: nrow: 245 rrow: 245)
   leaf: 0x10000b6 16777398 (5: nrow: 189 rrow: 189)
   leaf: 0x10000b7 16777399 (6: nrow: 192 rrow: 192)
   leaf: 0x10000b4 16777396 (7: nrow: 380 rrow: 380)
   leaf: 0x10000c1 16777409 (8: nrow: 4 rrow: 4)
----- end tree dump

Oracle 中提供了dbms_utility来求的这个地址对应的文件号和块号(传入的参数是十进制的那个值).


SQL> select dbms_utility.data_block_address_file(16777397)fno,
dbms_utility.data_block_address_block(16777397) bkno from dual;   2  

       FNO	 BKNO
---------- ----------
	 4	  181



SQL> select file_id,block_id,blocks from dba_extents where segment_name='SYS_C0021515';

   FILE_ID   BLOCK_ID	  BLOCKS
---------- ---------- ----------
	 4	  176	       8
	 4	  192	       8


将索引数据dump出来,dump 4号文件的3236块,alter system dump datafile 4 block 181;

SQL> alter system dump datafile 4 block 181;

系统已更改。


row#0[4422] flag: ------, lock: 0, len=19, data:(6):  01 00 00 ab 00 00
col 0; len 10; (10):  31 20 20 20 20 20 20 20 20 20
row#1[4441] flag: ------, lock: 0, len=19, data:(6):  01 00 00 ab 00 09
col 0; len 10; (10):  31 30 20 20 20 20 20 20 20 20
row#2[4460] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ac 00 01
col 0; len 10; (10):  31 30 30 20 20 20 20 20 20 20
row#3[4479] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 42
col 0; len 10; (10):  31 30 30 30 20 20 20 20 20 20
row#4[4498] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 43
col 0; len 10; (10):  31 30 30 31 20 20 20 20 20 20
row#5[4517] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 44
col 0; len 10; (10):  31 30 30 32 20 20 20 20 20 20
row#6[4536] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 45
col 0; len 10; (10):  31 30 30 33 20 20 20 20 20 20
row#7[4555] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 46
col 0; len 10; (10):  31 30 30 34 20 20 20 20 20 20
row#8[4574] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 47
col 0; len 10; (10):  31 30 30 35 20 20 20 20 20 20
row#9[4593] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 48
col 0; len 10; (10):  31 30 30 36 20 20 20 20 20 20
row#10[4612] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 49
col 0; len 10; (10):  31 30 30 37 20 20 20 20 20 20
row#11[4631] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 4a
col 0; len 10; (10):  31 30 30 38 20 20 20 20 20 20
row#12[4650] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 4b
col 0; len 10; (10):  31 30 30 39 20 20 20 20 20 20
row#13[4669] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ac 00 02
col 0; len 10; (10):  31 30 31 20 20 20 20 20 20 20
row#14[4688] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 4c
col 0; len 10; (10):  31 30 31 30 20 20 20 20 20 20
row#15[4707] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 4d
col 0; len 10; (10):  31 30 31 31 20 20 20 20 20 20
row#16[4726] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 4e
col 0; len 10; (10):  31 30 31 32 20 20 20 20 20 20
row#17[4745] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 4f
col 0; len 10; (10):  31 30 31 33 20 20 20 20 20 20
row#18[4764] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 50
col 0; len 10; (10):  31 30 31 34 20 20 20 20 20 20
row#19[4783] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 51
col 0; len 10; (10):  31 30 31 35 20 20 20 20 20 20
row#20[4802] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 52
col 0; len 10; (10):  31 30 31 36 20 20 20 20 20 20
row#21[4821] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 53
col 0; len 10; (10):  31 30 31 37 20 20 20 20 20 20
row#22[4840] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 54
col 0; len 10; (10):  31 30 31 38 20 20 20 20 20 20
row#23[4859] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 55
col 0; len 10; (10):  31 30 31 39 20 20 20 20 20 20
row#24[4878] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ac 00 03
col 0; len 10; (10):  31 30 32 20 20 20 20 20 20 20
row#25[4897] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 56
col 0; len 10; (10):  31 30 32 30 20 20 20 20 20 20
row#26[4916] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 57
col 0; len 10; (10):  31 30 32 31 20 20 20 20 20 20
row#27[4935] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 58
col 0; len 10; (10):  31 30 32 32 20 20 20 20 20 20
row#28[4954] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 59
col 0; len 10; (10):  31 30 32 33 20 20 20 20 20 20
row#29[4973] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 5a
col 0; len 10; (10):  31 30 32 34 20 20 20 20 20 20
row#30[4992] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 5b
col 0; len 10; (10):  31 30 32 35 20 20 20 20 20 20
row#31[5011] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 5c
col 0; len 10; (10):  31 30 32 36 20 20 20 20 20 20
row#32[5030] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 5d
col 0; len 10; (10):  31 30 32 37 20 20 20 20 20 20
row#33[5049] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 5e
col 0; len 10; (10):  31 30 32 38 20 20 20 20 20 20
row#34[5068] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 5f
col 0; len 10; (10):  31 30 32 39 20 20 20 20 20 20



DECLARE
   n   VARCHAR2 (2000);
BEGIN
   DBMS_STATS.convert_raw_value ('31', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
END;

row#0[4422] flag: ------, lock: 0, len=19, data:(6):  01 00 00 ab 00 00
col 0; len 10; (10):  31 20 20 20 20 20 20 20 20 20 表示1




DECLARE
   n   VARCHAR2 (2000);
BEGIN
   DBMS_STATS.convert_raw_value ('31', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
END;

row#1[4441] flag: ------, lock: 0, len=19, data:(6):  01 00 00 ab 00 09
col 0; len 10; (10):  31 30 20 20 20 20 20 20 20 20 表示10


DECLARE
   n   VARCHAR2 (2000);
BEGIN
   DBMS_STATS.convert_raw_value ('31', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
END;

row#2[4460] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ac 00 01
col 0; len 10; (10):  31 30 30 20 20 20 20 20 20 20 表示100



DECLARE
   n   VARCHAR2 (2000);
BEGIN
   DBMS_STATS.convert_raw_value ('31', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
END;


row#3[4479] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 42
col 0; len 10; (10):  31 30 30 30 20 20 20 20 20 20 表示1000



DECLARE
   n   VARCHAR2 (2000);
BEGIN
   DBMS_STATS.convert_raw_value ('31', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('31', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
END;


row#4[4498] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 43
col 0; len 10; (10):  31 30 30 31 20 20 20 20 20 20 表示1001




DECLARE
   n   VARCHAR2 (2000);
BEGIN
   DBMS_STATS.convert_raw_value ('31', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('30', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('32', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
   DBMS_STATS.convert_raw_value ('20', n);
   DBMS_OUTPUT.put_line (n);
END;

row#5[4517] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 44
col 0; len 10; (10):  31 30 30 32 20 20 20 20 20 20 表示1002




row#6[4536] flag: ----S-, lock: 2, len=19, data:(6):  01 00 00 ba 00 45
col 0; len 10; (10):  31 30 30 33 20 20 20 20 20 20 表示1003



SQL> select id from t1 where rownum<20;

ID
----------
1
10
100
1000
1001
1002
1003
1004
1005
1006
1007
 
证明索引数据是有序的,而且heap表的主键 只有对应列的数据

posted @ 2016-06-14 21:04  czcb  阅读(147)  评论(0编辑  收藏  举报