| 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..?
|
|
|
|
|
|
| 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..?
|
|
|
|
| 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: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
