Need help on partition create script
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Need help on partition create script

  1. #1
    Join Date
    Jul 2000
    Location
    brewster,Newyork
    Posts
    87

    Question Need urgent help on partition create script

    I have an Index which has 70 mil rows in DSS. I was assigned to create a global partitions on the column (part_name)
    by range can somebody help with the query about how to create partitions.
    1. My Index is "CREATE INDEX IND_POR_DT_UTI ON
    PORT_VISION(PART_NAME, UTI);"
    2. And the data type of Varchar2(50) with 60 distinct values
    and data of part_name is like this"ISETSTMT,LOADER55,LINER55,PGMSTZVE43,KMULTSTRT21...)
    3. Oracle 9i
    Thanks & I appreciate your help
    Last edited by portal; 11-12-2004 at 11:48 AM.
    sat

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    This may help you:

    PHP Code:

    CREATE TABLE TAMIL
    .MY_TABLE
    (
      
    ID         INTEGER,
      
    PART_NAME  INTEGER,
      
    UTI        INTEGER
    )
    TABLESPACE USERS
    PCTUSED    40
    PCTFREE    10
    INITRANS   1
    MAXTRANS   255
    PARTITION BY RANGE 
    (ID
    (  
      
    PARTITION P1 VALUES LESS THAN (300)
        
    LOGGING
        NOCOMPRESS
        TABLESPACE USERS
        PCTUSED    40
        PCTFREE    10
        INITRANS   1
        MAXTRANS   255
        STORAGE    
    (
                    
    INITIAL          40K
                    NEXT             40K
                    MINEXTENTS       1
                    MAXEXTENTS       505
                    PCTINCREASE      50
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      
    DEFAULT
                   ),  
      
    PARTITION P2 VALUES LESS THAN (500)
        
    LOGGING
        NOCOMPRESS
        TABLESPACE USERS
        PCTUSED    40
        PCTFREE    10
        INITRANS   1
        MAXTRANS   255
        STORAGE    
    (
                    
    INITIAL          40K
                    NEXT             40K
                    MINEXTENTS       1
                    MAXEXTENTS       505
                    PCTINCREASE      50
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      
    DEFAULT
                   ),  
      
    PARTITION P3 VALUES LESS THAN (1200)
        
    LOGGING
        NOCOMPRESS
        TABLESPACE USERS
        PCTUSED    40
        PCTFREE    10
        INITRANS   1
        MAXTRANS   255
        STORAGE    
    (
                    
    INITIAL          40K
                    NEXT             40K
                    MINEXTENTS       1
                    MAXEXTENTS       505
                    PCTINCREASE      50
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      
    DEFAULT
                   )
    )
    NOCACHE
    NOPARALLEL
    ;


    CREATE INDEX TAMIL.MY_TABLE_GL_IDX ON TAMIL.MY_TABLE
    (PART_NAMEUTI)
      
    TABLESPACE USERS
      INITRANS   2
      MAXTRANS   255
    GLOBAL PARTITION BY RANGE (PART_NAMEUTI) (  
      
    PARTITION IDX_P1 VALUES LESS THAN (6001000)
        
    LOGGING
        NOCOMPRESS
        TABLESPACE USERS
        PCTFREE    10
        INITRANS   2
        MAXTRANS   255
        STORAGE    
    (
                    
    INITIAL          40K
                    NEXT             40K
                    MINEXTENTS       1
                    MAXEXTENTS       505
                    PCTINCREASE      50
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      
    DEFAULT
                   ),  
      
    PARTITION IDX_P2 VALUES LESS THAN (13002000)
        
    LOGGING
        NOCOMPRESS
        TABLESPACE USERS
        PCTFREE    10
        INITRANS   2
        MAXTRANS   255
        STORAGE    
    (
                    
    INITIAL          40K
                    NEXT             40K
                    MINEXTENTS       1
                    MAXEXTENTS       505
                    PCTINCREASE      50
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      
    DEFAULT
                   ),  
      
    PARTITION IDX_P3 VALUES LESS THAN (20003000)
        
    LOGGING
        NOCOMPRESS
        TABLESPACE USERS
        PCTFREE    10
        INITRANS   2
        MAXTRANS   255
        STORAGE    
    (
                    
    INITIAL          40K
                    NEXT             40K
                    MINEXTENTS       1
                    MAXEXTENTS       505
                    PCTINCREASE      50
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      
    DEFAULT
                   ),  
      
    PARTITION IDX_P4 VALUES LESS THAN (MAXVALUEMAXVALUE)
        
    LOGGING
        NOCOMPRESS
        TABLESPACE USERS
        PCTFREE    10
        INITRANS   2
        MAXTRANS   255
        STORAGE    
    (
                    
    INITIAL          40K
                    NEXT             40K
                    MINEXTENTS       1
                    MAXEXTENTS       505
                    PCTINCREASE      50
                    FREELISTS        1
                    FREELIST GROUPS  1
                    BUFFER_POOL      
    DEFAULT
                   )
    )
    NOPARALLEL

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