Try using the primary key value like if PRD_LVL_CHILD is the primary key then just add
a where cond using and operator like PRD_LVL_CHILD0. This will make the usage of
index in the table PRCMSTEE.
May be this is not the actual way, but we have tuned several queries like this and
they all are
Hi Suhen,
The UNION ALL will return duplicates from the two queries, whereas the
original query will not, right? So the query is tuned, but it returns a
different result set.
Do you agree?
Best regards,
- Greg
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg
Greg,
Correct me if I am wrong but both will return duplicates since the OR
operator is used.
The primary key is on PRD_LVL_CHILD.PRDMSTEE.
Regards
Suhen
Hi Suhen,
The UNION ALL will return duplicates from the two queries, whereas the
original query will not, right? So the query is tuned,
both will return duplicates since the
OR operator is used.
select ename from emp where mgr = 1 or
mgr in (1);
-- If there are three rows where mgr = 1, this
query will return three rows.
select ename from emp where mgr =
1
union all
select ename from emp where mgr in
(1)
-- But this
List,
I require help with tuning a query.
SELECT PRC_TYPE FROM PRCMSTEE
WHERE PRD_LVL_CHILD = 505
OR PRD_LVL_CHILD IN (SELECT PRD_LVL_PARENT
FROM PRDMSTEE
WHERE PRD_LVL_CHILD = 505 )
/
Query Plan
--
SELECT STATEMENT
List,
After playing around with this query on TEST I tried using a UNION
ALL rather than the OR operator.
SELECT PRC_TYPE FROM PRCMSTEE
WHERE PRD_LVL_CHILD = 505
UNION ALL
SELECT PRC_TYPE FROM PRCMSTEE
WHERE PRD_LVL_CHILD IN (SELECT
Assuming there is an index on PRDMSTEE.PRD_LVL_PARENT
and PD.PRD_LVL_PARTENT:
SELECT PC.PRC_TYPE
FROM PRCMSTEE PC
WHERE PC.PRPRD_LVL_CHILD = 505
OR EXISTS (
SELECT NULL
FROM PRDMSTEE PD
WHERE PRD_LVL_CHILD = 505
AND PD.PRD_LVL_PARENT = PC.PRD_LVL_CHILD
)
