SQL Access path Question

Peter Jones

SQL Access path Question
Could someone can help me on this ?

SELECT COLA , COLB, COLD
FROM TABLEA
WHERE COLC > ' '


TABLEA has 2 million records and currently does tablespace scan.
COLC is CHAR(1)
COLC has COLCARDF = 3
TABLEA has Primary index on COLA ( the only index on the TABLEA)

I have created an index(INDEX_C) on COLC but optimiser doesn't use it ? I
guess the Fillter factor is (1/3 = 0.333) 0.3 then 660000 records (2M* 0.3 )
qualifies. I think COLC is indexable and stage 1 predicate .

1)Why the optimiser doesn't use the index on COLC ?

2)If COLCARDF is a low value(eg 2) for a given COLUMN. Is it any point of
creating an index on such a column to utilise that index for access path
selection ?

Another question:
3) If all the rows of TABLEA has COLC= ' ' . Then the above INDEX_C is still
useful for the above query?

Thanks in advance.

Pete

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Sam Baugh

Re: SQL Access path Question
(in response to Peter Jones)
I suppose that the table will have to be clustered on colc.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Peter Jones
Sent: Monday, December 22, 2008 3:43 PM
To: [login to unmask email]
Subject: [DB2-L] SQL Access path Question

Could someone can help me on this ?

SELECT COLA , COLB, COLD
FROM TABLEA
WHERE COLC > ' '


TABLEA has 2 million records and currently does tablespace scan.
COLC is CHAR(1)
COLC has COLCARDF = 3
TABLEA has Primary index on COLA ( the only index on the TABLEA)

I have created an index(INDEX_C) on COLC but optimiser doesn't use it ? I
guess the Fillter factor is (1/3 = 0.333) 0.3 then 660000 records (2M* 0..3 )
qualifies. I think COLC is indexable and stage 1 predicate .

1)Why the optimiser doesn't use the index on COLC ?

2)If COLCARDF is a low value(eg 2) for a given COLUMN. Is it any point of
creating an index on such a column to utilise that index for access path
selection ?

Another question:
3) If all the rows of TABLEA has COLC= ' ' . Then the above INDEX_C is still
useful for the above query?

Thanks in advance.

Pete

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

-----------------------------------------

The information in this communication, including any attachments,
is intended only for the person or entity to which it is addressed
and may contain confidential, proprietary, and/or privileged
material. Any review, retransmission, dissemination or other use
of, or taking of any action in reliance upon, this information by
persons or entities other than the intended recipient is
prohibited. If you received this in error, please contact the
sender and delete the material from all computers.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Wayne Campbell

Re: SQL Access path Question
(in response to Sam Baugh)
You have a cardinality of 3. So unless you have distribution stats on the column, db2 is going to assume that each value is contained in 1/3 of the row. You have a predicate with a greater than, this means you could get 0% or 33% or 66% or 100% matches. Again without the distribution stats, Db2 won't know how many of the Rows would qualify.

Db2 is going to default the clustering of the table to the only index, which is on COLA. You didn't mention how many rows are on a page, probably more than 3. DB2 would assume that the COLC is fairly well distributed among the pages. So even with only 33% matching DB2 thinks it will need to read every data page anyway, so why read the index. If you created an index on COLC, COLA, COLB, and COLD, you would then get an index only access.

Wayne Campbell
DB2 DBA
Administrative Office of the Courts
(360) 705-5268
Email: [login to unmask email]
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Peter Jones
Sent: Monday, December 22, 2008 1:43 PM
To: [login to unmask email]
Subject: [DB2-L] SQL Access path Question

Could someone can help me on this ?

SELECT COLA , COLB, COLD
FROM TABLEA
WHERE COLC > ' '


TABLEA has 2 million records and currently does tablespace scan.
COLC is CHAR(1)
COLC has COLCARDF = 3
TABLEA has Primary index on COLA ( the only index on the TABLEA)

I have created an index(INDEX_C) on COLC but optimiser doesn't use it ? I
guess the Fillter factor is (1/3 = 0.333) 0.3 then 660000 records (2M* 0.3 )
qualifies. I think COLC is indexable and stage 1 predicate .

1)Why the optimiser doesn't use the index on COLC ?

2)If COLCARDF is a low value(eg 2) for a given COLUMN. Is it any point of
creating an index on such a column to utilise that index for access path
selection ?

Another question:
3) If all the rows of TABLEA has COLC= ' ' . Then the above INDEX_C is still
useful for the above query?

Thanks in advance.

Pete

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Simpson

Re: SQL Access path Question
(in response to Wayne Campbell)
Another stat to look at is the cluster ratio of your COLC index. If the ratio is low then optimizer may determine that it will need to visit nearly every page of the tablespace to get the 33% of the data, so a tablespace scan is still the best access path. If you gathered stats on your new index the frequency stats for COLC should also have been gathered, so optimizer will know if one of the 3 values has a much greater or lower filter factor than the other 2.

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Peter Jones
Sent: Monday, December 22, 2008 3:43 PM
To: [login to unmask email]
Subject: [DB2-L] SQL Access path Question

Could someone can help me on this ?

SELECT COLA , COLB, COLD
FROM TABLEA
WHERE COLC > ' '


TABLEA has 2 million records and currently does tablespace scan.
COLC is CHAR(1)
COLC has COLCARDF = 3
TABLEA has Primary index on COLA ( the only index on the TABLEA)

I have created an index(INDEX_C) on COLC but optimiser doesn't use it ? I
guess the Fillter factor is (1/3 = 0.333) 0.3 then 660000 records (2M* 0.3 )
qualifies. I think COLC is indexable and stage 1 predicate .

1)Why the optimiser doesn't use the index on COLC ?

2)If COLCARDF is a low value(eg 2) for a given COLUMN. Is it any point of
creating an index on such a column to utilise that index for access path
selection ?

Another question:
3) If all the rows of TABLEA has COLC= ' ' . Then the above INDEX_C is still
useful for the above query?

Thanks in advance.

Pete

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Fred Edgar

Re: SQL Access path Question
(in response to David Simpson)
I pretty much agree with what the other replies. DB2 might use your index
if you use "optimize for 1 row". This could perform much worse than a tablespace
scan if a large percentage of the rows qualify. Or it might use your index if you added
COLA, COLB and COLD to your index so DB2 could use index-only access.
Bottom line is, how many rows qualify? Looks like all of them might. If they
all do, you can't beat a TS scan, unless you have an index with all the columns
you reference and it has few pages than the tablespace.

Fred

Peter Jones <[login to unmask email]> wrote:
Could someone can help me on this ?

SELECT COLA , COLB, COLD
FROM TABLEA
WHERE COLC > ' '


TABLEA has 2 million records and currently does tablespace scan.
COLC is CHAR(1)
COLC has COLCARDF = 3
TABLEA has Primary index on COLA ( the only index on the TABLEA)

I have created an index(INDEX_C) on COLC but optimiser doesn't use it ? I
guess the Fillter factor is (1/3 = 0.333) 0.3 then 660000 records (2M* 0.3 )
qualifies. I think COLC is indexable and stage 1 predicate .

1)Why the optimiser doesn't use the index on COLC ?

2)If COLCARDF is a low value(eg 2) for a given COLUMN. Is it any point of
creating an index on such a column to utilise that index for access path
selection ?

Another question:
3) If all the rows of TABLEA has COLC= ' ' . Then the above INDEX_C is still
useful for the above query?

Thanks in advance.

Pete

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Larry Kintisch

Re: SQL Access path Question
(in response to Fred Edgar)
Hi Peter,

Wayne's comment about the "index-only" access with a "fat index"
[see Tapio's course description in my link below] is true. If all
rows were COLC = ' ' then none would qualify as > ' ' and the
index-only access would return the SQLCODE +100 [SQLSTATE '02000']
blazingly fast! The table scan would take about 20 seconds [using
QUBE estimation].

You didn't mention your platform and version. If any of the
columns are VARCHAR, DB2 8 for z/OS started to allow index-only
access on such columns if the index was created "NOT PADDED". My
knowledge of LUW is unclear about this issue of VARCHAR in indexes.

Contact me if you have questions about the Index Design class.

Larry Kintisch, Pres. email: [login to unmask email]
"Save money on every click, every transaction"
Index design class http://www.DBIndexDesign.com
Next "Index Design" class can be at a city near you

ABLE Information Services phone: (845)-353-0885
208 Hilltop Drive Nyack NY 10960-1119
"DB2, QMF, Data Modeling; DB2 & Index Tuning"
At 06:30 PM 12/22/2008, you wrote:
>You have a cardinality of 3. So unless you have distribution stats
>on the column, db2 is going to assume that each value is contained
>in 1/3 of the row. You have a predicate with a greater than, this
>means you could get 0% or 33% or 66% or 100% matches. Again without
>the distribution stats, Db2 won't know how many of the Rows would qualify.
>
>Db2 is going to default the clustering of the table to the only
>index, which is on COLA. You didn't mention how many rows are on a
>page, probably more than 3. DB2 would assume that the COLC is
>fairly well distributed among the pages. So even with only 33%
>matching DB2 thinks it will need to read every data page anyway, so
>why read the index. If you created an index on COLC, COLA, COLB,
>and COLD, you would then get an index only access.
>
>Wayne Campbell
>DB2 DBA
>Administrative Office of the Courts
>(360) 705-5268
>Email: [login to unmask email]
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
>Behalf Of Peter Jones
>Sent: Monday, December 22, 2008 1:43 PM
>To: [login to unmask email]
>Subject: [DB2-L] SQL Access path Question
>
>Could someone can help me on this ?
>
>SELECT COLA , COLB, COLD
> FROM TABLEA
> WHERE COLC > ' '
>
>
>TABLEA has 2 million records and currently does tablespace scan.
>COLC is CHAR(1)
>COLC has COLCARDF = 3
>TABLEA has Primary index on COLA ( the only index on the TABLEA)
>
>I have created an index(INDEX_C) on COLC but optimiser doesn't use it ? I
>guess the Fillter factor is (1/3 = 0.333) 0.3 then 660000 records (2M* 0.3 )
>qualifies. I think COLC is indexable and stage 1 predicate .
>
>1)Why the optimiser doesn't use the index on COLC ?
>
>2)If COLCARDF is a low value(eg 2) for a given COLUMN. Is it any point of
>creating an index on such a column to utilise that index for access path
>selection ?
>
>Another question:
>3) If all the rows of TABLEA has COLC= ' ' . Then the above INDEX_C is still
>useful for the above query?
>
>Thanks in advance.
>
>Pete
>
>______________________________________________________________________
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>______________________________________________________________________
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in
>IDUG. The DB2-L list archives, FAQ, and delivery preferences are at
>http://www.idug.org/lsidug under the Listserv tab. While at the
>site, you can also access the IDUG Online Learning Center, Tech
>Library and Code Place, see the latest IDUG conference information
>and much more. If you have not yet signed up for Basic Membership
>in IDUG, available at no cost, click on Member Services at
>http://www.idug.org/lsms
>
>______________________________________________________________________
>
>* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
>______________________________________________________________________
>
>
>
>The IDUG DB2-L Listserv is only part of your membership in
>IDUG. The DB2-L list archives, FAQ, and delivery preferences are at
>http://www.idug.org/lsidug under the Listserv tab. While at the
>site, you can also access the IDUG Online Learning Center, Tech
>Library and Code Place, see the latest IDUG conference information
>and much more. If you have not yet signed up for Basic Membership
>in IDUG, available at no cost, click on Member Services at
>http://www.idug.org/lsms


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html