Home » RDBMS Server » Performance Tuning » tuning needed for a query
tuning needed for a query [message #227070] Tue, 27 March 2007 04:34 Go to next message
Messages: 48
Registered: February 2007
I have 2 tables named X nad Y.Table X is having 12 columns out of which CUST_ID and MONTH_CODE are two fields.Table X is partitioned on the basis of MONTH_CODE.This table is not indexed on CUST_ID.Table X contains 2 months of data.But we are concerned about the latest month data.Similarly table Y contains 6 fields out of which 2 main fields are CUST_ID and CLIENT_ID.Table Y is indexed on CUST_ID.Table X is having 67 million records for latest month and table Y is filtered on the basis of a particular CLIENT_ID(say ABC is the CLIENT_ID).After filteraion Y is having 3 lakhs records.And both the tables are joined with CUST_ID as key and the join is inner join.My SQL for this purpose is.........

(select * from X where MONTH_CODE=200703) A
(select * from Y where CLIENT_ID='ABC') B

This query is taking a lot of time to give an out put of 67 million records.Please tune this query .As of now there is no plans to create index on CUST_ID on table X.
Re: tuning needed for a query [message #227074 is a reply to message #227070] Tue, 27 March 2007 04:44 Go to previous messageGo to next message
Messages: 611
Registered: July 2006
Senior Member
Post explain.
Re: tuning needed for a query [message #227084 is a reply to message #227070] Tue, 27 March 2007 05:26 Go to previous messageGo to next message
Messages: 15
Registered: April 2005
Location: Chennai, India
Junior Member

Create Index on Cust_Id for Table A

Also post the explain plan.
Re: tuning needed for a query [message #227146 is a reply to message #227084] Tue, 27 March 2007 07:48 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You might have trouble tuning this one. The best option would be to hash sub-partition X on CUST_ID and partition Y on CUST_ID. Reasoning is explained in this article.

The slower alternative is to create an index on X that begins with CUST_ID and includes every column referenced in the query.

Ross Leishman
Previous Topic: list all active connections with sql statement used?
Next Topic: Slow Query Execuation
Goto Forum:

Current Time: Fri Oct 21 06:28:42 CDT 2016

Total time taken to generate the page: 0.26998 seconds