SQL - How to select the Minimum Entry for One Column in Numerous Tables?

I have several tables (all in the same Schema) that have a Column called UPLOAD_TIME.
I'm trying to find the Minimum UPLOAD_TIME for each table.

I can Query for SELECT MIN(UPLOAD_TIME) FROM (each_table).
But is there a quicker way of doing this?

Let's take two of the tables for now:

• LASER_VISION_VERIFICATIONS (Min Upload Time is 24NOV2020)
• MACHINE_RNR_RECORDS (Min Upload Time is 12FEB2019)

Can I get a results table like this?:

You can consolidate into a single query:

        SELECT 'TABLE1' table_name,MIN(upload_time) min_upload_time
          FROM table1
        UNION ALL
        SELECT 'TABLE2' table_name,MIN(upload_time)
          FROM table2
        UNION ALL
        SELECT 'TABLE3' table_name,MIN(upload_time)
          FROM table3

But that won't run noticeably faster than running independent queries per table. The only advantage is a shared parse and fewer network round trips which is inconsequential in this situation. If you care about speed, you'll want to ensure that upload_time is indexed in each table. The downside is it doesn't lend itself well to extensibility - if you add a table, you have to modify the query. If your table list might grow over time, you're better of using a PL/SQL script and looping:

CREATE OR REPLACE TYPE result_rowtype IS OBJECT (table_name varchar2(128),
                                                 min_upload_time date);

CREATE OR REPLACE TYPE result_tabtype IS TABLE OF result_rowtype;

CREATE OR REPLACE FUNCTION myearliestdatecol(in_column_name IN varchar2)
  RETURN result_tabtype PIPELINED
AS
  rec_result_row result_rowtype;
BEGIN
  FOR rec_table IN (SELECT table_name
                      FROM user_tab_columns
                     WHERE column_name = in_column_name)
  LOOP
    EXECUTE IMMEDIATE 'SELECT result_rowtype('''||rec_table.table_name||''',
                                             MIN("'||in_column_name||'"))
                         FROM "'||rec_table.table_name||'"' 
                 INTO rec_result_row;

    PIPE ROW (rec_result_row);       
  END LOOP;
END;

Now query it:

SELECT *
  FROM TABLE(myearliestdatecol('UPLOAD_TIME'))

If you add more tables or drop tables there is no need to modify your code. You can also use this for different date columns, so you can change the column name without modifying code.

You will need to use dynamic sql for that

DECLARE
  mystr VARCHAR(1000);
  v_mystamp TIMESTAMP(6);
  cursor c1 is 
    select distinct data_table from table_list order by data_table asc;
 
  
BEGIN
FOR rec IN c1 LOOP
     mystr := 'select min(UPLOAD_TIME) from ' || rec.data_table;
 
     execute immediate mystr into v_mystamp ;
     DBMS_OUTPUT.PUT_LINE(rec.data_table);
     DBMS_OUTPUT.PUT_LINE(v_mystamp);
   END LOOP;
END;