I have some performance problems with subqueries in Oracle 9. THe performance problems are regarding Subqueries with OR's (see below). I' m searching for hints to let this query better perform (f.e. translate into UNIONS?). I hope you can help me!
SELECT DREL.RELNR , MIN(DREL.HHNR) FROM P_CHORDIANT.DREL DREL, P_DWH.DPERS DPERS WHERE
( ( DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS6.A1 FROM P_CHORDIANT.BS6 BS6)
OR DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS2.A1 FROM P_CHORDIANT.BS2 BS2)
OR DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS4.A1 FROM P_CHORDIANT.BS4 BS4)
OR DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS5.A1 FROM P_CHORDIANT.BS5 BS5)
OR DREL.RELNR IN ( SELECT /*+ USE_HASH(DPERS DREL DSEG SEGMENTATIE ) */ BS16.A1 FROM P_CHORDIANT.BS16 BS16) )
AND DPERS.VERSRCI = 'J' ) AND DPERS.RELNR = DREL.RELNR
SELECT DREL.RELNR , MIN(DREL.HHNR)
FROM P_CHORDIANT.DREL DREL,
P_DWH.DPERS DPERS ,
SELECT A1 FROM P_CHORDIANT.BS6
SELECT A1 FROM P_CHORDIANT.BS2
SELECT A1 FROM P_CHORDIANT.BS4
SELECT A1 FROM P_CHORDIANT.BS5
SELECT A1 FROM P_CHORDIANT.BS16
where DREL.RELNR = BS.A1
AND DPERS.VERSRCI = 'J'
AND DPERS.RELNR = DREL.RELNR
GROUP BY DREL.RELNR
but performance does depend a lot on data distribution, when you last analyzed the data, indexes etc..
Those subqueries are generated by a SQL generator for campaign management. So i cannot change the SQL. I can change hints for it. Oracle says there's a hint which is converting the SQL in a union like operator. I'm searching for a hint whoch make this query faster.
Originally posted by robertosp
Is it possible to automatrically change and OR into a UNION using a hint or Oracle stored outlines. Which hint to use? CAn anybody explain more in depth the Oracle stored outlines tool?
That's what Oracle does according to the docs (always worth a read!):
If a query contains a WHERE clause with multiple conditions combined with OR operators, then the optimizer transforms it into an equivalent compound query that uses the UNION ALL set operator, if this makes the query execute more efficiently:
It also says:
The USE_CONCAT hint forces combined OR conditions in the WHERE clause of a query to be transformed into a compound query using the UNION ALL set operator. Generally, this transformation occurs only if the cost of the query using the concatenations is cheaper than the cost without them.