Printable View
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
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_NAME, UTI)
TABLESPACE USERS
INITRANS 2
MAXTRANS 255
GLOBAL PARTITION BY RANGE (PART_NAME, UTI) (
PARTITION IDX_P1 VALUES LESS THAN (600, 1000)
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 (1300, 2000)
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 (2000, 3000)
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 (MAXVALUE, MAXVALUE)
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;