Re: Performance bad with and without index

From: Subodh Deshpande <deshpande.subodh_at_gmail.com>
Date: Tue, 4 Oct 2011 12:40:02 +0530
Message-ID: <CAJsOtB6_GGU_tuE39teR0d_hzMkUS_pv=jRwhQFNXCYfe4JnPQ_at_mail.gmail.com>

Hello,
as many others said explain plan will help..and is there any index on the below table having null values..
PRD2> select count(*) from VOUCHER_LINE;

           COUNT(*)

---

          9,894,236
what if if you write below and fire the query..what type of index column INV_ITEM_ID having..if the values are sequence generated or liner then can you try reverse-key index..

PRD2> select INV_ITEM_ID, count(*)
2 from VOUCHER_LINE
3 where INV_ITEM_ID in ( '3250', '3255','3260' ) 4 group by INV_ITEM_ID;

thanks and take care..subodh
On 4 October 2011 04:44, Orlando L <oralrnr_at_gmail.com> wrote:

> 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(*), 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
>
>
>

-- 
==============================
DO NOT FORGET TO SMILE TODAY
==============================


--
http://www.freelists.org/webpage/oracle-l

Received on Tue Oct 04 2011 - 02:10:02 CDT