Re: altering table to make it "organized index"

Assuming you are using the Cost Based Optimizer (the default in the more recent versions of Oracle) you have to analyze your tables on a regular basis. Also, you must analyze an index after it is created or it won't be used. The command is:

	ANALYZE TABLE <table name> ESTIMATE STATISTICS;  or
	ANALYZE INDEX <index name> COMPUTE STATISTICS;  

You can use either estimate or compute. Compute takes longer to run, but I have noticed that if you do it just after creating the index it is quite fast.

>>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<

On 4/13/00, 2:32:38 PM, Otis Gospodnetic <otis_at_my-deja.com> wrote regarding 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 wonderin
 g
 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

> > >
> > >The PK index is not being used, but if I do:
> > > SELECT a PK column FROM foo WHERE PK column

> > >
> > >then it is.
> > >Is this normal?
> > >I'm using WHERE pk column
in both cases so I would think that t
 he
> > >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 Fri Apr 14 2000 - 00:00:00 CDT