need help on partition
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: need help on partition

  1. #1
    Join Date
    Jan 2001
    Posts
    230
    I am in process of creating partition table based on range.

    If the key on the partition table is different then LOCAL Indexes, will it impact on performance?
    i.e.
    CREATE TABLE TEST
    (c1 number,
    c2 varchar2(20),
    c3 varchar2(10),
    c4 varchar2(10))
    PARALLEL(DEGREE 4 INSTANCES 1)
    PARTITION by range (c1,c2)
    (
    PARTITION T1 VALUES LESS THAN (100,A)
    STORAGE(INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    FREELISTS 1
    FREELIST GROUPS 1)
    LOGGING,

    )
    CREATE INDEX TEST_IND on TEST(c1,c3,c2)
    LOCAL (PARTITION T1
    STORAGE(INITIAL 1M
    NEXT 1M
    MINEXTENTS 1
    MAXEXTENTS UNLIMITED
    FREELISTS 1
    FREELIST GROUPS 1)
    TABLESPACE TSIND LOGGING,

    )
    If table is created with PARALLEL DEGREE and INDEX is not, what will happen?

    Thanks.


  2. #2
    Join Date
    Feb 2001
    Posts
    180
    I am not fully sure,
    but to me it seems it will not have a serious negative impact on the performance.
    But to be fast it needs the fields c1, c2
    in any query to access only 1 partition.
    When using only the fields c1, c3 the index is used, but not a direct access to to the right partition.

    Regards
    Ben de Boer

  3. #3
    Join Date
    Apr 2002
    Posts
    291
    Definitely there will be a difference in performance. If you have both the partition key and the index keys are same, then definitely you enjoy the better performance.
    PNRDBA

  4. #4
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    For OLTP applications: Global indexes and local prefixed indexes are beffer than nonunique local indexes because they minimize the number of index partition probes.

    For DSS applications: Local non-prefixed indexes are ideal for DSS systems where you need to partition by a date range but want to query often on another column.
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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