Re: Index is not used, though HINT specified

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:IVAkc.63324$G_.24385_at_nwrddc02.gnilink.net...
>
> "dbdude" <dbdude08_at_yahoo.com> wrote in message
> news:f41b54eb.0404301317.8da6a40_at_posting.google.com...
> > I have a table named "org_cols" with a index "org_cols" on a column
> > "COL13". Table is analyzed. I am writing following query and its doing
> > full table scan. But if I write second query, it uses index. I know
> > here that all the values in column "COL13" is greater then zero so I
> > am safe, but is there a way to force the query without WHERE clause to
> > use an index.
> >
> > SELECT /*+ INDEX (org_cols org_cols)*/ COUNT(*)
> > FROM org_cols;
> > Does full table scane.
> >
> > SELECT COUNT(*)
> > FROM org_cols
> > WHERE col13> 0;
> > Uses the index and return result in one tenth of the time taken by
> > previous query.
> >
> > Thanks,
>
> Check if the column on which the index is based is a not null column.
> If its not a "not null" column then you should know that index entries exclude null entries.
> That would prevent oracle from using the index unless it can be absolutely sure that what you are
> counting is all present in the index.
>
> Try SELECT /*+ INDEX (org_cols org_cols)*/ COUNT(*)
> FROM org_cols where the_indexed_column is not null
> /
>
> Anurag
>
>

.. and btw: your second clause works because col13 > 0 (assuming col13 is the indexed column) makes it obvious that you are looking to count not null / indexed entries.

Anruag Received on Fri Apr 30 2004 - 18:03:59 CDT