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.
结论 字符串和数值型 排序方式不同
