Re: Index Help !

A copy of this was sent to Jorge Torralba <Jorge.Torralba_at_Intel.Com> (if that email address didn't require changing) On Wed, 25 Aug 1999 08:48:20 -0700, you wrote:

>I have transitioned from Informix to Oracle and am having some
>difficulties in performing some fundamental db tasks in my new
>environment. I need to create a clustered index on a table. Maybe its
>just lack of knowledge on my part. But, when I mention this to anyone
>they seems to confuse a clustered index with an Index on a cluster. Is
>it possible to create a clustered index ( an index which reorder the
>physical table in the order that the index designates.) on a table
>using version 8 ? If so, what is the syntax ?
>
>Thanks.

Oracle does not have a clustered index Ala Informix.

We have clusters and a cluster key index -- but this is to store >1 table physically together (eg: rows from the emp and dept table for a given deptno would be stored in the same exact block -- prejoined).

So, we do not have exactly a cluster index like you are looking for.

We do have index organized tables (an table stored in a index structure). It is similar to what you are looking for (without the extra overhead. As i understand clustered indexes in Informix and Sybase, they have something like a 110% overhead -- the entire row is stored in the index as well as the table and the index becomes the table itself for all intents and purposes).

If you are using Oracle8.0 and have no need for additional indexes on the table, this might apply. In Oracle8i, release 8.1 you can add other indexes to index organized tables as well.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 25 1999 - 11:39:47 CDT