OraFAQ Forum: Performance Tuning » Index on Range-List Subpartitions Table using template

Home » RDBMS Server » Performance Tuning » Index on Range-List Subpartitions Table using template (Oracle 11.2.0.2.0 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Index on Range-List Subpartitions Table using template [message #528340]

Mon, 24 October 2011 06:20

ric90
Messages: 42
Registered: May 2011
Location: Belfort

Member

Hi all,

I created table Rang pertitionned, and List subpartitionned.
My table is Interval partitionning.
My subpartition is template based as is :

    PARTITION BY RANGE ( DINFOIDENTITE ) 
        INTERVAL ( (NUMTOYMINTERVAL(1,'MONTH')) ) 
    SUBPARTITION BY LIST ( AVANT_DERNIER_MATCLE ) 
    SUBPARTITION TEMPLATE 
    ( 
        SUBPARTITION MATCLE_SP_0 VALUES ( 0 ) 
        TABLESPACE "&tbs_big_sp_0" , 
        SUBPARTITION MATCLE_SP_1 VALUES ( 1 ) 
        TABLESPACE "&tbs_big_sp_1" , 
        SUBPARTITION MATCLE_SP_2 VALUES ( 2 ) 
        TABLESPACE "&tbs_big_sp_2" , 
        SUBPARTITION MATCLE_SP_3 VALUES ( 3 ) 
        TABLESPACE "&tbs_big_sp_3" , 
        SUBPARTITION MATCLE_SP_4 VALUES ( 4 ) 
        TABLESPACE "&tbs_big_sp_4" , 
        SUBPARTITION MATCLE_SP_5 VALUES ( 5 ) 
        TABLESPACE "&tbs_big_sp_5" , 
        SUBPARTITION MATCLE_SP_6 VALUES ( 6 ) 
        TABLESPACE "&tbs_big_sp_6" , 
        SUBPARTITION MATCLE_SP_7 VALUES ( 7 ) 
        TABLESPACE "&tbs_big_sp_7" , 
        SUBPARTITION MATCLE_SP_8 VALUES ( 8 ) 
        TABLESPACE "&tbs_big_sp_8" , 
        SUBPARTITION MATCLE_SP_9 VALUES ( 9 ) 
        TABLESPACE "&tbs_big_sp_9" 
    )

I have LOCAL Index on this table.
But all subpartitions are in the same tablespace, according to this script :

CREATE INDEX RRG.RRGQXZY01A ON RRG.RRGQTZY01 
    ( 
     MATRICULE_RH ASC 
    ) 
    LOCAL 
    ( 
    PARTITION RRGQTZY01_2011_05 
        LOGGING 
    ) 
    TABLESPACE "&tbs_index_part" 
    LOGGING 
    NOCOMPRESS 
    NOPARALLEL 
;

i would have the same repartition with 10 differents tablespaces, one for each subpartition.

I search on Oracle documentation this morning, but the only thing i've found is that this option is possible with the STORE IN clause, but only for HASH partitionning.

Is there a way to specify STORAGE clause with a template for my indexes ?

Tank you for advice.

Ric

Report message to a moderator

Re: Index on Range-List Subpartitions Table using template [message #528506 is a reply to message #528340]

Tue, 25 October 2011 06:26

ric90
Messages: 42
Registered: May 2011
Location: Belfort

Member

Any idea ?

Report message to a moderator

Re: Index on Range-List Subpartitions Table using template [message #528511 is a reply to message #528506]

Tue, 25 October 2011 07:23

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I didn't understand your problem. Didn't you see the following example in the documentation?

Quote:

Creating an Index on a Composite-Partitioned Table: Example

The following statement creates a local index on the composite_sales table, which was created in "Composite-Partitioned Table Examples". The STORAGE clause specifies default storage attributes for the index. However, this default is overridden for the five subpartitions of partitions q3_2000 and q4_2000, because separate TABLESPACE storage is specified.

The creator of the index must have quota on the tablespaces specified. See CREATE TABLESPACE for examples that create tablespaces tbs_1 and tbs_2.

CREATE INDEX sales_ix ON composite_sales(time_id, prod_id)
STORAGE (INITIAL 1M)
LOCAL
(PARTITION q1_1998,
PARTITION q2_1998,
PARTITION q3_1998,
PARTITION q4_1998,
PARTITION q1_1999,
PARTITION q2_1999,
PARTITION q3_1999,
PARTITION q4_1999,
PARTITION q1_2000,
PARTITION q2_2000
(SUBPARTITION pq2001, SUBPARTITION pq2002,
SUBPARTITION pq2003, SUBPARTITION pq2004,
SUBPARTITION pq2005, SUBPARTITION pq2006,
SUBPARTITION pq2007, SUBPARTITION pq2008),
PARTITION q3_2000
(SUBPARTITION c1 TABLESPACE tbs_02,
SUBPARTITION c2 TABLESPACE tbs_02,
SUBPARTITION c3 TABLESPACE tbs_02,
SUBPARTITION c4 TABLESPACE tbs_02,
SUBPARTITION c5 TABLESPACE tbs_02),
PARTITION q4_2000
(SUBPARTITION pq4001 TABLESPACE tbs_03,
SUBPARTITION pq4002 TABLESPACE tbs_03,
SUBPARTITION pq4003 TABLESPACE tbs_03,
SUBPARTITION pq4004 TABLESPACE tbs_03)
);

Report message to a moderator

Re: Index on Range-List Subpartitions Table using template [message #528513 is a reply to message #528511]

Tue, 25 October 2011 07:36

ric90
Messages: 42
Registered: May 2011
Location: Belfort

Member

Thanx for you reply, but nothing guarentee that if I add a ne PARTITION q1_2001 on my table that the index subpartition are in the right tablespace.
According to your script, you specify a new tablespace for each subpartition in a partition.
The thing I want to do is to reuse the same tablespace for each subpartition (eg : MATCLE_SP_0 ) whatever the partition is.
And i don't want to specifiy the tablespace for each subpartition (36 partition * 10 subpartition initialization) when creating index.
Moreover, my table is INTERVAL partitionning, in order to create new partition when it needs. I don't want to have to move/split my tablespace for my index.

But according to the oracle documentation, there is no way to do this, except for HASH partitionning
Quote:

The STORE IN clause is valid only for range-hash or list-hash composite-partitioned
tables. It lets you specify one or more default tablespaces across which Oracle
Database will distribute all index hash subpartitions for all partitions. You can
override this storage by specifying different default tablespace storage for the
subpartitions of an individual partition in the second STORE IN clause in the index_
subpartition_clause.

source : http://download.oracle.com/docs/cd/E11882_01/server.112/e26088.pdf

Report message to a moderator

Re: Index on Range-List Subpartitions Table using template [message #528518 is a reply to message #528513]

Tue, 25 October 2011 08:12

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

Seems to be a problem.

Report message to a moderator

Previous Topic:	Snap IDs not found for awr reports generation
Next Topic:	Tracing long running program

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 22:20:51 CDT 2024