SQL Access Path Enhancement – Second of the day

(Originally posted 2006-02-22.)

I’m deeply involved in a nice complex study at one of my favourite clients at the moment – so I’ve just come up for air for half an hour or so. So I’m catching up on blogging about interesting APARs.

So APAR PK15890 is my second SQL Access Path Enhancement of the day…

Take the case of a query like

SELECT T1.*
  FROM SYSADM.TP1 T1, SYSADM.TP2 T2
  WHERE T1.C2 = T2.C2
  AND T2.C1 < '2003-12-31'

where T2.C1 is the partitioning column of the T2 table.

Suppose further that Hybrid Join was the chosen access path and that T2 was accessed as the inner table via List Prefetch.

Prior to PK15890 Page Range screening would not be used to limit the scan of T2 to a subset of its partitions. In this example you’d want the only the partitions where the

T2.C1 < '2003-12-31' 

local predicate is true to be scanned.

The fix will be made available for both Versions 7 and 8.

Published by Martin Packer

.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: