Re: Performance bad with and without index
Date: Tue, 4 Oct 2011 08:30:55 +0300
Message-ID: <OFB9EC8F20.405BC835-ONC225791F.001E36BE-C225791F.001E4C3D_at_seb.lt>
without going into IO, hardware - parallel option ?
Please consider the environment before printing this e-mail
From: Orlando L <oralrnr_at_gmail.com>
To: oracle-l_at_freelists.org
Date: 2011.10.04 02:14
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.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 04 2011 - 00:30:55 CDT