验证secondary key 含有主键列数据 SQL> select object_name,object_id,OBJECT_TYPE from user_objects; OBJECT_NAME OBJECT_ID OBJECT_TYPE
-------------------- ---------- -------------------
T2 101449 TABLE
SYS_IOT_TOP_101449 101450 INDEX
T2_IDX1 101451 INDEX 将索引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_5435.trc SQL> alter session set events 'immediate trace name treedump level 101451'; 会话已更改。 ----- begin tree dump
branch: 0x10000bb 16777403 (0: nrow: 8, level: 1)
leaf: 0x10000bc 16777404 (-1: nrow: 266 rrow: 266)
leaf: 0x10000bd 16777405 (0: nrow: 266 rrow: 266)
leaf: 0x10000be 16777406 (1: nrow: 266 rrow: 266)
leaf: 0x10000bf 16777407 (2: nrow: 266 rrow: 266)
leaf: 0x10000c0 16777408 (3: nrow: 266 rrow: 266)
leaf: 0x10000c1 16777409 (4: nrow: 267 rrow: 267)
leaf: 0x10000c2 16777410 (5: nrow: 266 rrow: 266)
leaf: 0x10000c3 16777411 (6: nrow: 137 rrow: 137)
----- end tree dump SQL> select dbms_utility.data_block_address_file(16777404)fno,
dbms_utility.data_block_address_block(16777404) bkno from dual 2 ; FNO BKNO
---------- ----------
4 188 alter system dump datafile 4 block 188; row#0[8008] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 61 31 20 20 20 20 20 20 20 20
col 1; len 2; (2): c1 02
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 ac
row#1[7984] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 61 31 30 20 20 20 20 20 20 20
col 1; len 2; (2): c1 0b
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 ac
row#2[7960] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 61 31 30 30 20 20 20 20 20 20
col 1; len 2; (2): c2 02
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 ac
row#3[7936] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 61 31 30 30 30 20 20 20 20 20
col 1; len 2; (2): c2 0b
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 b5
row#4[7911] flag: K-----, lock: 0, len=25
col 0; len 10; (10): 61 31 30 30 31 20 20 20 20 20
col 1; len 3; (3): c2 0b 02
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 b5
row#5[7886] flag: K-----, lock: 0, len=25
col 0; len 10; (10): 61 31 30 30 32 20 20 20 20 20
col 1; len 3; (3): c2 0b 03
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 00 b5 DECLARE
n VARCHAR2 (2000);
BEGIN
DBMS_STATS.convert_raw_value ('61', 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);
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; declare
n number;
begin
dbms_stats.convert_raw_value('c102',
n);
dbms_output.put_line(n);
end; col 0; len 10; (10): 61 31 20 20 20 20 20 20 20 20 表示a1
col 1; len 2; (2): c1 02 表示1 row#1[7984] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 61 31 30 20 20 20 20 20 20 20 表示a10
col 1; len 2; (2): c1 0b 表示10 row#2[7960] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 61 31 30 30 20 20 20 20 20 20 表示a100
col 1; len 2; (2): c2 02 --表示100 row#3[7936] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 61 31 30 30 30 20 20 20 20 20 表示a1000
col 1; len 2; (2): c2 0b --表示1000 row#4[7911] flag: K-----, lock: 0, len=25
col 0; len 10; (10): 61 31 30 30 31 20 20 20 20 20 表示a10001
col 1; len 3; (3): c2 0b 02 表示1001 row#5[7886] flag: K-----, lock: 0, len=25
col 0; len 10; (10): 61 31 30 30 32 20 20 20 20 20 表示a1002
col 1; len 3; (3): c2 0b 03 表示1002 结论:secondary key 包含了主键对应的列 顺序为secondary key对应的列加上主键对应的列。