Re: performance problem

In article <35696D47.AA701111_at_delphinsys.de>, "Klaus Hümmeler" <khuemmeler_at_delphinsys.de> wrote:

> Hi,
>
> I'm testing the performance of an Oracle8 evaluating version. Therefor I
> created a table with nearly 20000 rows containing 180 MB of data (number
> and varchar2).
> I made an primary index of one Column (varchar2) wich contains the
> entry-number.
>
> Every question like e.g. "select * from MaschDat where number = 100"
> takes
> nearly 2 Minutes until I get the selected row(s).
>
>snip

Since entry_number is a varchar2 column, you need to compare it to a character literal i.e. where [entry_]number = '100'

When comparing characters and numbers, Oracle must convert the [var]char to number format so that, for example, both '00100' and '100.00' are equal to 100.

Hence, the query is converted internally to the form "where to_number(char_col) = numeric_literal'

Hence, the index on the [var]char column cannot be used.

HTH --
Chrysalis Received on Tue May 26 1998 - 08:02:12 CDT