SQL> create user scan identified by scan default tablespace users; User created. SQL> grant dba to scan; Grant succeeded. 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; / 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 IOT表排序: SQL> select * from t2; 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. SQL> select * from t2 order by t2.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. /********dump iot表**********************/ 将索引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_29689.trc SQL> alter session set events 'immediate trace name treedump level 260452'; 会话已更改。 ----- begin tree dump leaf: 0x1000093 16777363 (0: nrow: 25 rrow: 25) ----- end tree dump SQL> select dbms_utility.data_block_address_file(16777363)fno, dbms_utility.data_block_address_block(16777363) bkno from dual 2 3 ; FNO BKNO ---------- ---------- 4 147 alter system dump datafile 4 block 147; row#0[8002] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 02 --表示1 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 20 20 20 20 20 20 20 20 20 --表示1 col 1: [10] 61 31 20 20 20 20 20 20 20 20 --表示a1 row#1[7972] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 03 --表示2 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 32 20 20 20 20 20 20 20 20 20 --2 col 1: [10] 61 32 20 20 20 20 20 20 20 20 --表示a2 row#2[7942] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 04 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 33 20 20 20 20 20 20 20 20 20 col 1: [10] 61 33 20 20 20 20 20 20 20 20 row#3[7912] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 05 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 34 20 20 20 20 20 20 20 20 20 col 1: [10] 61 34 20 20 20 20 20 20 20 20 row#4[7882] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 06 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 35 20 20 20 20 20 20 20 20 20 col 1: [10] 61 35 20 20 20 20 20 20 20 20 row#5[7852] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 07 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 36 20 20 20 20 20 20 20 20 20 col 1: [10] 61 36 20 20 20 20 20 20 20 20 row#6[7822] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 08 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 37 20 20 20 20 20 20 20 20 20 col 1: [10] 61 37 20 20 20 20 20 20 20 20 row#7[7792] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 09 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 38 20 20 20 20 20 20 20 20 20 col 1: [10] 61 38 20 20 20 20 20 20 20 20 row#8[7762] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 0a tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 39 20 20 20 20 20 20 20 20 20 col 1: [10] 61 39 20 20 20 20 20 20 20 20 row#9[7732] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 0b tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 30 20 20 20 20 20 20 20 20 col 1: [10] 61 31 30 20 20 20 20 20 20 20 row#10[7702] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 0c tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 31 20 20 20 20 20 20 20 20 col 1: [10] 61 31 31 20 20 20 20 20 20 20 row#11[7672] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 0d tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 32 20 20 20 20 20 20 20 20 col 1: [10] 61 31 32 20 20 20 20 20 20 20 row#12[7642] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 0e tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 33 20 20 20 20 20 20 20 20 col 1: [10] 61 31 33 20 20 20 20 20 20 20 row#13[7612] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 0f tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 34 20 20 20 20 20 20 20 20 col 1: [10] 61 31 34 20 20 20 20 20 20 20 row#14[7582] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 10 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 35 20 20 20 20 20 20 20 20 col 1: [10] 61 31 35 20 20 20 20 20 20 20 row#15[7552] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 11 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 36 20 20 20 20 20 20 20 20 col 1: [10] 61 31 36 20 20 20 20 20 20 20 row#16[7522] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 12 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 37 20 20 20 20 20 20 20 20 col 1: [10] 61 31 37 20 20 20 20 20 20 20 row#17[7492] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 13 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 38 20 20 20 20 20 20 20 20 col 1: [10] 61 31 38 20 20 20 20 20 20 20 row#18[7462] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 14 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 31 39 20 20 20 20 20 20 20 20 col 1: [10] 61 31 39 20 20 20 20 20 20 20 row#19[7432] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 15 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 32 30 20 20 20 20 20 20 20 20 col 1: [10] 61 32 30 20 20 20 20 20 20 20 row#20[7402] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 16 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 32 31 20 20 20 20 20 20 20 20 col 1: [10] 61 32 31 20 20 20 20 20 20 20 row#21[7372] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 17 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 32 32 20 20 20 20 20 20 20 20 col 1: [10] 61 32 32 20 20 20 20 20 20 20 row#22[7342] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 18 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 32 33 20 20 20 20 20 20 20 20 col 1: [10] 61 32 33 20 20 20 20 20 20 20 row#23[7312] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 19 tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 32 34 20 20 20 20 20 20 20 20 col 1: [10] 61 32 34 20 20 20 20 20 20 20 row#24[7282] flag: K-----, lock: 2, len=30 col 0; len 2; (2): c1 1a tl: 25 fb: --H-FL-- lb: 0x0 cc: 2 col 0: [10] 32 35 20 20 20 20 20 20 20 20 col 1: [10] 61 32 35 20 20 20 20 20 20 20 ----- end of leaf block dump ----- End dump data blocks tsn: 4 file#: 4 minblk 147 maxblk 147 declare n number; begin dbms_stats.convert_raw_value('c102', n); dbms_output.put_line(n); end; DECLARE n VARCHAR2 (2000); BEGIN DBMS_STATS.convert_raw_value ('34', 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; 结论:IOT索引存储表的所有数据