Re: Performance bad with and without index

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: oralrnr@xxxxxxxxx
  • Date: Tue, 04 Oct 2011 08:58:46 +0200

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>


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


Other related posts: