OraFAQ Forum: Performance Tuning » Effect of size of composite primary key on IOT performance.

Home » RDBMS Server » Performance Tuning » Effect of size of composite primary key on IOT performance.

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Effect of size of composite primary key on IOT performance. [message #302229]

Mon, 25 February 2008 00:19

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..?

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

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..?

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

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?

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

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

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?

Report message to a moderator

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

Michel Cadot
Messages: 68665
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

Report message to a moderator

Previous Topic:	Tune the SQL Qury
Next Topic:	Oracle Slows down.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jun 27 19:47:54 CDT 2024