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索引存储表的所有数据
