Re: Optimizer problem?

Marcel,

Sounds reasonable, but the Oracle optimizer doesn't consider available memory when determining an access plan.

-- 
Regards,

Jeff Gentry                                   Carpe Diem!!!
Computer Systems Authority        Systems Consultant
Mailto:jgentry_at_csac.com             Mailto:jlgentry_at_worldnet.att.net
http://www.csac.com/~jgentry       http://home.att.net/~jlgentry

Marcel Claus <Marcel.Claus_at_Informatik.Uni-Oldenburg.DE> wrote in article
<61fcnu$ce5_at_news.Informatik.Uni-Oldenburg.DE>...


> 

> 

> Phil Tsao wrote:

> 

> > Hi,

> >

> > I was wondering if any one has experienced the same problem.

> > I have a loop to add 10,000 rows to a table one by one then another

 loop


> >

> > to delete each row one by one. After doing so a few times, the

> > "select count(*) from table" starts doing  table scan ( and physical

> > read)

> > every time. I thought the index (on primary key) was corrupted due to

> > the massive insert & delete and rebuilt it. It did not help.

> > The only way I can force using index is to use hint or the following:

> >

> >     select count(*) from table where pkey > -9999;

> >

> > Is there anything you know that causes this to happen?

> > Thanks in advance.

> >

> > --

> > Phil Tsao

> > philt_at_technologist.com

> 

> Did you check the amount of free RAM for ORACLE?

> Maybe this is insufficient, so ORACLE has to use the disks.

> 

> 

>