Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Index Ordered Table or clustering
hi,
I should say that I am mainly a SQL Server person and am entering a strange world. I do not know much about tuning Oracle databases.
I have a table structured like this:
deleted char(1)
transNum int
objectId char(32)
some other columns
The table retains history of an object by inserting a new row for each change, thus a specific object could have hundreds of tuples, each with a different transNum.
Our application does 2 types of select:
1) An entire section of current rows:
select * from table t1 where transNum = (select max(transNum) from
table t2 where t2.id = t1.id and transNum <= @trans) and deleted =
'F'
2) A select of changes since last read (I guess this needs to be seen as potentially a) with a wide range and b) with a narrow range): select * from table t1 where transNum = (select max(transNum) from table t2 where t2.id = t1.id and transNum > @oldTrans and transNum <= @trans)
Currently it is doing a Full Table Access on the second select despite 2 indexes (one on transNum, objectId the other on objectId, transNum). Bearing in mind that transNum is always incrementing (but that multiple objectIds could be inserted with the same transNum) - should I use an Index Ordered Table (I currently use a Clustered Index in the SQL Server version of the same table)? If so, should it be on transNum, objectId? Would clustering (whatever that is exactly) be a better solution or something entirely different?
thanks
Phil
Received on Thu Mar 15 2007 - 08:49:17 CDT