I have some very large tables divided into yearly archives. I want to run a
report where the user enters the date range of the data desired. The date
field is indexed.

I have constructed a view essentially:

create view all_data as
(select x,y,data_date from table_2000 where ... [some additional criteria] )
union all
(select x,y,data_date from table_2001 where ...)
union all
(select x,y,data_date from table_2002 where ...)


I then query the view:

select * from all_data
where data_date > todate('01/10/2000','DD/MM/YYYY')
and data_date < todate('01/12/2000','DD/MM/YYYY')

Since the date is indexed, I was expecting the query to run almost as fast
as the same query run against the table_2000 - but it essentially takes
3x as long - and if I add other archive tables to the union it extends the
execution time.

any thoughts on how to speed this up? Thx, Marty