Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I asked this question on SO but havn't got many bites and even a vote to close for being "Off Topic." So, I thought this stack exchange might be better suited.

Consider the following SQL...

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)
       AND allStuff.clazz_= ? /* if I remove this line the query is fast (index fast)
                                 but if it stays in then DB2 does a tablescan
                                 on MyStuff and MyWarehousedStuff (very expensive) */

To make things a bit more confusing, if I remove the LEFT OUTER JOIN or make it an INNER JOIN DB2 does the right thing. I find it highly unusuall that an AND would cause worse perforamnce. I am also surprised by the use of a table scan when the field in question isn't actually on the table and is constant.

Note, this is a cleaned up Hibernate query so I'm not to worried about fixing the SQL in question but rather I want to know why DB2 is acting a particular way.

What would be causing this behavior?

[update] - It appears that even adding allStuff.clazz_ to the column list is enough to trigger the table scanning.

[update] - Also seems that the IN subslect is part of the problem. Switching that to a literal subselect IN (?, ?) is fast but IN (select 1 from SYSIBM.SYSDUMMY1) is not

[update] - Using DB2 v9.1 z/OS

[update] - To be clear, the following is also slow

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)

But this is fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (?, ?)

Fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       INNER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (?, ?)

Fast...

SELECT allStuff.id, allStuff.clazz_
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)

Fast...

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (select 1 from sysibm.sysdummy1)
share|improve this question
    
FYI the "Off topic" vote was to move the question here :) I'm glad you brought it here though. Any complaints on us closing the other one? –  JNK Jun 6 '12 at 12:25
    
I don't know much about DB2 but I'm guessing the presence of the calculated field is indicating to the engine it needs to be run for each row. –  JNK Jun 6 '12 at 12:27
    
@JNK sure you can close the other one and add a link to this one if you can. –  Andrew White Jun 6 '12 at 12:33
    
@JNK as to your theory, I don't see how that would cause the INNER JOIN to be fast or how switching the IN to use literals would help then. –  Andrew White Jun 6 '12 at 12:35
    
Again this is hypothetical since I'm not a DB2 guy (I do SQL Server) but the INNER JOIN would allow an index to be used to limit the result set initially that the calculation needs to be done for. LEFT JOIN means every row in the subquery needs to be generated regardless –  JNK Jun 6 '12 at 12:37

1 Answer 1

Just off the top of my head, it makes sense that it needs to tablescan those two tables. They are both present in a UNION ALL. This essentially means all records are in consideration. You then also use a LEFT OUTER JOIN meaning everything from the left side (your two union'd) tables even if it doesn't match the right side (your Children table). This is telling DB2 that no matter what every possible record in the two tables of your UNION ALL is under consideration.

Now...to fix that problem. I would ask, do you have any indexes over the clazz column in both tables? Since clazz is a predicate of your where clause you may wish to have a index over that field in both tables (MyStuff and MyWarehouseStuff). You don't need an index over the id fields (as long as they are primary keys), because you gain that by default in DB2.

To better verify what DB2 is doing, you should use the visual explain (of course you may have done that). It should show you why it is picking what it is. A few other things to keep in mind when examining the explain, do you run stats? If you don't, you should. Running stats will keep DB2 better informed as to statistics about its tables, data, indexes, etc, and may cause it to choose a better access path. Also, this may seem a silly question, what version and fix pack of DB2 are you on? Occasionally certain versions/fix packs end up buggy and there are performance issues due to poor access path selection by the optimizer. An example of this, if you are on DB2 9.7 then you want to be on fix pack 4 or 5. There is at least one fix pack below those that had serious performance issues due to a bug in the optimizer.

Anyway, check out your indexes first. Adding one to both the tables I mentioned may solve your problem. Worse case, I suppose you could re-write your query to put the clazz predicate up into the sub select "table". That may cause the optimizer to eliminate rows before it unions and joins. Just another thought.

EDIT: Here is a thought on your query

SELECT allStuff.id
  FROM    (SELECT id, 1 AS clazz_
             FROM MyStuff
           UNION ALL
           SELECT id, 2 AS clazz_
             FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)
       AND allStuff.clazz_= ? 

You are using a constant basically that from what I understand doesn't exist in the table. You also are not selecting this, but you have applied it to every row, and you use it in the WHERE clause. So I ask why do this? It really is just saying select every row. You may as well be doing select *. This will contribute to the table scan. Plus you are not displaying this value back. So why use it? Why not rather do the following:

SELECT allStuff.id
  FROM    (SELECT id FROM MyStuff
           UNION ALL
           SELECT id, FROM MyWarehousedStuff) allStuff
       LEFT OUTER JOIN
          Chilren child
       ON allStuff.id = child.id
 WHERE allStuff.id IN (/*fast sub select */)

Since you are not selecting the data, unless you oops'd on your query you are posting, isn't this what you would rather have? It union (all)'s the ids, joins them to the child table and then returns what you want?

Although I would also question the point of your LEFT OUTER JOIN. Because if your left table (in this case allStuff) has a NULL for the id it shouldn't match your "fast sub select", unless that allows NULL's as well. (Does it?), so you would be excluding these. I don't know the context of what you are trying to accomplish so perhaps I am not speaking to it appropriately, but it makes me question having a LEFT OUTER JOIN as well.

share|improve this answer
    
Note that clazz_ is a literal defined in the UNION ALL, it is NOT on the physical table so impossible to index. I have done explains but all it shows me is that tablescans are occuring not why they are occuring. Also, consder that even if I put the clazz_ in the select columns and take it out of the where, it does the same thing. –  Andrew White Jun 6 '12 at 13:23
    
Well if you are not defining predicates that are indexed, then the optimizer will essentially see what you are selecting as the entire table, and thus it will do table scans. There is not much you can do about that. Also, I will edit my post to reflect a thought on your query. –  Chris Aldrich Jun 6 '12 at 14:07
    
The why in all of this is; Hibernate generated the code, not me. That clazz_ may end up in the select clause (which still causes the slowdown). There are no NULL ids and there may or may not be a Child for Stuff. Again this is a Hibernate-ism so not something I can control easily. I'm more interested in the why then anything. –  Andrew White Jun 6 '12 at 14:21
    
Also, remember, if I take clazz_ out of the where clause and only have it in the column select list, it still falls back to table scans. –  Andrew White Jun 6 '12 at 14:23
1  
Well, the why is essentially you have left DB2 to understand that it can potentially retrieve all data from the tables, thus it falls back on a table scan. As I have been taught by those much smarter than me: "Tell DB2 what you want. Not what you don't want." I realize Hibernate is generating query. Perhaps don't use Hibernate's defaults. Figure out what you want and then "override" Hibernate by passing it the query you wish to run. So for starters I'd ask, what is it you really want? (from the tables). We can work on a better query then. –  Chris Aldrich Jun 6 '12 at 14:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.