-
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|