sql - Optimizing a huge Union operation on many, large tables in Oracle -

sql - Optimizing a huge Union operation on many, large tables in Oracle -

i have next problem @ hand. have 18 big oracle tables; each has row count changing between 2 , 15 millions , additionally each table has around 450 columns. tables have same column types , structures. need unify these tables large, single table generated result of other operations. while generating each of these tables, utilize next optimizations:

1) utilize create table command each table, hints "nologging parallel 32 compress query high". our database server supports parallelism 32 units.

2)after creation of each table, run "dbms_stats.gather_table_stats" on table in order optimize execution path.

now, basic thing came mind next statement:

create table unified_table nologging parallel 32 compress query high (select * table_0 union select * table_1 ... union select * table_17 )

this command doesn't finish in hour; keeps running , running, need more efficient way.

currently have next in mind: generate final table re-create of table_0 in beginning. create 17 additional, parallel calls application database server, each of contains next command:

insert final_table select * table_i

i hope since database server supports parallelism, oracle handle parallel insertions different insert statements same table gracefully.

would strategy begin with? how can optimize unification task in best way, there other improve options?

if each tables info (id) on distinct range, create empty partitioned table same construction 17 tables. create empty partitions ranges corresponding each table. swap/exchange tables appropriate partition.

this, beingness ddl quick.

if info not "ranged", add together column partition key (e.g. table_no).

sql database oracle