Re: Oracle Performance Problem W/ Very Large Database

Hi.

 What optimizer are you using ( RULE or COST based )?  Make SELECT * FROM user_tables
   WHERE table_name IN ('PARENTTBL','CHILDTBL');  Look for num_rows column values.
 If these values are not NULL then you are using COST base optimizer.  Verify that these values correspond to the actual number of rows in  your tables ( otherwise you must ANALYZE both tables ).

 HTH. Michael.

 P.S. Just a question : Why do you need join to ParentTbl at all in your

      example?

In article <kovH4.1128$F%5.45507_at_news.uswest.net>,   "Mark Himeda" <mhimeda_at_halcyon.com> wrote:
> Help! We have a simple, but very large database for which we cannot
> understand why the optimizer is producing the following explain plans.
> Specifically, for Explain Plan1 below, why is a table scan being
 performed
> on the ParentTbl when the primary key index should be used? For
 Explain
> Plan2 below, why is an full index scan being performed instead of a
 range
> scan? Even optimizer hints don't seem to work!
>
> We have a simple schema similar to the following:
>
> ParentTbl (SeqNo integer, Col1 varchar(50), Col2 varchar(50)...)
> ChildTbl(DateTimeStamp datetime, SeqNo integer, Col1 varchar(50)...)
>
> ParentTbl has 100,000,000 rows, each row approx. 800 k max.
> ChildTbl has 700,000,000 rows, each row approx. 100 k max.
>
> Primary keys: ParentTbl (SeqNo), ChildTbl (TimeStamp)
> Additional Idx: ChildTbl (SeqNo, DateTimeStamp)
>
> Query 1: Produces approx. 50,000 rows.
> Select ChildTbl.Col1, count(ChildTbl.*)
> From ChildTbl, ParentTbl
> WHERE ChildTbl.DateTimeStamp >= to_date({some date}...)
> AND ChildTbl.DateTimeStamp < to_date({some larger date}...)
> AND ParentTbl.SeqNo = ChildTbl.SeqNo
> GROUP BY ChildTbl.Col1;
>
> Explain Plan 1:
> 1) Table scan on ParentTbl
> 2) ChildTbl PK Index used
>
> Query 2: 1 row (aggregate)
> Select count(ChildTbl.*)
> From ChildTbl, ParentTbl
> WHERE ChildTbl.DateTimeStamp >= to_date({some date}...)
> AND ChildTbl.DateTimeStamp < to_date({some larger date}...)
> AND ParentTbl.SeqNo = ChildTbl.SeqNo;
>
> Explain Plan 2:
> 1) Index scan on ChildTbl PK
> 2) ParentTbl PK Index used
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Apr 08 2000 - 00:00:00 CDT