one query for many similar tables

Question

I have an Oracle database with many tables that have identical structure (columns are all the same). The table names are similar also. The names of the tables are like table_1, table_2, table_3...

I know this isn't the most efficient design, but I don't have the option of changing this at this time.

In this case, is it possible to make a single sql query, to extract all rows with the same condition across multiple tables (hundreds of tables) without explicitly using the exact table name?

I realize I could use something like select * from table_1 UNION select * from table_2 UNION select * from table_3...select * from table_1000

But is there a more elegant sql statement that can be run that extracts from all matching table names into one result without having to name each table explicitly.

Something like

select * from table_%

Is something like that possible? If not, what is the most efficient way to write this query?

Select * from table_% .. will not work . If you do not want to write the query by yourself . You can search in sybObject for the table like table_% and build the query by parsing them one by one . — Ajay, Aug 28 '15 at 15:43
Yes, I know I can do that, but I was hoping for a more elegant solution. If something more elegant isn't possible, then I'll have to go with some sort of loop which I'm trying to avoid. — Jonjilla, Aug 28 '15 at 15:46
Assuming the number of tables remains static, you could create a view that does all the unioning of the tables together, and then query the view. However, I wouldn't like to vouch for whether Oracle would be able to push predicates in that case - you'd have to test. Also, do you need the results to be distinct? I'd be tempted to create the view using UNION ALL rather than UNION, and then throwing a distinct around the end results if you did need the results to be distinct. — Boneist, Aug 28 '15 at 15:48
No, in this case, tables are being added and removed continuously so the table count will be changing. I've investigated a view, but I think the problem is still similar isn't it? You still have to make a long explicit multi table statement which I'm hoping there is a simpler way. — Jonjilla, Aug 28 '15 at 15:51
No, there isn't a simpler way. You're still going to have to tell Oracle exactly which tables to query at some point. Sounds like writing a query to write a query is the way to go (as @Ajay suggested), except it would be user_tables (or all_tables/dba_tables as appropriate) you'd be querying. — Boneist, Aug 28 '15 at 16:05
OK, thanks. I figured as much, considering all of the searching I've done on this. But I was hoping someone knew some trick. — Jonjilla, Aug 28 '15 at 16:08
`select 'select col1, col2, col3 from '||table_name||' where col4 = ''condition'' union all' from all_tables where table_name like 'TABLE%';` might be the best way to go then; it'll take most of the legwork out of creating the query at least (although you'll still have some tidying up to do, such as remove the final union all, etc). Or, indeed, Alex's solution. (I should learn to avoid making definitive statements such as "no, there isn't!" it would seem! *{;-) ) — Boneist, Aug 28 '15 at 16:12

score 3 · Accepted Answer · edited May 23 '17 at 12:33

You can use dbms_xmlgen to query tables using a pattern, which generates an XML document as a CLOB:

select dbms_xmlgen.getxml('select * from ' || table_name
  || ' where some_col like ''%Test%''') as xml_clob
from user_tables
where table_name like 'TABLE_%';

You said you wanted a condition, so I've included a dummy one, where some_col like '%Test%'.

You can then use XMLTable to extract the values back as relational data, converting the CLOB to XMLType on the way:

select x.*
from (
  select xmltype(dbms_xmlgen.getxml('select * from ' || table_name
    || ' where some_col like ''%Test%''')) as xml
  from user_tables
  where table_name like 'TABLE_%'
) t
cross join xmltable('/ROWSET/ROW'
  passing t.xml
  columns id number path 'ID',
    some_col varchar2(10) path 'SOME_COL'
) x;

SQL Fiddle demo which retrieves one matching row from each of two similar tables. Of course, this assumes your table names follow a useful pattern like table_%, but you suggest they do.

This is the only way I know to do something like this without resorting to PL/SQL (and having searched back a bit, was probably inspired by this answer to count multiple tables). Whether it's efficient (enough) is something you'd need to test with your data.

Ok, so I was wrong to say there wasn't a simpler way. Although I wouldn't exactly call XML "simple" *{;-) — Boneist, Aug 28 '15 at 16:14

score 0 · Answer 2 · answered Aug 28 '15 at 16:09

0

This is kind of messy and best performed in a middle-tier, but I suppose you could basically loop over the tables and use EXECUTE IMMEDIATE to do it.

Something like:

for t in (select table_name from all_tables where table_name like 'table_%') loop
  execute immediate 'select blah from ' || t.table_name;
end loop;

answered Aug 28 '15 at 16:09

Mark Brackett

84,552
17
108
152

You'd probably need to build up a string that does a union/union all of all those tables, and then maybe open a ref cursor using that? Otherwise you have to select all those individual tables into something; which could be a collection or GTT I guess. Depends how the OP needs to use the result perhaps. – Alex Poole Aug 28 '15 at 16:14

score -3 · Answer 3 · answered Aug 28 '15 at 15:52

-3

You can write "select * from table_1 and table_2 and tabl_3;"

answered Aug 28 '15 at 15:52

Frank Sun

11
6

thanks, but I clarified my question to see if there is someway to write this without having to explicitly name each table, since I have hundreds of them/ – Jonjilla Aug 28 '15 at 15:58
3

`select * from table_1 and table_2 and tabl_3;` isn't valid SQL though! At least, not in Oracle! – Boneist Aug 28 '15 at 16:09

one query for many similar tables

3 Answers3