Farsox Farsox - 1 month ago 5
SQL Question

Oracle SQL Select from a Large Number of Tables

I apologize if this has been answered elsewhere (There has to be something out there on the topic), but I can't seem to find a concise answer to my question.

I am relatively new to SQL, and when I have worked with it I have only used basic statements. Now I am working with a pretty large database (in Oracle) and was asked to explore it a little bit on the development side to get more familiar.

One of the questions that was sent to me to explore the db involved finding a list of "Run Controls" that are associated with a particular user.

There is a single table that keeps track of the different types of "Run Controls" that exist via a field titled

run_cntl_id
. There are 18 rows in this table associated with the specific user, each with a unique
run_cntl_id
. For each of the values in the
run_cntl_id
field, there is at least one corresponding table with multiple rows (pretty standard database stuff). Unfortunately, I do not have any reference material to display the table relationships.

There are just under 3,000 tables that contain both the
oprid
(the user identifier) and the
run_cntl_id
(the type of "Run Control") fields (when they are separated, there are 3,100 tables that contain the
run_cntl_id
field, and 8,800 that contain the
oprid
field). There are approximately 65,000 total tables in the database. Is there a way to search these 3,000 tables for the specific
operid
and
run_cntl_id
?

If I wanted to perform this query on one table, I would use the following statement:

SELECT *
FROM PS_JRNL_COPY_REQ
WHERE oprid = 'jle0010'
AND run_cntl_id = 'Copy_Jrnl';


To rephrase the question:

Is there a way to perform this statement on the 3,000 tables mentioned above without running a single statement 3,000 times?

Answer

If all of the 3,000 tables have the same columns then your query is going to look like :

SELECT * 
FROM   (
  select * from PS_JRNL_COPY_REQ union all
  select * from other_table_1    union all
  select * from other_table_2    union all
  ... and so on for 2,997 more)
WHERE oprid = 'jle0010'
AND run_cntl_id = 'Copy_Jrnl';

No guarantees on it parsing or running though.

You can build that inline view by querying user_tables of course.

Comments