select source, channel, count(*) totalDups from DUP_PAYMENT.DETAILS as details, DUP_PAYMENT.DUPLICATE as duplicate where details.sys_id = duplicate.sys_id and details.sys_entered_date between '2012-10-10 00:00:00.0' AND '2012-10-19 00:00:00.0' and duplicate.sys_dup = 'Y' and details.SYS_DELETED IS NULL group by source, channel with UR.
Database have 7,50,000 Rows.
Index - 2 used for SYS_ID,sys_entered_date,SYS_DUP column.
Tables - 2 tables
Above query is taking 18-20 sec for executing.
Can you please tell me why its taking so much time and how can i optimize above query for reducing time required for execution.?
Have you looked at the explain plan to see if the indexes are being used ?
Where did you get the info about the execution time ? On the database server ?
If you have done on a client, you should also see if there is a bottleneck in rendering the data (if using a GUI tool) or even network.
Your query will be doing sorting - do you know how good the clustering is on source and channel columns.
Do you have table stats up to date ?
BTW, you cannot tune a by posting on an internet forum , unless you have a pretty good grasp of query tuning already and are using the forum for more ideas.
Visit the new-look IDUG Website , register to gain access to the excellent content.