Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: October 29, 2009 - 3:03 pm UTC

Last updated: November 11, 2009 - 3:00 pm UTC

Version: 10.2

Viewed 1000+ times

You Asked

Hello Tom,


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:549370300346187664

while reading above URL I came across your response to phil "Affecting performance?"

you compare the insert between heap and hash partition table nice example but one quick question why there is so big difference in query and current count?

could you please flash light on this?


Thanks


and Tom said...

the insert into the heap table put the rows in order (bulk insert into the table, minimizes the work of the insert). So, it filled block 1, then 2, then 3, then 4......

And it only needed to get block one once (current mode) and fill it up, and then move on.

The insert into the hash partitioned table was scattered all over the place. The first row might go on the last partition(current mode get), then the second on the first (current mode get), the third on the middle partition (current...) the fourth on the last partition(GET it again in current mode) and so on.


Rating

  (9 ratings)

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

Comments

A question.

Fh.syed, October 30, 2009 - 6:24 am UTC

Sir,
with all due respect , kindly tell me do you approve this ?

http://www.4shared.com/file/57249411/70e853d5/Expert_one-on-one_ORACLE_by_Tom_Kyte.html?s=1
I have found it to moments ago , could'nt let myself downloading it.

Regards
fhsyed
Tom Kyte
October 30, 2009 - 10:30 am UTC

well, it is given away for free with expert oracle database architecture.. It is a bit long in the tooth - it was 8.1.7 and before.

We are working on the update to expert oracle database architecture right now to bring it up to 11g Release 2.

A reader, October 30, 2009 - 10:36 am UTC

So If I am not wrong from your above reply, you are currently working on Expert database Architecture for 11 gr2

One quuick question when it will available to have one great book in my catalogue

Thanks
Tom Kyte
November 09, 2009 - 11:08 am UTC

Probably middle of 2010.

Solution for this

A reader, October 30, 2009 - 2:09 pm UTC

Hello Sir,

This was really a eye opener where insert are slower then select when using partition tables. But then what is the solution for this kind of things.
Tom Kyte
November 09, 2009 - 11:29 am UTC

... But then what is the solution for this kind of things....

to understand that it is a fact? I don't know why people think something is in need of a solution like this?

It necessarily takes longer to find out where to put a row away in a partitioned table.

But you do it, because you derive some overwhelming benefit from partitioning that far outstrips any of the negatives (and if you do not derive this benefit then you should ask "ok, why did I partition then?" not "how do I 'solve' a fact").


You might partition to make inserts go faster - it might take us longer to figure out where the row goes, but because we have 128 table segments and 128 index segments - we find we can insert faster overall since we have decreased contention by a factor of 128.

You might partition to make inserts go faster - because you use insert /*+ append */ to load a table, and you would like to do it from many sessions in parallel - but only one session can insert /*+ append */ into a segment at a time, so by having 128 segments - you can do 128 direct path inserts at a time - one each for each partition.

You might partition, letting inserts take longer, because the payback you get in partition elimination pays off 1,000,000 times over.

You might partition, letting inserts take longer, because the ability to purge the data with a single DDL command far outstrips any perceived performance hit.


and so on. As with all things in life, it is all about tradeoffs - understand HOW it works, understand what you need to accomplish and you can make the cost benefit analysis to determine if "you will probably be better off after doing something"

That is how to solve it.

Oleksandr Alesinskyy, November 01, 2009 - 12:17 pm UTC

I understand that this slowdown is applicable to massive inserts from single session, but would it hold true for inserts from many parallel sessions? Would not partitioning reduce contention and such improve overall performance?
Tom Kyte
November 09, 2009 - 12:10 pm UTC

see above...

the answer to you is "it depends"

If you did not have massive contention, then introducing something that would reduce contention at the expense of figuring out where to put the row - would not do anything for you.


so, "it depends"

it could make it go faster
it could make it go slower
it could make it go neither faster nor slower

Thank you sir,

Fh.syed, November 02, 2009 - 10:52 pm UTC

Thank you for your reply. I feel it better to read about the previous versions as well as the current ones. So i've downloaded the book and have installed respective database software for practice.

There are two more books there for download .

3,024 KB

Oracle Press - Effective Oracle Database 10g Security by Design.rar

effective oracle by design.djvu 53.4 MB

http://www.4shared.com/file/121899475/83a5dd98/effective_oracle_by_design.html?s=1

http://www.4shared.com/file/95577028/5f5a2953/Oracle_Press_-_Effective_Oracle_Database_10g_Security_by_Design.html?s=1

I will be greatfull if you can tell me they are given away for free as well or not .

Respect and Regards
Fh.syed
Tom Kyte
November 09, 2009 - 12:45 pm UTC

you do know you are stealing that material - right?


Helena Marková, November 06, 2009 - 1:29 am UTC


Sub partition on different location

Arindam Mukherjee, November 06, 2009 - 7:44 am UTC

Sir,

I am new in RAC-ASM environment. In Oracle 10g, I partitioned one table and sub partitions are stored on different disks. In Oracle 10g on ASM, how can I store sub partitions on different disk as Disk group is used in ASM environment instead of single disk.
Please help me.

Regards,
Tom Kyte
November 11, 2009 - 1:43 pm UTC

well first, you wouldn't want to. the entire goal of striping here (and has been for years) is to make it so you don't have to think about that.


Unless you can tell me "the amount of IO - both read and write - is exactly the same on all three partitions - each gets exactly 1/nth of the read/write activity" - it would be a bad idea to do what you did anyway.

Use a disk group
Add to it as many disks as you can
we'll stripe across them - achieving even IO to all physical disk in the disk group

Striping makes it so we can think less hard.



does that mean you cannot do what you asked to do (please do not, it would be a bad idea)? No, you can - you would just put each physical disk into a separate disk group - but that would be a really bad idea.

i am confused.

Fh.syed, November 09, 2009 - 11:05 pm UTC

I'm stealing ? . Sir here is a great confusion. I did not add these books on 4shared.com .

I was browsing it for database related course material and found these two. I have already asked for your permission for the book which was given away for free.

my intention was to bring it to your knowledge that there are the stolen version of your book on a public sharing website www.4shared.com. And inquired you if they are also given away for free . IF NOT ! you can take proper action against it.

I am just an individual, having no rights what so ever to complain to the website on your behalf. You sir are the author , you can claim your right if they are stolen and published on website.

I did not expect you to stand up against me . I have been greatly disappointed by your unexpected followup

I have been a consistent reader of your site and will continue .


respect and regards
Fhsyed
Tom Kyte
November 11, 2009 - 3:00 pm UTC

you are using a site that has illegally posted copywritten material. You are stealing the content.



you asked if they were being given away, I responded "it would be stealing"


Now I understand

fh.syed, November 11, 2009 - 6:13 am UTC

No sir, i am not downloading it :) as it is indeed stealing.