Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Split partition causes indexes moved to same tablespace with underlying table
harryn
post Nov 12 2007, 01:44 AM
Post #1


Member
**

Group: Members
Posts: 16
Joined: 5-June 07
Member No.: 9,127



We created table with single partition on tablespace-1, then create local index on this single partition on tablespace-2. After splitting the partition, we found that both data partitions and index partitions are on the same tablespace (whatever the tablespace specified for the ALTER TABLE <tbl-name> SPLIT PARTITION ... INTO... TABLESPACE <tblspace-name>.

Anyway to achieve the same partition splitting (Oracle 9iR2) but still having data and indexes on separate tablespaces?
Go to the top of the page
 
+Quote Post
burleson
post Nov 12 2007, 04:09 AM
Post #2


Advanced Member
***

Group: Members
Posts: 11,401
Joined: 26-January 04
Member No.: 13



Hi Harry,

>> (whatever the tablespace specified for the ALTER TABLE <tbl-name> SPLIT PARTITION ... INTO... TABLESPACE <tblspace-name>.

Cannot you define separate tablespaces at partition creation time?

Dr. Hall shows that you can define the partitions in separate tablespaces:

http://www.oracle-base.com/articles/8i/Par...sAndIndexes.php

ALTER TABLE my_table SPLIT PARTITION my_table_part AT (3)
INTO (PARTITION my_table_part_1,
PARTITION my_table_part_2);


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
harryn
post Nov 12 2007, 05:18 AM
Post #3


Member
**

Group: Members
Posts: 16
Joined: 5-June 07
Member No.: 9,127



Thanks Don, The problem here is after splitting the table, Oracle (9i) also splits the local indexes accordingly but "moves" the indexes (originally defined from different tablespace) to the same tablespace of the underlying table. The steps I did were:

1. Create tables with single partition in one (data) tablespace

2. Create local indexes based on the single partition (above) in another (index) tablespace

3. Perform partition splitting on single partition to create multiple partitions

An illustrated example of all the above steps:

create table ETL_XCHANGE (
xchange_key NUMBER(16) NOT NULL,
account_key NUMBER(16),
period_key NUMBER(8),
partition_id NUMBER(16)
)
tablespace STAGE_DATA
partition by range (PERIOD_KEY)
subpartition by hash (ACCOUNT_KEY) subpartition template
( subpartition S01 tablespace STAGE_DATA
,subpartition S02 tablespace STAGE_DATA
,subpartition S03 tablespace STAGE_DATA
)
(partition ETLX_MAX values less than (MAXVALUE) tablespace STAGE_DATA);

create index XCHANGE_IDX1 on ETL_XCHANGE (XCHANGE_KEY, PERIOD_KEY, PARTITION_ID)
nologging local (partition ETLX_MAX tablespace STAGE_IDX);

alter table ETL_XCHANGE split partition ETLX_MAX at ($month18)
itno (partition ETLX_1, partition ETLX_MAX tablespace STAGE_DATA);

After splitting, Oracle automatically splits the local indexes accordingly as well but have both data and indexes in tablespace STAGE_DATA.

The Oracle 9i documentation also stated:
"Oracle splits the corresponding local index partition, even if it is marked UNUSABLE. Oracle marks UNUSABLE, and you must rebuild, the local index partitions corresponding to the split partitions. The new index partitions inherit their attributes from the partition being split. Oracle stores the new index partitions in the default tablespace of the index partition being split. If that index partition has no default tablespace, then Oracle uses the tablespace of the new underlying table partitions."


Any help will be much appreciated.
Go to the top of the page
 
+Quote Post
HAL9000
post Nov 12 2007, 06:40 AM
Post #4


Advanced Member
***

Group: Members
Posts: 879
Joined: 25-September 07
Member No.: 12,336



Hi Harry,

>> After splitting, Oracle automatically splits the local indexes accordingly as well but have both data and indexes in tablespace STAGE_DATA.

Just remember, that we only need to segregate tables and indexes from a management perspective, not for performance:

http://www.dba-oracle.com/t_segregate_tabl..._data_files.htm

I found this:

http://www.databasedesign-resource.com/ora...partitions.html

"You must rebuild these index partitions after issuing the ALTER TABLE SPLIT PARTITION statement."

You could just rebuild the indexes, right?

"alter index XCHANGE_IDX1 rebuild tablespace STAGE_IDX;"
Go to the top of the page
 
+Quote Post
burleson
post Nov 12 2007, 06:49 AM
Post #5


Advanced Member
***

Group: Members
Posts: 11,401
Joined: 26-January 04
Member No.: 13



Metalink note 199623.1 says that unless you specify UPDATE GLOBAL INDEXES, any global indexes, or all partitions of partitioned global indexes, are marked UNUSABLE and must be rebuilt.

https://metalink.oracle.com/metalink/plsql/...1,1,1,helvetica

I would just rebuild the indexes, specifying the proper tablespace . . . .


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd August 2014 - 12:17 PM