RE: Performance bad with and without index
Date: Mon, 3 Oct 2011 19:45:25 -0400
Message-ID: <6AFC12B9BFCDEA45B7274C534738067F77468981_at_AAPQMAILBX02V.proque.st>
You might want to provide us the execution plan and the DDL of the index, if you want us to take a closer look.
-Mark
From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] On Behalf Of Orlando L [oralrnr_at_gmail.com] Sent: Monday, October 03, 2011 16:14
To: oracle-l_at_freelists.org
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 Mon Oct 03 2011 - 18:45:25 CDT