| tuning needed for a query [message #227070] |
Tue, 27 March 2007 04:34  |
dr46014
Messages: 49
Registered: February 2007
|
Member |
|
|
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 A.CUST_IDA.MONTH_CODE,A.bla bla..,B.CLIENT_ID
from
(select * from X where MONTH_CODE=200703) A
,
(select * from Y where CLIENT_ID='ABC') B
where A.CUST_ID=B.CUST_ID
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 #227146 is a reply to message #227084] |
Tue, 27 March 2007 07:48  |
rleishman
Messages: 3728
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
