Home » RDBMS Server » Performance Tuning » Demonstrating if Insertion is Faster in Partitioned or Non-Partitioned Table
Demonstrating if Insertion is Faster in Partitioned or Non-Partitioned Table [message #65475] Thu, 30 September 2004 10:30 Go to next message
Shariq T
Messages: 29
Registered: February 2003
Junior Member
I have posted a similar question on SQL forum but have not heard from them

I am working with a 600 million record table that has about 15 million records inserted monthly into it by an ETL tool that integrates the warehouse with a couple of billing systems. This table has a primary index and is NOT partitioned and of late some runs are taking well over 15 hours

After reading in several places how partitioning this table on month key will help with exactly with this sort of thing, I created a partitioned table in the test environment with populated it with identical data with identical colume

The partitioned table has local indexes whereas the non-partitioned one has global indexes. Each table is sized approximately 18 GB

I created a PL/SQL block that inserted dummy records into each of these tables. I committed after a block of 10,000 records was inserted into each table until 10 million records were inserted into the table.

The performance improvement/gain was only marginal for the partitioned table. Should this been happening? Can anyone offer a more elegant way or an example showing the benefits of using partition on this table over not using it

Looking forward to hearing from you

Shariq

Report message to a moderator

Re: Demonstrating if Insertion is Faster in Partitioned or Non-Partitioned Table [message #65479 is a reply to message #65475] Fri, 01 October 2004 07:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
why are you committing every 10,000 records?.
It is not going to help anything.
I beleive indexes have nothing to do here, with INSERTS
( actually presence of an index will slow the insertion..BUT we cannot avoid it..becuase dropping index,load data and rebuild the index is DISASTROUS.
we can make an index UNUSABLE before load. But it will take the same time to make the index usable again).

Did you try with NOLOGGING? (avoid generation of redo records).
Trying insert in parallel?

Report message to a moderator

Re: Demonstrating if Insertion is Faster in Partitioned or Non-Partitioned Table [message #65528 is a reply to message #65479] Fri, 15 October 2004 04:02 Go to previous messageGo to next message
Mike
Messages: 417
Registered: September 1998
Senior Member
Instead of inserting you data in the exiting partitioned table, create a new table and try to directly insert your data in there using a CREATE TABLE AS SELECT.
Once the table created, create an index for it, create a new empty partition on your partitioned table, execute a partition exchange.
In case you are using 9iR2 I recommand you to have a look at the new table/segment compression feature

Report message to a moderator

the advantage of a partitinoned table is not the perfromance of the insert [message #65529 is a reply to message #65475] Fri, 15 October 2004 04:04 Go to previous message
Mike
Messages: 417
Registered: September 1998
Senior Member
but of the select

Report message to a moderator

Previous Topic: URGENT:-Query not performing well
Next Topic: Terms meaning in V$SYSSTAT
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Dec 22 23:34:05 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.