DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: partition indexes

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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you have to do this first

    ALTER INDEX XXXX
    MODIFY DEFAULT ATTRIBUTES storage(etc etc )


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











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

  5. #5
    Join Date
    Feb 2001
    Posts
    389
    Oracle9i has inbuilt solution for this, till then u have to write
    your own pricedure.

  6. #6
    Join Date
    Nov 2001
    Posts
    1
    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


  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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)

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

  9. #9
    Join Date
    Sep 2001
    Posts
    14
    I found a problem. I could not specify the storage when I create the local index. Any idea?


    Thanks.

  10. #10
    Join Date
    Nov 2001
    Posts
    15
    hi wfy,

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

    nick

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