RE: Performance bad with and without index
Date: Tue, 4 Oct 2011 17:05:17 +0300
Message-ID: <OFC31E31D5.E9F4D940-ONC225791F.004D5150-C225791F.004D63E5_at_seb.lt>
p.s. a fat index could help.
Please consider the environment before printing this e-mail
From: <rajendra.pande_at_ubs.com>
To: <oralrnr_at_gmail.com>, <oracle-l_at_freelists.org>
Date: 2011.10.04 16:11
Subject: RE: Performance bad with and without index
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Orlando L
Sent: Monday, October 03, 2011 7:14 PM
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
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.
--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l
Received on Tue Oct 04 2011 - 09:05:17 CDT