Re: DBA_TEMP_FILES view's zany behaviour ????????????

Spendius wrote:
>
> Hi,
> I'd just like to know if I'm getting crazy or what ? Every time I use
> more than one member in a WHERE .. IN <list> clause, Oracle returns
> me nothing:
>
> > SQL> select file_name,file_id,autoextensible
> > from dba_temp_files;
> >
> > FILE_NAME FILE_ID AUT
> > ----------------------------------- ---------- ---
> > /u02/oradata/WW3D/S971_T1_01.dbf 1 YES
> > /u03/oradata/WW3D/SC21_T1_01.dbf 2 YES
> > /u04/oradata/WW3D/sartw_t1_01.dbf 3 YES
> > /u01/oradata/WW3D/scperf_t1_01.dbf 4 YES
> > /u02/oradata/WW3D/scperf_t1_02.dbf 5 YES
> > /u03/oradata/WW3D/scperf_t1_03.dbf 6 NO
> >
> > 6 row(s) selected.
> >
> > SQL> select file_name,file_id,autoextensible
> > from dba_temp_files where file_id in (4,5,6);
> >
> > No rows selected.
> >
> > SQL> select file_name,file_id,autoextensible
> > from dba_temp_files where file_id in (4);
> >
> > FILE_NAME FILE_ID AUT
> > ----------------------------------- ---------- ---
> > /u01/oradata/WW3D/scperf_t1_01.dbf 4 YES
> >
> > SQL> select file_name,file_id,autoextensible
> > from dba_temp_files where autoextensible in ('YES');
> >
> > FILE_NAME FILE_ID AUT
> > ----------------------------------- ---------- ---
> > /u02/oradata/WW3D/S971_T1_01.dbf 1 YES
> > /u03/oradata/WW3D/SC21_T1_01.dbf 2 YES
> > /u04/oradata/WW3D/sartw_t1_01.dbf 3 YES
> > /u01/oradata/WW3D/scperf_t1_01.dbf 4 YES
> > /u02/oradata/WW3D/scperf_t1_02.dbf 5 YES
> >
> > SQL> select file_name,file_id,autoextensible
> > from dba_temp_files where autoextensible in ('YES','NO');
> >
> > No rows selected.
> >
> > SQL> select file_name,file_id,autoextensible
> > from dba_temp_files where autoextensible in ('NO');
> >
> > FILE_NAME FILE_ID AUT
> > ----------------------------------- ---------- ---
> > /u03/oradata/WW3D/scperf_t1_03.dbf 6 NO
> >
> > SQL> select file_name,file_id,autoextensible
> > from dba_temp_files where autoextensible in ('YES','NO');
> >
> > No rows selected.
> >
> >
>
> Am I missing something or there's really something bizarre ???
>
> Thanks.

Sounds like a bug...I get the same results under 8.1.7 Win2K.

The explain plans are different for "in (list)" and "in (singleitem)"

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."