asked on

Oracle sub-select causing poor query performance

I'm having a performance issue with an Oracle SQL query. When I add a subselect, the query takes 5 min, 40 sec.

SELECT DISTINCT
    prokeyi,
    probezc,
    provf2c,
    artbezc,
    artkeyi,
    artanrc,
    katbezc,
    etybezc,
    stabezc,
    vgbbezc,
    abpbezc,
    kvtbezc,
    kvtinac, 
    rafbezc,
    rfavf1c,
    rfavf2c,
    rfavf3c,
    rfavf4c,
    rfavf5c,
    depbezc,
    MIN((select distinct pflfldc from pfl,pty where pflptykeyi = 17 and pflobjkeyi = prokeyi and prokavkeyi = pflvarkeyi))
FROM
    pro,
    art,
    aez,
    kat,
    ety,
    dok,
    wob,
    abp,
    vgb,
    sta,
    kvt,
    kav,
    dep,
    rfa,
    raf
WHERE
    prokeyi = aezprokeyi (+)
    AND   aezkavkeyi (+) = prokavkeyi
    AND   artkeyi (+) = aezartkeyi
    AND   artkavkeyi (+) = aezkavkeyi
    AND   prokatkeyi = katkeyi
    AND   etykeyi (+) = proetykeyi
    AND   dokkatkeyi = katkeyi (+)
    AND   doksnei = prosnei (+)
    AND   wobabpkeyi = abpkeyi
    AND   wobobjkeyi (+) = dokkeyi
    AND   wobvarkeyi (+) = dokkavkeyi
    AND   wobstakeys = stakeys
    AND   dokvgbkeyi = vgbkeyi (+)
    AND   staabpkeyi = abpkeyi
    AND   abptyps = 150
    AND   wobtyps = 150
    AND   kavkatkeyi = katkeyi
    AND   kavkvtkeyi = kvtkeyi
    AND   kavkeyi (+) = prokavkeyi
    AND   rfakavkeyi (+) = dokkavkeyi
    AND   rfadokkeyi (+) = dokkeyi
    AND   rfadepkeyi = depkeyi
    AND   prorafkeyi = rafkeyi (+)
    AND   doksnei = prosnei (+)
    AND   probezc <> '---Page element---'
    AND   katinac = '3141'
    AND   prosnei = 2
    group by
    prokeyi,
    probezc,
    provf2c,
    artbezc,
    artkeyi,
    artanrc,
    katbezc,
    etybezc,
    stabezc,
    vgbbezc,
    abpbezc,
    kvtbezc,
    kvtinac, 
    rafbezc,
    rfavf1c,
    rfavf2c,
    rfavf3c,
    rfavf4c,
    rfavf5c,
    depbezc;

Open in new window

Is the better way to incorporate a subselect that might improve performance?

Kent Olsen

Are you referring to the MIN ((SELECT ... )) as the sub-query?

The initial DISTINCT clause should be redundant. The GROUP BY will result in unique values.

Without seeing an explain plan it's tough to know....

DDP Como

ASKER

Are you referring to the MIN ((SELECT ... )) as the sub-query?

Yes, if I remove this statement, the query runs in seconds.

Kent Olsen

You should be able to drop the DISTINCT from the sub-query, too. MIN will return only 1 value.

How many rows are returned (with or without the sub-query)?

Using the original query (without the subquery) to produce a set of results and then (essentially) joining the sub-query may speed things up.

DDP Como

ASKER

I dropped distinct from the query and sub-query. The results still take the same amount of time.

How many rows are returned (with or without the sub-query)?
7k

Using the original query (without the subquery) to produce a set of results and then (essentially) joining the sub-query may speed things up.
Could you give me an example?

DDP Como

ASKER

I've attached the explain plan
query2.html

Kent Olsen

Let's come back to that.. :)

Another approach is to just outer join the to tables of the sub-query into the original result set.

I've compressed the query into fewer lines just so it's easier to read.

	SELECT
		prokeyi, probezc, provf2c, artbezc, artkeyi, artanrc, katbezc, etybezc, stabezc, vgbbezc, abpbezc, kvtbezc, kvtinac,  rafbezc, rfavf1c, rfavf2c, rfavf3c, rfavf4c, rfavf5c, depbezc, MIN(pflfldc)
	FROM
		pro, art, aez, kat, ety, dok, wob, abp, vgb, sta, kvt, kav, dep, rfa, raf, pfl, pty
	WHERE
		prokeyi = aezprokeyi (+)
		AND   aezkavkeyi (+) = prokavkeyi
		AND   artkeyi (+) = aezartkeyi
		AND   artkavkeyi (+) = aezkavkeyi
		AND   prokatkeyi = katkeyi
		AND   etykeyi (+) = proetykeyi
		AND   dokkatkeyi = katkeyi (+)
		AND   doksnei = prosnei (+)
		AND   wobabpkeyi = abpkeyi
		AND   wobobjkeyi (+) = dokkeyi
		AND   wobvarkeyi (+) = dokkavkeyi
		AND   wobstakeys = stakeys
		AND   dokvgbkeyi = vgbkeyi (+)
		AND   staabpkeyi = abpkeyi
		AND   abptyps = 150
		AND   wobtyps = 150
		AND   kavkatkeyi = katkeyi
		AND   kavkvtkeyi = kvtkeyi
		AND   kavkeyi (+) = prokavkeyi
		AND   rfakavkeyi (+) = dokkavkeyi
		AND   rfadokkeyi (+) = dokkeyi
		AND   rfadepkeyi = depkeyi
		AND   prorafkeyi = rafkeyi (+)
		AND   doksnei = prosnei (+)
		AND   pflobjkeyi (+) = prokeyi
		AND   prokavkeyi = pflvarkeyi (+)
		AND   pflptykeyi=17
		AND   probezc <> '---Page element---'
		AND   katinac = '3141'
		AND   prosnei = 2
		group by prokeyi, probezc, provf2c, artbezc, artkeyi, artanrc, katbezc, etybezc, stabezc, vgbbezc, abpbezc, kvtbezc, kvtinac,  rafbezc, rfavf1c, rfavf2c, rfavf3c, rfavf4c, rfavf5c, depbezc

Open in new window

DDP Como

ASKER

When I run this query I only get 8 results, versus the 7K I should see.

slightwv (䄆 Netminder)

What are the join columns between pfl and pty?

Can you use ANSI join syntax or are you stuck with the old ways? ANSI joins make things much easier to read.

If you hard code some values from the inner select can you post that explain plan as well?
select distinct pflfldc from pfl,pty where pflptykeyi = 17 and pflobjkeyi = prokeyi and prokavkeyi = pflvarkeyi

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.