Oracle Performance Problem W/ Very Large Database

From: Mark Himeda <mhimeda_at_halcyon.com>
Date: 2000/04/07
Message-ID: <kovH4.1128$F%5.45507@news.uswest.net>#1/1

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 Received on Fri Apr 07 2000 - 00:00:00 CDT