SQL> create user scan identified by scan default tablespace users; User created. SQL> grant dba to scan; Grant succeeded. create table t1 (id char(10) primary key,a1 char(10),a2 char(10)); begin for i in 1 .. 25 loop insert into t1 values(i,i,'a'||i); end loop ; commit; end; BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCAN', tabname => 'T1', estimate_percent => 100, method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree => 8, cascade => TRUE); END; / create table t2 (id int primary key,a1 char(10),a2 char(10))organization index; begin for i in 1 .. 25 loop insert into t2 values(i,i,'a'||i); end loop ; commit; end; BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCAN', tabname => 'T2', estimate_percent => 100, method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree => 8, cascade => TRUE); END; / 先分别dump下t1和t2表: t1 heap t2 iot SQL> select TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME, IOT_NAME from user_tables; TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME ------------------------------ ------------------------------ ------------------------------ ------------------------------ T1 USERS T2 SQL> select object_name,object_id from user_objects; OBJECT_NAME OBJECT_ID ------------------------------ ---------- SYS_C00147516 260450 T2 260451 T1 260449 SYS_IOT_TOP_260451 260452 SQL> select index_name,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,table_type from user_indexes; INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE ------------------------------ --------------------------- ------------------------------ ------------------------------ ----------- SYS_C00147516 NORMAL SCAN T1 TABLE SYS_IOT_TOP_260451 IOT - TOP SCAN T2 TABLE 2.将索引dump到trace文件中 SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/ncdb/podinndb/trace/podinndb_ora_28524.trc SQL> alter session set events 'immediate trace name treedump level 260450'; 会话已更改。 ----- begin tree dump leaf: 0x100008b 16777355 (0: nrow: 25 rrow: 25) ----- end tree dump SQL> select index_name, PREFIX_LENGTH, BLEVEL, LEAF_BLOCKS from user_indexes where index_name='SYS_C00147516'; INDEX_NAME PREFIX_LENGTH BLEVEL LEAF_BLOCKS ------------------------------ ------------- ---------- ----------- SYS_C00147516 0 1 SQL> select dbms_utility.data_block_address_file('16777355') FILE_ID, dbms_utility.data_block_address_block('16777355') BLOCK_ID from dual; 2 3 FILE_ID BLOCK_ID ---------- ---------- 4 139 SQL> select header_file,header_block from dba_segments where segment_name='SYS_C00147516'; HEADER_FILE HEADER_BLOCK ----------- ------------ 4 138 select dbms_utility.data_block_address_file(16777355)fno, dbms_utility.data_block_address_block(16777355) bkno from dualSQL> 2 3 ; FNO BKNO ---------- ---------- 4 139; SQL> alter system dump datafile 4 block 139; row#0[8013] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 00 col 0; len 10; (10): 31 20 20 20 20 20 20 20 20 20 ---表示1 row#1[7842] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 09 col 0; len 10; (10): 31 30 20 20 20 20 20 20 20 20 ---表示10 row#2[7823] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 0a col 0; len 10; (10): 31 31 20 20 20 20 20 20 20 20 --表示11 row#3[7804] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 0b col 0; len 10; (10): 31 32 20 20 20 20 20 20 20 20 --表示12 row#4[7785] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 0c col 0; len 10; (10): 31 33 20 20 20 20 20 20 20 20 --表示13 row#5[7766] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 0d col 0; len 10; (10): 31 34 20 20 20 20 20 20 20 20 --表示14 row#6[7747] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 0e col 0; len 10; (10): 31 35 20 20 20 20 20 20 20 20 --表示15 row#7[7728] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 0f col 0; len 10; (10): 31 36 20 20 20 20 20 20 20 20 --表示16 row#8[7709] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 10 col 0; len 10; (10): 31 37 20 20 20 20 20 20 20 20 --表示17 row#9[7690] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 11 col 0; len 10; (10): 31 38 20 20 20 20 20 20 20 20 --表示18 row#10[7671] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 12 col 0; len 10; (10): 31 39 20 20 20 20 20 20 20 20 --表示19 row#11[7994] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 01 col 0; len 10; (10): 32 20 20 20 20 20 20 20 20 20 --表示2 row#12[7652] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 13 col 0; len 10; (10): 32 30 20 20 20 20 20 20 20 20 --表示20 row#13[7633] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 14 col 0; len 10; (10): 32 31 20 20 20 20 20 20 20 20 --表示21 row#14[7614] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 15 col 0; len 10; (10): 32 32 20 20 20 20 20 20 20 20 --表示22 row#15[7595] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 16 col 0; len 10; (10): 32 33 20 20 20 20 20 20 20 20 --表示23 row#16[7576] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 17 col 0; len 10; (10): 32 34 20 20 20 20 20 20 20 20 --表示24 row#17[7557] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 18 col 0; len 10; (10): 32 35 20 20 20 20 20 20 20 20 --表示25 row#18[7975] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 02 col 0; len 10; (10): 33 20 20 20 20 20 20 20 20 20 --表示3 row#19[7956] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 03 col 0; len 10; (10): 34 20 20 20 20 20 20 20 20 20 --表示4 row#20[7937] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 04 col 0; len 10; (10): 35 20 20 20 20 20 20 20 20 20 --表示5 row#21[7918] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 05 col 0; len 10; (10): 36 20 20 20 20 20 20 20 20 20 --表示6 row#22[7899] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 06 col 0; len 10; (10): 37 20 20 20 20 20 20 20 20 20 --表示7 row#23[7880] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 07 col 0; len 10; (10): 38 20 20 20 20 20 20 20 20 20 --表示8 row#24[7861] flag: ------, lock: 2, len=19, data:(6): 01 00 00 85 00 08 col 0; len 10; (10): 39 20 20 20 20 20 20 20 20 20 --表示9 ----- end of leaf block dump ----- End dump data blocks tsn: 4 file#: 4 minblk 139 maxblk 139 DECLARE n VARCHAR2 (2000); BEGIN DBMS_STATS.convert_raw_value ('35', 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; 结论 heap表主键只有对应的列 heap 表排序: SQL> select * from t1 order by t1.id; ID A1 A2 ---------- ---------- ---------- 1 1 a1 10 10 a10 11 11 a11 12 12 a12 13 13 a13 14 14 a14 15 15 a15 16 16 a16 17 17 a17 18 18 a18 19 19 a19 ID A1 A2 ---------- ---------- ---------- 2 2 a2 20 20 a20 21 21 a21 22 22 a22 23 23 a23 24 24 a24 25 25 a25 3 3 a3 4 4 a4 5 5 a5 6 6 a6 ID A1 A2 ---------- ---------- ---------- 7 7 a7 8 8 a8 9 9 a9 25 rows selected. t1表 id列为字符串,排序是按字符串排序 /***创建t3表改为数值型/ create table t3 (id int primary key,a1 char(10),a2 char(10)); begin for i in 1 .. 25 loop insert into t3 values(i,i,'a'||i); end loop ; commit; end; BEGIN DBMS_STATS.GATHER_TABLE_STATS(ownname => 'SCAN', tabname => 'T3', estimate_percent => 100, method_opt => 'for all columns size repeat', no_invalidate => FALSE, degree => 8, cascade => TRUE); END; / SQL> select TABLE_NAME,TABLESPACE_NAME,CLUSTER_NAME, IOT_NAME from user_tables; TABLE_NAME TABLESPACE_NAME CLUSTER_NAME IOT_NAME -------------------- ------------------------------ ------------------------------ ------------------------------ T1 USERS T3 USERS T2 SQL> select object_name,object_id from user_objects; OBJECT_NAME OBJECT_ID ------------------------------ ---------- SYS_C00147518 260454 T3 260453 SYS_IOT_TOP_260451 260452 T1 260449 T2 260451 SYS_C00147516 260450 6 rows selected. SQL> select index_name,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,table_type from user_indexes; INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE ------------------------------ --------------------------- ------------------------------ -------------------- ----------- SYS_C00147516 NORMAL SCAN T1 TABLE SYS_IOT_TOP_260451 IOT - TOP SCAN T2 TABLE SYS_C00147518 NORMAL SCAN T3 TABLE 2.将索引dump到trace文件中 SQL> select value from v$diag_info where name='Default Trace File'; VALUE -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- /u01/app/oracle/diag/rdbms/ncdb/podinndb/trace/podinndb_ora_29363.trc SQL> alter session set events 'immediate trace name treedump level 260454'; 会话已更改。 ----- begin tree dump leaf: 0x10000a3 16777379 (0: nrow: 25 rrow: 25) ----- end tree dump SQL> select index_name, PREFIX_LENGTH, BLEVEL, LEAF_BLOCKS from user_indexes where index_name='SYS_C00147518'; INDEX_NAME PREFIX_LENGTH BLEVEL LEAF_BLOCKS ------------------------------ ------------- ---------- ----------- SYS_C00147518 0 1 SQL> select dbms_utility.data_block_address_file('16777379') FILE_ID, dbms_utility.data_block_address_block('16777379') BLOCK_ID from dual; 2 3 FILE_ID BLOCK_ID ---------- ---------- 4 163 SQL> select header_file,header_block from dba_segments where segment_name='SYS_C00147518'; HEADER_FILE HEADER_BLOCK ----------- ------------ 4 162 SQL> select dbms_utility.data_block_address_file(16777379)fno, dbms_utility.data_block_address_block(16777379) bkno from dual 2 ; FNO BKNO ---------- ---------- 4 163 SQL> alter system dump datafile 4 block 163; row#0[8021] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 00 col 0; len 2; (2): c1 02 ---表示1 row#1[8010] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 01 col 0; len 2; (2): c1 03 --表示2 row#2[7999] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 02 col 0; len 2; (2): c1 04 --表示3 row#3[7988] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 03 col 0; len 2; (2): c1 05 --表示4 row#4[7977] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 04 col 0; len 2; (2): c1 06 --表示5 row#5[7966] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 05 col 0; len 2; (2): c1 07 row#6[7955] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 06 col 0; len 2; (2): c1 08 row#7[7944] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 07 col 0; len 2; (2): c1 09 row#8[7933] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 08 col 0; len 2; (2): c1 0a row#9[7922] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 09 col 0; len 2; (2): c1 0b row#10[7911] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 0a col 0; len 2; (2): c1 0c row#11[7900] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 0b col 0; len 2; (2): c1 0d row#12[7889] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 0c col 0; len 2; (2): c1 0e row#13[7878] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 0d col 0; len 2; (2): c1 0f row#14[7867] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 0e col 0; len 2; (2): c1 10 row#15[7856] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 0f col 0; len 2; (2): c1 11 row#16[7845] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 10 col 0; len 2; (2): c1 12 row#17[7834] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 11 col 0; len 2; (2): c1 13 row#18[7823] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 12 col 0; len 2; (2): c1 14 row#19[7812] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 13 col 0; len 2; (2): c1 15 row#20[7801] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 14 col 0; len 2; (2): c1 16 row#21[7790] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 15 col 0; len 2; (2): c1 17 row#22[7779] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 16 col 0; len 2; (2): c1 18 --表示23 row#23[7768] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 17 col 0; len 2; (2): c1 19 --表示24 row#24[7757] flag: ------, lock: 2, len=11, data:(6): 01 00 00 9d 00 18 col 0; len 2; (2): c1 1a --表示25 ----- end of leaf block dump ----- End dump data blocks tsn: 4 file#: 4 minblk 163 maxblk 163 declare n number; begin dbms_stats.convert_raw_value('c102', n); dbms_output.put_line(n); end; SQL> select * from t3 order by t3.id; ID A1 A2 ---------- ---------- ---------- 1 1 a1 2 2 a2 3 3 a3 4 4 a4 5 5 a5 6 6 a6 7 7 a7 8 8 a8 9 9 a9 10 10 a10 11 11 a11 ID A1 A2 ---------- ---------- ---------- 12 12 a12 13 13 a13 14 14 a14 15 15 a15 16 16 a16 17 17 a17 18 18 a18 19 19 a19 20 20 a20 21 21 a21 22 22 a22 ID A1 A2 ---------- ---------- ---------- 23 23 a23 24 24 a24 25 25 a25 25 rows selected. 结论 字符串和数值型 排序方式不同