Re: Index Ordered Table or clustering

On Mar 15, 9:49 am, d..._at_2bytes.co.uk wrote:
> 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

Oracle <> SQL Server
Oracle and SQL Server have different locking and read consistency models. Understanding the differences here is the first step to porting applications between the two.

Oracle clustering is the storing of multiple tables within the same data block hence clustered tables have a cluster_name and no tablespace_name since the cluster is the logical object that is assigned a tablespace.

A SQL Server clustered table relates more closely to an Oracle IOT, indexed orgranized table. An IOT is a table where the row data is stored in a btrieve index structure. Inserts and updates against key columns cause the data to move. (It is an index after all and keys must fit in specific logical locations by value)

If you are a DBA you need to read the Concepts and DBA Administration manual before you do anything with Oracle. If you are a developer you need to read the Applications Developers Guide - Fundamentals.

Make sure the table statistics are current. If the range is never that large in absolute values you might consider using a hint to guide the CBO into using the index instead of full scanning if updating the statistics might help.

Normally we go after history using the same PK as the original data plus a sequence or date field that identifies the change time. We often also store the username who made the change. So I am not sure about your d3sign but it is hard to tell from the information in posts what is really being done.

HTH -- Mark D Powell -- Received on Thu Mar 15 2007 - 09:19:51 CDT