Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Query from Forms not using Index
"Mark" <maiden.mark_at_gmail.com> wrote in message
news:1113899744.185521.136890_at_g14g2000cwa.googlegroups.com...
> Hi, The following query is produced by an Oracle Forms application :
>
> SELECT {multiple_columns)
> FROM gg_inbox
> WHERE ind_order_id = :1
> ORDER BY entered_gg_date DESC;
>
> this query does a full table scan of the table in question when there
> is an index associated with ind_order_id column. When I run the query
> manually in SQLPlus like below :
>
> SELECT {multiple_columns)
> FROM gg_inbox
> WHERE ind_order_id = '111236441'
> ORDER BY entered_gg_date DESC;
>
> the index is used and the result comes back instantly. Does anybody
> know why it is not using the index in the Forms application? Any help
> would be greatly appreciated.
>
> Mark.
>
One possibility to check is whether the form has the field where ind_order_id is entered defined as a character type or a numeric type. If it is numeric, the optimizer would have to do an implicit coercion on the column, which would disable the index.
viz:
SELECT {multiple_columns) FROM gg_inbox WHERE to_number(ind_order_id) = :1 ORDER BY entered_gg_date DESC;
You should see the same effect if you change your SQL*Plus example to eliminate the quotes:
SELECT {multiple_columns)
FROM gg_inbox WHERE ind_order_id = 111236441 ORDER BY entered_gg_date DESC;
-- Regards Jonathan Lewis http://www.jlcomp.demon.co.uk/faq/ind_faq.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/seminar.html Public Appearances - schedule updated April 5th 2005Received on Tue Apr 19 2005 - 04:38:50 CDT