This has to be looked at in the context of what else is happening around this table in the application. What are the average volumes per index compared to the over all table. Any details around blocks accessed (data distribution for indexes) At a high level, have you looked at Materialized views -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Orlando L Sent: Monday, October 03, 2011 7:14 PM To: oracle-l@xxxxxxxxxxxxx Subject: Performance bad with and without index All, We have a query that runs slow on a 10g database for certain set of values only. After getting complaints from user, we investigated it. the query was selecting rows from a table with 10 million rows, but without index. so we added an index. If the query uses index it becomes slower or gives about the same response time. the reason is because for the set of values the users complain about there are more than 50,000 rows: PRD2> select count(*) from VOUCHER_LINE; COUNT(*) -------------------- 9,894,236 PRD2> select count(*), INV_ITEM_ID 2 from VOUCHER_LINE 3 where INV_ITEM_ID in ('3260', '3250', '3255' ) 4 group by INV_ITEM_ID; COUNT(*) INV_ITEM_ID -------------------- ------------------ 54,882 3255 72,522 3250 66,574 3260 The time taken is anywhere from 9 minutes to 14 minutes. Can anyone suggest an idea to speed up this query. Orlando. -- //www.freelists.org/webpage/oracle-l Please visit our website at http://financialservicesinc.ubs.com/wealth/E-maildisclaimer.html for important disclosures and information about our e-mail policies. For your protection, please do not transmit orders or instructions by e-mail or include account numbers, Social Security numbers, credit card numbers, passwords, or other personal information. -- //www.freelists.org/webpage/oracle-l