-
Questions
-
INSERT SELECT with APPEND NOLOGGING PARALLEL(t,8) - Whether this can able to process upto 15 crores records ?
Breadcrumb
Thanks for the question, ajay.
Asked: July 28, 2017 - 11:30 am UTC
Last updated: July 30, 2017 - 9:41 am UTC
Version: oracle 12.1
Viewed 50K+ times! This question is
You Asked
As part of data migration project , i need to transfer data from staging table to target table (with out filter criteria) for about 41 tables.
Its a simple insert select and below query i am using for 41 tables.
INSERT /*+ APPEND NOLOGGING */
INTO crcborpt.AD_TIME
(id_ad_time,
date_id,
ad_time,
month_id,
month_desc,
quarter_id,
quarter_desc,
day_of_week_id,
day_of_week_desc,
week_id,
week_desc,
year_id,
week_of,
week_range,
prev_date_id,
prev_month_id,
prev_quarter_id,
prev_week_id,
prev_year_id,
prev_week_date_id,
BATCH_ID,
LOAD_DATE)
select /*+ PARALLEL(EXTL_AD_TIME_P,8) */
id_ad_time,
date_id,
ad_time,
month_id,
month_desc,
quarter_id,
quarter_desc,
day_of_week_id,
day_of_week_desc,
week_id,
week_desc,
year_id,
week_of,
week_range,
prev_date_id,
prev_month_id,
prev_quarter_id,
prev_week_id,
prev_year_id,
prev_week_date_id,
g_batch_id,
sysdate
from crcborpt.EXTL_AD_TIME_P;
But the processing is going slow for the source table with more than 8 crores....(expected).
But whether the single insert select with (APPEND,NOLOGGING,PARALLEL(t,8) can able to process upto 15 crores....in 1 go.
ARe you suggest batching.....( Note: Batching - committing for every 5 lacs is bit slow).
Any other suggestions are appreciated.
Thanks in advance.
Regards
Ajay L
and Connor said...
Its not a problem with the INSERT.
For example, check out this whitepaper
http://www.oracle.com/technetwork/database/in-memory/overview/twp-bp-for-iot-with-12c-042017-3679918.html
You can load millions of rows *per second*
You'll want to look at a SQL Monitor report or do some tracing to see *why* it is not running as fast as you need it - could be I/O, could be CPU, could be something else.
Is this answer out of date? If it is, please let us know via a Comment
