Re: altering table to make it "organized index"

In article <20000413132608.22777.00000274_at_ng-ct1.aol.com>,   sanrenkur_at_aol.com (Sanrenkur) wrote:
> >OTIS WROTE:
 
> >Hi,
> >
> >I've created a bunch of tables without specifying "organized index"
> >clause with CREATE TABLE. I didn't know I would need that in order to
> >create indexes on those tables.
> >Well, I need to create indexes on those tables now and I'm wondering
 if
> >it can be done or is it too late?
> >
> >I tried making tables "organized index" from Sql*plus:
> > ALTER TABLE foo MOVE PCTTHRESHOLD 50;
> >
> >This didn't work. Is this the right syntax?
> >Is there any way to make them "organized index"?
> >
> >Also, a related question:
> >I've noticed that if I do:
> > SELECT non_PK_column FROM foo WHERE PK_column=1;
> >
> >The PK index is not being used, but if I do:
> > SELECT a PK_column FROM foo WHERE PK_column=1;
> >
> >then it is.
> >Is this normal?
> >I'm using WHERE pk_column=1 in both cases so I would think that the
> >index would be used both times.
> >
> >Thanks,
> >
> >Otis
> >
> >
> Hi Otis
> CREATE INDEX idx_name ON table_name(column_name)
> hth
> s

That is the first thing I tried, but even after creating an index my queries were not using it. After looking through some Oracle books it seemed that if one wants to be able to create indexes for columns in a table one has to include "organized index" when creating that table.

Is this correct? Do I really have to add that to CREATE TABLE clauses if I want to be able to make indexes? (sounds crazy that I wouldn't be able to create indexes without this)

If this is really required, is there a way to alter a table to allow me to create indexes?

I _can_ create indexes even now, I see them in 'user_indexes' table, but my queries are not using them...and I figured that that is because I didn't CREATE TABLE with "organzied index".

Any help would be appreciated.

Thanks,

Otis

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Apr 13 2000 - 00:00:00 CDT