SQL question matching cols ?

N. - Neil - Briggs

SQL question matching cols ?
Dear All

os390 db2 v5.1

Please can somebody tell me why DB2 is deciding that is has no matching
columns for the following SQL, I was expecting 1 matching column on M_CODE:-

SELECT M_CODE
,M_CODE_2
FROM SQLUSRA.AMAM24_CONV
WHERE M_CODE = '2-80031-02 ' result should be 1 row returned.

SQLUSRA.AMAM24_CONV is a table containing 90,000 rows MAXIMUM RECORD
LENGTH : 75

There exists one index on the table, the columns that make up this UNIQUE
index are:-
REGISTRAR_COMPANY
M_CODE

neither column of the index is unique in its own right but together they
create a unique clustering index.


PLANNO METHOD CREATOR TNAME TABNO ACCESSTYPE
MATCHCOLS PREFETCH
1 0 SQLUSRA AMAM24_CONV 1
I 0 S


Thanks in advance
Neil



Eric Pearson

Re: SQL question matching cols ?
(in response to N. - Neil - Briggs)
I think matchcols is based on the leading matching columns.
If the pred was on REGISTRAR_COMPANY, matchcols would be 1.
If it was on REGISTRAR_COMPANY and M_CODE, matchcols would be 2.
Instead of a matchcols you will get 'index screening'.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Briggs, N. - Neil - [mailto:[login to unmask email]
Sent: Wednesday, January 10, 2001 11:38 AM
To: [login to unmask email]
Subject: SQL question matching cols ?


Dear All

os390 db2 v5.1

Please can somebody tell me why DB2 is deciding that is has no matching
columns for the following SQL, I was expecting 1 matching column on M_CODE:-

SELECT M_CODE
,M_CODE_2
FROM SQLUSRA.AMAM24_CONV
WHERE M_CODE = '2-80031-02 ' result should be 1 row returned.

SQLUSRA.AMAM24_CONV is a table containing 90,000 rows MAXIMUM RECORD
LENGTH : 75

There exists one index on the table, the columns that make up this UNIQUE
index are:-
REGISTRAR_COMPANY
M_CODE

neither column of the index is unique in its own right but together they
create a unique clustering index.


PLANNO METHOD CREATOR TNAME TABNO ACCESSTYPE
MATCHCOLS PREFETCH
1 0 SQLUSRA AMAM24_CONV 1
I 0 S


Thanks in advance
Neil








Ashish Mohan

Re: SQL question matching cols ?
(in response to Eric Pearson)
If M_CODE were the 'first' column of your index, it would have given you the
result you are expecting.

Thanks.

Ashish.

-----Original Message-----
From: Briggs, N. - Neil - [SMTP:[login to unmask email]
Sent: Wednesday, January 10, 2001 8:38 AM
To: [login to unmask email]
Subject: SQL question matching cols ?

Dear All

os390 db2 v5.1

Please can somebody tell me why DB2 is deciding that is has no
matching
columns for the following SQL, I was expecting 1 matching column on
M_CODE:-

SELECT M_CODE
,M_CODE_2
FROM SQLUSRA.AMAM24_CONV
WHERE M_CODE = '2-80031-02 ' result should be 1 row
returned.

SQLUSRA.AMAM24_CONV is a table containing 90,000 rows MAXIMUM
RECORD
LENGTH : 75

There exists one index on the table, the columns that make up this
UNIQUE
index are:-
REGISTRAR_COMPANY
M_CODE

neither column of the index is unique in its own right but together
they
create a unique clustering index.


PLANNO METHOD CREATOR TNAME TABNO
ACCESSTYPE
MATCHCOLS PREFETCH
1 0 SQLUSRA AMAM24_CONV 1
I 0 S


Thanks in advance
Neil




can



John Hardy

Re: SQL question matching cols ?
(in response to Tim Lowe)
DB2 cannot carry out a matching index scan in this case, as you have not
supplied the high order column of the key. As the value of
REGISTRAR_COMPANY is unknown, the best that DB2 can do is scan the entire
index searching for the supplied value of M-CODE. Your options are:

1. Swap the columns around. (another consideration: M-CODE may well have
much higher cardinality than REGISTRAR-COMPANY, and where practical, you
should put the higher cardinality column first).

2. Create a secondary index on M-CODE.

Depending upon the current usage of the index, option 1 may not be viable.
Option 2 is only worth considering if the likely usage of the new index
justifies the extra overhead which it will incur.


On Wed, 10 Jan 2001 17:38:15 +0100, Briggs, N. - Neil - <[login to unmask email]
EUROPA.COM> wrote:

>Dear All
>
>os390 db2 v5.1
>
>Please can somebody tell me why DB2 is deciding that is has no matching
>columns for the following SQL, I was expecting 1 matching column on
M_CODE:-
>
>SELECT M_CODE
> ,M_CODE_2
>FROM SQLUSRA.AMAM24_CONV
>WHERE M_CODE = '2-80031-02 ' result should be 1 row returned.
>
>SQLUSRA.AMAM24_CONV is a table containing 90,000 rows MAXIMUM RECORD
>LENGTH : 75
>
>There exists one index on the table, the columns that make up this UNIQUE
>index are:-
>REGISTRAR_COMPANY
>M_CODE
>
>neither column of the index is unique in its own right but together they
>create a unique clustering index.
>
>
>PLANNO METHOD CREATOR TNAME TABNO ACCESSTYPE
>MATCHCOLS PREFETCH
> 1 0 SQLUSRA AMAM24_CONV 1
>I 0 S
>
>
>Thanks in advance
>Neil
>
>
>





[login to unmask email]

Re: SQL question matching cols ?
(in response to John Hardy)
Neil,

For the second column to be matched, you must include a predicate for the
first column e.g. REGISTRAR_COMPANY = '01'

Jim.
-----Original Message-----
From: Briggs, N. - Neil - [mailto:[login to unmask email]
Sent: 10 January 2001 16:38
To: [login to unmask email]
Subject: SQL question matching cols ?


Dear All

os390 db2 v5.1

Please can somebody tell me why DB2 is deciding that is has no matching
columns for the following SQL, I was expecting 1 matching column on M_CODE:-

SELECT M_CODE
,M_CODE_2
FROM SQLUSRA.AMAM24_CONV
WHERE M_CODE = '2-80031-02 ' result should be 1 row returned.

SQLUSRA.AMAM24_CONV is a table containing 90,000 rows MAXIMUM RECORD
LENGTH : 75

There exists one index on the table, the columns that make up this UNIQUE
index are:-
REGISTRAR_COMPANY
M_CODE

neither column of the index is unique in its own right but together they
create a unique clustering index.


PLANNO METHOD CREATOR TNAME TABNO ACCESSTYPE
MATCHCOLS PREFETCH
1 0 SQLUSRA AMAM24_CONV 1
I 0 S


Thanks in advance
Neil







***************************************************************
The contents of this Email and any files transmitted with it
are confidential and intended solely for the use of the
individual or entity to whom it is addressed. The views stated
herein do not necessarily represent the view of the company.
If you are not the intended recipient of this Email you may not
copy, forward, disclose or otherwise use it or any part of it
in any form whatsoever. If you have received this mail in
error please Email the sender.
***************************************************************



Tim Lowe

Re: SQL question matching cols ?
(in response to Ashish Mohan)
Neil,
Since your only index does not start with the only column that you have a
predicate for, then DB2 will need to scan the entire index to satisfy your
request. Therefore, matchcols must be 0.

I hope this helps.

Thanks,
Tim



Nick Cianci

Re: SQL question matching cols ?
(in response to Jim.Leask@RS-COMPONENTS.COM)
Neil,
in the case of the supplied Query, this would be because the M_CODE
column is not the first Column in the index ( I would presume). For match
on the index it needs to the first column (of course if you do that other
queries may suffer)

Given that there is only the 1 unique index DB2 has 2 choices. Non-matching
index scan ~ Scan the entire index find the matching RIDS and get the data
from the TableSpace. OR . A TableSpace scan. Given AccessType=I was chosen
you've got the non-matching Index scan. This is not as bad as a TS scan if
there are significantly less pages in the index than the TS, judging by what
should be in the DB2 Stat.s, but it will not be as good as Matching Index
scan.

Your call as to where you go next :-
* Live with it?
* Build an additional index?
* Swap the columns around in the index (if no other query is using it)

Cheers,
Nick Cianci
DB2 DBA - CCRI Project
2nd Floor (West) 484 StKilda Rd MELBOURNE 3004
* (+613) 9865-8554
(+61) 0408-64 06 01
* [login to unmask email]
_______________________________________________________________

No trees were killed in the sending of this
message. However - a large number of
electrons were terribly inconvenienced.
_______________________________________________________________

> -----Original Message-----
> From: Briggs, N. - Neil - [SMTP:[login to unmask email]
> Sent: Thursday, January 11, 2001 3:38 AM
> To: [login to unmask email]
> Subject: SQL question matching cols ?
>
> Dear All
>
> os390 db2 v5.1
>
> Please can somebody tell me why DB2 is deciding that is has no matching
> columns for the following SQL, I was expecting 1 matching column on
> M_CODE:-
>
> SELECT M_CODE
> ,M_CODE_2
> FROM SQLUSRA.AMAM24_CONV
> WHERE M_CODE = '2-80031-02 ' result should be 1 row returned.
>
> SQLUSRA.AMAM24_CONV is a table containing 90,000 rows MAXIMUM RECORD
> LENGTH : 75
>
> There exists one index on the table, the columns that make up this UNIQUE
> index are:-
> REGISTRAR_COMPANY
> M_CODE
>
> neither column of the index is unique in its own right but together they
> create a unique clustering index.
>
>
> PLANNO METHOD CREATOR TNAME TABNO ACCESSTYPE
> MATCHCOLS PREFETCH
> 0 SQLUSRA AMAM24_CONV 1 I 0 S
>
>
> Thanks in advance
> Neil
>
>
>
>
>