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

The problem was solved.
But I perform some ANALYZE statements on the table and the index. After that, the problem reappear, the select count gives 0 !

Before the ANALYZE, the explain plan indicates a full table acces, but after it becomes a range scan on the index...

I think your right, it's a problem with the optimizer... I try your first select preventing use of index... and it works.

To solve this problem, i will alter the table column 'etat' with a default value '0' for example...
Thanks

michael_bialik_at_my-deja.com a écrit dans le message <7sbe8v$r6b$1_at_nnrp1.deja.com>...
>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 Thu Sep 23 1999 - 04:27:38 CDT