Indexes in Union Views

Hi,

I have a question regarding union views and indexes. (using Oracle 8.0.5 on WinNT).

I have several tables log_entries_1, log_entries_2, .. all of the type:

log_string varchar2(100)
log_date date

For each table I have an index on the column log_date. The log_date columns do not overlap, log_entries_1 has only data from January, log_entries_2 from February, and so on.
Each table has something like 500 000 records.

To access all data I created a view:

create view all_stuff
as
 select log_string, log_date from log_entries_1 union
 select log_string, log_date from log_entries_2 union
 ....
;

Now I want to retrieve the latest data ...

 select max(log_date) from log_entries_6; or
 select max(log_date) from log_entries_2;

all of those queries against a single table are fast.

But if I do a

 select max(log_date) from all_stuff;

I can wait for at least half an hour without any results. The database really seems to do a full table scan. Why can it not use the index?

I did not specify any optimizer method in the init...ora file of the database. And the statistics tables for the cost based optimizer are not filled, either.

Is there any way to pass some optimizer hints to increase the performance for the union view.

Thanks for your help,
Rolf. Received on Tue Apr 02 2002 - 15:32:27 CST