FAQ
Hi

I was wondering if anyone has experience bulk loading data to partitioned
tables? I have run some tests and running bulk load (insert append) into
partitioned tables is actually 40% more costy. For example to load up a 80
million rows table it takes around 8 minutes whereas with plain heap table
it only takes 5

Test used:
LMT with 16MB uniform size extent
No ASSM
Parallel DML
Parallel Query
Degree 16

Regards

Alex

Search Discussions

  • Enrique Fernandez-Pampillon at Jun 23, 2006 at 11:26 am
    Hi,

    When you load data into a partitioned table oracle has to check every record
    to decide in which partition store it.

    When you load data in a heap table oracle hasn't to check it. All records
    are saved in the same segment.

    But .... have you test how long it takes to finish some queries in both
    cases?

    You can save time when you load in partitioned tables using partition
    clause.

    HTH
    On 6/23/06, amonte wrote:

    Hi

    I was wondering if anyone has experience bulk loading data to partitioned
    tables? I have run some tests and running bulk load (insert append) into
    partitioned tables is actually 40% more costy. For example to load up a 80
    million rows table it takes around 8 minutes whereas with plain heap table
    it only takes 5

    Test used:
    LMT with 16MB uniform size extent
    No ASSM
    Parallel DML
    Parallel Query
    Degree 16

    Regards

    Alex

    --
    ------------------------------------------------
    Enrique

    --
    http://www.freelists.org/webpage/oracle-l
  • Ryan_gaffuri_at_comcast.net at Jun 23, 2006 at 5:48 pm
    What kind of partitioning did you use? How did you spread out the partitions. did you have them in sepearte datafiles?

    In the past I have had problems with full tablescanning hash partitioned tables if I did not use parallel slaves. It was slower than full scanning a heap table.
    -------------- Original message --------------
    From: amonte
    Hi

    I was wondering if anyone has experience bulk loading data to partitioned tables? I have run some tests and running bulk load (insert append) into partitioned tables is actually 40% more costy. For example to load up a 80 million rows table it takes around 8 minutes whereas with plain heap table it only takes 5

    Test used:
    LMT with 16MB uniform size extent
    No ASSM
    Parallel DML
    Parallel Query
    Degree 16

    Regards

    Alex
  • Amonte at Jun 23, 2006 at 10:32 pm
    Hi Ryan

    The table is hash partitioned into 16 partitions. The tablespace has 16
    datafiles. The degree used is 16.

    The problems seems with Parallel DML, when I stopped using Parallel DML the
    HW space management lock which I had contention with disappeared.

    Does anyone know how HW enqueue works?
    On 6/23/06, ryan_gaffuri_at_comcast.net wrote:


    What kind of partitioning did you use? How did you spread out the
    partitions. did you have them in sepearte datafiles?

    In the past I have had problems with full tablescanning hash partitioned
    tables if I did not use parallel slaves. It was slower than full scanning a
    heap table.

    -------------- Original message --------------
    From: amonte
    Hi

    I was wondering if anyone has experience bulk loading data to partitioned
    tables? I have run some tests and running bulk load (insert append) into
    partitioned tables is actually 40% more costy. For example to load up a 80
    million rows table it takes around 8 minutes whereas with plain heap table
    it only takes 5

    Test used:
    LMT with 16MB uniform size extent
    No ASSM
    Parallel DML
    Parallel Query
    Degree 16

    Regards

    Alex
    --
    http://www.freelists.org/webpage/oracle-l
  • Stephen Andert at Jun 30, 2006 at 4:38 am
    When I was loading data into partitioned tables, we were able to get our
    AppDev folks to provide the data in separate files for each partition. Data
    loading just FLEW. Sorry if this doesn't help your situation, but may be
    something to discuss for future loads.

    Stephen
    On 6/23/06, amonte wrote:

    Hi

    I was wondering if anyone has experience bulk loading data to partitioned
    tables? I have run some tests and running bulk load (insert append) into
    partitioned tables is actually 40% more costy. For example to load up a 80
    million rows table it takes around 8 minutes whereas with plain heap table
    it only takes 5

    Test used:
    LMT with 16MB uniform size extent
    No ASSM
    Parallel DML
    Parallel Query
    Degree 16

    Regards

    Alex

    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 23, '06 at 11:00a
activeJun 30, '06 at 4:38a
posts5
users4
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase