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: Query from Forms not using Index

Re: Query from Forms not using Index

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 19 Apr 2005 09:38:50 +0000 (UTC)
Message-ID: <d42jja$npr$1@hercules.btinternet.com>

"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 2005
Received on Tue Apr 19 2005 - 04:38:50 CDT

Original text of this message

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