Re: Inconsistant query results - Please help

Thanks Niall.

> I don't believe this to be true (at least in 9i). At least it ought not to
> be true ISTM that the results described are just wrong and if repeatable
are
> a bug. See demo below (9.0.1). If the OP can reproduce I suggest logging a
> TAR.

Yes it is repeatable. I was trying to figure out what was happening for quiet some time before posting this and it happened every time (and still is happening).

> IIRC there were some problems with concatenated indexes and NULLs around
> 8.1.5. The actual version might help.

Yes it's 8.1.5

Regards,
Bliss

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:3f07dcbe$0$18495$cc9e4d1f_at_news.dial.pipex.com...
> "Sybrand Bakker" <gooiditweg_at_sybrandb.demon.nl> wrote in message
> news:j8vdgvgmv23m6f62r6n3erhjn17d87jo23_at_4ax.com...
>
> > >My queries are
> > >1)According to the documentation (as I understand it), using a "is
null"
> in
> > >the select should do a full table scan. So how come it's doing an
index
> > >scan?
> > >
> >
> > You have a concatenated index. You only have a criterion on the
> > non-leading column *and* you must be using the Rule Based Optimizer
> > (which you shouldn't do): the index is present, so the index will be
> > used. If you would query on both columns the index would likely not be
> > used.
>
> I don't believe this to be true (at least in 9i). At least it ought not to
> be true ISTM that the results described are just wrong and if repeatable
are
> a bug. See demo below (9.0.1). If the OP can reproduce I suggest logging a
> TAR.
>
> > >2)Whatever the scan methodology used, why is it showing an incorrect
> output
> > >after adding the index? Does this have to do anything with any
> optimization
> > >parameters or something?
>
> IIRC there were some problems with concatenated indexes and NULLs around
> 8.1.5. The actual version might help.
>
> anyway hopefully the demo below illustrates that correct results should be
> obtained even with a concatenated index and no stats, hence RBO.
>
> SQL> create table truck_journey
> 2 (tj_date,
> 3 tj_truck_id,
> 4 tj_truck_no,
> 5 tj_km)
> 6 as
> 7 select
> 8 last_ddl_time,
> 9 object_id,
> 10 object_name,
> 11 object_id*10
> 12 from dba_objects;
>
> Table created.
>
> SQL> insert into truck_journey(tj_date,tj_truck_no,tj_km)
> 2 select sysdate,object_name,object_id*203 from dba_objects;
>
> 31220 rows created.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> set autot on explain ;
> SQL> select max(distinct(tj_truck_no) from truck_journey where tj_truck_id
> is null;
> select max(distinct(tj_truck_no) from truck_journey where tj_truck_id is
> null
> *
> ERROR at line 1:
> ORA-00907: missing right parenthesis
>
>
> SQL> ed
> Wrote file afiedt.buf
>
> 1* select max(distinct(tj_truck_no)) from truck_journey where
tj_truck_id
> is null
> SQL> /
>
> MAX(DISTINCT(TJ_TRUCK_NO))
> --------------------------------------------------------------------------

--


> ----

> sun/tools/util/CommandLine

>

>

> Execution Plan

> ----------------------------------------------------------

>    0      SELECT STATEMENT Optimizer=CHOOSE

>    1    0   SORT (GROUP BY)

>    2    1     TABLE ACCESS (FULL) OF 'TRUCK_JOURNEY'

>

>

>

> SQL> create index tj_ind on truck_journey(tj_date,tj_truck_id);

>

> Index created.

>

> SQL> select max(distinct(tj_truck_no)) from truck_journey where

tj_truck_id


> is null;

>

> MAX(DISTINCT(TJ_TRUCK_NO))

> --------------------------------------------------------------------------

--


> ----

> sun/tools/util/CommandLine

>

>

> Execution Plan

> ----------------------------------------------------------

>    0      SELECT STATEMENT Optimizer=CHOOSE

>    1    0   SORT (GROUP BY)

>    2    1     TABLE ACCESS (FULL) OF 'TRUCK_JOURNEY'

>

>

>

> SQL> select distinct(tj_truck_no) from truck_journey where tj_truck_id is

> null;

>

> TJ_TRUCK_NO

> --------------------------------------------------------------------------

--


> ----

> /1001a851_ConstantDefImpl

> <snip>

> sun/tools/util/CommandLine

>

> 17742 rows selected.

>

>

> Execution Plan

> ----------------------------------------------------------

>    0      SELECT STATEMENT Optimizer=CHOOSE

>    1    0   SORT (UNIQUE)

>    2    1     TABLE ACCESS (FULL) OF 'TRUCK_JOURNEY'

>

>

>

> SQL> spool off

>

>

> --

> Niall Litchfield

> Oracle DBA

> Audit Commission UK

> *****************************************

> Please include version and platform

> and SQL where applicable

> It makes life easier and increases the

> likelihood of a good answer

> ******************************************

>

>