Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Thomas.

Asked: June 10, 2021 - 12:47 pm UTC

Last updated: June 11, 2021 - 5:09 pm UTC

Version: ORACLE ENTERPRISE 12CR1

Viewed 100+ times

You Asked

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?

and we said...

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  

Rating

  (1 rating)

Comments

A reader, June 11, 2021 - 2:20 pm UTC

Thanks for the explanation, Chris.
BTW, as you mentioned, below query is used to get relevant segment_name:
select segment_name, segment_type, tablespace_name
from user_extents where segment_name in (
select object_name from user_objects
where created > sysdate - 1
);
I think, this one can also work and it only focus on the info of desired table:
select segment_name, segment_type, tablespace_name from user_extents where segment_name in (select table_name from user_tables where iot_name='TB_ZXP' union select index_name from user_indexes where table_name='TB_ZXP');

Chris Saxon
June 11, 2021 - 5:09 pm UTC

Yes, your query works (and only returns what you're looking for unlike mine ;)

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.