Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index is not used, though HINT specified

Re: Index is not used, though HINT specified

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Fri, 30 Apr 2004 23:02:00 GMT
Message-ID: <IVAkc.63324$G_.24385@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 Received on Fri Apr 30 2004 - 18:02:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US