Old 2: -from table(dbms_xplan.display('sys.plan_table$','&id',nvl2('&parms','typical &parms','typical'))) automatic DOP: Computed Degree of Parallelism is 128 because of degree limitġ7 rows not really that hint, but how we nominate parallelism, egĢ insert /*+ append parallel(t 4) */ into target tģ select /*+ parallel(s 4) */ * from source s | 3 | LOAD AS SELECT (HYBRID TSM/HWMB)| TARGET | | | | | Q1,00 | PCWP | | Parallel_degree_policy string delete from plan_table Ģ insert /*+ enable_parallel_dml parallel(target) */ into targetģ select /*+ parallel(source) */ * from hash value: 2718911588 automatic DOP: Computed Degree of Parallelism is 2Ģ6 rows show parameter parallel_degree_policy Total hints for statement: 2 (U - Unused (2)) Hint Report (identified by operation id / Query Block Name / Object Alias): | 1 | LOAD AS SELECT | TARGET | | | | | | | |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
However with that hint in place, all PX process performs the write on the target table (however extent trimming were possible for those segments created on tablespace having extent allocation create table sourceĢ insert /*+ append parallel */ into targetģ select /*+ parallel */ * from hash value: 1770633649 Without "enable_parallel_dml" hint, the QC process performs the write on the target table. kind of like table A end of record now points to table B start of record. Why cannot I append one table to another?īut is a DDL operation. Handle for failure by maintaining which partitions have been executedįor some reason I am not able to explain my requirement. INSERT INTO destination SELECT * FROM staging ġ) I can use parallel hint to tune this up.Ģ) Write a block to evoke sessions in parallel. Now I need to copy data from staging to destination. let me explain the problem I am trying to solve. Thanks for the response!! Not that simple. If we can exchange partition with another table why can't I append a table to a partition or a table to another table for that matter? The above works good!! Now my requirement is something like following -ġ) tbl_partitioned partition part_1 has dataģ) I just want to append stg_tbl_partitioned to tbl_partitioned table's partition part_1 (avoiding a INSERT which is slow) INSERT INTO stg_tbl_partitioned VALUES ('A',TO_DATE('0','DD-MON-YYYY')) ĪLTER TABLE tbl_partitioned EXCHANGE PARTITION part_1 WITH TABLE stg_tbl_partitioned Select * from user_tab_partitions where lower(table_name) = 'tbl_partitioned' select NUM_ROWS ,BLOCKS ,EMPTY_BLOCKS ,AVG_SPACE ,AVG_ROW_LEN ,SAMPLE_SIZE ,LAST_ANALYZED ,PARTITIONED from user_tables where table_name = 'your_table' ġ1773331860 308566208 0 3331860 27-DEC-15 NO Is there any other method that can complete it faster - 1 hour. Is there any other utility that can be used to achieve this ? For 1 month there will be around 2 million record. When i did for 10000 records - it took 8 hours to complete. Created a stored procedure and did the above stuff in a cursor and a forall loop. insert into table2(col1, col2.) as select col1, col2. I need to copy 1 month data form table1 to table2.ġ. Table2 is the same table as table1 but contains only 1 month data. I actually do not know how many record are present in table1 but it is for sure that it contains huge data. Table1 has many records and the count(*) query did not return after 48 hours of execution. I need to copy data(13 columns) from an existing table (say table1 of 25 columns) to another existing table table2(13 columns).
0 kommentar(er)
