Home » RDBMS Server » Server Utilities » Partitioning the table
| Partitioning the table [message #250064] |
Sat, 07 July 2007 09:31  |
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
|
Member |
|
|
I need to partition this table, Partition on tradedate 1 month per partition. date starts from 01/01/2006. I know the syntax for partitioning the table. Is anybody share me the synatax how to partition the index, and the syntax for the primary key will be the same or i need to do some changes there too.
Thanks in advance
CREATE TABLE table1
(
FACEALLOCATIONID NUMBER NOT NULL,
TRADEDATE DATE NOT NULL,
DELETESTATE CHAR(1 BYTE),
UPDATESYSTEMTIME DATE,
UPDATEDBTIME DATE,
DELETESYSTEMTIME DATE,
DELETEDBTIME DATE,
OMSYSTEMID CHAR(2 BYTE) NOT NULL,
ALLOCATIONSOURCE CHAR(2 BYTE),
ROUTEINFO1 VARCHAR2(12 BYTE),
ROUTEINFO2 VARCHAR2(12 BYTE),
REPORTNO CHAR(5 BYTE),
BOOKINGORDERID CHAR(22 BYTE),
FIRSTCLIENTORDERID CHAR(22 BYTE),
EXECUTIONID CHAR(22 BYTE),
ALLOCATEDQTY NUMBER(21,7),
AVGPX NUMBER(21,7),
PARENTID CHAR(22 BYTE),
SALESMANCODE CHAR(10 BYTE),
CLIENTNAME VARCHAR2(128 BYTE),
OFFICECODE CHAR(12 BYTE) NOT NULL,
ACCOUNTNO CHAR(12 BYTE) NOT NULL,
RISKCLASS VARCHAR2(128 BYTE),
STRATEGY VARCHAR2(128 BYTE),
ARBITRAGECODE CHAR(1 BYTE),
CATEGORY3 VARCHAR2(128 BYTE),
SETTLEMENTCODE CHAR(2 BYTE),
BROKEROFFICECODE CHAR(12 BYTE),
BROKERACCOUNTNO CHAR(12 BYTE),
SETTLEMENTDATE DATE,
TAXCODE CHAR(2 BYTE),
TAXAMOUNT NUMBER(21,7),
ACCOUNTTYPE CHAR(1 BYTE),
GROSSNETTYPE CHAR(1 BYTE),
OPPOSITEMEMBERCODE CHAR(10 BYTE),
COMMISSION NUMBER(21,7),
PURGE CHAR(1 BYTE),
POSITIONID NUMBER(10),
COMMTYPE CHAR(1 BYTE),
COMMBP NUMBER(21,7),
COMMDISCRATE NUMBER(21,7),
COMMTBL CHAR(5 BYTE),
COMMPATTERN CHAR(1 BYTE),
PROGRAMCODE CHAR(10 BYTE),
TICKETMEMO VARCHAR2(255 BYTE),
SOURCEFILENAME VARCHAR2(32 BYTE),
ROUTEFROM CHAR(50 BYTE),
ROUTEACK CHAR(50 BYTE),
ROUTEACKTIM VARCHAR2(100 BYTE)
)
TABLESPACE LC_FEED_DATA
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
CREATE INDEX JP_FACE_TDALLOC_IDX2 ON table1
(TRADEDATE, OMSYSTEMID, EXECUTIONID)
LOGGING
TABLESPACE LC_FEED_IDX
NOPARALLEL;
CREATE INDEX JP_FACE_TDALLOC_IDX1 ON table1
(TRADEDATE, FIRSTCLIENTORDERID)
LOGGING
TABLESPACE LC_FEED_IDX
NOPARALLEL;
ALTER TABLE table1 ALLOCATION ADD (
PRIMARY KEY
(FACEALLOCATIONID, TRADEDATE)
USING INDEX
TABLESPACE LC_FEED_IDX);
GRANT DELETE, INSERT, SELECT, UPDATE ON table1 TO LC_ACT_CM27;
GRANT SELECT ON table1 TO LC_BOBJ;
GRANT DELETE, INSERT, SELECT, UPDATE ON table1 TO LC_MONITOR;
[Updated on: Sat, 07 July 2007 09:32] Report message to a moderator |
|
|
|
|
|
| Re: Partitioning the table [message #250071 is a reply to message #250068] |
Sat, 07 July 2007 10:30  |
qasim845
Messages: 95
Registered: March 2007
Location: Philadelphia
|
Member |
|
|
Thanks Michel. I found the syntax for partitioning the indexes. It will be very nice of you if you can give the answer for this thing. the syntax for primary key will be the same or i have to change it during partitioning the table ?
ALTER TABLE table1 ALLOCATION ADD (
PRIMARY KEY
(FACEALLOCATIONID, TRADEDATE)
USING INDEX
TABLESPACE LC_FEED_IDX);
Much appreciated
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Jun 26 14:05:39 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
