Re: ORACLE 8.1.5.i : Strange problem with SELECT COUNT(*) WHERE ... IS NULL

Hi.

 My guess thats Oracle optimizer is to blame.

 Try either ( preventing use of index )  "SELECT COUNT(*) FROM T_TRANSFERT WHERE etat || '' IS NULL"  or ( enforcing full table scan )
 "SELECT /*+ FULL ( T_TRANSFERT ) */ COUNT(*)     FROM T_TRANSFERT WHERE etat IS NULL"

 Good luck. Michael.

In article <7s9v4i$ouf$1_at_pollux.ip-plus.net>,   "Genoud" <tgenoud_at_cisel.ch> wrote:
> Hello,
>
> on Oracle 8.1.5i, the query :
> Select count(*) from t_transfert where etat is null;
> gives 0.
> It's not correct... there's a lot of rows where etat is null !!!!!!!!!
>
> The query :
> Select etat,id_pm from t_transfert where etat is null;
> gives the correct result...
>
> There an index on this table:
> CREATE INDEX IX_ETAT_PM ON T_TRANSFERT (ETAT, ID_PM) TABLESPACE
> TS_IX_TRANSFERT;
>
> If I drop the index, then the 'select count(*)' gives the correct
result !!
> Why the creation of this simple index affects the queries ??
>
> Can someone help me ?
> Is it a bug in Oracle 8.1.5i ?
> Thanks..
>
> Here's a paste of a SQL*plus session showing that ...
>
> ****************
>
> SQL*Plus: Release 3.3.4.0.0 - Production on Wed Sep 22 08:52:37 1999
>
> Copyright (c) Oracle Corporation 1979, 1996. All rights reserved.
>
> Connected to:
> Oracle8i Enterprise Edition Release 8.1.5.0.0 - Production
> With the Partitioning and Java options
> PL/SQL Release 8.1.5.0.0 - Production
>
> SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
>
> Session altered.
>
> SQL> desc t_transfert
> Name Null? Type
> ------------------------------- -------- ----
> ETAT VARCHAR2(10)
> ID_PM NOT NULL VARCHAR2(40)
> DATE_VAL NOT NULL DATE
> DATE_SEQ NOT NULL NUMBER(38)
> CD_SAISON VARCHAR2(2)
> DATE_VAL_FIN DATE
> CD_SAISON_FIN VARCHAR2(2)
> GENRE_VAL CHAR(1)
> TYPE_VAL CHAR(1)
> TYPE_INTRO CHAR(1)
> VALIDITE NUMBER
> VAL NUMBER
> VAL_ALPHA VARCHAR2(120)
> STATUS VARCHAR2(10)
> SYST_ORIG NOT NULL VARCHAR2(20)
> TEXT_ERREUR VARCHAR2(200)
> DATE_DERN_ETAT NOT NULL DATE
>
> SQL> select count(*) from t_transfert where etat is null;
>
> COUNT(*)
> ---------
> 0
>
> SQL> select etat,date_dern_etat from t_transfert where etat is null;
>
> ETAT DATE_DERN_ETAT
> ---------- -------------------
> 21.09.1999 15:20:20
> 21.09.1999 15:19:51
> 21.09.1999 15:19:28
> 21.09.1999 15:19:09
> 21.09.1999 15:18:33
> 21.09.1999 15:18:10
> 21.09.1999 15:17:36
> 21.09.1999 15:17:16
> 21.09.1999 15:16:47
> 21.09.1999 15:16:32
> 21.09.1999 15:15:50
> 21.09.1999 15:15:28
> 21.09.1999 15:14:59
> 21.09.1999 15:14:30
> 21.09.1999 15:14:04
> 21.09.1999 15:13:39
> 21.09.1999 15:13:07
> 21.09.1999 15:12:37
> 21.09.1999 15:12:00
> 21.09.1999 15:11:20
> 21.09.1999 15:10:39
>
> ETAT DATE_DERN_ETAT
> ---------- -------------------
> 21.09.1999 15:10:07
> 21.09.1999 15:09:35
> 21.09.1999 15:09:04
> 21.09.1999 15:08:46
> 21.09.1999 15:07:31
> 21.09.1999 15:07:04
> 21.09.1999 15:06:39
> 21.09.1999 15:06:05
> 21.09.1999 15:05:38
> 21.09.1999 15:05:16
> 21.09.1999 15:04:45
>
> etc.....
>
>

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 22 1999 - 15:30:22 CDT