Home » SQL & PL/SQL » SQL & PL/SQL » sql in sql
sql in sql [message #349155] Fri, 19 September 2008 02:21 Go to next message
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 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: Selecting Partial Duplicate Records
Next Topic: Avoid Mutating Trigger error
Goto Forum:
  


Current Time: Fri Dec 09 09:59:12 CST 2016

Total time taken to generate the page: 0.04655 seconds