Re: altering table to make it "organized index"

From: Otis Gospodnetic <otis_at_my-deja.com>
Date: 2000/04/14
Message-ID: <8d7st2$371$1@nnrp1.deja.com>#1/1

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=20
> regular basis. Also, you must analyze an index after it is created
or=20
> it won't be used. The command is:
>
> ANALYZE TABLE <table_name> ESTIMATE STATISTICS; or
> ANALYZE INDEX <index_name> COMPUTE STATISTICS; =20

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

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

This last statement does this:

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=56 Card=397 Bytes=22
          232)

   1    0   SORT (ORDER BY) (Cost=56 Card=397 Bytes=22232)
   2    1     TABLE ACCESS (FULL) OF 'MY_T'' (Cost=49 Card=397

Bytes=22232)

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,=20
> but I have noticed that if you do it just after creating the index
it=20
> 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=3D1;
> > > >
> > > >The PK index is not being used, but if I do:
> > > > SELECT a PK_column FROM foo WHERE PK_column=3D1;
> > > >
> > > >then it is.
> > > >Is this normal?
> > > >I'm using WHERE pk_column=3D1 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=20
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.

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