I have two tablespaces named USERS and INDX, respectively. The dufault tablespace for current user is USERS. I created an IOT table whose name is tb_zxp. Since no need to specify a tablespace storing data for IOT, I'd like to put the whole index of tb_zxp on tablespace INDX:
create table tb_zxp (customer_id integer ,
store_id integer,
trans_date date,
amt number,
goods_name varchar2(20),
rate number(8,1),
quantity integer,
constraint pk_zxp primary key (customer_id,store_id,trans_date))
organization index including amt overflow
tablespace indx;
insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);
insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200);
insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1);
commit;
However, with this query, we can find the index is still assigned on default tablespace USERS:
select tablespace_name from user_extents where segment_name in (select 'TB_ZXP' c from dual union select index_name from user_indexes where table_name='TB_ZXP');
TABLESPACE_NAME
------------------------------------------------------------------------------------------
USERS
Then,I remove the INCLUDING OVERFLOW clause from table creation statement, and try it again:
create table tb_zxp (customer_id integer ,
store_id integer,
trans_date date,
amt number,
goods_name varchar2(20),
rate number(8,1),
quantity integer,
constraint pk_zxp primary key (customer_id,store_id,trans_date))
organization index
tablespace indx;
insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);
commit;
This time, the index falls upon tablespace INDX as expected:
select tablespace_name from user_extents where segment_name in (select 'TB_ZXP' c from dual union select index_name from user_indexes where table_name='TB_ZXP');
TABLESPACE_NAME
------------------------------------------------------------------------------------------
INDX
Could any guru kindly explain why the removal of including overflow can provide us desired result?
When you have an overflow clause, the overflow area is a table segment, not an index segment. This has a system-generated name, so is not returned by those queries on user_extents.
With these properties:
organization index including amt
overflow tablespace indx
The tablespace clause applies to the overflow segment only. To state the tablespace for the index, put another tablespace clause before overflow:
organization index
tablespace TBLSP
including amt
overflow tablespace TBLSP
For example:
create table tb_zxp (customer_id integer ,
store_id integer,
trans_date date,
amt number,
goods_name varchar2(20),
rate number(8,1),
quantity integer,
constraint pk_zxp primary key (customer_id,store_id,trans_date))
organization index including amt overflow
tablespace TBLSP;
insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);
insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200);
insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1);
commit;
select segment_name, segment_type, tablespace_name
from user_extents where segment_name in (
select object_name from user_objects
where created > sysdate - 1
);
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
SYS_IOT_OVER_151150 TABLE TBLSP
PK_ZXP INDEX USERS
drop table tb_zxp
cascade constraints purge;
create table tb_zxp (customer_id integer ,
store_id integer,
trans_date date,
amt number,
goods_name varchar2(20),
rate number(8,1),
quantity integer,
constraint pk_zxp primary key (customer_id,store_id,trans_date))
organization index
tablespace TBLSP;
insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);
insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200);
insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1);
commit;
select segment_name, segment_type, tablespace_name
from user_extents where segment_name in (
select object_name from user_objects
where created > sysdate - 1
);
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
PK_ZXP INDEX TBLSP
drop table tb_zxp
cascade constraints purge;
create table tb_zxp (customer_id integer ,
store_id integer,
trans_date date,
amt number,
goods_name varchar2(20),
rate number(8,1),
quantity integer,
constraint pk_zxp primary key (customer_id,store_id,trans_date))
organization index
tablespace TBLSP
including amt overflow
tablespace TBLSP;
insert into tb_zxp values (11,21,date '2021-04-10',500,'Cocacola',2,250);
insert into tb_zxp values (11,25,date '2021-04-11',760,'Tower',3.8,200);
insert into tb_zxp values (24,9,date '2021-05-11',5200,'Washing machine',5200,1);
commit;
select segment_name, segment_type, tablespace_name
from user_extents where segment_name in (
select object_name from user_objects
where created > sysdate - 1
);
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
SYS_IOT_OVER_151155 TABLE TBLSP
PK_ZXP INDEX TBLSP