Home » SQL & PL/SQL » SQL & PL/SQL » which block will execute faster. please suggest (oracle xe)
which block will execute faster. please suggest [message #348880] Thu, 18 September 2008 03:45 Go to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Suppose we have 2 code block. which block will execute faster.
If possible please suggest, why?
block:one
BEGIN
  SELECT COUNT (*)
    INTO v_cnt
    FROM TABLE1
   WHERE column1 = VALUE1;

  IF (v_cnt <= 0)
  THEN
     p_result := 'Y';      
  ELSE
     p_result := 'N';
  END IF;
END;


block:two
BEGIN
  SELECT 'Y' 
    INTO v_char
    FROM TABLE1
   WHERE column1 = VALUE1;

EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
       p_result := 'N';
   WHEN TOO_MANY_ROWS
   THEN
       p_result := 'Y';
END;


Re: which block will execute faster. please suggest [message #348884 is a reply to message #348880] Thu, 18 September 2008 03:54 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Block 2 might execute faster, but there is a bug in it.

What happens when there is exactly ONE row "WHERE column1 = VALUE1" in the table?

Re: which block will execute faster. please suggest [message #348887 is a reply to message #348880] Thu, 18 September 2008 04:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select decode(count(*),0,'N','Y') into p_result ...

(or the opposite as your 2 blocks return the opposite value.)
This is the fastest.

Regards
Michel
Re: which block will execute faster. please suggest [message #348892 is a reply to message #348880] Thu, 18 September 2008 04:12 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well for starters, the two pieces of code don't do the same thing: what happens when there is a single matching row?

In general, the non-exception verison can be expected to be quicker, as raising and hand;ing exceptions is a relatively time consuming process.
It's possibly to alter the relative costs of the two processes by the distribution of the data - if both queries have to do a FTS, AND the table is large, AND there are multiple matching rows near the start if the table, it is possible to make the Exception version more competative.

Proof:
Using the example below, if you set the table to have 10,000 rows, then you get relative times like this:
Select count, multiple values 370
Exception, multiple values 1520
Select count, no values 364
Exception, no values 747
Select count, values up front 360
Exception, values up front 1557


If you increse the number of rows to 1,000,000, then the relative costs shift to this (I dropped the number of iterations to 50 to get it to finish before I retire):
Select count, multiple values 356
Exception, multiple values 19
Select count, no values 784
Exception, no values 366
Select count, values up front 349
Exception, values up front 16



drop table test_0099;

create table test_0099 (col_1  number,col_2 varchar2(1));

insert into test_0099 select level ,'N' from dual connect by level <= 10000;

declare
  v_iter  pls_integer := 5000;
  v_time  pls_integer;
  v_cnt   pls_integer;
  v_res   varchar2(1);
begin
  update test_0099 set col_2 = 'Y' where mod(col_1,1000) =1;
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    select count(*)
    into   v_cnt
    from   test_0099
    where  col_2 ='Y';
    
    if v_cnt = 0 then
      v_res := 'Y';
    else 
      v_res := 'N';
    end if;
  end loop;
  
  dbms_output.put_line('Select count, multiple values '||to_char(dbms_utility.get_time - v_time));
  
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    BEGIN
    select 'Y'
    into   v_res
    from   test_0099
    where  col_2 ='Y';
    
    EXCEPTION
      when no_data_found then
        v_res := 'Y';
      when too_many_rows then
        v_res := 'N';
    END;
  end loop;
  
  dbms_output.put_line('Exception, multiple values '||to_char(dbms_utility.get_time - v_time));
  
  update test_0099 set col_2 = 'N';
  
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    select count(*)
    into   v_cnt
    from   test_0099
    where  col_2 ='Y';
    
    if v_cnt = 0 then
      v_res := 'Y';
    else 
      v_res := 'N';
    end if;
  end loop;
  
  dbms_output.put_line('Select count, no values '||to_char(dbms_utility.get_time - v_time));
  
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    BEGIN
    select 'Y'
    into   v_res
    from   test_0099
    where  col_2 ='Y';
    
    EXCEPTION
      when no_data_found then
        v_res := 'Y';
      when too_many_rows then
        v_res := 'N';
    END;
  end loop;
  
  dbms_output.put_line('Exception, no values '||to_char(dbms_utility.get_time - v_time));  
  
  update test_0099 set col_2 = 'Y' where col_1 < 5;
  
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    select count(*)
    into   v_cnt
    from   test_0099
    where  col_2 ='Y';
    
    if v_cnt = 0 then
      v_res := 'Y';
    else 
      v_res := 'N';
    end if;
  end loop;
  
  dbms_output.put_line('Select count, values up front '||to_char(dbms_utility.get_time - v_time));
  
  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop
    BEGIN
    select 'Y'
    into   v_res
    from   test_0099
    where  col_2 ='Y';
    
    EXCEPTION
      when no_data_found then
        v_res := 'Y';
      when too_many_rows then
        v_res := 'N';
    END;
  end loop;
  
  dbms_output.put_line('Exception, values up front '||to_char(dbms_utility.get_time - v_time));  
end;
/
Re: which block will execute faster. please suggest [message #348910 is a reply to message #348880] Thu, 18 September 2008 04:55 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
If you add
and rownum = 1
to the where clause of the first block the sql will be as fast as the one in the second block, but on PL/SQL side you won't need to activate exception handling mechanism witch is more time consuming than a simple if-then-else.

Bye Alessandro

[Updated on: Thu, 18 September 2008 04:56]

Report message to a moderator

Re: which block will execute faster. please suggest [message #348911 is a reply to message #348892] Thu, 18 September 2008 04:57 Go to previous message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

Thanks team,
this is really very vital explaination.
but how oracle will behave for these two code blocks.?
(condidering that select will return multiple values). For 'block one'
I understand, select will fetch all rows,count and process further.
but in case of 'block two' , does select will fetch all rows, then
raise exception.



Previous Topic: sql query
Next Topic: ERROR Handling in Multi Table Inserts
Goto Forum:
  


Current Time: Sat Dec 10 04:51:23 CST 2016

Total time taken to generate the page: 0.20563 seconds