The optimizer picks the best method of gathering the data based upon the
fewest I/O's, and it does look at each of the columns involved, whether there
is an index on it, etc in making that determination.  With that said though, I'm
not sure it takes the "value" of the constraint into consideration when determining
if a particular table will contribute to the result set.  I would "assume" (with all of the
negative connotations that word implies :) that the given your example (with row counts
large enough for the optimizer to forgo a table scan) the optimizer would in fact choose
index scans of *each*, but then the actual I/O to the tables would not occur since no
entries would qualify except from the first table.
  This is speculation, and only a call to the RC (which I'm sure would have to be
escalated due to it's complexity), or setting up your example and doing a GENPLAN
could truly answer it for you.  In either event, if you do pursue it, could you please
let me know what you find out?

Regards,
Michael L Gueterman
Easy Does It Technologies
email: [log in to unmask]
http://www.editcorp.com
voice: (888) 858-EDIT -or- (509) 943-5108
fax:   (509) 946-1170
--


----------
From:  Rudderow, Evan[SMTP:[log in to unmask]]
Sent:  Thursday, August 28, 1997 5:05 AM
To:  [log in to unmask]
Subject:  Re: [HP3000-L] Allbase question

<snip>


Let's say you've got a *large* table that has several logical partitions --
groups of records that share a common value for a key column; you can break
this into several smaller tables that are queried together by a UNION ALL .
 By setting up the tables and the UNION ALL query the right way, Oracle can
detect which of the tables won't contribute to the result set and will be
smart enough to not read them.

Example: let's say you've got 4 quarters of sales data in a table, SALES;
you first create a table for each quarter:

create table SALES_PERIOD_1
  ( Company_ID, Period_ID, Sales_Total) as
  select Company_ID, Period_ID, Sales_Total
  from SALES
  where Period_ID = 1;

alter table SALES_PERIOD_1
  add constraint CHECK_SALES_PERIOD_1
  check (Period_ID = 1);

alter table SALES_PERIOD_1
  add constraint SALES_PERIOD_1_PK
  primary key (Company_ID, Period_ID);

create index SALES_PERIOD_1$PERIOD_ID
  on SALES_PERIOD_1(Period_ID);

.. and the same for periods 2, 3, and 4; you then create a view like:

create view SALES_ALL as
  select * from SALES_PERIOD_1
  union
  select * from SALES_PERIOD_2
  union
  select * from SALES_PERIOD_3
  union
  select * from SALES_PERIOD_4;


Then if you executed a query like:

select * from SALES_ALL
  where Period_ID = 1
  and Company_ID > 10000

then Oracle would only access the SALES_PERIOD_1 table and not the 2, 3, and
4 tables.

 -- Evan