Re: Problem with cache usage -> very bad performance?

plm_at_gmx.li wrote:
>
> > > On a Solaris 8 with 8GB of ram and oracle 9i I run a query like this:
> > >
> > > select * from sometab p
> > > where p.month='31-jan-2003'
> > > and not exists (select 1 from sometab q where p.id=q.id and
> > > q.month='31-dec-2002')

Since the column is named "month" rather than "date", I'm asssuming everything is stored as the last day of their respective month, right?

> > > sometab is a table (partitioned on month b.t.w), index on id is being
> > > used. It contains about 80000 records per 'month' totalling about
> > > 100MB per 'month'.

>
> SELECT STATEMENT, GOAL = CHOOSE 476 1 296
> FILTER
> PARTITION RANGE SINGLE
> TABLE ACCESS FULL BIS PORTFOLIO 476 1 296
> PARTITION RANGE SINGLE
> TABLE ACCESS BY LOCAL INDEX ROWID BIS PORTFOLIO 26
> 22 242
> INDEX RANGE SCAN BIS PORTFOLIO_IDX2 1 22
>
> (formatting somewhat unclear, sorry for that).

Perhaps this is a dumb question, but shouldn't one of the numbers 476, 1, or 296 correspond to the estimated cardinality Jan-2003 partition? None of them are even close to the 80,000 estimate.

Also, why would it need to access the table in the second rowset of the filter? It knows the date is right based on the partition it chose, and the id is in the index, so why not just an index range scan with no subsequent table look up?

Not that my questions help the OP at all, but this just piqued my interest.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB