Hello, I'm trying to do a left outer join between simple table to a
UNION ALL view of two base tables and I'm getting horrible performance
due to a tablescan of the base tables of the view even though the join
columns have indexes and great cardinality.

My table config and sql are as follows:

TableA
id int,
idb int (foreign key from TableB)

TableB/TableB2 and ViewB = select * from TableB UNION ALL
TableB2
id int

select * from TableA as A left outer join ViewB as VB on VB.id=A.idb
where A.id>? and A.id<?


If I substitute one of the base tables (TableB or TableB2) in place of
ViewB everything is great.
I've tried upgrading from V9.1 to V9.7 and same issues. It seems to me
that the join predicates are not
being pushed down to the base tables of ViewB.


Can anyone help? I'm completely confused.

On 6 Feb, 19:05, "cjflor... (AT) gmail (DOT) com" <cjflor... (AT) gmail (DOT) com> wrote:
Why is there a table B and B2, is this some kind of partitioning? If
that is the case, do you have check constraints on the partitions so
db2 can determine which one to investigate? If you post ddl and the
output from db2exfmt it will be easier to help out.


/Lennart

<cjflorian (AT) gmail (DOT) com> 写入消�
news:b325ffe3-740e-4a75-859a-e670e1f406ca (AT) z26g2000yqm (DOT) googlegroups.com...
access plan first,

Here is the DDL and some output from db2exfmt....

CREATE TABLE ENTITYA
(
"ID" VARCHAR(20) NOT NULL ,
"VERSION" SMALLINT NOT NULL WITH DEFAULT 0 ,
"RECORDID" BIGINT NOT NULL ,
"LASTVERSION" SMALLINT NOT NULL ,
"RECORDTIME" TIMESTAMP NOT NULL ,
"EFFECTIVETIME" TIMESTAMP NOT NULL ,
"C1ID" VARCHAR(20),
"C2ID" VARCHAR(20),
"BID" VARCHAR(20),
"BVRS" SMALLINT,
"IDX" INT
)
IN "TBLSP1" ;

ALTER TABLE ENTITYA ADD CONSTRAINT "ENTITYA_PKEY" PRIMARY KEY ("ID",
"VERSION");
CREATE INDEX EA_INDX2 ON ENTITYA ("BID" ASC, "BVRS" ASC) PCTFREE 10
MINPCTUSED 10 ALLOW REVERSE SCANS;

CREATE TABLE ENTITYB
(
"ID" VARCHAR(20) NOT NULL ,
"VERSION" SMALLINT NOT NULL WITH DEFAULT 0 ,
"RECORDID" BIGINT NOT NULL ,
"LASTVERSION" SMALLINT NOT NULL ,
"RECORDTIME" TIMESTAMP NOT NULL ,
"EFFECTIVETIME" TIMESTAMP NOT NULL ,
"C1ID" VARCHAR(20),
"C2ID" VARCHAR(20),
"IDX" INT
)
IN "TBLSP1" ;

CREATE TABLE ENTITYBHISTORY
(
"ID" VARCHAR(20) NOT NULL ,
"VERSION" SMALLINT NOT NULL WITH DEFAULT 0 ,
"RECORDID" BIGINT NOT NULL ,
"LASTVERSION" SMALLINT NOT NULL ,
"RECORDTIME" TIMESTAMP NOT NULL ,
"EFFECTIVETIME" TIMESTAMP NOT NULL ,
"C1ID" VARCHAR(20),
"C2ID" VARCHAR(20),
"IDX" INT
)
IN "TBLSP1" ;

ALTER TABLE ENTITYB ADD CONSTRAINT "ENTITYB_PKEY" PRIMARY KEY ("ID",
"VERSION");
ALTER TABLE ENTITYBHISTORY ADD CONSTRAINT "ENTITYBHIST_PKEY" PRIMARY
KEY ("ID", "VERSION");
ALTER TABLE ENTITYB ADD CONSTRAINT "ENTITYB_CON1" CHECK (VERSION=0)
ENFORCED ENABLE QUERY OPTIMIZATION;
ALTER TABLE ENTITYBHISTORY ADD CONSTRAINT "ENTITYBHIS_CON1" CHECK
(VERSION!=0) ENFORCED ENABLE QUERY OPTIMIZATION;

CREATE VIEW ENTITYBVIEW AS SELECT * FROM ENTITYB UNION ALL SELECT *
FROM ENTITYBHISTORY;




******************** EXPLAIN INSTANCE ********************

DB2_VERSION: 09.01.0
SOURCE_NAME: SYSSH200
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2010-02-07-23.20.06.749496
EXPLAIN_REQUESTER: DB2INST1

Database Context:
----------------
Parallelism: None
CPU Speed: 2.834065e-07
Comm Speed: 0
Buffer Pool size: 201000
Sort Heap size: 81
Database Heap size: 2842
Lock List size: 28452
Maximum Lock List: 98
Average Applications: 1
Locks Available: 1784509

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability



---------------- STATEMENT 1 SECTION 65 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1

Original Statement:
------------------
select *
from test.entitya as ea left outer join test.entitybview as ebv on
ea.bid=ebv.id and ea.bvrs=ebv.version
where ea.id>? and ea.id<?


Optimized Statement:
-------------------
SELECT Q14.$C15 AS "ID", Q14.$C14 AS "VERSION", Q14.$C13 AS
"RECORDID",
Q14.$C12 AS "LASTVERSION", Q14.$C11 AS "RECORDTIME", Q14.$C10
AS
"EFFECTIVETIME", Q14.$C9 AS "C1ID", Q14.$C8 AS "C2ID",
Q14.$C17 AS
"BID", Q14.$C16 AS "BVRS", Q14.$C7 AS "ID", Q14.$C6 AS
"VERSION",
Q14.$C5 AS "RECORDID", Q14.$C4 AS "LASTVERSION", Q14.$C3 AS
"RECORDTIME", Q14.$C2 AS "EFFECTIVETIME", Q14.$C1 AS "C1ID",
Q14.$C0
AS "C2ID"
FROM
(SELECT Q13.$C7, Q13.$C6, Q13.$C5, Q13.$C4, Q13.$C3, Q13.$C2,
Q13.$C1,
Q13.$C0, Q13.$C8, Q13.$C9, Q13.$C10, Q13.$C11, Q13.$C12,
Q13.$C13,
Q13.$C14, Q13.$C15, Q13.$C16, Q13.$C17, ROWNUMBER() OVER
(PARTITION BY Q13.$C16 ORDER BY Q13.$C0)
FROM
(SELECT Q1.ID, Q1.VERSION, Q1.RECORDID, Q1.LASTVERSION,
Q1.RECORDTIME,
Q1.EFFECTIVETIME, Q1.C1ID, Q1.C2ID, Q5.$C7, Q5.$C6,
Q5.$C5,
Q5.$C4, Q5.$C3, Q5.$C2, Q5.$C1, Q5.$C0, Q5.$C9, Q5.$C8
FROM TEST.ENTITYBHISTORY AS Q1 RIGHT OUTER JOIN
(SELECT Q4.ID, Q4.VERSION, Q4.RECORDID, Q4.LASTVERSION,
Q4.RECORDTIME, Q4.EFFECTIVETIME, Q4.C1ID, Q4.C2ID,
Q4.BID,
Q4.BVRS
FROM
(SELECT Q2.$C0
FROM (VALUES 0) AS Q2
WHERE (:? < :? SELECTIVITY 1.000000)) AS Q3, TEST.ENTITYA
AS Q4
WHERE (Q4.ID < :?) AND (:? < Q4.ID)) AS Q5 ON (Q5.$C9 =
Q1.VERSION)
AND (Q5.$C8 = Q1.ID)
UNION ALL
SELECT Q7.ID, Q7.VERSION, Q7.RECORDID, Q7.LASTVERSION,
Q7.RECORDTIME,
Q7.EFFECTIVETIME, Q7.C1ID, Q7.C2ID, Q11.$C7, Q11.$C6,
Q11.$C5,
Q11.$C4, Q11.$C3, Q11.$C2, Q11.$C1, Q11.$C0, Q11.$C9,
Q11.$C8
FROM TEST.ENTITYB AS Q7 RIGHT OUTER JOIN
(SELECT Q10.ID, Q10.VERSION, Q10.RECORDID, Q10.LASTVERSION,
Q10.RECORDTIME, Q10.EFFECTIVETIME, Q10.C1ID,
Q10.C2ID,
Q10.BID, Q10.BVRS
FROM
(SELECT Q8.$C0
FROM (VALUES 0) AS Q8
WHERE (:? < :? SELECTIVITY 1.000000)) AS Q9, TEST.ENTITYA
AS Q10
WHERE (:? < Q10.ID) AND (Q10.ID < :?)) AS Q11 ON (Q11.$C9 =
Q7.VERSION) AND (Q11.$C8 = Q7.ID)) AS Q13) AS Q14
WHERE (Q14.$C7 IS NOT NULL OR (Q14.$C7 IS NULL AND (Q14.$C18 = 1)))




Rows
RETURN
( 1)
Cost
I/O
FILTER
( 2)
849.608
2295.15
TBSCAN
( 3)
849.28
2295.15
SORT
( 4)
849.255
2295.15
UNION
( 5)
848.517
2295.15
/-----------------+----------------\
168.642
168.642
HSJOIN
MSJOIN
( 6)
( 14)
746.636
101.881
2118.57
176.573
/--------+--------\ /------------
+-----------\
168.642 100000
168.642 0
NLJOIN TBSCAN
TBSCAN FILTER
( 7) ( 13)
( 15) ( 23)
101.725 624.794
101.822 0.0115823
176.573 1942
176.573 0
/------+------\ |
168.642 0
TBSCAN FETCH TABLE: TEST
SORT FETCH
( 8) ( 9) ENTITYBHISTORY
( 16) ( 24)
0.000160125 50.6973
101.81 0.0115823
0 87.7864
176.573 0
168.642 0 0
TABFNC: SYSIBM RIDSCN TABLE: TEST
NLJOIN IXSCAN TABLE: TEST
GENROW ( 10) ENTITYA ( 17)
( 25) ENTITYB
15.3583
101.725 0.0113244
2.01354
176.573 0
168.642 1
168.642 0
SORT TBSCAN FETCH
INDEX: TEST
( 11) ( 18) ( 19)
ENTITYB_PKEY
15.3579 0.000160125 50.6973
2.01354 0 87.7864
100000
IXSCAN TABFNC: SYSIBM RIDSCN TABLE:
TEST
( 12) GENROW ( 20)
ENTITYA
15.3225 15.3583
2.01354 2.01354
INDEX: TEST SORT
ENTITYA_PKEY ( 21)
15.3579
2.01354
IXSCAN
( 22)
15.3225
2.01354
INDEX: TEST
ENTITYA_PKEY











On Feb 6, 8:42Â*pm, "Hardy" <wyh... (AT) gmail (DOT) com> wrote:

Yes, this is a partitioning scheme. EntityB is current data,
EntityBHistory is old data. A composite key is used to track both the
id and version. Both tables have constraints, but our application does
not know if the data is current or old, so needs to query both.

Also the entityb table is near empty and entitybhistory table is about
100k rows, but will likely grow to 10-100 million. Hence a tablescan
is out of the question.





On Feb 8, 12:02*am, "cjflor... (AT) gmail (DOT) com" <cjflor... (AT) gmail (DOT) com> wrote:
> Here is the DDL and some output from db2exfmt....

db2 => select TABNAME, CARD, FPAGES, NPAGES, OVERFLOW from
sysstat.tables where tabname like 'ENTITY%'

TABNAME CARD
FPAGES NPAGES OVERFLOW
----------------------- - -------------------- --------------------
-------------------- --------------------
ENTITYA 100000
2299 2299 0
ENTITYB 0
1 0 0
ENTITYBHISTORY 100000
1942 1942 0



As an experiment I changed the predicates on the where clause to
something a lot more selective and the optimizer did use the indexes
as I would have expected.

select *
from test.entitya as ea left outer join test.entitybview as ebv on
ea.bid=ebv.id and ea.bvrs=ebv.version
where ea.id=?

On 8 Feb, 17:58, "cjflor... (AT) gmail (DOT) com" <cjflor... (AT) gmail (DOT) com> wrote:

I think it is a combination of different things that makes this tricky
for the optimizer (I didnt have the column statitics to play with).
First as you indicated:

a) poor selectivity of id < ? and id > ?

This could mean a single row or the whole table. I tried playing with
volatile on the B tables but that did not help much. Depending on the
number of times you will ask this query (how many times you will have
to recompile it), it might (might) be better to use hardcoded values
instead of parameter markers. Also static sql gives you better control
so if you succed in convincing the optimizer to use the index on the
history table, you might want to stuff the query inside a procedure

b) outer join

Not much to say, if you need it you need it. I tested with an inner
join, and db2 uses the index on the history table.


c) all columns in both tables in select part. Do you really need all
of them? I tested the query as:

select ea.id, ebv.id, ea.bvrs, ebv.version
from entitya as ea left outer join entitybview as ebv on ea.bid=ebv.id
and
ea.bvrs=ebv.version
where ea.id>? and ea.id<?

Total Cost: 864.779
Query Degree: 1

and now db2 uses the index on the history table which of course yeilds
a better result.

/Lennart

Hi Lennart, first of all I appreciate the help.

All your suggestions are valid and I did experiment with setting the
tables to "volatile" as well as using the "selectivity" keyword with
no success.

I guess we'll have to change our model somewhat and may be able to use
the inner join.


Thanks again.

On Feb 8, 1:25*pm, Lennart <erik.lennart.jons... (AT) gmail (DOT) com> wrote:

<cjflorian (AT) gmail (DOT) com> 写入消�
news:1117215b-6a96-4134-9e6f-81ffb2cc0779 (AT) j31g2000yqa (DOT) googlegroups.com...
I notice this:

Sort Heap size: 81
Database Heap size: 2842


how about your sortheap/dbheap/bufferpool setting?