Re: altering table to make it "organized index"

Two things.

1. Are you sure that the full table scan is bad. How many blocks does the data in the table fit into. If it is relatively few then a FTS may be better anyway. Assuming that you are running Oracle 8.05 or above then the cost based optimiser is *generally* pretty efficient. (CUE host of replies telling me how wrong I am).
2. You appear to be suffering from a misunderstanding of what 'organisation index' means. This statement creates an Index Organised table. This is a different fish altogether to a table and an index.
3. OK so I can't count... If you really must have indexes used alter the optimser to the rule based optimiser. (or delete all your statistics)

HTH

--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Otis Gospodnetic" <otis_at_my-deja.com> wrote in message
news:8dalua$cb$1_at_nnrp1.deja.com...


> 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.