| Index on normal and random column data [message #411143] |
Thu, 02 July 2009 02:59  |
prachij593
Messages: 266
Registered: May 2009
|
Senior Member |
|
|
I have a table emp which contains data (emp id ) randomly inserted.
And another table emp1 which contains data (emp id ) in ordered way.
Now if I create index on emp(empid) and emp1(empid) would there be any performance difference? what difference would be there?
How range scan can be handled for random table emp1.
The table has say 100000 rows and emp id is primary key for both the tables
Many Thanks....
[Updated on: Thu, 02 July 2009 03:13] Report message to a moderator |
|
|
|
| Re: Index on normal and random column data [message #411196 is a reply to message #411143] |
Thu, 02 July 2009 07:12  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
| Quote: | And another table emp1 which contains data (emp id ) in ordered way.
|
No You don't. Data in heap tables does not have an inherent order. No ifs, no buts, no order.
| Quote: | Now if I create index on emp(empid) and emp1(empid) would there be any performance difference? what difference would be there?
|
Why not test it?
| Quote: | How range scan can be handled for random table emp1.
The table has say 100000 rows and emp id is primary key for both the tables
|
Don't know what you're asking here.
|
|
|
|
| Re: Index on normal and random column data [message #411212 is a reply to message #411196] |
Thu, 02 July 2009 08:09 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
| Quote: | | Quote: | And another table emp1 which contains data (emp id ) in ordered way.
|
No You don't. Data in heap tables does not have an inherent order. No ifs, no buts, no order.
|
Not entirely true - the data in the table has no order, but the data stored in the blocks can be ordered - that's what the clustering factor for an index represents: the amount of order of the rows in the table based on the values of the index.
It can make a noticalbe difference for a range scan - if all the rows in the range are on a small set of blocks, then most of the I/O will be from the buffer cache, whereas if the rows in the range as scattered randomly across the whole table then most of the io will be physical disk reads.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
