Performance bad with and without index

From: Orlando L <oralrnr_at_gmail.com>
Date: Mon, 3 Oct 2011 18:14:02 -0500
Message-ID: <CAL8Ae76bTvqw9XjYvwV8X9WLM2y1aXT8_L-Af5sRqs8ByM_VyQ_at_mail.gmail.com>

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.

--

http://www.freelists.org/webpage/oracle-l Received on Mon Oct 03 2011 - 18:14:02 CDT