Hi,
I have been given to optimize the indexes in a database.
There are some tables where I found there are about 6-8 columns as a composite primary key and all of them in clustered indexe. The tables are having around 10 millions data and are high on DML operations.
The table script is as below :-
CREATE TABLE ACDENODETAILS
(
CUSTCUSTOMERCODE VARCHAR2(12) NOT NULL,
ACCOUNTID VARCHAR2(16) NOT NULL,
DNDENO VARCHAR2(10) NOT NULL,
DNNOTES VARCHAR2(10) NOT NULL,
COMPCODE VARCHAR2(10) NOT NULL,
GENDATE DATE NOT NULL,
ACTDATE DATE NOT NULL,
CLCALLNO NUMBER(5),
CLOFFCD VARCHAR2(6),
CLCUSTCD VARCHAR2(6),
CLCUSTBRCD VARCHAR2(6),
CLACTCD CHAR(1),
USERID VARCHAR2(6),
TSACDENODETAILS FLOAT(126),
DEPOSLIPNO VARCHAR2(20),
CUSTREFDETAILS1 VARCHAR2(20),
CUSTREFDETAILS2 VARCHAR2(20)
)
CREATE UNIQUE INDEX PK_ACDENODETAILS ON ACDENODETAILS
(CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE,
GENDATE, ACTDATE)
ALTER TABLE ACDENODETAILS ADD (
CONSTRAINT PK_ACDENODETAILS
PRIMARY KEY
(CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE, ACTDATE))
Insert statements:-
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000012', '123456', '10-0', '20', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2028, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5768287037, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000012', '123456', '100-0', '30', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2028, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5768287037, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000012', '123456', '1000-0', '40', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2028, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5768287037, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000012', '123456', '20-0', '90', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2028, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5768287037, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000012', '123456', '50-0', '100', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2028, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5768287037, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000012', '123456', '500-0', '100', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2028, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5768287037, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000014', 'ACCOUNT1', '100-1', '50', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2030, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5937268519, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000014', 'ACCOUNT1', '1000-1', '10', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2030, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5937268519, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000014', 'ACCOUNT1', '500-1', '10', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2030, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5937268519, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000014', 'ACCOUNT2', '1000-1', '1', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2030, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5937268519, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000014', 'ACCOUNT2', '500-1', '38', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2030, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5937268519, NULL, NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000019', 'ACCOUNT191', '1000-1', '10', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2034, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5971527778, '12345', NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000019', 'ACCOUNT191', '500-1', '2', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2034, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5971527778, '12345', NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000019', 'ACCOUNT192', '100-1', '10', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2034, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5971527778, '45612', NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000019', 'ACCOUNT192', '1000-1', '15', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2034, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5971527778, '45612', NULL, NULL);
INSERT INTO ACDENODETAILS ( CUSTCUSTOMERCODE, ACCOUNTID, DNDENO, DNNOTES, COMPCODE, GENDATE,
ACTDATE, CLCALLNO, CLOFFCD, CLCUSTCD, CLCUSTBRCD, CLACTCD, USERID, TSACDENODETAILS, DEPOSLIPNO,
CUSTREFDETAILS1, CUSTREFDETAILS2 ) VALUES (
'ABC011000019', 'ACCOUNT192', '500-1', '6', 'CSL', TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, TO_Date( '09/03/2009 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 2034, '011', 'A00003'
, 'ABC011', 'I', 'HO0011', 40059.5971527778, '45612', NULL, NULL);
Some of them might look unique but I looked into and found they together make a unique record.
The records are such that a unique record is created on combination of the above columns. So the unique index is created and hence the primary key. The queries are performing slow so I have been given the task to look into the indexes but I am not sure what to do with them when they make a combination for the uniqueness.
Please help me on this as Clustered indexes shouldn't be on so many columns. It might be adding on the slowness...but not sure what to do when the constraint for uniqueness is also required.
Please advice.
Mahi
[Updated on: Sun, 04 October 2009 23:36] Report message to a moderator |
] 
Blog
Search
Help
Members
Register
Login
Home
