optimize oracle multiple subqueries in select

Question

Right now I am facing a query that I think which could be optimized.

select i.itemID,
(select image from imagetable where imageType = 1 and itemID = i.itemID)
(select image from imagetable where imageType = 2 and itemID = i.itemID)
(select image from imagetable where imageType = 5 and itemID = i.itemID)
from 
    item i
where
    i.itemID = 3

This is a sample of what needs to be optimized, the thing is that the result is being consumed by java as a single row. and I need to include even more subqueries if I want to include more "image types".

So the question is, how can I optimize this?

use conditional aggregation

Daniel Marcus
– Daniel Marcus

2018-04-20 19:07:21 +00:00
Commented Apr 20, 2018 at 19:07 — Daniel Marcus
– Daniel Marcus, Commented Apr 20, 2018 at 19:07

Gordon Linoff · Accepted Answer · 2018-04-21 02:19:25Z

1

Your query should be fine. It might even be the most efficient method, with an index on imagetable(itemitem, imageType).

The canonical alternative would be conditional aggregation:

select i.itemID,
       max(case when it.imageType = 1 then it.image end),
       max(case when it.imageType = 2 then it.image end),
       max(case when it.imageType = 5 then it.image end)
from item i left join
     imagetable it
     on it.itemId = i.itemId and it.imageType in (1, 2, 5)
where i.itemID = 3
group by i.itemId;

edited Apr 21, 2018 at 2:19

answered Apr 20, 2018 at 19:07

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kjetil S. Over a year ago

Wouldn't this return just 1 instead of images in the result row?

Kjetil S. · Accepted Answer · 2018-04-20 20:19:09Z

1

Maybe this:

select i.itemID, t1.image im1, t2.image im2, t5.image im5
from item i
left join imagetable t1 on t1.itemId = i.itemId and t1.imageType=1
left join imagetable t2 on t2.itemId = i.itemId and t1.imageType=2
left join imagetable t5 on t5.itemId = i.itemId and t1.imageType=5
where i.itemID = 3

"the thing is that the result is being consumed by java as a single row"

This seems very strict, the select would be a lot simpler if your java would accept more than one row. But if that's how it is. I'm not sure however if it's more efficient than your select, I would guess (without trying) about the same. (Your first two sub queries needs a comma behind their ending parenthesis btw)

The following should also work, depending maybe on the image datatype:

select i.itemID, 
  max(decode(imageType,1,image)) im1,
  max(decode(imageType,2,image)) im2,
  max(decode(imageType,5,image)) im5
from item i left join imagetable t on t.itemId = i.itemId 
where i.itemID = 3
group by i.itemID

edited Apr 20, 2018 at 20:19

answered Apr 20, 2018 at 20:13

Kjetil S.

3,8591 gold badge26 silver badges23 bronze badges

1 Comment

jaramator Over a year ago

I like your answer, plus 1

Daniel Marcus · Accepted Answer · 2018-04-20 19:08:45Z

0

select i.itemID, 
case when imageType = 1 then image else null end, 
case when imageType = 2 then image else null end, 
case when imageType = 5 then image else null end
from 
    item i left join
 imagetable t
 on t.itemId = i.itemId 
where
    i.itemID = 3

answered Apr 20, 2018 at 19:08

Daniel Marcus

2,6841 gold badge10 silver badges13 bronze badges

1 Comment

Kjetil S. Over a year ago

Wouldn't this return more than one row?

Collectives™ on Stack Overflow

optimize oracle multiple subqueries in select

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related