Home » RDBMS Server » Server Utilities » Partitioning the table
Partitioning the table [message #250064] Sat, 07 July 2007 09:31 Go to next message
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 #250068 is a reply to message #250064] Sat, 07 July 2007 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Then,
If you know how to partition a table I don't see why you can't know how to partition an index.
Maybe reading SQL Reference would help you: CREATE INDEX

Regards
Michel
Re: Partitioning the table [message #250071 is a reply to message #250068] Sat, 07 July 2007 10:30 Go to previous messageGo to next message
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
Re: Partitioning the table [message #250077 is a reply to message #250071] Sat, 07 July 2007 11:08 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read constraint page of SQL Reference.

Regards
Michel
Previous Topic: SQL Loader - problem loading data from csv files
Next Topic: About Tablespce not found
Goto Forum:
  


Current Time: Fri Dec 09 15:42:25 CST 2016

Total time taken to generate the page: 0.22367 seconds