CODEWITHSUNDEEP
sqloracle-database
jaramator
jaramator

Reputation: 310

optimize oracle multiple subqueries in select

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?

Upvotes: 0

Views: 67

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1271023

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;

Upvotes: 1

Kjetil S.
Kjetil S.

Reputation: 3785

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

Upvotes: 1

Daniel Marcus
Daniel Marcus

Reputation: 2686

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

Upvotes: 0

Related Questions