Quotes not mandatory for String condition ?

From: Zboub <zboub_at_softhome.net>
Date: 12 Jul 2005 05:12:37 -0700
Message-ID: <1121170357.678382.196060@g14g2000cwa.googlegroups.com>

Hello Guys,
I would like to have your comments on this strange behaviour.

Oracle Server used : 8.1.7.0.0 on a ALPHA (VMS) server.

A table and an index are defined like this : CREATE TABLE POINTAGE (

	DATMAJ              DATE            ,
	HEURMAJ             VARCHAR2  (08)  ,
	MATRICUL            VARCHAR2  (20)  NOT NULL,
	PERIODE             VARCHAR2  (10)  ,
	PTJDEB              DATE            ,
	PTJFIN              DATE            );

CREATE UNIQUE INDEX POINTAGE_IDX ON POINTAGE (MATRICUL); The table contains about 500.000 rows.

The MATRICUL column contains data composed only by numbers, like "01234567" or "74125896".

When I made a select order with the quotes, the result is given immediatly
Select * from POINTAGE where MATRICUL = '45612378'; ---> immediat result.

When I made the select without the quotes, I have to wait about 10 seconds to have the result.
Select * from POINTAGE where MATRICUL = 45612378; ---> result after a long wait.

It seems that the select without the quote assumes the value to check is a numeric value, so existing index is not used, so the table is read sequentially, and the result is very slow to obtain when 1000 different select have to be done.

The good way to solve the problem is to change the select orders and add the quotes, but the select without the quotes are done by a program that I can't change.

My only way to solve the problem is to change something on the database.

So my questions are :
- why Oracle accept the select without the quotes although the column
MATRICUL is defined as VARCHAR2 ?

• is there anything to do to say the Oracle server to considere any condition on column MATRICUL as a STRING condition, no matter if there is or not the quotes ?
• Or is it possible to create a second index on table POINTAGE which will assume the numeric conditions on column MATRICUL ? Something like this : CREATE UNIQUE INDEX POINTAGE_NUM_IDX ON POINTAGE (TO_NUMBER(MATRICUL));

Any advise would be appreciated, thanks in advance :)

Patrick. Received on Tue Jul 12 2005 - 07:12:37 CDT