Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Hemant.

Asked: September 29, 2016 - 3:03 pm UTC

Last updated: October 04, 2016 - 11:16 am UTC

Version: 11g

Viewed 1000+ times

You Asked

I am inserting data into non partitioned table contained data 250M records. But it is taking too long time to insert data from source system table having same number of data both the tables in different databases.
I would like to know different loading methods which I can use to speedup data loading process.

Source table partitioned with Yearly and sub partitioned with month.
Target table not partitioned.
both source and target tables are same structure
Load incremental data everyday at night from source table to DWH target table which is used for reporting.

and Connor said...

The number of rows *already* in the table is of little consequence - what is really important is the number of rows you are loading.

Having said that, even 250m rows is not a "massive" amount of data to load into table - so if its taking a long time, the most likely culprits are either

a) the number of indexes on the table, and/or
b) the mechanism for loading

So things to consider

1) consider direct load (ie, insert /*+ APPEND */ ). Note that this will lock the table

2) look at what indexes are on the table, and whether they are all in fact needed. Or whether you can mark them unsuable before the load, and rebuild them afterwards.

But ultimately...if the data volume is expected to grow, then partitioning really needs to be considered (either manually or using the partitioning option)

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Manual partitioning

Shankar, October 03, 2016 - 8:03 am UTC

Hi Connor,
Need to understand the term manual partitioning. Would like to explain a bit more about it. What's the concept and how one can implement it.
Thanks.
Connor McDonald
October 04, 2016 - 11:16 am UTC

Manual partitioning is when *you* take control over the partitioning process if you do not have the license for the partitioning option.

So you would have your own tables, each of which is a "partition" and place a view over the top of them to mimic the overall table. Its what we used to do back in Oracle 7 before the partitioning feature was introduced.

So you have

create or replace
view MY_TABLE as
select * from sub_table_1 where column = 'value1'
union all
select * from sub_table_2 where column = 'value2'
etc

to simulate partitioned tables.

But obviously, if you're serious about partitioning, you would be better off getting the partitioning license.

Question

Chuck Jolley, October 04, 2016 - 2:40 pm UTC

Not sure what "both the tables in different databases" means.
Is this coming across a database link?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.