Re: altering table to make it "organized index"

In article <20000415.5432700_at_noname.nodomain.nowhere>,   Jerry Gitomer <jgitomer_at_erols.com> wrote:
>
> If you include the column you wish to order by in your where
 clause=20
> it MAY help. You can use an impossible value for example:
>
> WHERE sort_column < <min_value>

Just tried it:

select user_id,date_column
from nm_t
where user_id=21 order by date_column desc;

Still does a full table scan. Damn! :(

Thanks,

Otis

> >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
>
> On 4/14/00, 2:52:10 PM, Otis Gospodnetic <otis_at_my-deja.com> wrote=20
> 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=3D=
 20
> > > more recent versions of Oracle) you have to analyze your tables on
 a=3D20
> > > regular basis. Also, you must analyze an index after it is
 created
 or=3D20
> > > it won't be used. The command is:
> > >
> > > ANALYZE TABLE <table_name> ESTIMATE STATISTICS; or
> > > ANALYZE INDEX <index_name> COMPUTE STATISTICS; =3D20
 

> > 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=3D21; -- this uses index
 

> > select user_id from nm_t where user_id=3D21 order by date_column
 desc;=  

> > This last statement does this:
 

> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=3DCHOOSE (Cost=3D56 Card=3D397
 By=
 tes=3D22
> > 232)
 

> > 1 0 SORT (ORDER BY) (Cost=3D56 Card=3D397 Bytes=3D22232)
> > 2 1 TABLE ACCESS (FULL) OF 'MY_T'' (Cost=3D49 Card=3D397
> > Bytes=3D22232)
 

> > 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,=3D20
> > > but I have noticed that if you do it just after creating the index
 it=3D20
> > > is quite fast.
> > >
> > > >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<<
> > >
> > > On 4/13/00, 2:32:38 PM, Otis Gospodnetic <otis_at_my-deja.com>
 wrote=3D=
 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"=3D  

> > > > > >clause with CREATE TABLE. I didn't know I would need that in
 order =3D
 to
> > > > > >create indexes on those tables.
> > > > > >Well, I need to create indexes on those tables now and I'm
 wonderin=3D
 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=3D3D1;
> > > > > >
> > > > > >The PK index is not being used, but if I do:
> > > > > > SELECT a PK_column FROM foo WHERE PK_column=3D3D1;
> > > > > >
> > > > > >then it is.
> > > > > >Is this normal?
> > > > > >I'm using WHERE pk_column=3D3D1 in both cases so I would
 think
 that t=3D
 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=3D  

> > > > 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=3D  

> > > > able to create indexes without this)
 

> > > > If this is really required, is there a way to alter a table to
 allow=3D20
 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=3D  

> > > > I didn't CREATE TABLE with "organzied index".
 

> > > > Any help would be appreciated.
 

> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
>
>

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