Re: Performance bad with and without index

From: Stephane Faroult <sfaroult_at_roughsea.com>
Date: Tue, 04 Oct 2011 08:58:46 +0200
Message-ID: <4E8AAEA6.4000009_at_roughsea.com>

On 10/04/2011 01:14 AM, Orlando L 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:
>
>

Orlando,

What do you want to do with 50,000+ rows? Display them on a single HTML page (scroll ... scroll ... scroll ...) ? Print a 1,000 page report? Execute them as trades?
I'd try to move back and understand in what kind of process these rows are fed. The problem that has been submitted to you may just be the tip of the iceberg.

Think about partitioning, too, if fetching by INV_ITEM_ID is your staple query. Try to have partitions that you can remorselessly scan for the INV_ITEM_ID that correspond to massive volumes, so that they are separate from the others.

HTH

-- 
Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>


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

Received on Tue Oct 04 2011 - 01:58:46 CDT