Home » SQL & PL/SQL » SQL & PL/SQL » Interval Partitioning and local index
Interval Partitioning and local index [message #647232] Sat, 23 January 2016 15:55 Go to next message
menoarc
Messages: 3
Registered: January 2016
Junior Member
I am partitioning a table A (volume - 400 mB).
The partitioned table includes one manual partition and other interval partitions, with each partition consisting 3 months data.

create table A PARTITION BY RANGE (date)
INTERVAL(NUMTOYMINTERVAL(3, 'MONTH'))
(
PARTITION A_2006 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY')),
) as select * from A_BKP;

Now I am creating index on this table :

Create index A_IDX on A(c1,c2,date,c3) Tablespace A_IDX_TBS local logging;

select table_name,partition_name,partition_position,high_value,num_rows from USER_TAB_PARTITIONS where table_name='A' ;

TABLE_NAME | PARTITION_NAME | PARTITION_POSITION | NUM_ROWS | HIGH_VALUE
A | A_2006 | 1 | 0 | TO_DATE('1-7-2006', 'DD-MM-YYYY')
A | SYS_P11 | 2 | 137387 | TO_DATE('1-1-2010', 'DD-MM-YYYY')
A | SYS_P12 | 3 | 299589 | TO_DATE('1-4-2010', 'DD-MM-YYYY')
A | SYS_P13 | 4 | 558850 | TO_DATE('1-7-2010', 'DD-MM-YYYY')

select index_name,partition_name,status,num_rows,high_value from USER_IND_PARTITIONS where index_name ='A_IDX' ;

INDEX_NAME | PARTITION_NAME | STATUS | NUM_ROWS | HIGH_VALUE
A_IDX | A_2006 | USABLE | 0 | TO_DATE('1-7-2006', 'DD-MM-YYYY')
A_IDX | SYS_P32 | USABLE | 137387 | TO_DATE('1-1-2010', 'DD-MM-YYYY')
A_IDX | SYS_P33 | USABLE | 299589 | TO_DATE('1-4-2010', 'DD-MM-YYYY')
A_IDX | SYS_P34 | USABLE | 558850 | TO_DATE('1-7-2010', 'DD-MM-YYYY')

THe user_ind_partiton and user_tab_parttion shows different partition name.
Please explain why.

Shouldn't the partition name be same?
Please help !!

Report message to a moderator

Re: Interval Partitioning and local index - Urgent [message #647234 is a reply to message #647232] Sat, 23 January 2016 16:27 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Interval Partitioning and local index [message #647235 is a reply to message #647232] Sat, 23 January 2016 16:45 Go to previous messageGo to next message
menoarc
Messages: 3
Registered: January 2016
Junior Member
I am partitioning a table A (volume - 400 mB).
The partitioned table includes one manual partition and other interval partitions, with each partition consisting 3 months data.

create table A PARTITION BY RANGE (date) 
INTERVAL(NUMTOYMINTERVAL(3, 'MONTH')) 
(  
   PARTITION A_2006 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY')),
   )  as select * from A_BKP;

Now I am creating index on this table :

Create index A_IDX on A(c1,c2,date,c3) Tablespace A_IDX_TBS local logging;



select table_name,partition_name,partition_position,high_value,num_rows from USER_TAB_PARTITIONS where table_name='A' ;


TABLE_NAME | PARTITION_NAME | PARTITION_POSITION | NUM_ROWS | HIGH_VALUE
A | A_2006 | 1 | 0 | TO_DATE('1-7-2006', 'DD-MM-YYYY')
A | SYS_P11 | 2 | 137387 | TO_DATE('1-1-2010', 'DD-MM-YYYY')
A | SYS_P12 | 3 | 299589 | TO_DATE('1-4-2010', 'DD-MM-YYYY')
A | SYS_P13 | 4 | 558850 | TO_DATE('1-7-2010', 'DD-MM-YYYY')

select index_name,partition_name,status,num_rows,high_value from USER_IND_PARTITIONS where index_name ='A_IDX' ;

INDEX_NAME | PARTITION_NAME | STATUS | NUM_ROWS | HIGH_VALUE
A_IDX | A_2006 | USABLE | 0 | TO_DATE('1-7-2006', 'DD-MM-YYYY')
A_IDX | SYS_P32 | USABLE | 137387 | TO_DATE('1-1-2010', 'DD-MM-YYYY')
A_IDX | SYS_P33 | USABLE | 299589 | TO_DATE('1-4-2010', 'DD-MM-YYYY')
A_IDX | SYS_P34 | USABLE | 558850 | TO_DATE('1-7-2010', 'DD-MM-YYYY')

THe user_ind_partiton and user_tab_parttion shows different partition name.
Please explain why.

Shouldn't the partition name be same?
Please help !!

[Updated on: Sun, 24 January 2016 00:41] by Moderator

Report message to a moderator

Re: Interval Partitioning and local index [message #647236 is a reply to message #647235] Sat, 23 January 2016 17:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Shouldn't the partition name be same?
What difference does it make?

post URL to Oracle documentation that says partition names should be the same for table & for index partitions.

Report message to a moderator

Re: Interval Partitioning and local index [message #647237 is a reply to message #647235] Sat, 23 January 2016 20:09 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Oracle uses same sequence to generate both table and index partition names. Therefore table and index system generated partition names do not match. In general, index partition is tied to table partition via partition position:

SQL> create table A(c1 number,c2 number,c3 number,dt date) PARTITION BY RANGE (dt)
  2  INTERVAL(NUMTOYMINTERVAL(3,'MONTH'))
  3  (
  4  PARTITION A_2006 VALUES LESS THAN (TO_DATE('1-7-2006', 'DD-MM-YYYY'))
  5  )
  6  /

Table created.

SQL> insert into a
  2  select level,level,level,add_months(date '2006-06-01',(level - 1) * 3)
  3  from dual
  4  connect by level <= 3
  5  /

3 rows created.

SQL> commit;

Commit complete.

SQL> select partition_name from user_tab_partitions where table_name = 'A';

PARTITION_NAME
--------------------------------------------------------------------------------
A_2006
SYS_P4351
SYS_P4352

SQL> create index A_IDX on A(c1,c2,dt,c3) local;

Index created.

SQL> select partition_name from user_ind_partitions where index_name = 'A_IDX';

PARTITION_NAME
--------------------------------------------------------------------------------
A_2006
SYS_P4353
SYS_P4354

SQL> select  ip.partition_name index_partition,
  2          tp.partition_name table_partition
  3    from  user_ind_partitions ip,
  4          user_indexes i,
  5          user_tab_partitions tp
  6    where ip.index_name = 'A_IDX'
  7      and i.index_name = ip.index_name
  8      and tp.table_name = i.table_name
  9      and ip.partition_position = tp.partition_position;

INDEX_PARTITION      TABLE_PARTITION
-------------------- --------------------
A_2006               A_2006
SYS_P4353            SYS_P4351
SYS_P4354            SYS_P4352

SQL>



SY.

Report message to a moderator

Re: Interval Partitioning and local index [message #647238 is a reply to message #647237] Sat, 23 January 2016 20:31 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
Senior Member
Also, index & partition names will get different names if index partition is created not at the same time as table partition

SQL> insert into a
  2  select level,level,level,add_months(date '2006-06-01',(level + 2) * 3)
  3  from dual
  4  connect by level <= 3
  5  /

3 rows created.

SQL> select  ip.partition_name index_partition,
  2          tp.partition_name table_partition
  3    from  user_ind_partitions ip,
  4          user_indexes i,
  5          user_tab_partitions tp
  6    where ip.index_name = 'A_IDX'
  7      and i.index_name = ip.index_name
  8      and tp.table_name = i.table_name
  9      and ip.partition_position = tp.partition_position;

INDEX_PARTITION      TABLE_PARTITION
-------------------- --------------------
A_2006               A_2006
SYS_P4353            SYS_P4351
SYS_P4354            SYS_P4352
SYS_P4355            SYS_P4355
SYS_P4356            SYS_P4356
SYS_P4357            SYS_P4357

6 rows selected.

SQL>


SY.

Report message to a moderator

Re: Interval Partitioning and local index [message #647247 is a reply to message #647238] Sun, 24 January 2016 04:47 Go to previous message
menoarc
Messages: 3
Registered: January 2016
Junior Member
Thankyou so much Solomon for the detailed explanation !!
THat was really hepful Smile

Report message to a moderator

Previous Topic: INSERT into a Partition
Next Topic: help to make sql query
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 16:12:39 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.