partition indexes

I am trying to create a local index on a partitioned table.
Is there any way that I can specify the storage for the index creation?

Thanks.

you have to do this first

ALTER INDEX XXXX
MODIFY DEFAULT ATTRIBUTES storage(etc etc )

hi there,

yes, you can specify storage parameters for a local index creation. for example, on a range partitioned table (EMP), you can create a local index (EMPX1) with the following sql:

CREATE INDEX empx1 ON emp (empno)
LOCAL
PCTFREE 5
STORAGE(INITIAL 8M NEXT 1M PCTINCREASE 0)
TABLESPACE empx_ts NOLOGGING PARALLEL;

please note, however, that any storage parameters specified are for EACH partition and not for whole table. e.g.:

table EMP is range partitioned by column YEARMO. the current existing partitions are:

- emp_199901
- emp_199902
- emp_199903

so the aforementioned CREATE INDEX script will create a local index with an INITIAL 8M size for EACH of the EMP partitions. so in this example, there will be 3 local 8MB indexes created, for a total size of 24MB

but if table EMP is composite partitioned. e.g:

table EMP is range partitioned by YEARMO and then hash partitioned by SSN:

(range)...emp_199901*
.....(hash)....emp_199901_a
..................emp_199901_b
..................emp_199901_c
(range) emp_199902
.....(hash)....emp_199902_a
..................emp_199902_b
..................emp_199902_c
(range) emp_199903
.....(hash)....emp_199903_a
..................emp_199903_b
..................emp_199903_c

* Please ignore all those annoying dots. i did that just to preserve the formatting.

now the aforementioned CREATE INDEX script will create 9 8MB local indexes!! one for each subpartition for a total of 72MB!

so just be careful when specifying storage parameters for a local index on a partitioned table.

have fun :)

nick

That is exactly what I want to know. Thanks.

But I have a question on how to manager the partitioned table.

For example EMP is partitioned by range ( START_DATE). There are four partitions:
emp_100101 less than ( to_date('10/01/2001','mm/dd/yyyy')
emp_010102 less than ( to_date('01/01/2002','mm/dd/yyyy')
emp_040102 less than ( to_date('04/01/2002','mm/dd/yyyy')
emp_070102 less than ( to_date('07/01/2002','mm/dd/yyyy')

Then I must keep an eye on this table. If the new partition is not added after 07/01/2002 then I will get a problem. How could we easily do this? If I create a scheduled job the problem is:
Every time when the new partition is added we must specify the default tablespace for the local indexes otherwise they will be created on the same tablespace as table.

So far I did not find a good way to manage the partitioned tables & indexes. Any idea is welcome.

Thanks.

Oracle9i has inbuilt solution for this, till then u have to write
your own pricedure.

hi,

well, first of all, my partitioned tables always have an extra partition at the end, an Error Partition. this Error Partition has a list value of MAXVALUE. so modifying your example a little and showing the ddl:

CREATE TABLE emp
(
..empno.......VARCHAR2(10),
..gender.......VARCHAR2(1),
..name.........VARCHAR2(40),
..location......VARCHAR2(2),
..start_date...DATE
)
PARALLEL TABLESPACE emp_ts
PARTITION BY RANGE (start_date)
(PARTITION emp_100101 VALUES LESS THAN ( TO_DATE('10/01/2001','mm/dd/yyyy')) PCTFREE 5 PCTUSED 75
..STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE emp_ts NOLOGGING,
.PARTITION emp_010102 VALUES LESS THAN ( TO_DATE('01/01/2002','mm/dd/yyyy')) PCTFREE 5 PCTUSED 75
..STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE emp_ts NOLOGGING,
.PARTITION emp_040102 VALUES LESS THAN ( TO_DATE('04/01/2002','mm/dd/yyyy')) PCTFREE 5 PCTUSED 75
..STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE emp_ts NOLOGGING,
.PARTITION emp_070102 VALUES LESS THAN ( TO_DATE('07/01/2002','mm/dd/yyyy')) PCTFREE 5 PCTUSED 75
..STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE emp_ts NOLOGGING,
.PARTITION emp_err VALUES LESS THAN (MAXVALUE) PCTFREE 5 PCTUSED 75
..STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0) TABLESPACE emp_ts NOLOGGING);

-- Remember that your partition list values are non-inclusive, meaning that the list value itself is not included in the partition!

as you can probably guess, all rows with a start date of equal to or greater than 07/01/2001 will always go into the Error Partition EMP_ERR. this setup makes it easy to check for invalid data as you can simply select from the emp_err partition:

SELECT COUNT(ROWID) FROM emp PARTITION (emp_err);

this setup also has some other administrative benefits as i will explain shortly.


the local indexes created for this partitioned table EMP could look like this:

CREATE INDEX empx1 ON emp (empno)
LOCAL
PCTFREE 5
STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)
TABLESPACE empx_ts NOLOGGING PARALLEL;

CREATE BITMAP INDEX empx2 ON emp (gender)
LOCAL
PCTFREE 5
STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)
TABLESPACE empx_ts NOLOGGING PARALLEL;

CREATE INDEX empx3 ON emp (start_date)
LOCAL
PCTFREE 5
STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0)
TABLESPACE empx_ts NOLOGGING PARALLEL;


so now it looks like we've got everything up and running. but not for long... there will most likely come a day that is beyond 07/01/2002 and it will be necessary to contain this data. this involves creating a new partition. this is where that other administrative benefit comes into play. instead of ADDING a partition, i simply SPLIT the Error Partition. :

ALTER TABLE emp SPLIT PARTITION emp_err AT (TO_DATE('10/01/2002','mm/dd/yyyy')) INTO
(PARTITION emp_100102 TABLESPACE emp_ts
..STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0) LOGGING,
.PARTITION emp_err);

-- for me, this is a scheduled PL/SQL task.

also by adding (more accurately 'splitting') this partition, we've also split the local indexes. there's no need to create an index for this partition, as the local indexes will automatically be applied. also, you do not need to specify a tablespace for these new indexes since they will inherit the tablespaces that were specified at their creation time.

i hope this answers your question and helps you out.

have fun :)

nick

Quote:

---

Originally posted by wfy
Every time when the new partition is added we must specify the default tablespace for the local indexes otherwise they will be created on the same tablespace as table.

---

well if you add partitions you have to change the DEFAULT index attribute with the command I mentioned in previous post, after the change you add the partition the new local index will be created in the tablespace you specify for the default attribute, if it's not adding but splitting the partitions you probably have to do the same, change the attribute (splitting I am not sure, havent done it yet but adding definitely works)

hi,

pando is correct. if you want to specify NEW attributes for any new partitions for a local index, you have to use the ALTER INDEX MODIFY DEFAULT ATTRIBUTES ddl.

ALTER INDEX empx3 MODIFY DEFAULT ATTRIBUTES
STORAGE ( NEXT 100K ) TABLESPACE empx2_ts;

after that, when you add or split a partitioned table, the local indexes that are subsequently created will implement those new attributes.

nick :)

I found a problem. I could not specify the storage when I create the local index. Any idea?


Thanks.

hi wfy,

you'll have to be more specific. can you provide more details and maybe the ddl for the table and indexes?

nick

Thanks all for the response.

The script to create index is:

CREATE INDEX empx1 ON emp (empno)
LOCAL
STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0)
( PARTITION emp_idex_1 TABLESPACE INDEX_1,
PARTITION emp_index_2 TABLESPACE INDEX_2,
..... );

hi wfy,

well, believe it or not, that statement should work if you simply switch the lines with LOCAL and your STORAGE parameters; LOCAL has to be specified IMMEDIATELY BEFORE your specific partition attributes...no default parameters can be specified between LOCAL and your specific partition attributes.

anyway, i went ahead and typed out a case scenario for you. hopefully, this will clear up any confusion and problems you might encounter.

-- Step 1: Create the initial partitioned table
CREATE TABLE emp
(
empno VARCHAR2(10),
gender VARCHAR2(1),
name VARCHAR2(40),
location VARCHAR2(2),
start_date DATE
)
PARALLEL TABLESPACE emp1_ts
PARTITION BY RANGE (start_date)
(PARTITION emp_100101 VALUES LESS THAN ( TO_DATE('10/01/2001','mm/dd/yyyy')) PCTFREE 5 PCTUSED 75
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0) TABLESPACE emp1_ts NOLOGGING,
PARTITION emp_010102 VALUES LESS THAN ( TO_DATE('01/01/2002','mm/dd/yyyy')) PCTFREE 5 PCTUSED 75
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0) TABLESPACE emp2_ts NOLOGGING,
PARTITION emp_040102 VALUES LESS THAN ( TO_DATE('04/01/2002','mm/dd/yyyy')) PCTFREE 5 PCTUSED 75
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0) TABLESPACE emp1_ts NOLOGGING,
PARTITION emp_070102 VALUES LESS THAN ( TO_DATE('07/01/2002','mm/dd/yyyy')) PCTFREE 5 PCTUSED 75
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0) TABLESPACE emp2_ts NOLOGGING,
PARTITION emp_err VALUES LESS THAN (MAXVALUE) PCTFREE 5 PCTUSED 75
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0) TABLESPACE emp1_ts NOLOGGING);

-- Shows you the partitions and tablespaces
-- for the table just created
SELECT partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'EMP'
ORDER BY partition_name;

-- Step 2: Create the indexes for our partitioned table
--
-- Note that for this first index EMPX1, we have to
-- specify each partition to be indexed. This is the
-- only way (that i know of) to specify the tablespaces
-- for your partitioned indexes at creation time.
-- Also, note that the name of each index partition is the
-- same as its corresponding table's partition. this is fine.
-- This is actually the default name for index partitions
-- if you don't specify specific attributes for each partition
-- as I did earlier in this forum thread.
CREATE INDEX empx1 ON emp (empno)
STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0) -- THESE ARE DEFAULT PARAMETERS!
NOLOGGING PARALLEL -- ONLY DEFAULT PARAMETERS!
TABLESPACE empx1_ts -- AGAIN, ONLY DEFAULT PARAMETERS!
LOCAL
( PARTITION emp_100101 TABLESPACE empx1_ts PCTFREE 5
STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0),
PARTITION emp_010102 TABLESPACE empx2_ts PCTFREE 5
STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0),
PARTITION emp_040102 TABLESPACE empx1_ts PCTFREE 5
STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0),
PARTITION emp_070102 TABLESPACE empx2_ts PCTFREE 5
STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0),
PARTITION emp_err TABLESPACE empx1_ts PCTFREE 5
STORAGE (INITIAL 16K NEXT 16K PCTINCREASE 0));

-- Or you could alternately have created empx1
-- the way you specified in your last forum post
--
--CREATE INDEX empx1 ON emp (empno)
--STORAGE ( INITIAL 1M NEXT 1M PCTINCREASE 0)
--LOCAL
--( PARTITION emp_100101 TABLESPACE empx1_ts,
--PARTITION emp_010102 TABLESPACE empx2_ts,
--PARTITION emp_040102 TABLESPACE empx1_ts,
--PARTITION emp_070102 TABLESPACE empx2_ts);
--PARTITION emp_100102 TABLESPACE empx1_ts);

-- Create another index
CREATE BITMAP INDEX empx2 ON emp (gender)
LOCAL
PCTFREE 5
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0)
TABLESPACE empx1_ts NOLOGGING PARALLEL;

-- Create another index
CREATE INDEX empx3 ON emp (start_date)
LOCAL
PCTFREE 5
STORAGE(INITIAL 16K NEXT 16K PCTINCREASE 0)
TABLESPACE empx2_ts NOLOGGING PARALLEL;

-- Shows us partitions and corresponding tablespace
-- for all 3 our indexes just created
SELECT index_name, partition_name, tablespace_name
FROM user_ind_partitions
WHERE index_name in ('EMPX1','EMPX2','EMPX3')
ORDER BY index_name, partition_name;

-- Okay! Now everything's up and working! Let's
-- go through a Fiscal Quarter End and build the
-- new partitions and indexes for our next
-- Fiscal Quarter.

-- Step 3: Alter the default attributes
-- of our indexes so that when we build the
-- new table partition, the new index partition
-- goes where we want it.
ALTER INDEX empx1 MODIFY DEFAULT ATTRIBUTES
STORAGE ( NEXT 100K ) TABLESPACE empx2_ts;

-- Step 4: Split the table's Error Partition
-- to get a new table partition
--
-- Note that a new index partition will also be built
-- automatically using its default attributes (which
-- we just modified in the last step
ALTER TABLE emp SPLIT PARTITION emp_err AT (TO_DATE('10/01/2002','mm/dd/yyyy')) INTO
(PARTITION emp_100102 TABLESPACE emp1_ts
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) LOGGING,
PARTITION emp_err);

-- Now let's check to confirm that the new
-- table partition went where we wanted it
-- to go...into the EMP1_TS Tablespace
SELECT partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'EMP'
ORDER BY partition_name;

-- Okay that worked. Now let's confirm that
-- the new index partition was built into
-- the correct tablespace, EMPX2_TS
SELECT partition_name, tablespace_name
FROM user_ind_partitions
WHERE index_name = 'EMPX1'
ORDER BY partition_name;

-- EVERYTHING CHECKS OUT!!!
-- But let's do it again for the next Fiscal
-- Quarter, just to make sure.

ALTER INDEX empx1 MODIFY DEFAULT ATTRIBUTES
STORAGE ( NEXT 100K ) TABLESPACE empx1_ts;

ALTER TABLE emp SPLIT PARTITION emp_err AT (TO_DATE('01/01/2003','mm/dd/yyyy')) INTO
(PARTITION emp_010103 TABLESPACE emp2_ts
STORAGE ( INITIAL 16K NEXT 16K PCTINCREASE 0) LOGGING,
PARTITION emp_err);

-- Check new table partition
SELECT partition_name, tablespace_name
FROM user_tab_partitions
WHERE table_name = 'EMP'
ORDER BY partition_name;

-- Check new index partition
SELECT partition_name, tablespace_name
FROM user_ind_partitions
WHERE index_name = 'EMPX1'
ORDER BY partition_name;

well, hopefully everything checks out. please let me know if you need further examples or explanations with anything.

have fun :)

nick

Hi, nratanasin:

That is excellent. Thanks sooo much!

Happy Thanksgiving.