sql in sql [message #349155] |
Fri, 19 September 2008 02:21 |
xyzt
Messages: 27 Registered: April 2008
|
Junior Member |
|
|
hello
i saw a query in the project that i work and i wonder if that query causes a performance bottleneck or oracle optimizes the query.
here's the query:
SELECT
col1,col2
FROM T1
WHERE T1.col3=( SELECT
MAX(col4)
FROM T1 )
won't it be better if the MAX(col4) is assigned to a variable at first and use it in the query?
I mean:
v := SELECT MAX(col4)FROM T1
SELECT
col1,col2
FROM T1
WHERE T1.col3 = v
May you help please?
Thanks in advance.
|
|
|
Re: sql in sql [message #349156 is a reply to message #349155] |
Fri, 19 September 2008 02:34 |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Ican recommend no better approach than trying it yourself and measuring the speed.
I had 5 minutes free, so I did just that:create table test_0100 (col_1 number, col_2 number);
insert into test_0100 select level, mod(level,floor(level/2)) from dual connect by level <= 10000;
declare
v_iter pls_integer := 10000;
v_time pls_integer;
v_max pls_integer;
v_res varchar2(1);
begin
v_time := dbms_utility.get_time;
for i in 1..v_iter loop
SELECT col_2
into v_res
FROM test_0100
WHERE col_1=( SELECT MAX(col_1)
FROM Test_0100 );
end loop;
dbms_output.put_line('One query '||to_char(dbms_utility.get_time - v_time));
for i in 1..v_iter loop
select max(col_1) into v_max from test_0100;
select col_2 into v_res from test_0100 where col_1 = v_max;
end loop;
dbms_output.put_line('Two Queries '||to_char(dbms_utility.get_time - v_time));
end;
/
One query 2455
Two Queries 4183
As you can see - one query is substantially faster in this test case.
A good rule of Thumb:
Never do in Pl/Sql what you can do in SQL instead.
|
|
|
Re: sql in sql [message #349157 is a reply to message #349155] |
Fri, 19 September 2008 02:34 |
harrysmall3
Messages: 109 Registered: April 2008 Location: Massachusetts
|
Senior Member |
|
|
xyzt,
Both of your examples are invalid syntax, try running them. To answer in general,
Quote: | won't it be better if the MAX(col4) is assigned to a variable at first and use it in the query?
|
No. Why execute two SQL calls and one PL/SQL statement when one
query will suffice?
Regards,
Harry
|
|
|