Re: Multiple Table Query

Thanks again Jonathan, I'll give this a try. I really appreciate the help I get from people like you in this group.

Mark.

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:950567715.14204.0.nnrp-09.9e984b29_at_news.demon.co.uk...
> Plan B:
>
> Each day as you create a new archive table,
> execute a procedure which does something like:
>
> for r1 in (select table_name from user_tables where table_name like
> 'ARCH%') loop
> build an array-based SQL string
> end loop;
>
> dbms_sql(the created string)
>
> The string should be something like:
> create or replace view all_arch_tables as
> select * from arch_0001
> union all select * from arch_0002
> union all ....
> union all select * from arch_NNNN
>
>
> This is a partitioned view, and if you have
> partitioned_views_enabled = true
> can be used almost like a normal table
> when queried. It will certainly handle
> your 'select count(*)'.
>
> There are a couple of articles on my
> web-site about partition views in 7.3.
> Unfortunately they are a deprecated
> feature in 8.
>
>
>
>
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> M. Misener wrote in message <889r1m$55e$1_at_mohawk.hwcn.org>...
> >I have an application that archives data each day into a table.
> >Every day, and new table is created with same structure (same fields,
 same
> >columns).
> >The difference being, that the table_name is unique. ex: ARCH_020100,
> >ARCH_020200, etc., base on Date.
> >
> >I'm not sure how construct a query to select count(*) where table_name
 "like
> >ARCH_%" (something like that).
> >
> >Can anyone help?
> >Thanks.
> >
> >
> >
>
>
Received on Mon Feb 14 2000 - 00:00:00 CST