Home » RDBMS Server » Performance Tuning » Index on normal and random column data (Oracle 9.2.0.3)
Index on normal and random column data [message #411143] Thu, 02 July 2009 02:59 Go to next message
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 Go to previous messageGo to next message
pablolee
Messages: 2834
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 Go to previous message
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.
Previous Topic: 9i cbo mode first_rows
Next Topic: Statistics on Empty Partition
Goto Forum:
  


Current Time: Sun Dec 04 00:21:24 CST 2016

Total time taken to generate the page: 0.04832 seconds