IOT table 的including 放在overflow的column

lory 发表于 2013-11-27 15:56:41

oracle:10.2.0.5
OS:windows 2008 64 bit
在oracle(10g)的手册中解释including:
The primary key columns are always
stored in the index. column_name can be either the last primary key column or any
non primary key column. All non primary key columns that follow column_name are
stored in the overflow data segment.

如果我在创建表的时候，主键并不是放在第一位的CREATE TABLE "SAJET"."IOT1"
( "EMP_NO" CHAR(800),

"EMP_NAME" VARCHAR2(20),

"EMP_ID" NUMBER,

"QUIT_DATE" DATE,

   CONSTRAINT "EMP_IOT1_PK" PRIMARY KEY ("EMP_ID") ENABLE

) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSBS"
PCTTHRESHOLD 50 INCLUDING "EMP_NAME" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSBS这个时候是哪些column被放置在overflow的segment呢，按照以下结果是不是就只有quit_date放在overflow的segment呢

select l.column_name,l.segment_column_id from user_tab_cols l where table_name='IOT1'COLUMN_NAME SEGMENT_COLUMN_ID
EMP_NO 2
EMP_NAME 3
EMP_ID 1
QUIT_DATE 4

Maclean Liu(刘相兵 发表于 2013-11-27 16:31:11

FYICREATE TABLE "IOT1"
(       "EMP_NO" CHAR(800),
      "EMP_NAME" VARCHAR2(20),
      "EMP_ID" NUMBER,
      "QUIT_DATE" DATE,
      CONSTRAINT "EMP_IOT1_PK" PRIMARY KEY ("EMP_ID") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE USERS
PCTTHRESHOLD 50 INCLUDING "EMP_NAME" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE USERS;






SQL> col object_id for 999999
SQL> col object_name for a20
SQL> col object_type for a20
SQL> /

OBJECT_ID OBJECT_NAME       OBJECT_TYPE
--------- -------------------- --------------------
102341 IOT1                TABLE
102342 SYS_IOT_OVER_102341  TABLE
102343 EMP_IOT1_PK       INDEX

SELECT c.table_name, c.column_name,
      CASE WHEN c.column_id <= i.include_column THEN 'TOP' ELSE 'OVERFLOW' END segment
FROM user_tab_columns c, user_indexes i
WHERE i.table_name = c.table_name
ORDER by table_name, column_id;

TABLE_NAME                   COLUMN_NAME                   SEGMENT
------------------------------ ------------------------------ --------
IOT1                         EMP_NO                      TOP
IOT1                         EMP_NAME                      TOP
IOT1                         EMP_ID                      OVERFLOW
IOT1                         QUIT_DATE                   OVERFLOW
IOT上除去主键外还有 QUIT_DATE

lory 发表于 2013-11-27 20:38:10

主键在overflow上么？EMP_ID是主键呀。我不是很理解，是我错过什么了么

Maclean Liu(刘相兵 发表于 2013-11-27 20:50:29

常规管理] IOT索引组织表内部结构 http://t.askmaclean.com/thread-3438-1-1.html

lory 发表于 2014-1-5 16:59:45

Maclean Liu(刘相兵发表于 2013-11-27 16:31 static/image/common/back.gif
FYIIOT上除去主键外还有 QUIT_DATE

参考了http://jonathanlewis.wordpress.com/2011/12/11/iot-trap/

index segment是不会包含quit_date的。
  CREATE TABLE "SAJET"."IOT"
( "OWNER" VARCHAR2(30) NOT NULL ENABLE,

"OBJECT_TYPE" VARCHAR2(19),

"OBJECT_NAME" VARCHAR2(30) NOT NULL ENABLE,

   CONSTRAINT "IOT_PK" PRIMARY KEY ("OBJECT_NAME","OWNER", "OBJECT_TYPE") ENABLE

) ORGANIZATION INDEX COMPRESS 2 PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "SYSBS"
PCTTHRESHOLD 50

insert into iot2 (emp_no,emp_name,emp_id,quit_date) values('523698','Kevin',1,sysdate);

insert into iot2 (emp_no,emp_name,emp_id,quit_date) values('52684','ivsds',2,sysdate);
commit；

dump leaf block的其中一个entry。看得出来quit_date没有在index block的row#1 flag: K-----, lock: 2, len=823
col 0; len 2; (2):  c1 03
tl: 818 fb: --H-F--- lb: 0x0  cc: 2
nrid:  0x054bba68.1
col  0:
35 32 36 38 34 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
col  1: [ 5]  69 76 73 64 73
----- end of leaf block dump -----
End dump data blocks tsn: 12 file#: 21 minblk 768620 maxblk 768620
但是有个怪现象，如果最初是插入insert into iot2 (emp_no,emp_name,emp_id) values('523698','Kevin',1);

insert into iot2 (emp_no,emp_name,emp_id) values('52684','ivsds',2);
commit；
update iot2 set quit_date=sysdate;
commit;那么dump出来的leaf block里面quit_date就会在index block里面

页: [1]

Oracle数据库数据恢复、性能优化's Archiver

IOT table 的including 放在overflow的column