Re: Indexes in Union Views

since the table data are disjunct,
wouldn't a UNION ALL improve performance by avoiding unnecessary sorting?

We've done the same before and
I think there are valid reasons for this type of design:

Oracle Standard Edition (=> no Partitioning) and a requirement for fast Purging of old records (by truncating a table)

HTH Dieter

BTW: The maximum of all records must be among the maximums of each table.

Select max(log_date) from
(select max(log_date) log_date from log_entries_1  union all
 select max(log_date) log_date from log_entries_2  union all ....
)
but I know that's not the real problem .... :-)

"damorgan" <damorgan_at_exesolutions.com> schrieb im Newsbeitrag news:3CAA370C.4AAAD6CA_at_exesolutions.com...
> 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 Wed Apr 03 2002 - 15:06:20 CST