Re: altering table to make it "organized index"

        If you include the column you wish to order by in your where clause it MAY help. You can use an impossible value for example:

        WHERE sort column < <min value>

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

On 4/14/00, 2:52:10 PM, Otis Gospodnetic <otis_at_my-deja.com> wrote regarding Re: altering table to make it "organized index":

> In article <20000414.4581400_at_noname.nodomain.nowhere>,
> Jerry Gitomer <jgitomer_at_erols.com> wrote:
> > Assuming you are using the Cost Based Optimizer (the default in the 20
> > 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;
 

> I just did that and now I can get one query to use the index, but if I
 

> add 'order by column that is also indexed desc' then again full table
> scan is performed.
 

> This is what I did:
 

> create index my i on my t(FK column, date column desc);
> analyze table my t compute statistics;
> analyze index my i compute statistics;
 

> select user id from my t where user id!; -- this uses index
 

> select user id from nm t where user id! order by date column desc;

> This last statement does this:
 

> Execution Plan
> ----------------------------------------------------------
> 0 SELECT STATEMENT OptimizerOOSE (CostV Card97 By
 tes"
> 232)
 

> 1 0 SORT (ORDER BY) (CostV Card97 Bytes"232)
> 2 1 TABLE ACCESS (FULL) OF 'MY T'' (CostI Card97
> Bytes"232)

> Any idea why?
> I can't think of anything else to do...am I skipping some important
> step?
 

> Thanks,
 

> Otis

> > 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 20
> > 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=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 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 tabl
 e.  

> > > 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' tabl
 e,
> > > 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.

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