Normal Table vs Clustered hash Tables
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