Re: Performance bad with and without index
- From: Subodh Deshpande <deshpande.subodh@xxxxxxxxx>
- To: oralrnr@xxxxxxxxx
- Date: Tue, 4 Oct 2011 12:40:02 +0530
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@xxxxxxxxx> 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.
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
==============================
DO NOT FORGET TO SMILE TODAY
==============================
--
//www.freelists.org/webpage/oracle-l
Other related posts:
