Re: Multiple Table Query

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.

>

>

>