partition indexes - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: partition indexes

  1. #11
    Join Date
    Sep 2001
    Posts
    14
    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,
    ..... );



  2. #12
    Join Date
    Nov 2001
    Posts
    15
    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

  3. #13
    Join Date
    Sep 2001
    Posts
    14
    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
  •  


Click Here to Expand Forum to Full Width