Home » RDBMS Server » Performance Tuning » Too many columns in one index (Oracle 10g)
Too many columns in one index [message #424623] Sun, 04 October 2009 23:30 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
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

Re: Too many columns in one index [message #424628 is a reply to message #424623] Sun, 04 October 2009 23:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> It might be adding on the slowness.
Or slowness might result from skewed data or other cause.

Without proof, only idle speculation.


Re: Too many columns in one index [message #424630 is a reply to message #424628] Sun, 04 October 2009 23:53 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hey Blackswan,
Thanks for looking into this. This application is going slow since a long time...when there are more concurrent users. The queries are also getting optimized but can we do something for the indexes? Or we can have that number of columns in clustered index.

Re: Too many columns in one index [message #424633 is a reply to message #424630] Mon, 05 October 2009 00:40 Go to previous messageGo to next message
anurag.bishnoi01
Messages: 1
Registered: June 2008
Location: UK, Milton Keynes
Junior Member

Inserts and deletes can become an expensive affair in case of clustered index, as data needs to be shuffled to stored physically sorted becoz in case of clustered indexes, table data is physically stored in order of keys
Re: Too many columns in one index [message #424634 is a reply to message #424633] Mon, 05 October 2009 00:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
anurag.bishnoi01 wrote on Sun, 04 October 2009 22:40
Inserts and deletes can become an expensive affair in case of clustered index, as data needs to be shuffled to stored physically sorted becoz in case of clustered indexes, table data is physically stored in order of keys


Please provide reproducible test cases.
Re: Too many columns in one index [message #424703 is a reply to message #424623] Mon, 05 October 2009 07:26 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Can you post an Explain Plan for some of the queries that are running slowly?
Previous Topic: flush shared pool
Next Topic: hard parse for SQL (merged)
Goto Forum:
  


Current Time: Thu Dec 08 06:25:45 CST 2016

Total time taken to generate the page: 0.23094 seconds