Re: Querying a number column.

From: Matthias Rogel <rogelREMOVE_at_THISweb.de>
Date: Fri, 24 Jan 2003 08:01:21 +0100
Message-ID: <b0qoc2$pto94$1@ID-86071.news.dfncis.de>

sorry sybrand, but it is not true
(at least I checked it on 9.x):

it is implicitly converted to
column = to_number('<hardcoded numeric literal>'),

where the index can be used
(and actually is, as far as explain plan tells me)

when I hardcode a NON-numeric-literal instead, I get FEHLER in Zeile 1:
ORA-01722: Ung³ltige Zahl
,

from which U also can conclude that the (internal) conversion to_number fails.

matthias

Sybrand Bakker wrote:
> On Fri, 24 Jan 2003 00:56:13 -0500, "Anurag Varma" <avdbi_at_hotmail.com>
> wrote:
>
>

>>why would the index be ignored?
>>I don't think so. Though I agree about the type conversion.

>
>
>
> BECAUSE of the type conversion.
> The query is silently transformed into
> to_char(column) = '<hardcoded numeric literal>'
>
> left hand side of a where clause function: NO index.
>
>
> Sybrand Bakker, Senior Oracle DBA
>
> To reply remove -verwijderdit from my e-mail address
Received on Fri Jan 24 2003 - 01:01:21 CST