Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Normal Table vs Clustered hash Tables

Tomy3k_BakrMay 4 2008 — edited May 4 2008
Hi All,
I have a situation here and i need your help with me to correct my decision.
I have one table that will be used for frequent DML (Insert and Update) and SELECT. I want to make a comparison between creating this table as a Normal Table and Cluster version of it.
The Normal table structure is as follows:
CREATE TABLE OMS_ORDER
(SL_NO NUMBER NOT NULL,
ORDER_SIDE VARCHAR2(1),
MARKET_SL_NO VARCHAR2(15),
ORDER_DATE DATE,
NIN VARCHAR2(60),
C_ACCOUNT VARCHAR2(60),
COMPANY_CODE VARCHAR2(10),
VOLUME NUMBER NOT NULL,
ACTUAL_VOLUME NUMBER DEFAULT 0,
REMAIN_VOLUME NUMBER DEFAULT null,
EXECUTED_VOLUME NUMBER DEFAULT 0,
ORDER_TYPE VARCHAR2(1) NOT NULL,
ORDER_PRICE NUMBER,
AVG_PRICE NUMBER DEFAULT 0,
ACTUAL_PRICE NUMBER DEFAULT 0,
ORDER_STATUS NUMBER,
ORDER_SOURCE VARCHAR2(10),
REF_SL_NO NUMBER,
MARKET VARCHAR2(4) DEFAULT 'QA',
VALIDITY VARCHAR2(1) NOT NULL,
MAX_FIX_SEQ NUMBER DEFAULT 0)
/


-- Constraints for OMS_ORDER

ALTER TABLE OMS_ORDER
ADD CONSTRAINT ORDER_PRIM_SLNO PRIMARY KEY (SL_NO)
USING INDEX
/

The Cluster version DDL is as follows:
CREATE CLUSTER OMS_ORD_CLS (
SL_NO NUMBER(22,0))
SIZE 72
SINGLE TABLE
HASHKEYS 5000011
/

CREATE TABLE OMS_ORDER_CLS
(SL_NO NUMBER(22,0),
ORDER_SIDE VARCHAR2(1),
MARKET_SL_NO VARCHAR2(15),
ORDER_DATE DATE,
NIN VARCHAR2(60),
C_ACCOUNT VARCHAR2(60),
COMPANY_CODE VARCHAR2(10),
VOLUME NUMBER NOT NULL,
ACTUAL_VOLUME NUMBER DEFAULT 0,
REMAIN_VOLUME NUMBER DEFAULT null,
EXECUTED_VOLUME NUMBER DEFAULT 0,
ORDER_TYPE VARCHAR2(1) NOT NULL,
ORDER_PRICE NUMBER,
AVG_PRICE NUMBER DEFAULT 0,
ACTUAL_PRICE NUMBER DEFAULT 0,
ORDER_STATUS NUMBER,
ORDER_SOURCE VARCHAR2(10),
REF_SL_NO NUMBER,
MARKET VARCHAR2(4) DEFAULT 'QA',
VALIDITY VARCHAR2(1) NOT NULL,
MAX_FIX_SEQ NUMBER DEFAULT 0)
CLUSTER OMS_ORD_CLS(SL_NO)
/


Which will be better using a normal table or a cluster one?
A little note The PK is SL_NO wich is a unique number and all the queries will be like this
SELECT * FROM OMS_ORDER_XXX WHERE SL_NO = XXXXXX;

Please Help Me.

Thanks in advance

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 1 2008
Added on May 4 2008
10 comments
6,865 views