Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Index Ordered Table or clustering

Index Ordered Table or clustering

From: <deja_at_2bytes.co.uk>
Date: 15 Mar 2007 06:49:17 -0700
Message-ID: <1173966557.928008.46160@p15g2000hsd.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US