Home » RDBMS Server » Performance Tuning » Effect of size of composite primary key on IOT performance.
Effect of size of composite primary key on IOT performance. [message #302229] Mon, 25 February 2008 00:19 Go to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Hello sir...

I am doing some performance analysis of IOT.
I have one heap organized table having more than 2 lakh rows.
I made another table from above one having same number and type of constraints , indexes and records..
Now whenever i try any kind of queries, always IOT takes more time to execute query than the HOT.
(i tried select *, count(*), sum(numeric_filed), where with primary key columns, where with other columns etc)

I have checked the chaining and migration.. it is not there..

The primary key is composite of 7 columns..
This is heavily loaded OLTP table used in the application..

So i want to know whether the number of columns in composite primary key affects the performance of IOT?
What are the other aspects affecting the performance of IOT..?
Re: Effect of size of composite primary key on IOT performance. [message #302237 is a reply to message #302229] Mon, 25 February 2008 00:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So i want to know whether the number of columns in composite primary key affects the performance of IOT?

What about making some tests and posting your results here?
It would be helpful.

Regards
Michel

[Updated on: Mon, 25 February 2008 00:41]

Report message to a moderator

Re: Effect of size of composite primary key on IOT performance. [message #302258 is a reply to message #302237] Mon, 25 February 2008 01:27 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

yes michel..
I have made some tests..

during those test, timming was on.. so i am posting the real time taken by the query for executing..

Query type: count (*)
number or rows returned: 1404227
time taken by IOT: 16437 (first time), 9547 (second time)
time taken by HOT: 516 (first time), 500 (second time)

Query type: sum(pcs)
result set: same in both cases..
time taken by IOT: 7688
time taken by HOT: 4938

Query type: sum(pcs) , group by 3 leading fields of primary key.
result set: same in both cases..
time taken by IOT: 17782
time taken by HOT: 14360

Query type: distinct field.
result set: same in both cases..
time taken by IOT: 8907
time taken by HOT: 4422

Michel, i read the documentation and other materials from net.
All are saying same things that IOT will give better performance if accessed by primary key.
But what in case where there is no where clause..
More over, is index skip scan concept there for IOT, if we use some of the columns of primary key in where clause..?




Re: Effect of size of composite primary key on IOT performance. [message #302261 is a reply to message #302258] Mon, 25 February 2008 01:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well, it seems you already have the answer. Of course, IOT is made for index-like accesses so it is less performant for full scan accesses.

Quote:
is index skip scan concept there for IOT, if we use some of the columns of primary key in where clause..?

Once again, you can make some tests but yes all index access paths are available for IOT.

Regards
Michel

[Updated on: Mon, 25 February 2008 02:13]

Report message to a moderator

Re: Effect of size of composite primary key on IOT performance. [message #302268 is a reply to message #302261] Mon, 25 February 2008 01:59 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

okay michel..
thanks..

and what about the size of primary key?
Does the number of fields in primary key affects the IOT performance..?

One other confusion is :
If we see at the structure of IOT, it stores both primary index and other data togather. then why it takes more time for full table scan than HOT?
Re: Effect of size of composite primary key on IOT performance. [message #302271 is a reply to message #302268] Mon, 25 February 2008 02:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
For key size, the answer for IOT is the same as for index.

Quote:
why it takes more time for full table scan than HOT?

It has a complex structure (the index one) and requires more blocks. Unlike HOT where all blocks are data ones (but a couple for headers), some IOT blocks are branch/node blocks just for the B*Tree structure.

Note you can have better performances if you smartly use of overflow (and don't need all columns).

Regards
Michel
Re: Effect of size of composite primary key on IOT performance. [message #302284 is a reply to message #302271] Mon, 25 February 2008 03:21 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

All right michel Smile
Thanks a lot..

so can i conclude that..
for queries which need full table scan like the one which uses sum(filed), count(*), distinct, select * etc, would always be less performant than HOT?
Re: Effect of size of composite primary key on IOT performance. [message #302292 is a reply to message #302284] Mon, 25 February 2008 03:46 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not always.
As I said, if you smartly use overflow you can have better performances with IOT (except for select *).

Regards
Michel
Previous Topic: Tune the SQL Qury
Next Topic: Oracle Slows down.
Goto Forum:
  


Current Time: Thu Dec 08 01:58:13 CST 2016

Total time taken to generate the page: 0.05221 seconds