Inconsistant query results - Please help

#1

Hi All,

I have a table Truck_Journey with the following structure

Tj_Date Date
Tj_Truck_Id Number(5)
Tj_Truck_No Varchar2(10)
Tj_Km Number(9)
....

If I give a
select distinct (tj_truck_no) from truck_journey where tj_truck_id is null;
it works and shows all the truck numbers where the truck id is null

If I create a non-unique index on tj_date + tj_truck_id then the above query
does not show any records. When I run an explain plan, it shows that it's
doing a index scan.

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?

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?

Thanks in advance

Regards,
Bliss

#2

Thanks for the reply

Regards,
Bliss
"Sybrand Bakker" <gooiditweg (AT) sybrandb (DOT) demon.nl> wrote

Quote:

On Sat, 5 Jul 2003 23:31:51 +0800, "Bliss"
bliss_is_ignorance (AT) hotmail (DOT) com> wrote:

Hi All,

I have a table Truck_Journey with the following structure

Tj_Date Date
Tj_Truck_Id Number(5)
Tj_Truck_No Varchar2(10)
Tj_Km Number(9)
...

If I give a
select distinct (tj_truck_no) from truck_journey where tj_truck_id is
null;
it works and shows all the truck numbers where the truck id is null

If I create a non-unique index on tj_date + tj_truck_id then the above
query
does not show any records. When I run an explain plan, it shows that
it's
doing a index scan.

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.
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?

Definitely not.
And you should rather question your table design with null allowed
columns only.

Thanks in advance

Regards,
Bliss

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address

#3

"Sybrand Bakker" <gooiditweg (AT) sybrandb (DOT) demon.nl> wrote

Quote:

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.

Quote:

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
******************************************

#4

Thanks Niall.

Quote:

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).

Quote:

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 (DOT) pipex.com> wrote

Quote:

"Sybrand Bakker" <gooiditweg (AT) sybrandb (DOT) demon.nl> wrote in message
news:j8vdgvgmv23m6f62r6n3erhjn17d87jo23 (AT) 4ax (DOT) 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
******************************************

#5

"Bliss" <bliss_is_ignorance (AT) hotmail (DOT) com> wrote

Quote:

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

I'd definitely try raising a TAR with Oracle then. That said some digging
around produces bug 924671 which *could* be related and is fixed in 8.1.6.
It probably doesn't help you much but 8.1.7.4 is the currently supported 8i
release and an upgrade might well be the suggested solution.

--
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
******************************************

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Inconsistant query results - Please help

comp.databases.oracle.misc comp.databases.oracle.misc

Inconsistant query results - Please help - 07-05-2003 , 10:31 AM

Re: Inconsistant query results - Please help - 07-05-2003 , 11:04 PM

Re: Inconsistant query results - Please help - 07-06-2003 , 03:26 AM

Re: Inconsistant query results - Please help - 07-11-2003 , 11:38 PM

Re: Inconsistant query results - Please help - 07-12-2003 , 10:14 AM