Need more inputs
Nikhil Patil, November 21, 2016 - 11:03 am UTC
Thanks a lot Chris!
Actually I heard APPEND hint wiull insert data above HWM, thats whay i raise this quesy.
Just to give you my exact scenario -
We are creating a new database for data archival to move older data..
So here we will be creating new table in archival databse with the same name and via dblink will be inserting those records using below comands -
insert /*+ APPEND */ into local_table select * from table@database_link;
Since it will be a new table, will it create ant problem?
Could you please share your views
November 21, 2016 - 5:24 pm UTC
If it's a new table, I'd create it with:
create table t as select * from t@dblink
Parallel DML
Rajeshwaran, Jeyabal, November 21, 2016 - 11:06 am UTC
You could even switch over to parallel dml and enjoy its speed.
Parallel DML's are direct path by default.
demo@ORA11G> alter session force parallel dml parallel ;
Session altered.
demo@ORA11G> explain plan for
2 insert into t
3 select * from big_table;
Explained.
demo@ORA11G> @xplan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 3773427756
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1000K| 98M| 1108 (3)| 00:00:01 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 1000K| 98M| 1108 (3)| 00:00:01 | Q1,00 | P->S | QC (RAND) |
| 3 | LOAD AS SELECT | T | | | | | Q1,00 | PCWP | |
| 4 | PX BLOCK ITERATOR | | 1000K| 98M| 1108 (3)| 00:00:01 | Q1,00 | PCWC | |
| 5 | TABLE ACCESS FULL| BIG_TABLE | 1000K| 98M| 1108 (3)| 00:00:01 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------
12 rows selected.
demo@ORA11G>
November 21, 2016 - 5:32 pm UTC
if you have the spare capacity to benefit from parallel!
Think about partitioning
Reviewer, November 21, 2016 - 4:42 pm UTC
Hi,
I would really suggest you to partition your new table (especially if the old one is not) and then pull data based on partitions in parallel. The APPEND hint would work with partitions as well.
If need be you could performance test the approach.
Regards!!
November 21, 2016 - 5:35 pm UTC
Or you could place each partition in it's own tablespace and use transportable tablespaces at the partition level...
As you say, you should test to see how it performs in your environment!