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 -> Re: Index Ordered Table or clustering

Re: Index Ordered Table or clustering

From: <deja_at_2bytes.co.uk>
Date: 15 Mar 2007 08:30:40 -0700
Message-ID: <1173972640.199300.149190@n59g2000hsh.googlegroups.com>


On 15 Mar, 14:19, "Mark D Powell" <Mark.Pow..._at_eds.com> wrote:
> 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 --

I know about the locking and read consistency. In this particular case it ends up being the same because write transactions are all serialized. Readers only ever read where transNum <= (the last written transNum), and because no row is ever updated, only inserted, then these rows can be read in both systems without being locked out. transNum is effectively the sequence field you were talking about.

The incremental read could be using a large or a small range - depends when the client last updated. Not sure how in Oracle to make sure statistics are always up to date - with SQL Server it's just a flag in the database properties. I guess the full access is required because it is not a covered index whereas an IOT would be.

So I think an IOT is probably worth a shot on transNum, objectId...... What is the statement to create one?

There are cases when, due to the application design, thousands of rows can get inserted in a batch. I guess this could throw the statistics - how to ensure they are kept up to date automatically?

thanks
Phil Received on Thu Mar 15 2007 - 10:30:40 CDT

Original text of this message

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