Performance bad with and without index
- From: Orlando L <oralrnr@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 3 Oct 2011 18:14:02 -0500
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
Other related posts:
