Re: Indexes in Union Views

When you say: "The database really seems to do a full table scan." I interpret "seems" to mean you haven't run an explain plan.

but likely you are suffering from bad design. Put all the data into a single table with a column for log number. Index it. Many problems are solved simultaneously.

Daniel Morgan

Rolf Unger wrote:

> 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 - 16:56:08 CST