Hi All,
I am having one query as below:
There is a master table named: MSTR_TABLE (It's having partitions on a month column [YYYYMM] )
I am having 4 temp Tables As: Temp1, Temp2, temp3, Temp4 (These tables are not having any index, no partitions)
Scenario 1: I am trying to insert data into MSTR_TABLE from Temp1, Temp2, temp3, Temp4 tables (Select data from temp tables by Outer Join), which i taking lot's of time. Coming to no of records in temp tables are Temp1: 5 Million Records,
Temp2: 3 Million Records,
Temp3: 0.5 Million records,
Temp4: 5000 Records
Scenario 2: Creating a new Table as TEMP_MASTER using Temp1, Temp2, temp3, Temp4 tables. This is executing much faster and the table is getting created in less than 10 mins.
Scenario 3: Now i am inserting data from TEMP_MASTER to MSTR_TABLE. This is is also getting executed in less than 5 mins.
I tried following methods:
1) Creating a table with partitions and tried Inserting Data - No Luck
2) Disabled Logging on table and tried Inserting data - No Luck
3) used /*+ append nologging*/ and tried inserting data - No Luck
4) Altered Next Extent to 8MB and tried - No Luck
Please let me know, why while inserting into main table (Scenario 1) from query is not working and running long time (More than 2 hrs).
Thanks in Advance,
Venu Gopal K