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 1000+ 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 Chris 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)

Is this answer out of date? If it is, please let us know via a Comment

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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

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