Cost Base Optimizer on 3 table select

From: <richard.endo_at_unisys.com>
Date: Wed, 21 Oct 1998 19:05:02 GMT
Message-ID: <70lb8u$gke$1@nnrp1.dejanews.com>

I'm trying to do a 3 table select with Oracle8.0.4 and NT4 enterprise

select t2col1,t1col1,t2col4
from t3,t2,t1
where

t3col1='BUILDING' and
t3col2=t2col2 and
t1col2=t2col3 and

t2col1 in (
'01-feb-1998',
) order by t1col,t2col1;

t1 has 6million rows, t2 has 1.5million and t3 has 150,000 rows. There is an index for each column.

I ran on a multiprocessor machine and got the following execution plan nested loops
nested loops
  table access(by index rowid) of 't2'
   index(range scan) of 't2col1' (non-unique)   index (range scan) of 't3col1' (non-unique) table access (by index rowid) of 't1'
  index (range scan) of 't2col3' (non-unique)

This was the most efficient plan in terms of the number of total buffer gets. I've recreated the db on another multiprocessor machine and I can't get the same execution plan. I've tried various iterations of ANALYZE on the different tables or indexes, using 10-40 percent sample sizes, using ANALYZE with columns to get histograms(which I didn't need to use the first time), deleting stats all together and running rules based, and changing various init parameters.

One of the main problems is that it picks the wrong table order to look at first. I think the above picks t2 first selecting 626 rows with the 01-feb date. The best of my other plans pick t3 first selecting 30142 rows based with the 'BUILDING' value. Some of the plans like to do full table scans using parallel query even though I specify 0 on the optimize_parallel? parameter.

I would like to get the efficient execution plan without explicit hints and not changing the select statement.

Does the amount of memory I've allocated affect the cbo analysis or the way I created the tablespace?

Any ideas would be appreciated. Thanks in advance.

Rick

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Oct 21 1998 - 14:05:02 CDT