Solved

A question related to NVL

Posted on 2003-11-18
9
1,507 Views
Last Modified: 2012-05-05
Hi Experts,

I have a question regarding Using functions in where clasue, in fact it is a question for where clause

Suppos, I have a query like

SELECT    count ( ROWID )
   FROM    table1
  WHERE   Upper ( Trim ( col1 ) ) = NVL ( :as_para, '' )

Now is the NVL operation performed for every row in table1 while comparing OR it is performed only once and then used for all the rows later

Thanks in advance

Amit
0
Comment
Question by:Sys_Prog
9 Comments
 
LVL 13

Expert Comment

by:riazpk
ID: 9776590
Quite Intresting question...


To my knowledge:

the NVL function will be performed for each and every row.
0
 
LVL 47

Expert Comment

by:schwertner
ID: 9776928
I am sure that Oracle will not calculate NVL function in every row selection if the argument is a bind variable (parameter).
But if you hesitate you can ensure that it is so. I base my consideration on the fact you use bind variable.
It seems to be parameter in a procedure or item in Forms, reports

Do this

v_param     Varchar2(90) :=  NVL ( :as_para, '' ) ;
.....
begin
......
SELECT    count ( ROWID )
  FROM    table1
 WHERE   Upper ( Trim ( col1 ) ) = v_param    

You have not to bother about the function NVL. It is very simple gunction.
In contrast to SIN(X) = x/1! - x**3/3! +x**5/5! + ..........
and other "monster" functions.

0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 9776952
It will only be computed once.  The thing you will have to worry about with this one is what you are doing to the col1 column, Upper ( Trim ( col1 ) ) will be done for every row.  

A better performing option (sometimes) would be to do:

SELECT    count (1)
   FROM    table1
  WHERE   :as_para IS NULL
        OR   Upper ( Trim ( col1 ) ) = :as_para

So at least you wouldn't be doing your upper(trim()) if the passed in value is null - but it would depend on how often it is run with null, or when not null.  Ah, but in fact you're using bind variables, so, that's sometimes a bit of a grey area (depending on which version of Oracle you are using).  If it is vital that the text be in the Upper'ed format and trimmed, it may be worth putting a function based index on the table, or the possibility of converting the value to always be uppercased, and trimmed when inserted into the database (possibly by a trigger).

There were a couple of posts on here recently for the use of OR and the bind variables stuff, I'll try to find them for you...
0
 
LVL 7

Expert Comment

by:grim_toaster
ID: 9776961
As mentioned in previous post, this may help you (or be completely useless) :)

http://www.experts-exchange.com/Databases/Oracle/Q_20790890.html
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Author Comment

by:Sys_Prog
ID: 9776994
Hi Guys,

Thanks for your quick response

I am using Oracle 9i and yes, it is a local variable. Also, I am trigerring this Select statement as an embedded SQL from PB.

In this case, will the NVL be executed on every row OR only once. In either case, if someone could provide me with some link, it would be of great help

Thanks in adv

Amit
0
 
LVL 2

Accepted Solution

by:
vanmeerendonk earned 125 total points
ID: 9778095
Just another testcase to get you thinking:
Table Test with 1 column with numbers with 4 rows
10
10
10
5
Function testrow:
create or replace function testrow (p_in number) return number
is
begin
  dbms_application_info.set_client_info(nvl(userenv('CLIENT_INFO'),0)+1);
  return p_in;
end;

select * from table where p_in = testrow(:b);   --> :b_in is 10

Then:
3 rows returned and client_info-column in V$session says 4
So:
the function is executed for every row.

When you index the column, then you see that the function is not executed for every row.
NVL is just another (in this case kernel-)function.
In your case I would say the NVL goes for every row

0
 
LVL 23

Expert Comment

by:seazodiac
ID: 9779277
Sys_Prog:

The NVL function will, without any doubts, perform for each and every row. You using bind variable has nothing to do with the number of times it executes, it only affect the number of the parse times.
0
 
LVL 10

Author Comment

by:Sys_Prog
ID: 9784841
Hi all,

logically speaking, oracle should execute only once because it's a variable and not a column.

vanmeerendonk's  answer was really really good, just have one doubt after that, are user-defined functions treated the same way as the built-in functions.

Thanks in adv

Amit
0
 
LVL 10

Author Comment

by:Sys_Prog
ID: 9919924
Hi Guys,

Not yet confirmed of the answer but really liked  vanmeerendonk's approach,

Therefore accepted his answer [It's for his approach and not the answer because I am yet to confirm that]

Anyways,

Thanks again

Amit

0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now