DB2 LUW compressed table causes tablespace scan

David Chapman

DB2 LUW compressed table causes tablespace scan

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________


Hello,

We have DB2 v9.1 running on AIX. We have just turned on row compression and now have a query doing a tablespace scan. The SELECT is looking for values of a column that is defined as VARCHAR(98). The column is the second key in an index. We expected to see an index scan but instead see a tablespace scan.

We are assuming that as the table is compressed, and the index key contains the entire 98 characters (with null padding), DB2 will scan the table instead, as the table row size (with compression) is smaller than the index key size (without compression and with null padding).

Has anyone encountered this problem before and do you have any suggestions?

Thanks,

David.




_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential 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 have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________


______________________________________________________________________

* 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

Peter Vanroose

Re: DB2 LUW compressed table causes tablespace scan
(in response to David Chapman)
>We are assuming that as the table is compressed, and the index key
> contains the entire 98 characters (with null padding), DB2 will scan the
> table instead, as the table row size (with compression) is smaller than
> the index key size (without compression and with null padding).

This answers your question, doesn't it?
The optimizer estimated a higher cost to do an index screening (without
decompression) than to do a table scan (with decompression), hence chose the
cheaper tablescan.
Nothing wrong with that, I would say.

-- Peter.


______________________________________________________________________

* 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