Home » SQL & PL/SQL » SQL & PL/SQL » What is the best way of forming a query?
What is the best way of forming a query? [message #375780] Sun, 14 December 2008 10:12 Go to next message
umajava
Messages: 8
Registered: December 2008
Junior Member
I would like to know what is the best way of forming a query in terms of standards and performance.

Query1
SELECT T1.COL1,
       T1.COL2,
       T2.COL1,
       CALL_SOME_FUNCTION(T2.COL2)
FROM TABLE1 T1, TABLE T2
WHERE T1.COL1 = T2.COL1

CREATE OR REPLACE CALL_SOME_FUNCTION(P_ID VARCHAR2) RETURN VARCHAR2 IS
RES VARCHAR2(100);
BEGIN

--THIS QUERY IS BIT MORE COMPLICATED THAN IT LOOKS BELOW IT WILL HAVE MORE TABLES AND JOINS. 
--KEEPING IT SIMPLE FOR GIVING OUT THE PROBLEM
SELECT T3.COL1 INTO RES FROM TABLE3 T3 WHERE T3.ID = P_ID

RETURN RES;
END;

In Query 2, I wanted to bring the query used in the function to be used in the main query itself. But as I said in above the query in the function is bit more complicated then it looks. But I am trying to understand the process / standards / performance issues.
Query2
SELECT T1.COL1,
       T1.COL2,
       T2.COL1,
       T3.COL1
FROM TABLE1 T1, TABLE T2,
(SELECT T3.COL1 COL1 FROM TABLE3 T3) T3
WHERE T1.COL1 = T2.COL1
AND T3.ID = T2.COL2

[Updated on: Sun, 14 December 2008 10:31] by Moderator

Report message to a moderator

Re: What is the best way of forming a query? [message #375782 is a reply to message #375780] Sun, 14 December 2008 10:34 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Always merge PL/SQL inside SQL when you can.
Never use PL/SQL inside SQL when you can.

Depending on many and many things, you can put the query as scalar subquery inside select list instead of the table inside from list.

Regards
Michel
Previous Topic: Challenging Query
Next Topic: how to emulate "order siblings by" in 8i?
Goto Forum:
  


Current Time: Mon Dec 05 13:16:41 CST 2016

Total time taken to generate the page: 0.09901 seconds