Re: Inconsistant query results

From: Niall Litchfield <niall.litchfield_at_dial.pipex.com>
Date: Sun, 6 Jul 2003 09:26:37 +0100
Message-ID: <3f07dcbe$0$18495$cc9e4d1f@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
******************************************

Received on Sun Jul 06 2003 - 03:26:37 CDT

Re: Inconsistant query results - Please help