Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, patibandla.

Asked: March 04, 2001 - 6:43 am UTC

Last updated: September 10, 2012 - 7:26 pm UTC

Version: 8.1.6.0

Viewed 10K+ times! This question is

You Asked

I am writing sql which extensively uses user defined functions.

The Usedefined functions basically consists
1. Arithemetic operations intensive (No Select Statements)
2. SQL Intensive (Selection based on the parameters)

1. select * from table where ar_operation(column) and
sql_operations(column)
or

2. select * from table where all_operations(column)

which of the above is better
1. when u have more than 3 or more sql statements in a functions and around 100 lines of ar_operations code.

2. when u have les than 3 sql statements in a functions and around 20/30 lines of ar_operations code.

Thanks in advance

Regards





and Tom said...

Well, if SOME of the functionality is deterministic -- that is:

f(x,y,z)

always returns the same value given the same values for X, Y, and Z (eg: f(1,2,3) always returns 5. No matter what values I pass to F, a deterministic value is returned) I would break my functions into:

o deterministic
o non-deterministic.


For the deterministic functions, we can create an index on them and massively speed up processing. For exmaple, UPPER() is deterministic. I can create an index:

create index emp_idx on emp(upper(ename));

Now, when I search:

select * from emp where upper(ename) = 'KING';

that query will NEVER actually call the the upper() function, rather it will take the value 'KING' and goto the index and find the hits.

So, if you have some complex function f that takes some inputs -- you could:

create index t_idx on t(f(x,y,z));

and later when you query:

select * from t where f(x,y,z) between 1 and 20;

it'll never actually execute f, it will use the index. In this way you can:


select *
from T
where DETERMINISTIC_FUNCTION ....
and NON_DETERMINISTIC_FUNCTION

and the optimizer will use the index when possible, avoiding calling PLSQL for those values.

See
</code> http://asktom.oracle.com/~tkyte/article1/index.html <code>
for details.


Rating

  (115 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

limitations of calling functions from sql

Kulguru, January 04, 2002 - 2:56 pm UTC

Tom

when we call functions from sql

1.We cannot return a date
2.we cannot pass out and inout parameters

Why is this so .. and

what are the other limitations.



Tom Kyte
January 04, 2002 - 8:12 pm UTC

1) is false.  you can so return a date

ops$tkyte@ORA817DEV.US.ORACLE.COM> create function foo return date
  2  as
  3  begin
  4  return sysdate;
  5  end;
  6  /

Function created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select foo from dual;

FOO
---------
04-JAN-02

2) it doesn't MAKE SENSE to have OUT parameters.  I mean, what would it mean to:

select foo(dummy) from dual;

where the parameter to foo was an OUT parameter?  What would it change (rhetorical question -- it wouldn't change anything).

This is not a limitation, rather its more like common sense.  There is no place to return that OUT parameter TO..


 

Correction

Kulguru, January 04, 2002 - 10:32 pm UTC

A function called in a sql cannot return a boolean. This was my doubt. Why is it so.

Tom Kyte
January 05, 2002 - 10:54 am UTC

because you cannot:


create table t ( x boolean );

boolean is not a SQL type. SQL can only deal with SQL types. The function can return ANY datatype that you can use to define a database column with -- no more, no less (this includes UDT's -- object types, they can return those)

Where can I get a list of sql types and pl/sql types

Kulguru, January 05, 2002 - 11:08 am UTC

Tom

Can you give the link to the documents where I can find a list of sql types and pl/sql types.

Thanks

Tom Kyte
January 05, 2002 - 8:12 pm UTC

concepts guide (the MOST UNDER-READ document in the oracle doc set. if you read this from cover to cover and retain 20% of it, you'll know more then 80% of the rest of the Oracle world!)

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c10datyp.htm#629

for plsql types, lo and behold -- the PLSQL manual has a section titled "datatypes"

http://docs.oracle.com/docs/cd/A87860_01/doc/appdev.817/a77069/02_funds.htm#10531 <code>

....

What could be the effect when it is in RBO mode

lakshmi narasimhan, March 24, 2002 - 5:12 am UTC

Tom

Great. In fact i am having the same kind of problem while using User defined functions.

I am just cut and paste my query.
our DB is RULE (ver 8.1.7)

Due to some requirement, i need to call function in the where clause
as row value as parameter. the column i am sending is one of the
column in the unique index along with some other parameter

Wrote file afiedt.buf

1 SELECT A.tarnum, A.cardsgcod, orgloctyp,orgloccod orgctycod,
2 dstloctyp,dstloccod dstctycod,
3 A.fabcod, jnytyp, faramt, farcurcod, tvleffdat, tvldisdat,a.crltyp,
4 A.rounum, A.rulcod, fntcod,A.datsou,A.lnknum,A.seqnum
5 FROM fapfarhdr a
6 WHERE Datsou='0'
7 and Cardsgcod='EK'
8 and (1 = check_location(orgloccod,orgloctyp))
9 and (1 = check_location(dstloccod,dstloctyp))
10 AND fabcod = 'Y'
11* AND crltyp = '3'

to facilitate the user to enter what ever the type of or group of location in a single
row. ( they will file air lines Fares from set of cities to set of cities)

Now my performance is going for all time low.

with out the function it is taking around 2 mts for more than 1000 plus records, with function it is taking more than 10 mts. is it calling a function from the Query in caseof RBO is the reason?

Tom Kyte
March 24, 2002 - 3:43 pm UTC

create a function based index on

check_location(orgloccod,orgloctyp),
check_location(dstloccod,dstloctyp),
datsou,
cardsgcod,
fabcod,
crltyp


and see what happens. I don't know what "mts" is (is that some sort of abbreviation for "minutes"??) but it would be 100% a function of how long it takes to execute your function. If it takes a quarter of a second or thereabouts (a little less actually) to run YOUR code, then easily it would take an extra 8 minutes as 8*60 = 1000*2*.24 for example.



But It is Rule Based

A reader, March 24, 2002 - 11:40 pm UTC

Hi Tom,

Just thought of highlighting this. The Previous Poster is using a Rule Based Optimiser so What is the use of him creating a Function Based Index.

Your comments pls.

Regards,
Ganesh R

Tom Kyte
March 25, 2002 - 7:55 am UTC

I ignored that -- when they read about fbi's they will discover that you need to use the CBO and will use it if they want the phenomenal performance gains that can be achieved by that optimizer (and access to the host of other functionality that is not otherwise available).

You can always use a HINT as well, especially with FBI's.

But i am using Rule Base

Lakshmi narasimhan R, March 25, 2002 - 2:18 am UTC

Hi tom

thanks for immediate response

But i cannot create function based since i am
using RBO. Is there any efficient workaround in RBO
itself.



Tom Kyte
March 25, 2002 - 8:03 am UTC

Don't use PLSQL then. Incorporate the logic inline. I find that 99.99% of the time, the logic in the plsql routine can be done using SQL with decodes and/or CASE statements.

You can ALWAYS hint the query as well - to tell it to use the index (which will invoke the CBO and will use the function based index)

You should use the CBO. Think outside the box. Use the tools available.

A reader, March 25, 2002 - 8:47 am UTC

hi tom.

Can you explain more about o deterministic and
o non-deterministic ?

What are these?

you told :
f(x,y,z)

always returns the same value given the same values for X, Y, and Z (eg:
f(1,2,3) always returns 5. No matter what values I pass to F, a deterministic
value is returned) .

What do you mean always return 5 ?

Tom Kyte
March 25, 2002 - 12:18 pm UTC

deterministic means that given the SAME inputs, the function will return the same OUTPUT.

take:

x := f(1,2,3);
y := f(1,2,3);


if F is deterministic, that can be written as:

x := f(1,2,3);
y := x;

if F is NOT deterministic, it cannot.


Review

Momen, March 26, 2002 - 12:56 am UTC

Hi Tom,

Is the following function a Deterministic or a Non-Deterministic function ???

Create or Replace Function Det_or_Not(p_emp_no in emp.emp_no%type) return emp.emp_name%type is
v_emp_name emp.emp_name%type;
begin
select emp_name into v_emp_name
from emp
where emp_no = p_emp_no;

return v_emp_name;
end;
/

The above query returns emp_name based on emp_no, what if I delete the row or even update the name ???

2. How do I find all of your articles from osi
i.e. </code> http://asktom.oracle.com/~tkyte/ <code>

Thanks

Tom Kyte
March 26, 2002 - 7:44 am UTC

It is non-deterministic.

A simple "update emp set emp_name = 'foo' " will change the return value for a given p_emp_no. Given the input 12345 -- this function may return a different value at two different points in time.

as for "how to find all fo the articles on osi" -- you have the link right there. they are all listed there.

function take longer ?

Charanjiv, March 26, 2002 - 1:16 am UTC

you said - "but it would be 100% a function of how long it
takes to execute your function. If it takes a quarter of a second or thereabouts (a little less actually) to run YOUR code, then easily it would take an extra 8 minutes as 8*60 = 1000*2*.24 for example."
Could you elaborate? esp in the context if i have 1000 lines of pl/sql code in a procedure and i take out some code and put then in a function. does this hold true then also?.


Tom Kyte
March 26, 2002 - 7:48 am UTC

It is simple math here. Very simple math

You fetch 1,000 records.
You call a function 2 times for each record.
Hence you call a function 2,000 times.

If that function takes N units of time to execute, you will consume 2,000*N units of time AT LEAST during the processing of your query. If your function takes 0.24 seconds to execute on average, you will consume 480 seconds of runtime in 2,000 calls. That is 8 minutes.

Replacing 100 lines of code in a procedure that started with 1000 with a function that has 100 lines of code will result in -- 1,000 lines of code. Functions are good for programmers, they do nothing to increase performance, only code reuse (in fact, a compiler optimization is to "inline code" to remove functions since a function itself has overhead in the calling of it).



calling the same function twice

john, July 07, 2003 - 6:03 am UTC

Tom,

Question on the answer to last followup(Charanjiv's question)

if i use function two times in a query with same parameters will CBO calls the function twice?

say for example:

select your_bonus(salary) from emp where your_bonus(salary) > 0;

so, your_bonus(salary) is being called twice?

I want this to be called only once, is there any hint with which i can make that to happen?



Tom Kyte
July 07, 2003 - 7:58 am UTC

It may, it may not.  It might never be called, it might be called once for each row and then again for each row that satisfied the predicate.  It might be called once for each row.

It depends.  Consider:


ops$tkyte@ORA920> create table emp ( empno number primary key, comm number ) organization index;
Table created.

ops$tkyte@ORA920> insert into emp
  2  select object_id, decode( mod(rownum,2), 0, object_id, null )
  3    from all_objects
  4   where rownum <= 1000;
1000 rows created.

ops$tkyte@ORA920> analyze table emp compute statistics;
Table analyzed.

<b>so, emp is a table with 1,000 rows and every other COMM is NULL -- so the query will return 500 out of 1,000 rows</b>

ops$tkyte@ORA920> create or replace function your_bonus( p_empno in number ) return number
  2  deterministic
  3  as
  4          l_comm number;
  5  begin
  6          dbms_application_info.set_client_info( sys_context( 'userenv', 'client_info')+1 );
  7
  8          select comm
  9            into l_comm
 10            from emp
 11           where empno = p_empno;
 12
 13          return l_comm;
 14  exception
 15          when no_data_found then
 16                  raise program_error;
 17  end;
 18  /
Function created.

<b>that function will let us easily count how many times it's called due to the dbms_application_info stuff...</b>


ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> set autotrace traceonly
ops$tkyte@ORA920> select your_bonus(empno) b from emp;
1000 rows selected.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=3000)
   1    0   INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_38955' (UNIQUE) (Cost=2 Card=1000 Bytes=3000)

ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
--------------------
1000

<b>that is to be expected -- 1,000 rows -- 1,000 calls</b>


ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> set autotrace traceonly
ops$tkyte@ORA920> select your_bonus(empno) b from emp where your_bonus(empno) > 0;

500 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=50 Bytes=150)
   1    0   INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_38955' (UNIQUE) (Cost=2 Card=50 Bytes=150)


ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
--------------------
1500

<b>that shows that in this case, the function was called ONCE in the predicate for each of the 1,000 rows and then again in the select list for the 500 rows that actually met the criteria.</b>


ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> set autotrace traceonly
ops$tkyte@ORA920> select b
  2    from (select your_bonus(empno) b
  3            from emp
  4                   where rownum > 0 ) EMP
  5   where b > 0;

500 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1000 Bytes=13000)
   1    0   VIEW (Cost=2 Card=1000 Bytes=13000)
   2    1     COUNT
   3    2       FILTER
   4    3         INDEX (FAST FULL SCAN) OF 'SYS_IOT_TOP_38955' (UNIQUE) (Cost=2 Card=1000 Bytes=3000)


ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
--------------------
1068

<b>rownum is very handy for helping us out there.  The optimizer cannot merge the predicate "where b > 0" into the subquery due to that. So, your_bonus(empno) is materialized into "temp" in effect and reused.  Now you might wonder why 1,068?  well, if you 


ops$tkyte@ORA920> set arraysize 1000
ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> set autotrace traceonly
ops$tkyte@ORA920> select b
  2    from (select your_bonus(empno) b
  3            from emp
  4                   where rownum > 0 ) EMP
  5   where b > 0;

500 rows selected.

ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
--------------------
1002

You can see it is a function of the number of times we fetched from this result set.  Larger array fetches will reduce this number

Now for the "best" perhaps</b>


ops$tkyte@ORA920> create index bonus_idx on emp(your_bonus(empno));
Index created.

ops$tkyte@ORA920> alter session set query_rewrite_enabled=true;
Session altered.

ops$tkyte@ORA920> alter session set query_rewrite_integrity=trusted;
Session altered.


ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.

ops$tkyte@ORA920> set autotrace traceonly
ops$tkyte@ORA920> select your_bonus(empno) b from emp where your_bonus(empno) > 0;

500 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=50 Bytes=150)
   1    0   INDEX (RANGE SCAN) OF 'BONUS_IDX' (NON-UNIQUE) (Cost=2 Card=50 Bytes=150)



ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
--------------------
0

ops$tkyte@ORA920>

<b>you cannot beat 0 times</b>

 

CHAR Datatype question

A reader, July 07, 2003 - 12:32 pm UTC

The following is taken from the Oracle Documentation.
I am unable to understand the third point.
<quote>
CHAR Datatype
The CHAR datatype stores fixed-length character strings. When you create a table with a CHAR column, you must specify a string length (in bytes, not characters) between 1 and 2000 for the CHAR column width. The default is 1. Oracle then guarantees that:

==1) When you insert or update a row in the table, the value for the CHAR column has the fixed length.

==2) If you give a shorter value, then the value is blank-padded to the fixed length.

==3) If you give a longer value with trailing blanks, then blanks are trimmed from the value to the fixed length.

==4) If a value is too large, Oracle returns an error.

Oracle compares CHAR values using blank-padded comparison semantics.

</quote>


It says if the value is longer than the defined size and has trailing blanks then these are trimmed and the value is inserted or updated in the table. I tried to do this but it gives me an error. Is this a doc error or am I misinterpreting the documentation?

RKPD01> drop table t1;

Table dropped.

RKPD01> create table t1(x char(5));

Table created.

RKPD01> insert into t1 values(rpad('A', 6));
insert into t1 values(rpad('A', 6))
*
ERROR at line 1:
ORA-01401: inserted value too large for column


I am inseting a longer value with trailing blanks. But it throws an error as mentioned in point 4. Point 3 and 4 seem to contradict each other can you enlighten me about the same.

Thanks


Tom Kyte
July 07, 2003 - 2:01 pm UTC

seems to be a doc bug, I've logged on on it as of reading this.

calling the same function twice

John, July 09, 2003 - 6:03 am UTC

Tom, thanks for the followup.

an extract from your followup:

"
The optimizer cannot merge the
predicate "where b > 0" into the subquery due to that. So, your_bonus(empno) is
materialized into "temp" in effect and reused."

so,i have a query like this:

select sum(alias1),
sum(decode(alias2,0,0,alias1))

(

select function1 alias1, function2 alias2 from
<tables> where <where clause>

)


in the above query's inner query also do i need to use rownum > 1?

Tom Kyte
July 09, 2003 - 11:06 am UTC

if you want the function called a minimum number of times, quite possibly.

test it and see -- I showed how to use dbms_application_info to easily benchmark these situations.

OK

Chakravarthi, August 06, 2003 - 8:21 am UTC

Dear sir,
Well and wish the same from you.I have a question for you related to function.
1)I tried to create a function like
create or replace function f(arg in number,sqr out number,
cube out number)
return number is
begin
...code
I tried to out the parameters sqr and cube but the compiler is throwing errors.How to correct this and how to invoke that function after it gets created.
Whether I have to use another pl/sql block or use execute
immediate involving returning clauses.Please help me with code.
Thanks,
Yours sincerely
R.Chakravarthi


Tom Kyte
August 06, 2003 - 8:58 am UTC

ops$tkyte@ORA920> create or replace function f ( arg in number, sqr out number, cube out number ) return number
  2  is
  3  begin
  4          sqr := arg*arg;
  5          cube := sqr*arg;
  6          return arg;
  7  end;
  8  /

Function created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2          l_cube number;
  3          l_sqr  number;
  4  begin
  5          dbms_output.put( 'the square and cube of ' ||
  6                            f( 5, l_sqr, l_cube ) );
  7      dbms_output.put_line( ' is ' || l_sqr || ',' || l_cube );
  8  end;
  9  /
the square and cube of 5 is 25,125

PL/SQL procedure successfully completed.
 

Question on the function return varchar2 length

Frank, September 25, 2003 - 6:56 pm UTC

A question along the long of using functions. If I define a function with return data type of VARCHAR2, I always get VARCHAR2(4000). Is there any way to restrict the length of the return string?

Thanks!

Tom Kyte
September 25, 2003 - 11:36 pm UTC

other then using SUBSTR, no

function based index in rule

A reader, September 29, 2003 - 3:16 pm UTC

Tom,

I understand your article when it states "Use the Cost Based Optimizer. Function based indexes are only visible to the Cost Based Optimizer and will not be used by the Rule Based Optimizer ever.".

However, we are a software house and have some clients who will be using RULE and some using CBO. In our software upgrade, we are introducing function-based indexes. I would prefer to always create the index for all of our clients regardless of their optimizer mode. However, I don't want to create negative performance results for those using RULE. Is there a performance degradation in maintaining function-based indexes if no statisics exist and RULE is the optimizer mode?

Thanks for your reply.

Tom Kyte
September 29, 2003 - 4:35 pm UTC

I don't understand -- why are you not DICTATING the optmizer mode be CBO?

You own the code
You own the queries
You have to support it
I would assume they call you for performance issues.

I don't see how you could possibly do the last two things without using EITHER OR, but not both?????


your application has a requirement to have its tables analyzed, so be it.

Q on Functions used in Select

Anil Pant, October 09, 2003 - 1:56 am UTC

Hello,
I want to know why its not possible to perform DML operations in functions which are called in select statements on tables which are not used in select statement. 
In the example below I can understand if function foo tries to update table akp1. But what's the reason it does not allow to do any DML operation on akp2 or any other table even when the select is not using that table.

Here is the example
create table akp1 (col1 number(5), col2 number(5))

create table akp2 (col1 number(5), col2 number(5))

create or replace function foo (pcol number) return number  is
begin
update akp2 set col2 = pcol;
return pcol*10;
end;
/


insert into akp1 values(1,1)


insert into akp2 values(1,0)

SQL> select col1, col2, foo(col2) from akp1;
select col1, col2, foo(col2) from akp1
                   *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "ISV310_DEV.FOO", line 3

 

Tom Kyte
October 09, 2003 - 4:26 pm UTC



Tell me -- in a 100% deterministic fashion -- given that akp1 has the following data:


COL1 COL2
----- ------
1 2
2 1


what is the value of col2 in akp2 after that query runs? 1 or 2. No matter what you say, i'll show you it is the other value.

It is what is termed "not safe", the results are ambigous.

Tell me, can you tell (give that table T has 100 rows in it) how many times the function F would be called:


select * from t where x = f();

no matter what you answer, i'll show a different answer. It is ambigous -- hence, prevented.

You can "get around" it by using an autonomous tranaction but that is so not recommended.

Could not get you

Naina, October 09, 2003 - 11:43 pm UTC

Sorry Tom I cud not get you? Could you be more specific. What did you mean by this

"select * from t where x = f();
no matter what you answer, i'll show a different answer. It is ambigous -- hence, prevented."


Tom Kyte
October 10, 2003 - 8:01 am UTC

if f() did modifications in the database -- what would the end result of invoking that sql query be? 

You CANNOT answer that because you cannot answer the question "how many times will f() be called".  I can make F() be called a different number of times with that query using the SAME exact table with the SAME exact data.


ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t ( cnt int );
 
Table created.
 
ops$tkyte@ORA920> insert into t values (0);
 
1 row created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create table t2 ( x int );
 
Table created.
 
ops$tkyte@ORA920> insert into t2 select rownum from all_objects where rownum <= 100;
 
100 rows created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create or replace function f return number
  2  as
  3          pragma autonomous_transaction;
  4  begin
  5          update t set cnt = cnt+1;
  6          commit;
  7          return 55;
  8  end;
  9  /
 
Function created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from t2 where x = f();
 
         X
----------
        55
 
ops$tkyte@ORA920> select * from t;
 
       CNT
----------
       100
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> create index t2_idx on t2(x);
 
Index created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select * from t2 where x = f();
 
         X
----------
        55
 
ops$tkyte@ORA920> select * from t;
 
       CNT
----------
       102
 
ops$tkyte@ORA920>


<b>see how the addition of a simple index, or a change in a query plan or because it is raining on tuesday -- changes how many times F() is called, hence makes the result of F() updating data "ambigous"</b>

Also consider if F() was passed some data -- and the order in which F() was called "mattered".  ambiguities ABOUND.  This is a really bad idea. 

Pls clarify

Naina, October 13, 2003 - 7:30 am UTC

Going by your example here's what I've done.

Case 1 : First without creating the index ran the query

SQL> select * from t2 where x = f();

         X
----------
        55

SQL> select * from t;

       CNT
----------
       100

SQL> select * from t2 where x = f();

         X
----------
        55

SQL> select * from t;

       CNT
----------
       200

SQL> select * from t2 where x = f();

         X
----------
        55

SQL> select * from t;

       CNT
----------
       300


All the rows of the table t2 was scanned and table t was updated everytime. 
So, select * from t2 where x = f();
would update 100 to table t.
You can see the increments by 100 everytime

Case 2 : Created Index

Now I dropped the tables and created again. Also created an index. Now the values were different everytime I ran the query.

SQL> select * from t2 where x = f();

         X
----------
        55

SQL> select * from t;

       CNT
----------
       100

SQL> create index t2_idx on t2(x);

Index created.

SQL> select * from t2 where x = f();

         X
----------
        55

SQL> select * from t;

       CNT
----------
       102

SQL>  select * from t2 where x = f();

         X
----------
        55

SQL> select * from t;

       CNT
----------
       104

SQL> select * from t2 where x = f();

         X
----------
        55

SQL> select * from t;

       CNT
----------
       106


Now the table t was updated by 2 everytime. So what's happening and how its connected to Functions in SQL

 

Tom Kyte
October 13, 2003 - 7:34 am UTC


When you full scan -- we are:


for C in ( select * from t )
loop
if C.x = f() then output
end loop


we call f() once per row.


When we index scan -- we are processing:


temp := f()
do an index lookup to find rows with temp and output them


(please don't ask why we call it 2 times, i've no idea -- technically it seems once should do but it does it twice).


My point is -- you and I have NO CONTROL over how or when f() is called -- not even how many times f() is called and it can change from day to day -- hence it is "ambigous" and "totally unsafe" to have f() be part of a larger transaction. It would be like using dbms_random to update your salary.

What if function is complex and we are using Standard Edition

Matt, February 02, 2004 - 9:00 pm UTC

Standard Edition 9.2

I have a requirement to validate the sum of the individual characters of a number of records. I can do this in PL/SQL, but it is too slow - I need a more efficient (SQL) solution. As I don't know the length of the string in each case I don't think that this can be done in SQL.

I need to validate these strings as they arrive in a flat file. My validation is carried out as part of the data load (so FBI won't work, as I have STD EDITION)

Here is an example:

53 -> 8
108 -> 9
48 -> 12
96 -> 15
48 -> 12
98 -> 17
50 -> 5
102 -> 3
53 -> 8
110 -> 2
sum := 91

Any ideas?

Best Regards



Tom Kyte
February 03, 2004 - 7:43 am UTC

no idea what that data is supposed to mean?

what do the rows in a table look like here? I mean -- your example shows N number of rows we need to sum over. is that "for real"? what does the data you are needing to do this to look like in the TABLE.

One Approach....

Matt, February 02, 2004 - 11:19 pm UTC

This was part of a more complex problem. I think this does the job!

create type array
as table of number
/

create or replace function vtable (n in number default null)
return array
pipelined
as
begin
for i in 1.. nvl(n,999999999)
loop
pipe_row(i);
end loop;
return;
end;

create table t (col varchar2(4))
/
alter session set cursor_sharing=force
/
insert into t(col) values ('53')
/
insert into t(col) values ('108')
/
insert into t(col) values ('48')
/
insert into t(col) values ('96')
/
insert into t(col) values ('48')
/
insert into t(col) values ('17')
/
insert into t(col) values ('50')
/
insert into t(col) values ('102')
/
insert into t(col) values ('53')
/
insert into t(col) values ('110')
/

select char_pos
,pad
,sum(substr(pad,length(pad)- row_num + 1,1)) as ch
from ( select row_number() over (partition by char_pos,alt_ascii_dbl order by alt_ascii_dbl ) row_num
,char_pos
,lpad(alt_ascii_dbl,4,'0') as pad
from ( select rownum char_pos
,col alt_ascii_dbl
from t )
,table(vtable(4))
)
group by char_pos, pad
/

1 0053 8
2 0108 9
3 0048 12
4 0096 15
5 0048 12
6 0017 8
7 0050 5
8 0102 3
9 0053 8
10 0110 2

More Info from previous post (to add context )...

Matt, February 03, 2004 - 10:20 pm UTC

I have a set of varchar2(10) string. From these I need to generate a single digit using a defined algorithm. The algorithm carries out various operations on each individual character that makes up the string (converts to ascii, doubles these values, other stuff). The end result (the single digit) is used a validation that the varchar2(10) string is correct.

I have to validate lots and lots of these (4 million+) and currently have a PL/SQL procedure to carry this out. I have been looking at options to do this using standard edition.

Prior to the post above, I had all but the last step (described above) encapsulated in a single SQL statement.

I think that I now have all the info I need to carry out the entire validation in SQL.

Function call in Subselect

Olga, February 04, 2004 - 9:17 am UTC

Hi Tom,

I have the following query:

SELECT t1.col1,
t1.col2,
t1.col3
FROM tab1 t1, -- 1000 Rows
( SELECT t2.col1,
func( t2.col1 )
FROM tab2
) -- 10 Rows
WHERE t1.col1 = t2.col1 -- Result = 1000 Rows


The (deterministic) Function is very complex and I tried to reduce the calling of the function by using the subselect.
I thought: "Ok, first the inner select will be done and the the result against the outer select". But when the select was started the function was called 1000 times and not 10 times (I tested it with a simple dbms_output in the function).

Is there a chance to reduce the calls of the function? Function-Based Indexes were forbidden here by the DBA.

Bye,

Olga

Tom Kyte
February 04, 2004 - 4:27 pm UTC

Add "where rownum >= 0" to the inline view and start shopping for a new DBA soon.  You are in real need of one.  they'd rather cut off your nose, the end users nose and their own noses -- despite their face....  I really hate it when someone "outlaws" a feature that may be the difference between "working" and "not working" or "not writing code" and 'writing boatloads of code'.  I see it with views, stored procedures, triggers, you name it.  nothing bothers me more (well, maybe the developer that refuses to use bind variables...)


ops$tkyte@ORA10G> create table t1
  2  as
  3  select mod(rownum,10) col1, rownum col2, rownum col3
  4    from all_objects
  5   where rownum <= 1000;
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> create table t2
  2  as
  3  select mod(rownum,10) col1
  4    from all_objects
  5   where rownum <= 10;
 
Table created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec dbms_application_info.set_client_info(0);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> set autotrace traceonly
ops$tkyte@ORA10G> select *
  2    from t1, ( select col1, f(col1) x from t2 ) t2
  3   where t1.col1 = t2.col1
  4  /
 
1000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1000 Bytes=52000)
   1    0   HASH JOIN (Cost=7 Card=1000 Bytes=52000)
   2    1     TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=3 Card=10 Bytes=130)
   3    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=3 Card=1000 Bytes=39000)
 
 
 
 
Statistics
----------------------------------------------------------
         93  recursive calls
          0  db block gets
         93  consistent gets
          4  physical reads
          0  redo size
      26510  bytes sent via SQL*Net to client
       1234  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
       1000  rows processed
 
ops$tkyte@ORA10G> set autotrace off
ops$tkyte@ORA10G> select userenv('client_info') from dual;
 <b>
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1000
 </b>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> exec dbms_application_info.set_client_info(0);
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> set autotrace traceonly
ops$tkyte@ORA10G> select *
  2    from t1, ( select col1, f(col1) x from t2 WHERE ROWNUM >= 0) t2
  3   where t1.col1 = t2.col1
  4  /
 
1000 rows selected.
 
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=1000 Bytes=65000)
   1    0   HASH JOIN (Cost=7 Card=1000 Bytes=65000)<b>
   2    1     VIEW (Cost=3 Card=10 Bytes=260)
   3    2       COUNT</b> 
   4    3         FILTER
   5    4           TABLE ACCESS (FULL) OF 'T2' (TABLE) (Cost=3 Card=10 Bytes=130)
   6    1     TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=3 Card=1000 Bytes=39000)
 
 
 
 
Statistics
----------------------------------------------------------
         10  recursive calls
          0  db block gets
         85  consistent gets
          0  physical reads
          0  redo size
      26510  bytes sent via SQL*Net to client
       1234  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed
 
ops$tkyte@ORA10G> set autotrace off
ops$tkyte@ORA10G> select userenv('client_info') from dual;
<b> 
USERENV('CLIENT_INFO')
----------------------------------------------------------------
10
 </b>



 

Wow!

Olga, February 04, 2004 - 4:52 pm UTC

Many thanks for your answer, Tom! It's great!

You and your page are the best reasons for chosing Oracle over any other Database System. Your book is also wonderful, the best Oracle book I've read.

Bye,

Olga



Would it help here?

Gabriel, April 06, 2004 - 1:38 pm UTC

Hello Tom,

In the following update:

UPDATE mytable
SET retrycount = :1,
outcome = :2,
completetime = :3,
starttime = :4,
taskstatecode = :5,
jobfunctionid = :6,
repeatcount = :7,
manualind = :8,
dynamicgroupid = HEXTORAW (:14),
applicationtag = :15,
workitemid = :16,
createtime = :17,
taskmodelid = HEXTORAW (:18),
stepnumber = :19,
modelinstanceid = HEXTORAW (:20),
milestoneinstid = HEXTORAW (:21)
WHERE ((taskinstanceid = HEXTORAW (:22)))

Would it help if I create a function that simply returns the hextoraw and then create an index on that function? I am not sure as the function is not on the table column but on a value that this column is compared with. Would it help if we would use this function everywhere we have hextoraw?

Thank you very much,

Tom Kyte
April 07, 2004 - 8:44 am UTC

the way you have it coded allows a "normal" index on mytable(taskinstanceid) to be used. that is best.

nothing wrong at all with hextoraw

Using a function changes the plan completely!

A reader, June 23, 2004 - 5:13 pm UTC

I have a query like

1 select e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val
2 from accnt_emp_optn aeo,emp e,optn o,accnt a
3 where a.accnt_id=aeo.accnt_id
4 and aeo.optn_id=o.optn_id
5 and aeo.emp_id=e.emp_id
6 and decode(e.act_flg,1,'Y',0,'N') = 'Y'
7 and instr(',abc,xyz,',','||accnt_no||',') != 0
8* and o.optn_cd_val like '%XYZ'

The autotrace stats for this are

Elapsed: 00:00:05.35

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=37 Card=11 Bytes=770
)

1 0 NESTED LOOPS (Cost=37 Card=11 Bytes=770)
2 1 HASH JOIN (Cost=19 Card=176 Bytes=10208)
3 2 INDEX (FULL SCAN) OF 'OPTN#OPTN_ID_CD_VAL_UN' (UNIQUE)
(Cost=13 Card=97 Bytes=2037)

4 2 NESTED LOOPS (Cost=15 Card=5858 Bytes=216746)
5 4 TABLE ACCESS (FULL) OF 'EMP' (Cost=12 Card=20 Bytes=
440)

6 4 INDEX (RANGE SCAN) OF 'ACCNT_EMP_OPTN#ACCN_E_O_UK' (
UNIQUE) (Cost=2 Card=297 Bytes=4455)

7 1 TABLE ACCESS (BY INDEX ROWID) OF 'ACCNT' (Cost=2 Card=1
Bytes=12)

8 7 INDEX (UNIQUE SCAN) OF 'ACCNT#ACCNT_ID_PK' (UNIQUE) (C
ost=1 Card=20)





Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
125757 consistent gets
0 physical reads
0 redo size
905 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

This is bad. But if I take out the

and decode(e.act_flg,1,'Y',0,'N') = 'Y'

condition above, the stats change to

del 6

Elapsed: 00:00:01.63

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=232 Card=1148 Bytes=
76916)

1 0 HASH JOIN (Cost=232 Card=1148 Bytes=76916)
2 1 TABLE ACCESS (FULL) OF 'EMP' (Cost=12 Card=1974 Bytes=37
506)

3 1 HASH JOIN (Cost=218 Card=1148 Bytes=55104)
4 3 INDEX (FULL SCAN) OF 'OPTN#OPTN_ID_CD_VAL_UN' (UNIQUE)
(Cost=13 Card=97 Bytes=2037)

5 3 NESTED LOOPS (Cost=206 Card=38176 Bytes=1030752)
6 5 VIEW OF 'index$_join$_004' (Cost=51 Card=1489 Bytes=
17868)

7 6 HASH JOIN
8 7 INDEX (FAST FULL SCAN) OF 'ACCNT#ACCNT_NO_UK' (U
NIQUE) (Cost=15 Card=1489 Bytes=17868)

9 7 INDEX (FAST FULL SCAN) OF 'ACCNT#ACCNT_ID_PK' (U
NIQUE) (Cost=15 Card=1489 Bytes=17868)

10 5 INDEX (RANGE SCAN) OF 'ACCNT_EMP_OPTN#PK' (UNIQUE) (
Cost=2 Card=26 Bytes=390)





Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
313 consistent gets
0 physical reads
0 redo size
905 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed


The answer is the same but it is much faster.

The RBO comes up with a totally different plan which is THE SAME no matter if the DECODE predicate is present or not

Elapsed: 00:00:01.59

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 NESTED LOOPS
2 1 NESTED LOOPS
3 2 NESTED LOOPS
4 3 TABLE ACCESS (FULL) OF 'ACCNT'
5 3 INDEX (RANGE SCAN) OF 'ACCNT_EMP_OPTN#PK' (UNIQUE)
6 2 TABLE ACCESS (BY INDEX ROWID) OF 'OPTN'
7 6 INDEX (UNIQUE SCAN) OF 'OPTN#OPTN_ID_PK' (UNIQUE)
8 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
9 8 INDEX (UNIQUE SCAN) OF 'EMP#PK' (UNIQUE)




Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1955 consistent gets
4 physical reads
0 redo size
905 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

Question:

Why does adding the

and decode(e.act_flg,1,'Y',0,'N') = 'Y'

make the CBO tank?

Thanks

Tom Kyte
June 23, 2004 - 9:36 pm UTC

ok, append the TKPROF report as well......

tkprofs

A reader, June 23, 2004 - 11:15 pm UTC

Here is the tkprof from the first run (bad)

********************************************************************************

select e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val
from accnt_emp_optn aeo,emp e,optn o,accnt a
where a.accnt_id=aeo.accnt_id
and aeo.optn_id=o.optn_id
and aeo.emp_id=e.emp_id
and instr(',abc,xyz,',','||accnt_no||',') != 0
and o.optn_cd_val like '%XYZ'
and decode(e.act_flg,1,'Y',0,'N')='Y'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.02 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 7.37 34.58 4585 125755 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 7.39 34.60 4585 125755 0 5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 961

Rows Row Source Operation
------- ---------------------------------------------------
5 NESTED LOOPS (cr=125755 r=4585 w=0 time=34580821 us)
60896 HASH JOIN (cr=3960 r=2712 w=0 time=21070160 us)
10 INDEX FULL SCAN OPTN#OPTN_ID_CD_VAL_UN (cr=12 r=12 w=0 time=64800 us)(object id 55132)
563281 NESTED LOOPS (cr=3948 r=2700 w=0 time=19558621 us)
634 TABLE ACCESS FULL EMP (cr=72 r=69 w=0 time=144374 us)
563281 INDEX RANGE SCAN ACCNT_EMP_OPTN#ACCN_E_O_UK (cr=3876 r=2631 w=0 time=18836396 us)(object id 37307)
5 TABLE ACCESS BY INDEX ROWID ACCNT (cr=121795 r=1873 w=0 time=13090253 us)
60896 INDEX UNIQUE SCAN ACCNT#ACCNT_ID_PK (cr=60899 r=104 w=0 time=1657278 us)(object id 55080)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 4517 0.06 27.90
db file scattered read 9 0.02 0.12
SQL*Net message from client 2 0.03 0.05
********************************************************************************

Here is the tkprof after taking out the decode

********************************************************************************

select e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val
from accnt_emp_optn aeo,emp e,optn o,accnt a
where a.accnt_id=aeo.accnt_id
and aeo.optn_id=o.optn_id
and aeo.emp_id=e.emp_id
and instr(',abc,xyz,',','||accnt_no||',') != 0
and o.optn_cd_val like '%XYZ'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.02 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.33 0.65 111 311 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.36 0.68 111 311 0 5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 961

Rows Row Source Operation
------- ---------------------------------------------------
5 HASH JOIN (cr=311 r=111 w=0 time=653865 us)
1974 TABLE ACCESS FULL EMP (cr=71 r=0 w=0 time=4997 us)
5 HASH JOIN (cr=240 r=111 w=0 time=627517 us)
10 INDEX FULL SCAN OPTN#OPTN_ID_CD_VAL_UN (cr=12 r=0 w=0 time=3828 us)(object id 55132)
13 NESTED LOOPS (cr=228 r=111 w=0 time=609560 us)
2 VIEW (cr=221 r=106 w=0 time=570897 us)
2 HASH JOIN (cr=221 r=106 w=0 time=570841 us)
2 INDEX FAST FULL SCAN ACCNT#ACCNT_NO_UK (cr=106 r=101 w=0 time=458282 us)(object id 55081)
29777 INDEX FAST FULL SCAN ACCNT#ACCNT_ID_PK (cr=115 r=5 w=0 time=57384 us)(object id 55080)
13 INDEX RANGE SCAN ACCNT_EMP_OPTN#PK (cr=7 r=5 w=0 time=38615 us)(object id 55169)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 7 0.01 0.05
db file scattered read 14 0.02 0.29
SQL*Net message from client 2 0.00 0.01
********************************************************************************


Here is the tkprof from RBO

********************************************************************************

select /*+ rule */ e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val
from accnt_emp_optn aeo,emp e,optn o,accnt a
where a.accnt_id=aeo.accnt_id
and aeo.optn_id=o.optn_id
and aeo.emp_id=e.emp_id
and instr(',abc,xyz,',','||accnt_no||',') != 0
and o.optn_cd_val like '%XYZ'
and decode(e.act_flg,1,'Y',0,'N')='Y'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.24 0.71 142 1955 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.24 0.72 142 1955 0 5

Misses in library cache during parse: 1
Optimizer goal: RULE
Parsing user id: 961

Rows Row Source Operation
------- ---------------------------------------------------
5 NESTED LOOPS (cr=1955 r=142 w=0 time=719115 us)
5 NESTED LOOPS (cr=1943 r=138 w=0 time=691864 us)
13 NESTED LOOPS (cr=1914 r=133 w=0 time=657208 us)
2 TABLE ACCESS FULL ACCNT (cr=1907 r=133 w=0 time=656930 us)
13 INDEX RANGE SCAN ACCNT_EMP_OPTN#PK (cr=7 r=0 w=0 time=220 us)(object id 55169)
5 TABLE ACCESS BY INDEX ROWID OPTN (cr=29 r=5 w=0 time=34538 us)
13 INDEX UNIQUE SCAN OPTN#OPTN_ID_PK (cr=16 r=3 w=0 time=17016 us)(object id 55131)
5 TABLE ACCESS BY INDEX ROWID EMP (cr=12 r=4 w=0 time=27188 us)
5 INDEX UNIQUE SCAN EMP#PK (cr=7 r=4 w=0 time=27050 us)(object id 55092)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
db file sequential read 53 0.01 0.27
db file scattered read 28 0.02 0.25
SQL*Net message from client 2 0.00 0.00
********************************************************************************

Thanks

Tom Kyte
June 24, 2004 - 9:25 am UTC

it is hard to say without understanding what the indexes are on exactly -- but.


the autotrace shows the optimizer was expecting:

2 1 HASH JOIN (Cost=19 Card=176 Bytes=10208)

about 176 out of that hash join, tkprof shows:

60896 HASH JOIN (cr=3960 r=2712 w=0 time=21070160 us)

60,896 rows. going deeper into the plan, we see it started going bad here really:

6 4 INDEX (RANGE SCAN) OF 'ACCNT_EMP_OPTN#ACCN_E_O_UK' ( UNIQUE) (Cost=2 Card=297 Bytes=4455)


optimizer thought 297 rows.... reality says:

563281 INDEX RANGE SCAN ACCNT_EMP_OPTN#ACCN_E_O_UK (cr=3876 r=2631 w=0 time=18836396 us)(object id 37307)

563,281 rows.




One might question:

and decode(e.act_flg,1,'Y',0,'N')='Y'


why would you not just code:

and e.act_flg = 1

??


can you tell us more about that index, how current are the stats and what is the *exact* command you use to gather them?



show param optimizer

A reader, June 23, 2004 - 11:18 pm UTC

optimizer_dynamic_sampling 1
optimizer_features_enable 9.2.0
optimizer_index_caching 90
optimizer_index_cost_adj 10
optimizer_max_permutations 2000
optimizer_mode CHOOSE


A reader, June 25, 2004 - 10:28 am UTC

I set all the tables in the database to MONITORING and have a daily DBMS_JOB that does

dbms_stats.gather_database_stats(method_opt => 'for all columns size auto', cascade => true, options => 'gather stale');

So, the stats should be up to date.

I dont know why the plan (expected) vs. tkprof (actual) numbers are off by so much!

The reason I have the DECODE instead of the simple predicate is because thats what the view I use for this query has. Actually, it doesnt have the decode, it has a packaged function that does this i.e. a function decode_flag which is defined as a simple select decode(...) into v_return from dual; return v_return;

Help? What can I look into? Thanks

Tom Kyte
June 25, 2004 - 2:41 pm UTC

you are kidding, can't you fix the view (it is frustrating to have information hidden from me like that) -- that would be a really bad (eg: slower than need be by far no matter what) way to do something so simple.

the function is the reason for the (expected) to be off of the (actual), I demonstrated how to get your numbers exactly -- it is the magic of the function, the optimizer "guesses", it cannot really know.

A reader, June 25, 2004 - 3:12 pm UTC

Yes, I can take out the packaged function from the view and use the straigt DECODE() call in the view query, but as you can see, even that performs horribly.

I still dont understand why adding the DECODE predicate makes the CBO change the plan completely. Why cant it simply use the plan as if the decode were not present and simply filter the resulting rows with the DECODE

[I didnt keep anything hidden from you, my original question did mention the DECODE as the reason for the slowness]

But anyway, how can I fix the root cause of the problem? My database table has 0/1 for a column, my apps are coded to use the view which decodes this to N/Y

Thanks



Tom Kyte
June 25, 2004 - 4:29 pm UTC

I'm saying skip the decode/function all together -- you don't need it.

it sees "f(x) = value"

how many rows will that return?


decode is just a function, it takes an input (your column) and returns an output. You compare that to something else.

You took:

where x = 5

and made it be:

where f(x) = 5


x=5 is something KNOWN.

f(x) = 5 is totally "guess"



Perhaps a function based index is what you need/want? index the function, then we can have stats and everything about it.

Associate statistics worked wonders!

A reader, June 25, 2004 - 4:35 pm UTC

My DECODE function is actually wrapped in a package (UTIL.DECODE_FLAG)

So, I did

associate statistics with packages util default cost (1,1,1);

This improved things dramatically, here is the autotrace

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=336 Card=11 Bytes=77
0)

1 0 NESTED LOOPS (Cost=336 Card=11 Bytes=770)
2 1 HASH JOIN (Cost=218 Card=1148 Bytes=55104)
3 2 INDEX (FULL SCAN) OF 'OPTN#OPTN_ID_CD_VAL_UN' (UNIQUE)
(Cost=13 Card=97 Bytes=2037)

4 2 NESTED LOOPS (Cost=206 Card=38176 Bytes=1030752)
5 4 VIEW OF 'index$_join$_004' (Cost=51 Card=1489 Bytes=
17868)

6 5 HASH JOIN
7 6 INDEX (FAST FULL SCAN) OF 'ACCNT#ACCNT_NO_UK' (U
NIQUE) (Cost=15 Card=1489 Bytes=17868)

8 6 INDEX (FAST FULL SCAN) OF 'ACCNT#ACCNT_ID_PK' (U
NIQUE) (Cost=15 Card=1489 Bytes=17868)

9 4 INDEX (RANGE SCAN) OF 'ACCNT_EMP_OPTN#PK' (UNIQUE) (
Cost=2 Card=26 Bytes=390)

10 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 By
tes=22)

11 10 INDEX (UNIQUE SCAN) OF 'EMP#PK' (UNIQUE) (Cost=1 Card=
99)





Statistics
----------------------------------------------------------
44 recursive calls
0 db block gets
262 consistent gets
0 physical reads
0 redo size
905 bytes sent via SQL*Net to client
651 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed

Here is the tkprof

********************************************************************************

select e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val
from accnt_emp_optn aeo,emp e,optn o,accnt a
where
a.accnt_id=aeo.accnt_id
and aeo.optn_id = o.optn_id
and
aeo.emp_id = e.emp_id
and
instr(',abc,xyz,',','||accnt_no||',') != 0
and o.optn_cd_val like '%XYZ'
and srm.util.decode_flag(e.act_flg) = 'Y'

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.05 0.04 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1 0.24 0.23 0 250 0 5
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.29 0.28 0 250 0 5

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 961

Rows Row Source Operation
------- ---------------------------------------------------
5 NESTED LOOPS (cr=250 r=0 w=0 time=238256 us)
5 HASH JOIN (cr=238 r=0 w=0 time=237101 us)
10 INDEX FULL SCAN OPTN#OPTN_ID_CD_VAL_UN (cr=12 r=0 w=0 time=3670 us)(object id 55132)
13 NESTED LOOPS (cr=226 r=0 w=0 time=222867 us)
2 VIEW (cr=220 r=0 w=0 time=222655 us)
2 HASH JOIN (cr=220 r=0 w=0 time=222558 us)
2 INDEX FAST FULL SCAN ACCNT#ACCNT_NO_UK (cr=106 r=0 w=0 time=130907 us)(object id 55081)
29777 INDEX FAST FULL SCAN ACCNT#ACCNT_ID_PK (cr=114 r=0 w=0 time=33610 us)(object id 55080)
13 INDEX RANGE SCAN ACCNT_EMP_OPTN#PK (cr=6 r=0 w=0 time=153 us)(object id 55169)
5 TABLE ACCESS BY INDEX ROWID EMP (cr=12 r=0 w=0 time=1089 us)
5 INDEX UNIQUE SCAN EMP#PK (cr=7 r=0 w=0 time=117 us)(object id 55092)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
SQL*Net message from client 2 1.08 1.08
********************************************************************************

This is amazing. But I dont quite understand it.

I still would like to understand, as I asked in my previous review, why the DECODE screws things up so badly.

But, why/how did this associate statistics improve things so much?

I set the cost to (1,1,1) because decode_flag() is a simple one-line select from dual.

But ASSOCIATE STATISTICS applies to the whole package, not to specific functions/procedures in the package. So, I am afraid that the CBO will pick up this (1,1,1) cost when one of the *other*, more expensive functions in the package is called and generate a sub-optimal plan. Comments?

If these user-defined stats are not present, how does the CBO assign a cost to a predicate involving user-defined functions? something like 'where my_function() = value'

Thanks

Associate statistics

A reader, June 27, 2004 - 10:18 am UTC

<quote>
it sees "f(x) = value"
how many rows will that return?
f(x) = 5 is totally "guess"
Perhaps a function based index is what you need/want? index the function, then we can have stats and everything about it.
<quote>

This is a little disturbing. User-defined functions have become extremely common in today's systems. What you are basically saying is that CBO is stumped when it comes to handling UDFs. It guesses and in this particular case that causes the plan to change completely for the worse.

Almost seems like when using user defined functions in queries, function-based indexes are *required* for acceptable performance.

Yes, I can assign the cost manually using this ASSOCIATE STATISTICS and play with that, but assigning the cost to the whole package is a little limiting since different functions in the package could have totally different "costs"

IMHO, there needs to be a new init.ora parameter similar to optimizer_index_cost_adj called optimizer_func_cost_adj or something that is session-modifiable. This way, I can let the optimizer know how expensive my functions are.

Comments?

Thanks

Tom Kyte
June 27, 2004 - 11:39 am UTC

I'm saying "i as a HUMAN BEING am totally stumped", forget about the software here.

think about it. please -- just think about it -- I give you:

where f(x) = 5


Now, I personally wrote x. You did not. You have only the knowledge that f() is a function that takes X as input and returns a number.

Tell me -- what more can you say about that? Can you tell me about how many rows will be returned by that predicate? Even if I tell you "there are 1000 rows in the table", "x is unique in that table", "x has the values from 1 to 1000" in that table. What can you say about "where f(x) = 5"?

Nothing....


It is not the EXPENSE of the function that is in question here at all (and you already know how to associate a cost with a function) -- not at all.... It is the estimated cardinalities, period. You do not have them, you cannot have them, you have hidden that information -- unless you use a function based index, then the cardinalities can once again be known.

A reader, June 27, 2004 - 6:53 pm UTC

Tom, I understand everything you said and dont disagree. But I am still not getting my point across to you.

Let me put it another way.

This predicate "f(x)=5" is used in 100 different queries. 90 of them perform fine and the fact that the CBO "guesses" the cardinality of f(x)=5 isnt adversely affecting them. But in 10% of the queries, the guess is so bad that it tanks. Now how do I deal with this?

1. Put a function based index on f(x) so that CBO knows all the stats it needs.

This would, most likely, make the 10% problem queries perform well, but it just might make the other 90% tank! Guess more testing is needed.

2. Since the exact rules/heuristics the CBO uses to "guess" the cardinality of f(x)=5 are not documented, I really cant rely on that in a Production system. i.e. depending on the nature of my data changes and the OTHER predicates, one of my 90% of the queries that is working fine on Monday might tank on Tuesday because the "guess" is no longer right!

3. Since the cardinality is not known, instead of guessing how many rows f(x)=5 would return, why doesnt the CBO ignore this predicate completely? Do you know what is the number it guesses? Is it always the same or is it arrived at by some step-by-step process?

4. All this becomes more critical when my f(x) is non-deterministic. Now I *cannot* put a function-based index on it and am totally at the mercy of the CBO's "guess". I wouldnt be comfortable with this situation in a Production system, would you?

Thanks

Tom Kyte
June 28, 2004 - 8:02 am UTC

the guess would be the same in all cases.. 90% of the time you got "lucky" -- it could very well be that the 90% of the queries you are "ok with" are running 10times slower than they should as well.


1) testing is correct.

2) you can see the values in the plan clearly? I used that to demonstrate with?

3) that would mean "select * from t where f(x) = 5" would return ALL ROWS -- now what, that is worse than "a percentage of the rows" no?

4) think about what you are asking here, really -- think about it. what is doable here? ignore the predicate -- 100% of the rows come back. So, it just uses a number less than 100%. In your bad cases -- the number should have been MUCH MUCH smaller (negating that 100% case).

have you tried

Matthias Rogel, June 28, 2004 - 7:28 am UTC

hallo a reader,

when I have a phenomenon like

select e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val
from accnt_emp_optn aeo,emp e,optn o,accnt a
where a.accnt_id=aeo.accnt_id
and aeo.optn_id=o.optn_id
and aeo.emp_id=e.emp_id
and decode(e.act_flg,1,'Y',0,'N') = 'Y'
and instr(',abc,xyz,',','||accnt_no||',') != 0
and o.optn_cd_val like '%XYZ'

is badly handled by the optimizer (seems using the "wrong" execution plan in my opinion),
but

select e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val
from accnt_emp_optn aeo,emp e,optn o,accnt a
where a.accnt_id=aeo.accnt_id
and aeo.optn_id=o.optn_id
and aeo.emp_id=e.emp_id
and instr(',abc,xyz,',','||accnt_no||',') != 0
and o.optn_cd_val like '%XYZ'

is ok (seems using the "right" execution plan in my opinion),

I usually would go and try

select last_nm,first_nm,accnt_no,optn_cd_val from (
select e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val,decode(e.act_flg,1,'Y',0,'N') dec_act_flag
from accnt_emp_optn aeo,emp e,optn o,accnt a
where a.accnt_id=aeo.accnt_id
and aeo.optn_id=o.optn_id
and aeo.emp_id=e.emp_id
and instr(',abc,xyz,',','||accnt_no||',') != 0
and o.optn_cd_val like '%XYZ')
where dec_act_flag = 'Y'

or even

with t as (
select e.last_nm,e.first_nm,a.accnt_no,o.optn_cd_val,decode(e.act_flg,1,'Y',0,'N') dec_act_flag
from accnt_emp_optn aeo,emp e,optn o,accnt a
where a.accnt_id=aeo.accnt_id
and aeo.optn_id=o.optn_id
and aeo.emp_id=e.emp_id
and instr(',abc,xyz,',','||accnt_no||',') != 0
and o.optn_cd_val like '%XYZ')
select last_nm,first_nm,accnt_no,optn_cd_val from t
where dec_act_flag = 'Y'

have you tried these ?

How many rows?

A reader, June 28, 2004 - 7:42 pm UTC

"the guess would be the same in all cases...you can see the values in the plan clearly? I used that to demonstrate with?"

Um, sorry, but I dont quite see where to see the cardinality of my decode_flag() predicate that the CBO guesses in either the plan or the tkprof report. Can you please point it out?

"90% of the time you got "lucky" -- it could very well be that the 90% of the queries you are "ok with" are running
10times slower than they should as well"

Good point. I just assumed that they are fine because they return in a acceptable time (<1second). They might very well be much faster if I take out this stupid decode!

Thanks

Tom Kyte
June 28, 2004 - 8:01 pm UTC

sorry -- i was thinking of another question (very similar)...


ops$tkyte@ORA9IR2> create table t as select a.*, mod(rownum,2) X from all_objects  2  a;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function f( p_x in number ) return varchar2
  2  is
  3  begin
  4          return 'N';
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set  autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where x = 0;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=15380 Bytes=1353440)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=45 Card=15380 Bytes=1353440)
 
 
 
ops$tkyte@ORA9IR2> select * from t where f(x) = 'N';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=308 Bytes=27104)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=45 Card=308 Bytes=27104)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off


ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> insert /*+ append */ into t select * from t;
 
30760 rows created.
 
ops$tkyte@ORA9IR2> analyze table t compute statistics;
 
Table analyzed.
 
ops$tkyte@ORA9IR2> set  autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where x = 0;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=87 Card=30760 Bytes=2706880)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=87 Card=30760 Bytes=2706880)
 
 
 
ops$tkyte@ORA9IR2> select * from t where f(x) = 'N';
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=87 Card=615 Bytes=54120)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=87 Card=615 Bytes=54120)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off


it is guessing "about 1% of the rows"

 

This may be helpful

Gary, June 28, 2004 - 8:05 pm UTC

The Data Cartridge Developer's Guide -Chapter 10 (in the 10g documentation) has some interesting stuff on this.

"Currently, the optimizer uses a standard algorithm to estimate the selectivity of selection and join predicates. However, the algorithm does not always work well in cases in which predicates contain functions or type methods. "

"User-Defined Selectivity
For greater control over the optimizer's selectivity estimation, this feature lets you specify user-defined selectivity functions for predicates containing user-defined operators, standalone functions, package functions, or type methods"

and goes into how to use
ASSOCIATE STATISTICS ... DEFAULT SELECTIVITY ..
and ASSOCIATE STATISTICS ... USING ...
to override the 'guesses' oracle makes.



Tom Kyte
June 28, 2004 - 8:26 pm UTC

when you create an operator... like "contains" is.

but yes, good pointer, thanks -- appreciate the followup.

Thanks

A reader, June 28, 2004 - 9:21 pm UTC

"it is guessing "about 1% of the rows"

Thanks!

To Gary

A reader, June 28, 2004 - 9:24 pm UTC

Yes, I already mentioned my experience with ASSOCIATE STATISTICS in one of my earlier followups.

But, unfortunately, the ASSOCIATE STATISTICS WITH PACKAGES applies to the entire package, it doesnt allow to specify the cost/selectivity differently for different functions within the package.

Thanks

Deterministic or not?

A reader, July 23, 2004 - 11:07 am UTC

My problem is as follows:

As part of my procedure, I have

IF instr(v_string,table_a.column_b)>0
THEN
... do some processing;
END IF;

I get v_string from the front-end, which is a list of values for table_a.column_b selected. I want to create a function that does the INSTR processing, and returns char(1) - 'Y' or 'N', and create a function-based index on it on table_a. Though this function is not truly deterministic, what happens if I declare the function as deterministic?(according to my understanding, Oracle will trust you if you say the function is deterministic)

Tom Kyte
July 23, 2004 - 3:59 pm UTC

oracle will trust you and if you lie to it, you will get the wrong answer.

Think about it, an index is computed once. if the function doesn't return the same value time after time after time after time -- ummm -- well, it isn't useful if we call it once -- store the results and pretend that is the value it would always return.

Boolean data type

A reader, August 04, 2004 - 5:17 am UTC

I am in the process of migrating postgres db to oracle.
In one of the table(postgres) I found boolean datatype.What exacly I should use in oracle to store these values(As oracle doesn't have boolean data type in sql).Also I am looking for some tools or documention which can help me do this migration.

Any help will be appreciated.

Cheers


Tom Kyte
August 04, 2004 - 10:22 am UTC

create table t
( ....,
bool varchar2(1) not null check ( bool in ('Y','N') ),
....
)
/


would be one approach.

PLSQL functions in queries

A reader, August 05, 2004 - 3:39 pm UTC

"Don't use PLSQL then. Incorporate the logic inline. I find that 99.99% of the time, the logic in the plsql routine can be done using SQL with decodes and/or CASE statements"

Yes, that is true, but I find that I have a zillion views that do the same trivial transformations so instead of writing all the views as

select ....,
(case when ... end) c1
from

I wrote them as

select ....,
myfn c1
from

This way, when I have to make a change in the logic, I just have to change the 'myfn' function and not the 100s of views.

How can I work around this?



Tom Kyte
August 05, 2004 - 8:29 pm UTC

you make the decision between:

a) maximum performance
b) not a)


I find that i, well, I've never had a zillion views (not even more than one could count on a single hand -- missing digits) that needed the same myfn.

can you give an example? that would require double digit views to be maintained?

ASSOCIATE STATISTICS WITH PACKAGES does not work for join queries

Oliver Schoett, September 22, 2004 - 9:32 am UTC

The mechanism
ASSOCIATE STATISTICS WITH PACKAGES name DEFAULT SELECTIVITY percent;
only covers comparisons of the form f(t1.f1) = constant, not the form f(t1.f1) = t2.f2 that is needed frequently in join queries.

What can I do if I need that mechanism, i.e., I have such a query where t2.f2 has a unique index, but due to the *other* predicates in the query, the selectivity is not 1/(card(t2)), but rather 1/10 or nearly 1? Currently, the CBO just multiplies the selectivities of the various conditions, resulting in cardinality estimates way below 1 where the true number is millions, hence nonsensical query plans are chosen, and I see no way to correct this.

Most elegant would be selectivity hints like Raima Velocis used to have, because the altered selectivity applies only in the context of the other predicates of a specific query, not globally:

select * from t1, t2
where t2.fkey = t1.id
and f(t1.f1) = t2.id /*+ SELECTIVITY(0.1) */
and g(t1.f1) = t1.f3 /*+ SELECTIVITY(0.999) */



NAMED parameter in SQL function

Robert, November 07, 2004 - 7:09 pm UTC

8.1.7.4

Using NAMED parameter in SQL function is NOT allowed ???
I am getting PLS-00306 error.

PACKAGE BODY thispkg
IS
PROCEDURE promotion
IS
BEGIN
UPDATE scott.emp
SET job = thispkg.thisfunc(p1 => 'x', p2 => 1),
sal = thispkg.thatfunc('x', 1);
END ;
END;


Error: PLS-00306: wrong number or types of arguments in call to 'thispkg.thisfunc'


thanks

Tom Kyte
November 08, 2004 - 9:41 am UTC

named notation is not supported in plsql called from SQL that is correct.



1% cardinality

A reader, November 19, 2004 - 2:27 pm UTC

"it is guessing "about 1% of the rows"

"Don't use PLSQL then. Incorporate the logic inline. I find that 99.99% of the time, the logic in the plsql routine can be done using SQL with decodes and/or CASE statements"

Even so, I would need to do

create or replace view v as
select c1,c2,
case .... end c3,
from t

If I apply a predicate on v.c3, CBO assumes 1% cardinality. In some cases this is ok, in others it is wildly off and this throws the overall query plan totally on the wrong track.

So, I guess that if views/queries use predicates on any column that is either a) PL/SQL function b) inline function using DECODE, CASE, etc, using a FBI on that expression is highly recommended?

So in my example above, I would need to do
create index fbi on t(case .... end);

Thanks

Tom Kyte
November 19, 2004 - 7:13 pm UTC

no, not highly recommended.


if you where on c3 and would like to use an index access path when whereing on c3, then index c3.

else, do not.

A reader, November 19, 2004 - 10:54 pm UTC

"if you where on c3 and would like to use an index access path when whereing on c3, then index c3"

Um, but thats just the thing. I dont necessarily want to use the index as a access path. I want to use the FBI just so that the CBO can get accurate cardinality numbers! 1% for all function-based expressions is just too unrealistic.

I want to create the FBI just so that the CBO know the number of rows flowing out of that predicate?

Tom Kyte
November 20, 2004 - 8:16 am UTC

you can, if you need -- but if you have other predicates -- the optimizer will use them as well.

so, no, i won't say "every time", it is like histograms -- you don't need them "always"

A reader, November 20, 2004 - 9:39 am UTC

Yes, I have other predicates but this one predicate that uses the function returns a lot rows (almost 50%). So the 1% assumption is throwing off the overall plan completely.

Thanks

Tom Kyte
November 20, 2004 - 10:11 am UTC

<b>or, you can tell us what the default for that function should be....</b>


ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows => 1000000 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace function f( p_x in number ) return number
  2  as
  3  begin
  4          return mod(p_x,2);
  5  end;
  6  /
 
Function created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> set linesize 121
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select * from t where f(x) = 1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=10000 Bytes=130000)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=17 Card=10000 Bytes=130000)
 
 
 <b>
ops$tkyte@ORA9IR2> ASSOCIATE STATISTICS WITH FUNCTIONS f DEFAULT SELECTIVITY 50; 
Statistics associated.
 </b>
ops$tkyte@ORA9IR2> select * from t where f(x) = 1;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=17 Card=500000 Bytes=6500000)
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=17 Card=500000 Bytes=6500000)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2>
 

A reader, November 20, 2004 - 10:19 am UTC

Yes, I already experimented with ASSOCIATE STATISTICS (page up a few times).

Like I said, unfortunately, the ASSOCIATE STATISTICS WITH PACKAGES applies to the entire package, it doesnt allow to specify the cost/selectivity differently for
different functions within the package!

Moreover, the selectivity really depends on the inputs passed to the function!

f(x) returns 10 rows
f(y) returns 10000 rows

How to handle this?

Thanks

Tom Kyte
November 20, 2004 - 11:22 am UTC

well, one obvious thing would be to create a function "f" that simply calls the packaged function pkg.f and associate stats with "f"....

check out chapter 8 in this:
</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96595/toc.htm <code>

you can have a function get called by the optimizer -- along with information on the predicates/constants being passed -- you then return the appropriate selectivity.

else, if you "don't know" what to return -- your fbi works.

A reader, November 20, 2004 - 2:41 pm UTC

"you can have a function get called by the optimizer -- along with information on the predicates/constants being passed -- you then return the appropriate selectivity."

Not sure what you mean here. Can you please elaborate?

Thanks



Tom Kyte
November 20, 2004 - 3:29 pm UTC

see chapter 8 of the above referenced link, instead of just associating a single default selectivity -- you can be part of the optimization process itself, you'll get the inputs to the function and you can say "hmm, x -- that is 1%, or y -- that is 75%"

User defined stats

A reader, November 20, 2004 - 9:01 pm UTC

OK I read that chapter and it is interesting but light on examples. Lets take an example

create table t1(x int);
create table t2(x int);
create table t3(y int);

function f(i int) return int;

Here is what I need

Predicate Selectivity
f(t1.x)=1 50
f(t2.x)=1 75
f(t3.y)=1 10
f(literal)=1 100 -- This has nothing to do with the data so the selectivity is 100. All the others are based on my knowledge of the data and the algo in the function.

How can I do this?

Thanks

Tom Kyte
November 21, 2004 - 8:59 am UTC

you get passed into the ODCIStatsSelectivity function all of that information -- the args() array describes all of the inputs -- their table names, if they are literals, whatever.

you would turn that information into 50, 75, 10, 100. How you do that, totally up to you -- maybe you have a lookup table, maybe something more sophisticated.

ODCIStatsSelectivity

A reader, November 21, 2004 - 9:14 am UTC

Can you please give a example of using ODCIStatsSelectivity given my example? None of the docs have a example

Tom Kyte
November 21, 2004 - 9:22 am UTC

there are examples -- not your SPECIFIC case, but that is what development is all about? (if we had your example exactly, that would be a miracle).


did you read through the examples in that very same book? chapter 13 for example. chapter 18 has more, showing the call:



When the optimizer encounters the query

SELECT * FROM T WHERE Contains(resume, ORACLE ) = 1,

it will compute the selectivity of the predicate by invoking the user-defined selectivity function for the functional implementation of the Contains operator. In this case, the selectivity function is stat1.ODCIStatsSelectivity. It will be called as follows:

stat1.ODCIStatsSelectivity ( ODCIPredInfo( SCOTT , Contains_fn , NULL, 29),
sel,
ODCIArgDescList( ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL),
ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL),
ODCIArgDesc(ODCIConst.ArgCol, T , SCOTT , "resume" ),
ODCIArgDesc(ODCIConst.ArgLit, NULL, NULL, NULL)),
1, 1, NULL, ORACLE )

A reader, November 21, 2004 - 4:41 pm UTC

I tried but I just cant get this stuff to work

DROP TABLE t1;
DROP TABLE t2;
DROP TABLE t3;
DROP TYPE t;
create table t1(x int);
create table t2(x int);
create table t3(y int);
CREATE OR REPLACE FUNCTION f(i INTEGER) RETURN INTEGER
AS
BEGIN
    RETURN 1;
END;
/
CREATE OR REPLACE TYPE t AS object 
(
    dummy NUMBER,
    static FUNCTION ODCIGetInterfaces(ifclist OUT ODCIObjectList) RETURN NUMBER,
static FUNCTION ODCIStatsSelectivity(pred ODCIPredInfo, sel OUT NUMBER, args ODCIArgDescList, 
    p_start INTEGER,
      stop INTEGER, i INTEGER, 
      env ODCIEnv) RETURN NUMBER
)
/
CREATE OR REPLACE TYPE body t 
AS
    static FUNCTION ODCIStatsSelectivity(pred ODCIPredInfo, sel OUT NUMBER, args
          ODCIArgDescList, p_start INTEGER,stop INTEGER,
          i INTEGER,
          env ODCIEnv) RETURN NUMBER
    IS
    BEGIN
        RETURN 5;
        return ODCIConst.Success;
    END;
    static FUNCTION ODCIGetInterfaces(ifclist OUT ODCIObjectList) RETURN NUMBER
    IS
    begin
        ifclist.extend;
        ifclist(1).objectschema:='SYS';
        ifclist(1).objectname:='ODCISTATS2';
        RETURN odciconst.success;
    END;
END;
/
ASSOCIATE STATISTICS WITH FUNCTIONS f USING t;

The final ASSOCIATE gives an error

SQL> ASSOCIATE STATISTICS WITH FUNCTIONS f USING t;
ASSOCIATE STATISTICS WITH FUNCTIONS f USING t
*
ERROR at line 1:
ORA-06531: Reference to uninitialized collection

probably something to do with how I am populating the ifclist OUT parameter. Help?

Thanks 

Tom Kyte
November 21, 2004 - 7:13 pm UTC

you have to allocate the array -- ifclist is NULL.

ifclist := odciobjectList( odciobject('SYS','ODCISTATS2') );

for example, or

ifclist := odciobjetList();
ifclist.extend .......




A reader, November 21, 2004 - 5:43 pm UTC

OK I got it

CREATE OR REPLACE TYPE body t 
AS
    static FUNCTION ODCIStatsSelectivity(pred ODCIPredInfo, sel OUT NUMBER, args
          ODCIArgDescList, p_start INTEGER,stop INTEGER,
          i INTEGER,
          env ODCIEnv) RETURN NUMBER
    IS
    BEGIN
        <b>sel := 5; -- 5% selectivity</b>
        return ODCIConst.Success;
    END;
    static FUNCTION ODCIGetInterfaces(ifclist OUT ODCIObjectList) RETURN NUMBER
    IS
    begin
        <b>ifclist := odciobjectlist();
        ifclist.extend;
        ifclist(1):= odciobject('SYS','ODCISTATS2');</b>
        RETURN odciconst.success;
    END;
END;
/
SQL> exec dbms_stats.set_table_stats(user,'t1',numrows=>1000000)

PL/SQL procedure successfully completed.

SQL> l
  1* select * from t1 where f(x)=1
SQL> /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=319 Card=50000 Bytes=650000)

   1    0   TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=319 <b>Card=50000</b> Bytes=650000)

SQL> exec dbms_stats.set_table_stats(user,'t1',numrows=>100)

PL/SQL procedure successfully completed.

SQL>  select * from t1 where f(x)=1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=24 Card=5 Bytes=65
          )

   1    0   TABLE ACCESS (FULL) OF 'T1' (TABLE) (Cost=24 <b>Card=5</b> Bytes=65)

Selectivity is 5% of the number of rows. Great.

Questions:

1. Why do I need to specify the 'dummy number' attribute in the object type t? If I dont, I get 'object has no attributes'?

2. Why does the ODCI function need to be defined as STATIC? Why wont plain function or 'member function' do?

3. Can you please show how to navigate the ODCIArgDescList attrribute to dig deeper into what the arguments to the function are?

4. Similarly for the ODCIPredInfo parameter?

5. If my predicate is of the form

where f(x) = 1

I dont see that my selectivity function has access to the literal on the RHS of the predicate (1 in the above example), or am I not reading it right?

Thanks 

Tom Kyte
November 21, 2004 - 7:19 pm UTC

1) objects need an attribute.

2) it isn't working on an instance of an object (this is "OO" stuff). it just is the way they implemented it. it is treating the object procedure like a packaged procedure.

3) it is just an array of objects -- you iterate over the .count and access the i'th object and do whatever you want with the elements.

Very much like an array of plsql records....

4) same.

5) it (1) should be the parameter right after ODCIArgDescList in the call to ODCIStatsSelectivity

using rownum

a reader, December 16, 2004 - 3:11 am UTC

Tom, this thread is most useful,we used rownum in several queries,having in-line view and function,tested and, indeed, they became faster because of the function is called a minimum number of times. We use Oracle 8.1.7.4.1
But people who use Oracle 10g say they had to remove rownum from queries (they too added rownum in 8i to improve performance) because of in 10g these queries became work slow. They reference to Metalink Note 212809.1.
&#8220;Subquery Unnesting and View Merging
Oracle exhaustively attempts to transform statements containing subqueries and views in to simpler statements. The goal of this activity is to make more access paths accessible and find the optimal plan. However, Subquery Unnesting and View Merging is a heuristic process. Subqueries are unnested and views are merged based upon a set of rules. No costs are generated and compared back to the unchanged statement. This could mean that the transformed statement does not perform as well as the untransformed one.
N.B. Oracle 10G introduces Costed Subquery Unnesting and View Merging which should go some way to alleviating the effects of this limitation&#8221;
Tom, does this mean that our optimized this way queries (I mean adding rownum) will be not optimized in 10g and we&#8217;ll have to remove rownum, in other words - return these queries to their source state?
Thank you.


Tom Kyte
December 16, 2004 - 8:16 am UTC

I don't see how that applies -- rownum prevents the predicate pushing and view merging. If that was your goal, it still does that, even if 10g is costing it.


You or they would have to "throw us a bone" -- an example.

Hai Tom

Shreelakshmi, January 10, 2005 - 11:59 pm UTC

Will You Please Tell Me ,
1. What Is The Maximum Of Number of Parameters That We Can Send To The Procedures And Functions.
2. What Is The Priority To The Triggers In Reports?
3. In Back End Triggers How Commiting And Rollback Action Works?
4. Will You Please Tell Me The Difference Between Embedded SQL And Dynamic SQL?


Tom Kyte
January 11, 2005 - 8:58 am UTC

1) no documented limits. I've personally tried over 1,000 just to see if it would work. Only limits might be imposed by various languages (java, vb, whatever)


2) does not compute, not sure what you mean by "priority". but -- I've never written a report in my life using reports so..... suggest you rephrase the question and ask it on the discussion forums on otn.oracle.com

3) if by back end you mean triggers in the database, the quick answer is "you do not commit or rollback in triggers -- it would be insane if it worked, terrible, leading to many bugs in your developed code and killing data integrity". The longer answer is "there are these evil things called autonomous transactions, but that is all I'll say about them"

4) embedded sql is generally found in a "precompiled language", you embedded the SQL with the host language and precompile that source code into other source code which gets compiled into binary object code. A pro*c snippet:


for( i = 0; i < 10; i++ )
{
exec sql select count(*)
into :j
from t
where x = :i;

printf( "The count was %d\n", j );
}


the SQL is embedded in the language, we precompile that source code into some really ugly C code and then compile that.


Embedded SQL can be done dynamically as well. The above example is what is known as "static" sql, if I built the query in a string and executed it -- that would be dynamic sql in pro*c


In VB, Java/JDBC -- all sql is dynamic sql.

In PLSQL you have "embedded" sql and "embedded dynamic sql" (called Native Dynamic SQL)

for x in ( select * from t )
loop
execute immediate 'update ' || x.table_name || ' set x = 5';
end loop;


select * from t is embedded "static" sql.
'update ' || x.table_name || ' set x = 5' is embedded dynamic sql.




Report

Shreelakshmi, January 13, 2005 - 8:01 am UTC

Hai Tom,
Thanks For Your Answers.

1.I was Asking The Executing Priority for The Types Of triggers that are before report,after report,before parameter form ,after parameter form and between pages.

2. What is the maximum buffer size that can be specified using dbms_output.enable function. ?

3. What is utl_file? What are different procedures and functions associated with it ?

4. Can A fuction can take OUT parameters ? If not Why?

5. Give The Difference Between Binding And Lexical Parameters.


Thank You
TOM


Tom Kyte
January 13, 2005 - 9:06 am UTC

1) i suppose they would be documented, have you looked there. as stated "i've never actually written a report using reports in my entire life"

2) 1,000,000 (thats documented too)
</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_outpu2.htm#1000431

in 10gr1 and before....


3) hmm, that would be -- well, documented:

http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_file.htm#998101

4) that would, well, be documented as well.
http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#591 <code>

5) binds are good, lexical parameters (AKA constants glued into a query) are in general evil.


select * from emp where empno = :x; -- bind, good
select * from emp where empno = 12345; -- no bind, not good.


difference between 7i,8i,9i and 10g

Shreelakshmi, January 24, 2005 - 7:03 am UTC

Dear Tom,
Please Give tell me whats the difference between oracle 7i,8i,9i,10g .

Thanks In Advance
regards

Tom Kyte
January 24, 2005 - 8:42 am UTC

one digit and sometimes a letter.


see the new features guides available with the releases (either entitled "getting to know" or "new features"

all are freely avaialable on otn.oracle.com

forms

Shreelakshmi, January 25, 2005 - 2:12 am UTC

What Are The Record Groups And What Is The Use Of It



Thanks in Advance

Tom Kyte
January 25, 2005 - 9:13 am UTC

they are like 2 dimensional arrays -- rows and columns. a result set cached in a client.



Named parameter notation

A reader, March 09, 2005 - 10:09 pm UTC

Since PL/SQL functions called from SQL dont support named notation, how can I workaround it to call a function defined as

foo
(
p_1 in varchar2,
p_2 in varchar2 := 'foo',
p_3 in varchar2 := 'bar',
p_4 in varchar2 := 'abc'
)

How can I call the function with only p_1 and p_4 and let the others default?

Thanks

Tom Kyte
March 10, 2005 - 7:29 am UTC

you would have to overload foo() and have a variant of foo that only takes two parameters, or pass the defaults in the sql call.

Brilliant!

A reader, March 10, 2005 - 9:38 am UTC

Didnt think of overloading foo(), great idea! PL/SQL called from PL/SQL doesnt have this named parameter limitation so I can just expose all the overloaded variants of foo() that I need and internally just call the real foo() with named parameter notation, as appropriate.

Thanks

Associating Statistics with a package function

Andrew Markiewicz, March 11, 2005 - 5:12 pm UTC

This is a very useful thread. It helped me immensely. Thanks.
I would like to add on to the code from "a reader" concerning ODCISelectivity functions (Nov 04) with what I discovered by trying this.

You can associate the selectivity only at the package level (not the functions in the package), but by interrogating the ODCIPredInfo object, you can determine what package function was called and thus, what stats you want to use for each individual function in the package.

The following uses "a reader's" code with a few changes (t1->amat1....)

> create table amat1(x int);

Table created.

>
> exec dbms_stats.gather_table_stats(user, 'amat1')

PL/SQL procedure successfully completed.

>
> CREATE OR REPLACE FUNCTION amaf(i INTEGER) RETURN INTEGER
2 AS
3 BEGIN
4 RETURN 1;
5 END;
6 /

Function created.

>
> create or replace package ama_pkg1
2 as
3
4 function f1(i integer) return integer;
5 function f2(i integer) return integer;
6 pragma restrict_references (ama_pkg1, WNDS,WNPS);
7 end;
8 /

Package created.

>
> create or replace package body ama_pkg1
2 as
3
4 function f1(i integer) return integer
5 is
6 begin
7 return 1;
8 end;
9
10 function f2(i integer) return integer
11 is
12 begin
13 return 1;
14 end;
15 end;
16 /

Package body created.

>
> CREATE OR REPLACE TYPE t_ama1 AS object
2 (
3 dummy NUMBER,
4 static FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER,
5 static FUNCTION ODCIStatsSelectivity(
6 pred sys.ODCIPredInfo
7 , sel OUT NUMBER
8 , args sys.ODCIArgDescList
9 , p_start INTEGER
10 , stop INTEGER
11 , i INTEGER
12 , env sys.ODCIEnv
13 ) RETURN NUMBER
14 )
15 /

Type created.

>
> CREATE OR REPLACE TYPE body t_ama1
2 AS
3 static FUNCTION ODCIStatsSelectivity(
4 pred sys.ODCIPredInfo
5 , sel OUT NUMBER
6 , args sys.ODCIArgDescList
7 , p_start INTEGER
8 , stop INTEGER
9 , i INTEGER
10 , env sys.ODCIEnv
11 ) RETURN NUMBER
12 IS
13 BEGIN
14 s.log('Pred : ' || pred.ObjectName); -- log values
15 s.log('Meth : ' || pred.MethodName); -- ""
16 if (pred.MethodName = 'F1') then
17 sel := 5; -- 5% selectivity
18 else
19 sel := 1;
20 end if;
21 return ODCIConst.Success;
22 END;
23 static FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER
24 IS
25 begin
26 ifclist := sys.odciobjectlist();
27 ifclist.extend;
28 ifclist(1):= sys.odciobject('SYS','ODCISTATS2');
29 RETURN sys.odciconst.success;
30 END;
31 END;
32 /

Type body created.

>
> ASSOCIATE STATISTICS WITH FUNCTIONS amaf USING t_ama1;

Statistics associated.

Elapsed: 00:00:00.02
> ASSOCIATE STATISTICS WITH PACKAGES ama_pkg1 USING t_ama1;

Statistics associated.

>
> exec dbms_stats.set_table_stats(user, 'amat1',numrows=>1000000)

PL/SQL procedure successfully completed.

>
>
> set autotrace on explain
>
> select *
2 from amat1
3 where amaf(x) = 1
4 /

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10000 Bytes=130000)
1 0 TABLE ACCESS (FULL) OF 'AMAT1' (Cost=2 Card=10000 Bytes=130000)



>
> select *
2 from amat1
3 where ama_pkg1.f1(x) = 1
4 /

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=50000 Bytes=650000)
1 0 TABLE ACCESS (FULL) OF 'AMAT1' (Cost=2 Card=50000 Bytes=650000)



> select *
2 from amat1
3 where ama_pkg1.f2(x) = 1
4 /

no rows selected


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=10000 Bytes=130000)
1 0 TABLE ACCESS (FULL) OF 'AMAT1' (Cost=2 Card=10000 Bytes=130000)




Tom Kyte
March 12, 2005 - 9:59 am UTC

thanks!

A reader, June 29, 2005 - 11:37 am UTC

I have a situation like this:

I have to outer join two tables 'A' and 'B'. For every
record of table A, table B might not
have a matching record.

If there is a matching record, i have to return one of
its column, else return 0.

Would it be a good idea to call a user defined function
(which checks if a matching record exists and if yes, returns
the value of a specified column, else returns zero),
instead of outer join?

I have checked the explain plan, wanted to check with you if
this approach can have any other issues.

Thanks.

Tom Kyte
June 29, 2005 - 11:42 am UTC

databases were born to join. it is what they do best. let the database do your heavy lifting and write as little code as you can.

Great thread on ODCIStatsSelectivity/Data Catridges

Krous, February 09, 2006 - 12:07 pm UTC

Tom quick followup:
Considering ODCIStatsSelectivity/ Data Catridges and its focus on making the "database <has> itself been made extensible", how would you rate its importance?
Do you think this is similar to hints where the less we use it the better (ofcourse there is always the one off cases where first_row(1) etc are beneficial)?
Are we not just fooling the System to follow a particular plan ( Like a partial RBO) and perhaps jeopardizing future upgrades/enhancements (I am guessing it would be hell to figure out why some queries are tanking and to trace it back to stats defined) ?
How often have you personally used this functionality in a system and do you see it being used that much? What conditions would you look for before you use it?

Tom Kyte
February 10, 2006 - 11:21 am UTC

here, you are telling the optimizer more information - you KNOW what the selectivity of your function is - the OPTIMIZER does not. You are simply conveying what you know to it?

I don't know what you mean by "Are we not just fooling the System to follow a particular plan". You are giving the optimizer additional information that leads it to choose the correct plan - given that the assumptions it was making about the selectivity of your function was wrong in the first place (leading it to the wrong plan)

How can I convert this function using case....

A reader, March 09, 2006 - 11:28 am UTC

Tom,

You have said in this thread that is better to
use case....for performance. In this example
how can I convert this into a simple case select.
thanks.

create or replace function my_func
(market_id in national.market_id%type)
return varchar
is
l_count number;
begin
select count(1) into xcount from foreign m where m.int_market = market_id;
if l_count > 0 then
return 'Y';
else
return 'N';
end if;
exception
when others then
raise_application_error (-1000, 'blah blah...int_market = '||market_id|| sqlerrm);
end my_func;


Tom Kyte
March 09, 2006 - 3:20 pm UTC

select ....,
nvl( (select 'Y'
from foreign m
where m.int_market = x.market_id
and rownum = 1 ), 'N' ),
...
from table X;

would be one way to get rid of the un-necessary and very inefficiently implemented function (count all of the rows???)

Deterministic function doubt

Narendra, March 10, 2006 - 5:09 am UTC

Hi Tom,

In one of the posts above, you mentioned that function
Create or Replace Function Det_or_Not(p_emp_no in emp.emp_no%type) return
emp.emp_name%type is
v_emp_name emp.emp_name%type;
begin
select emp_name into v_emp_name
from emp
where emp_no = p_emp_no;

return v_emp_name;
end;
is non-deterministic because "A simple "update emp set emp_name = 'foo' " will change the return value for a
given p_emp_no. Given the input 12345 -- this function may return a different
value at two different points in time."

But after some followups, you have given an example
create or replace function your_bonus( p_empno in number )
return number
2 deterministic
3 as
4 l_comm number;
5 begin
6 dbms_application_info.set_client_info( sys_context( 'userenv',
'client_info')+1 );
7
8 select comm
9 into l_comm
10 from emp
11 where empno = p_empno;
12
13 return l_comm;
14 exception
15 when no_data_found then
16 raise program_error;
17 end;
I am confused over how can this function be deterministic. I can update comm for an employee and this function will give me different results (as you said above).

Also, I have this function that returns 0 if passed date is invalid and 1 if passed date is valid.
FUNCTION is_valid_date_fn(
p_date VARCHAR2,
p_not_in_future IN VARCHAR2
)
RETURN NUMBER
AS

BEGIN
IF p_date != To_Char(To_Date(p_date,'DD/MM/YYYY'),'DD/MM/YYYY')
THEN
RETURN 0;
ELSE
IF p_not_in_future='Y'
AND To_Date(p_date,'DD/MM/YYYY')>SYSDATE
THEN
RETURN 0;
ELSE
RETURN 1;
END IF;
END IF;

EXCEPTION

WHEN OTHERS
THEN
RETURN 0;

END is_valid_date_fn;

Is this function DETERMINISTIC ? Also, is using this function the best possible way to idnetify ALL records in a table that has INVALID dates in a VARCHAR2 field. ?

Thanks

Tom Kyte
March 10, 2006 - 12:22 pm UTC

I was demonstrating that deterministic had no effects on the number of times the function is called (at least until 10gr2). That function is in fact not truly deterministic.

</code> http://dizwell.com/main/index.php?option=com_jd-wiki&Itemid=112&id=deterministicfunctions <code>

there is no best way to do something that should never need to be done (because you should store dates in dates, sigh, never in a string). but that function is a bit overdone, could just be:

begin

begin
l_date := to_date( p_date, 'dd/mm/yyyy');
exception
when others
then return 0;
end;
if ( p_not_in_future = 'Y' and l_date > sysdate )
then
return 0;
else
return 1;
end if;
end;


that is not detereministic. sysdate changes, therefore the decision to return 0 or 1 changes.


Thanks

Narendra, March 11, 2006 - 3:37 am UTC

Tom,

Thanks for making things clear (and pointing out errors that I should have trapped).
I have learnt from your site only about using appropriate datatypes to store data i.e. use DATE to store dates.
But in my case problem is, I have to deal with data that comes from external source. The requirement is to log all INVALID dates that are coming in the file that needs to be loaded. Hence I have to store dates in VARCHAR2 and validates them using a user defined function so that all invalid dates can be logged.
I thought this (using a user-defined function) is the only best possible way to achieve this requirement. Would appreciate if you suggest any "better" option.

Tom Kyte
March 11, 2006 - 3:39 pm UTC

other than just trying to insert the string into date field directly and using bulk inserts and the "SAVE EXCEPTIONS" clause in PLSQL to catch errors and log them.

Or in 10gR2, using "log errors" on the insert statement directly.

Eg (from my newest seminar material on the 10gr2 log errors clause:)

in 10gr2 you would simply:

insert /*+ append */
into t1
select *
from t2
LOG ERRORS REJECT LIMIT UNLIMITED;



In 10gr1 and before you could code:

declare
cursor C is
select *
from t2
type array is table of c%rowtype;
l_data array;

dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
l_errors number;
l_errno number;
l_msg varchar2(4000);
l_idx number;
Begin
begin
open c;
loop
fetch c bulk collect into l_data limit 100;
begin
forall i in 1 .. l_data.count SAVE EXCEPTIONS
insert into t1 values l_data(i);
exception
when DML_ERRORS then
l_errors := sql%bulk_exceptions.count;
for i in 1 .. l_errors
loop
l_errno := sql%bulk_exceptions(i).error_code;
l_msg := sqlerrm(-l_errno);
l_idx := sql%bulk_exceptions(i).error_index;
insert into err$_t2
( ora_err_number$, ora_err_mesg$, ora_err_optyp$,
....)
values
( l_errno, l_msg, 'I',
l_data(l_idx).column_name, ....);
end loop;
end;
exit when c%notfound;
end loop;
close c;
end;



A function CAN return a boolean

Sinan Topuz, April 11, 2006 - 11:47 pm UTC

Tom,

With regards to your followup to Kulguru's question on functions and boolean return values, I have a function which does that. I am using Oracle 8.1.7.

CREATE OR REPLACE FUNCTION dw_isdate
(pstr  IN VARCHAR2 DEFAULT ''
,pfmt  IN VARCHAR2 DEFAULT 'MM/DD/YYYY') RETURN BOOLEAN
IS

DDUMMY DATE;

BEGIN

  DDUMMY := TO_DATE(pstr, pfmt);

  RETURN TRUE;

  EXCEPTION WHEN OTHERS THEN
    RETURN FALSE;
END;

SQL> declare
  2  
  3  begin
  4  
  5  if dw_isdate('1/1/2006') then
  6     raise_application_error(-20000, 'YES');
  7  else
  8     raise_application_error(-20000, 'NO');
  9  end if;
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-20000: YES
ORA-06512: at line 6 

Tom Kyte
April 12, 2006 - 8:00 am UTC

read the subject again "limitations of calling functions from sql "


call your function from SQL. Yes, a plsql function can return any type PLSQL understands (like boolean), but NO a plsql function called from SQL cannot - they are limited to datatypes SQL understands (which does not include boolean)

Can this small code be done in a single sqlquery?

Yves, April 12, 2006 - 12:24 pm UTC

Tom, right now I am running this by by doing:

SELECT getLatestGDSError from dual;

I was wondering if it is possible to formulate this logic in a single select statement.

Thanks.

CREATE OR REPLACE FUNCTION getLatestGDSError RETURN CLOB IS
result CLOB;
BEGIN
SELECT message
INTO result
FROM (SELECT message MESSAGE, rownum subQueryRowNum
FROM test_gds_log
WHERE session_id IN
(SELECT MAX(session_id) from test_GDS_SESSION)
AND classification = 'Error'
ORDER BY asof_date)
WHERE subQueryRowNum = 1;

RETURN(result);

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN('No Error found');
END getLatestGDSError;

Tom Kyte
April 12, 2006 - 7:36 pm UTC

you have a single select statment in your function already - so I'm confused?

that query looks rather funky! (eg: wrong)


SELECT message
INTO result
FROM (SELECT message MESSAGE, rownum subQueryRowNum
FROM test_gds_log
WHERE session_id IN (SELECT MAX(session_id) from test_GDS_SESSION)
AND classification = 'Error'
ORDER BY asof_date)
WHERE subQueryRowNum = 1;



first - select max(session_id) will return precisely ONE ROW, not a set. So, that should be "session_id = (select max() .... )

second - you assign ROWNUM (as subQueryRownum) AND THEN SORT by asof_date
AND THEN take the row where subqueryRownum = 1!!!

you assigned rownum 1, 2, 3, 4, 5 to the rows
AND THEN sorted them (mixing the 1,2,3,4,5 up...)
AND THEN took the row with 1

I believe you mean "where rownum = 1" to take the first row AFTER SORTING don't you??


Follow-up on my question

Yves, April 12, 2006 - 8:36 pm UTC

Yes, Tom you are correct. The function should be rewritten as:

CREATE OR REPLACE FUNCTION getLatestGDSError RETURN CLOB IS
result CLOB;
BEGIN
SELECT message
INTO result
FROM (SELECT message MESSAGE
FROM test_gds_log
WHERE session_id =
(SELECT MAX(session_id) from test_GDS_SESSION)
AND classification = 'Error'
ORDER BY asof_date)
WHERE rownum = 1;

RETURN(result);

EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN('No Error found');
END getLatestGDSError;


My questions still remain: rather that selecting this function from dual, is there a way to rewrite this SQL to return the string "No error Found" is the main SQL does not return any row?

Thanks.

Tom Kyte
April 12, 2006 - 9:18 pm UTC

ops$tkyte@ORA10GR2> create table t ( x clob );

Table created.

ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> select nvl( (select x from t), 'No Error Found' ) from dual;

NVL((SELECTXFROMT),'NOERRORFOUND')
-------------------------------------------------------------------------------
No Error Found


Your query is "(select x from t)" 

Thanks a million

Yves, April 13, 2006 - 1:02 am UTC

I did not know that the NVL function itself could take an SQL.

Thanks.

Tom Kyte
April 13, 2006 - 7:42 am UTC

it is a feature called "scalar subqueries", you can use a query that returns a single attribute and at most one row where you could normally use an expression or literal.

Sort first and then rownum predicate after

A reader, June 12, 2006 - 2:50 pm UTC

Tom, if I want to restrict my query based on rownum after the sort, is a subquery the only option available?

In the above example, the query posted is:

SELECT message
FROM (SELECT message MESSAGE
FROM test_gds_log
WHERE session_id =
(SELECT MAX(session_id) from test_GDS_SESSION)
AND classification = 'Error'
ORDER BY asof_date)
WHERE rownum = 1;

Is there a way to rewrite is without having a nested query? May be using analytic functions?

Thanks.


Tom Kyte
June 13, 2006 - 10:47 am UTC

why would you do the select max()??

select message
from ( select message
from t
where classification = 'Error'
order by session_id DESC, asof_date )
where rownum = 1;


that would be the correct, proper, and most efficient way to perform this query.

Are there other methods? Yes, but none of them would have the three important attributes I just listed.

Feedback

A reader, June 13, 2006 - 6:46 am UTC

Tom, Any feedback on the above?

Thanks.

Tom Kyte
June 13, 2006 - 12:25 pm UTC

yes, *give me some time*.

I have a day job see :)

And sometimes I take a night off...

Trying to find a better way..

A reader, June 23, 2006 - 1:00 pm UTC

Tom,

I have the following

FUNCTION is_some_FUNC( carID_in NUMBER
, color_in VARCHAR2
)
RETURN PLS_INTEGER
IS

CURSOR c_cur(PID NUMBER, color_in VARCHAR2)
IS
SELECT 1
FROM big_table
WHERE car_id = pID
AND color = color_in
AND date IS NULL;
dummy NUMBER := 0;
BEGIN

OPEN c_cur (carID_in, color_in);
FETCH c_cur INTO dummy;
CLOSE c_cur;

RETURN dummy;
END is_some_FUNC;

and then I called it like this..

IF area = 'BLACK' THEN
IF MACHINE > 0 OR MACHINE_CHK = 0 AND is_some_FUNC(carID_in, color_in) > 0
THEN
machine := 1;
RAISE EXIT_EXCEPTION;
END IF;
ELSE


I would like to replace the function or the way I am calling the function so I don't to call it soo many times
for the big_table. Any ideas on how to do this?




Tom Kyte
June 23, 2006 - 1:26 pm UTC

look at the code that believes it NEEDS TO KNOW THIS RECORD EXISTS

and then fix it, so it doesn't need to know that.


I hate code that counts records - 99999 time out of 100000 - it is wholly unneccessary (and in a multi-user environment sort of "not really even close to being safe or correct")

so, try to remove the check in the first place, only you can do that since only you know the algorith.

Thank you very much for your input

A reader, June 23, 2006 - 11:04 pm UTC


Clarification

Craig, June 27, 2006 - 10:48 am UTC

So what is it, you mean to say this is not the right way to do things

declare

Select count(1)
into v_x
from emp
where Commission > 0;

if v_x > 0 then
update abc set count_flag='Y';
end if;

if not why and what is the alternative?



Tom Kyte
June 27, 2006 - 11:05 am UTC

update abc
set count_flag = 'Y'
where exists (select null from emp where commission > 0);

would let you erase that procedural code.


count(1) - what is that? you want to count 1's? didn't you really want to count records - that would be COUNT(*).

but don't worry, internally, count(1) is optimized away these days and turned into count(*) since so many people did it wrong.

Carig, June 27, 2006 - 11:20 am UTC

Good Expalination, however just though that would be the answer how about this

if x> 0 then
for i in 1..10 loop
cntr:=cntr+1;
update abc set flag=cntr;
where id=i;
end loop;
else
null;
end if;

how do you think we can eliminate x>0 from here.

Regards,
Craig.

Tom Kyte
June 27, 2006 - 2:26 pm UTC

give the possible "logic" behind this.


but basically, what the heck is "x", I presume "x" is the v_x

and then, what is cntr?


basically, you seem to be saying "if that row exists" (same where exists), then update rows with ids = 1..10, setting flag = cntr + id


update abc
set flag = :cntr + id
where id between 1 and 10
and exists (select null from emp where commission > 0);




making a user defined function as normal functions

abz, November 06, 2006 - 7:22 am UTC

I made a user defined function IS_NUMBER. Its function is to take a VARCHAR2 as input and return 1 if its converatable to NUMBER otherwize it returns 0.

I want that all users of my database can easly use this
function as they use other builtin functions like TO_CHAR, TO_DATE etc.

Even if I create it in one schema and GRANT execute to PUBLIC, the users will have to concatenate the schema name before the function name to use it. I want them to use it as easily as they use built in functions.

Is there a way to do this?

Tom Kyte
November 06, 2006 - 10:16 am UTC

that would be called "a public synonym", but BE CAREFUL WITH THOSE. I would seriously recommend you use a "utility" schema.

I have NO PROBLEM with people coding:

select to_number(x), utility.is_number(y) from t;


In fact, I would seriously PREFER that as it makes it "clear" that is_number is NOT SQL, is not builtin and there is a performance implication involved in invoking it

(and it'll not confuse people who become to believe that is_number() is builtin when they go to their next job, you'll save them from looking "silly" in the future :)

performance?

abz, November 06, 2006 - 10:22 am UTC

Ok, you are right, but how can there be performance problems due to this?

Tom Kyte
November 06, 2006 - 10:27 am UTC

there is a measurable overhead involved in calling plsql from sql.  It gets reduced with each release, but it exists.


ops$tkyte%ORA10GR2> create or replace function f( p_data in number ) return number
  2  as
  3  begin
  4          return p_data;
  5  end;
  6  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t as select object_id from all_objects;

Table created.



select count(object_id) from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.01          0          1          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.02       0.03          2        162          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.02       0.05          2        163          0           2
********************************************************************************
select count(f(object_id)) from t

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          1          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        4      0.36       0.37          0        162          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8      0.36       0.37          0        163          0           2
 

name resolution

abz, November 06, 2006 - 11:03 am UTC

Ok, thanks, but one question comes to my mind is
if I use public synonym, then what happens
when my U.D.F. name conflicts with built in function name.


Tom Kyte
November 06, 2006 - 11:45 am UTC

that is part of my point.

I don't like public synonyms.
I don't think they should in general be used.
I have NOT A SINGLE problem with going schema.function, in fact - I'm suggesting that.

public synonym

abz, November 09, 2006 - 10:10 am UTC

I have created public synonym for a user defined function, but it still requires to GRANT EXECUTE on the function to every user.

Tom Kyte
November 09, 2006 - 2:31 pm UTC

and the issue is what exactly?

a synonym is NOT "grant", it is simply a synonym, it does not convey access.

you have to grant access to those things you want to have accessible. if you really want this for "everyone" grant execute to public.

A reader, December 19, 2006 - 9:31 am UTC

Tom

If I do a nested functions, and each of the function does a query, will there be a SQL<-> PL/SQL context overhead for

select f2(f3(f4(f5))) from mytable?

Will that be WORSER than replacing them with scalar subqueries, pulled out of each of the functions?

Tom Kyte
December 19, 2006 - 10:21 am UTC

"worser" ;)


you will have


sql -> f2


but then each routine does switches from plsql to sql.


If you do not have to use plsql (if you can do this in pure sql) that is always preferable.

convert into words

Dawar, April 15, 2007 - 2:47 pm UTC

Tom,

Is there any way to convert number into the words from sql?

e.g:

sql> select 1200 ...convert to word
.. from dual;

COLUMN
Twelve Hundred

Regards,
Dawar

Functions in predicates

David Rodrgiuez, December 03, 2007 - 11:02 am UTC

Hi

My developers make extensive use of PL/SQL functions in predicates such as

select ..
from .....
where x() = 'S'

It is a nightmare because of the prformance, I have made them a suggestion but I am not sure if I am toally right.

I told them instead of using the function in the WHERE clause using it in SELECT such as

create or replace function dname_lookup(p_deptno number)
return varchar2
is
l_dname varchar2(10);
begin
select dname
into l_dname
from dept
where deptno = p_deptno;
return l_dname;
end;
/

And instead of following:

select ename
from emp, dept
where dname_lookup(emp.deptno) = 'SALES'
and dept.deptno = 30
and emp.deptno = dept.deptno

Use

select *
from
(select ename, dname_lookup(emp.deptno) dname
from emp, dept
where dept.deptno = 30
and emp.deptno = dept.deptno)
where dname = 'SALES'


Is this the right way?

Thanks


Tom Kyte
December 03, 2007 - 11:45 am UTC

select ename
from emp, dept
where (select dname_lookup(emp.deptno) from dual) = 'SALES'
  and dept.deptno = 30
  and emp.deptno = dept.deptno 



would be sufficient to invoke scalar subquery caching (search for those three words in quotes to read more)

however, if your developers are using plsql with such simple things - just a look up, I'd have them ERASE the plsql altogether and JUST USE SQL.

In 11g, you'll be able to cache that function - new 11g feature - which removes the need for the "trick" of the select from dual...

Functions in predicates

David Rodrgiuez, December 03, 2007 - 12:54 pm UTC

Hi

Thanks for the reply.

I do think as you that some of PL/SLQ can by changed by SQL, in fact one of most executed SQL in the database (300 per second average) is executed from by these functions:

select
from t1, t2, t3
where ....
and look_account(company_id, account_id) = 'Y'

where look_acount code is this:


FUNCTION LOOK_ACOUNT ( P_COMPANY IN NUMBER ,
P_ACCOUNT IN VARCHAR2 ) RETURN VARCHAR2 IS
V_ACCOUNT VARCHAR2(1);
CAR VARCHAR2(1);
BEGIN
IF NVL( V_ALPHA( P_COMPANY, 'COST_CENTER') , 'N' ) <> 'Y' THEN
V_ACCOUNT := 'N';
ELSE
BEGIN
SELECT 'X'
INTO CAR
FROM PDA_ACCOUNTS
WHERE PDA_COMPANY = P_COMPANY
AND P_ACCOUNT BETWEEN PDA_ACCONT AND RPAD(PDA_XCC,12,'Z')
AND ROWNUM < 2;
V_ACCOUNT := 'Y';
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_ACCOUNT := 'N';
END;
END IF;
RETURN V_ACCOUNT ;
END LOOK_ACOUNT;
/

FUNCTION V_ALPHA (P_COMPANY IN NUMBER ,
P_CODE IN VARCHAR2)
RETURN VARCHAR2 IS
V_RETURN VARCHAR2(1000);
BEGIN
V_RETURN := NULL;
BEGIN
SELECT DECODE(D_VALUE,'A',D_ALPHA,NULL)
INTO V_RETURN
FROM X_PARAMETERS
WHERE COMPANY = P_COMPANY
AND CODE = P_CODE AND
AND STATUS = 'A';
EXCEPTION
WHEN NO_DATA_FOUND THEN
V_RETURN := NULL ;
END ;
RETURN TRIM(V_RETURN);
END;
/



I changed the query to this but I am not sure if it is correct?


select
from t1, t2, t3, x_parameters
where ....
AND EXISTS (SELECT NULL
FROM PDA_ACCONTS
WHERE PDA_EMPRESA = company_id AND
AND account_id BETWEEN PDA_ACCONT AND RPAD(PDA_XCC,12,'Z')
AND X_PARAMETERS.COMPANY = DOC_COMPANY
AND X_PARAMETERS.CODE = 'COST_CENTER'
AND X_PARAMETERS.STATUS = 'A'

Functions in predicates

David Rodrgiuez, December 03, 2007 - 1:02 pm UTC

I make a pardon the last part is

select
from t1, t2, t3, x_parameters
where ....
AND EXISTS (SELECT NULL
FROM PDA_ACCONTS
WHERE PDA_EMPRESA = company_id AND
AND account_id BETWEEN PDA_ACCONT AND RPAD(PDA_XCC,12,'Z')
AND X_PARAMETERS.COMPANY = company_id
AND X_PARAMETERS.CODE = 'COST_CENTER'
AND X_PARAMETERS.STATUS = 'A'

Thanx

function

A reader, January 21, 2008 - 10:10 pm UTC

Tom:

I have a function that computes a math formula (available credit).

Do you always program to return a number if you are doing that.

I have it return "-1" if the function runs into an exception and then an OUT parameter will tell me the name of the error like

if functions runs fine and result was 500 it returns
500

if function goes to runtime error
-1: No records in DB
-1: too many records in DB

1. Is this how you would write it?

2. I could not call this from the SQL function. is that due to the "OUT" parameter for exception output.
Tom Kyte
January 21, 2008 - 10:27 pm UTC

You have missed the boat on programming and error handling then.

Have it raise an EXCEPTION when............ something exceptional happens


return codes - they are so 1970.
what is the answer is -1?


USE AN EXCEPTION - raise_application_error() or raise USER_DEFINED_EXCEPTION


return codes, ugh, hate em.

out parameters with messages - nope, never.


function

A reader, January 21, 2008 - 10:48 pm UTC

Tom:

I do not understand your comment. Here is the function. It reads three inputs and then return an output. If an error occurs it returns the message. What is wrong with this and how do you rewrite this?

CREATE OR REPLACE FUNCTION CRT(p_mon IN VARCHAR2, p_year IN NUMBER, p_custid IN NUMBER, p_message OUT VARCHAR2)
RETURN NUMBER
IS

v_ac NUMBER := 0;
v_crt NUMBER := 0;

BEGIN

FOR i IN 1 .. TO_NUMBER(TO_CHAR(TO_DATE(p_mon,'MONTH'),'MM')) LOOP
SELECT annual_credit
INTO v_ac
FROM cust_credits
WHERE effective_date = (SELECT MAX(effective_date)
FROM cust_credits
WHERE effective_date <= TO_DATE('1-' || i || '-' || p_year,'DD-MM-YYYY'))
AND custid = p_custid;

v_crt := v_crt + v_ac;
END LOOP;

p_message := 'Annual Credit computed successfully';
RETURN v_crt / 12;

EXCEPTION

WHEN NO_DATA_FOUND THEN
p_message := 'No annual credit defined in DB';
RETURN -1;

WHEN TOO_MANY_ROWS THEN
p_message := 'Invalid Data in DB';
RETURN -1;

WHEN OTHERS THEN
p_message := SQLERRM;
RETURN -1;

END crt;

Tom Kyte
January 22, 2008 - 6:34 am UTC

I was quite clear:

DO NOT USE RETURN CODES. Return codes are so "1970's" meaning - ugh, they are ugly, they are easy to ignore, they cause other errors when people do ignore them, they make your code verbose, hard to understand, hard to follow, hard to debug, they add nothing, they remove a lot.



Never catch WHEN OTHERS, you have no clue what happened - neither does the person calling you - just let the ERROR PROPAGATE. The invoker gets an Oracle error code ( to log, to ignore, to whatever they want, not really relevant). The invoker gets an Oracle error message (to log, to ignore, to display, to whatever). They cannot ignore it easily. Hopefully they DO NOT ignore it, they let it propagate all of the way out to the original client application - where the entire transaction is rolled back (but would NOT be rolled back if you break the error handling innate to the language as you are now)

CREATE OR REPLACE FUNCTION CRT(p_mon IN VARCHAR2, p_year IN NUMBER, p_custid IN NUMBER )
RETURN NUMBER
IS
    v_ac  NUMBER := 0;
    v_crt NUMBER := 0;
BEGIN

FOR i IN 1 .. TO_NUMBER(TO_CHAR(TO_DATE(p_mon,'MONTH'),'MM')) LOOP
    SELECT  annual_credit
      INTO  v_ac
      FROM  cust_credits
      WHERE effective_date = (SELECT  MAX(effective_date)
                                FROM  cust_credits
                                WHERE effective_date <= TO_DATE('1-' || i || '-' || p_year,'DD-MM-YYYY'))
        AND custid = p_custid;
    v_crt := v_crt + v_ac;
END LOOP;

RETURN v_crt / 12;

EXCEPTION
    WHEN NO_DATA_FOUND THEN raise_application_error( -20001, 'No annual credit defined in DB' );
    WHEN TOO_MANY_ROWS THEN raise_application_error( -20002, 'Invalid Data in DB' );
END crt;
/


that is the most your routine should do.

I'm suspicious of your query - are you sure you didn't want an "and cust_id=" in the subquery as well?

@reader "What is wrong with this..."

Duke Ganote, January 22, 2008 - 3:58 am UTC

As Tom wrote: "raise an EXCEPTION". And CATCH the exception when the function is called.

For example, in the CRT function below, there's no P_MESSAGE OUT parameter. But it throws the same messages as your CRT function.

create or replace
function crt ( p_in IN VARCHAR2 ) return VARCHAR2
is l_out VARCHAR2(30);
begin
select dummy||dummy into l_out from dual where dummy = p_in;
return l_out;
exception
when NO_DATA_FOUND then
raise_application_error( -20000,'No annual credit defined in DB');
when TOO_MANY_ROWS then
raise_application_error( -20001,'Invalid Data in DB');
when OTHERS then
raise;
end;
/
ed
Wrote file afiedt.buf

1 declare l_msg VARCHAR2(30);
2 begin
3 dbms_output.put_line(crt('NOT THERE'));
4 exception
5 when others then
6 dbms_output.put_line(SQLERRM);
7* end;
/
ORA-20000: No annual credit defined in DB

PL/SQL procedure successfully completed.

Wrote file afiedt.buf

1 declare l_msg VARCHAR2(30);
2 begin
3 dbms_output.put_line(crt('X'));
4 exception
5 when others then
6 dbms_output.put_line(SQLERRM);
7* end;
/
XX

PL/SQL procedure successfully completed.


Tom Kyte
January 22, 2008 - 7:21 am UTC

  4  exception
  5    when others then
  6      dbms_output.put_line(SQLERRM);
  7* end;



the example was great until you put THAT there.


when others
not followed by RAISE or RAISE_APPLICATION_ERROR()

is a bug in the developed code 99.9999999999% of the time - here, most surely it would be.

"THAT"

Duke Ganote, January 22, 2008 - 4:17 pm UTC

My sole, feeble defense: it simply demonstrates that the function's user-defined exceptions can be caught and read by the calling routine. Agreed, it's not production-worthy code!

function

A reader, January 22, 2008 - 4:23 pm UTC

The example that the Duke provided DID NOT return a number. I only added the OUT because the function was returning a NUMBER. I would not need it if it was VARCHAR2

1. Concerning the exception handling

---This is the error I get with raise application
ERROR at line 1:
ORA-20002: Invalid Data in DB
ORA-06512: at "XXX.CRT", line 24

--This is the error I get with OUT parameter and using error code of "-1"

-1:Invalid Data in DB


What is the difference for the user when he sees either one?

Are you thinking from an application point of view on what action to do based on this error reported.

2.Can't I use "When others" and just report the SQLCODE and SQERRM back to client.

Most oracle books I have seen use this to inform that an error has occurred at runtime.


"This output informs you that an error has occured at runtime. However, you do not know what the error is and what caused it. Maybe no record in table or datatype mismatch.

This is good because you cannot always know all of the possible runtime errors that may occur when a program is run.
So it is a good practice to have OTHERS exception handler in your script with SQLCODE and SQLERRM

WHEN OTHERS THEN
v_err_Code :=SQLCODE;
v_err_msg := SUBSTR(SQLERRM,1,200);
DBMS_OUPTUT.PUT_LINE.('Error code: '||v_err_Code);
DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);


3. When I use the above function in a web procedure like

SELECT CRT(...) into v_Credit from dual;

then I get that ORA-20002 from the function.

Do I need to build another exception handler on the page to display to the user the error message? How do i handle this.


Tom Kyte
January 22, 2008 - 6:38 pm UTC

... --This is the error I get with OUT parameter and using error code of "-1" ...

I believe you haven't read nor tried my example.

EXCEPTION
    WHEN NO_DATA_FOUND THEN raise_application_error( -20001, 'No annual credit defined in DB' );
    WHEN TOO_MANY_ROWS THEN raise_application_error( -20002, 'Invalid Data in DB' );
END crt;
/



If you did it correctly, using exceptions you would not get the error stack you said, you would get your own nice little messages.

If you use return codes
then
   you have not very good code
end if;


2) YES, and I told you HOW

NOT VIA RETURN CODES

VIA EXCEPTIONS

EXCEPTION
    WHEN NO_DATA_FOUND THEN raise_application_error( -20001, 'No annual credit defined in DB' );
    WHEN TOO_MANY_ROWS THEN raise_application_error( -20002, 'Invalid Data in DB' );
END crt;
/



3) you get ora-20002 AND the error message. You get both. Just like you get a "return code" and a "string", your client application gets a sqlcode and a sql error msg!

one that it cannot ignore or skip!!!

one that no one can!


function

A reader, January 22, 2008 - 7:04 pm UTC

TOm:

I DID read and copied and ran exactly the function you had. I am not sure why you said that.

This is my exception block in the function:

EXCEPTION
    WHEN NO_DATA_FOUND THEN raise_application_error( -20001, 'No annual credit defined in DB' );
    WHEN TOO_MANY_ROWS THEN raise_application_error( -20002, 'Invalid Data in DB' );
END crt;


You can test it yourself to see what you get. BUt this is wht I get

-- A case where i have multiple records in cust_credits.

SQL> /
select crt('september',2008) from dual
       *
ERROR at line 1:
ORA-20002: Invalid Data in DB
ORA-06512: at "XXX.CRT", line 29


--A case where I have no data.

SQL> delete cust_credits;

3 rows deleted.

SQL> select crt('january',2008) from dual;
select crt('january',2008) from dual
       *
ERROR at line 1:
ORA-20001: No annual credit defined in DB
ORA-06512: at "XXX.CRT", line 28


Tom Kyte
January 22, 2008 - 7:17 pm UTC

You wrote:

---This is the error I get with raise application
ERROR at line 1:
ORA-20002: Invalid Data in DB
ORA-06512: at "XXX.CRT", line 24

--This is the error I get with OUT parameter and using error code of "-1"

-1:Invalid Data in DB



the problem with that statement is

a) with raise application error, you get AN ERROR
b) without it, you do not GET AN ERROR

you have hidden the error, return code are easy to ignore. Catching the exception in plsql and turning it into "not an exception" CHANGES the transactional logic of the database entirely (you break it basically, you break statement level atomicity)

That is what is wrong here - you do not understand that with a 'return code' you do not HAVE an error, you have the absolute and complete LACK OF ERROR.

Ok, so what - the client gets

a) 20002 (use whatever code you want)
b) the sql error message "Invalid data in DB"


and they get the line number and the procedure to boot (MORE information)

Now, your client application displays to the end user WHAT EVER IT FEELS LIKE, but it has

a) 20002, the error code (better than, oh, say -1 for everything in a variable)
b) sqlerrm which is the error message.

I'm not sure what the issue is?

smk - you are free to do what you will. I'm done on this thread.

exceptions are the right way to handle errors
return codes are not

function

A reader, January 22, 2008 - 7:34 pm UTC

Tom:

Now you convinced me you were correct in what the diff would be.

I will just add this other general handler. Is the 1st better or the second or both same.


exception
when others then raise_application_error( -20001, 'Error processing your query ' || sqlerrm
);



WHEN OTHERS THEN
v_err_Code :=SQLCODE;
v_err_msg := SUBSTR(SQLERRM,1,200);
DBMS_OUPTUT.PUT_LINE.('Error code: '||v_err_Code);
DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);
Tom Kyte
January 22, 2008 - 7:54 pm UTC

arg...

why are you catching it??????? ignore it, let it propagate out.


and why would you take a perfectly good ora-xxxx error number and obscure it from vision?

function

A reader, January 22, 2008 - 9:28 pm UTC

TOm:

You mean all the recommendations by oracle books are false.

"This is good because you cannot always know all of the possible runtime errors that may occur when
a program is run.
So it is a good practice to have OTHERS exception handler in your script with SQLCODE and SQLERRM".

You are saying never use it. the program will print the error regardless if it is there or not.

Tom Kyte
January 23, 2008 - 7:39 am UTC

Yes, that is what I am saying - if all of the books you read say "have when others, do not re-raise the exception, use dbms_output or just log the error and return" If they say that, burn em.


read this:
https://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47asktom-092692.html

I will say it as plainly as I can say it:

WHEN OTHERS, not followed by raise or raise-application-error(), is a bug in your developed code.

You have changed the transactional behavior of the database for the worse.

You have ignored the error (you HANDLED IT, but you could not have handled it, you have no clue what it even WAS for goodness sake.



I would say that is the most dangerous advice on the planet UNLESS it is followed by:

and then you re-raise the exception OR you invoke raise_application_error().




I was given the opportunity to add 3 things to plsql in 11g. The first choice on my list was simply:


REMOVE WHEN OTHERS FROM THE PLSQL LANGUAGE ENTIRELY, since the vast majority of people use it entirely inappropriately - leading to the single largest cause of bugs in developed plsql code - the error that goes MISSING.


They would not remove WHEN OTHERS (compatibility and all) but they did this for me (and Steve F. - he requested this as well) - but they did this for me:

ops$tkyte%ORA11GR1> create or replace
  2  procedure maintain_t
  3  ( p_str in varchar2 )
  4  as
  5  begin
  6    insert into t
  7    ( x ) values
  8    ( p_str );
  9  exception
 10    when others
 11    then
 12      -- call some log_error() routine
 13      null;
 14  end;
 15  /

Procedure created.

ops$tkyte%ORA11GR1> alter procedure maintain_t compile
  2  PLSQL_Warnings = 'enable:all'
  3  reuse settings
  4  /

SP2-0805: Procedure altered with compilation warnings

ops$tkyte%ORA11GR1> show errors procedure maintain_t
Errors for PROCEDURE MAINTAIN_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8      PLW-06009: procedure "MAINTAIN_T" OTHERS handler does not end in
         RAISE or RAISE_APPLICATION_ERROR




that feature will save me hours of digging through code like yours, looking for all of the when others and commenting them out.

Sweet! I like it.


Think about it - if it is a compile time warning - it is a bad practice...


WHEN OTHERS, not followed by raise or raise-application-error(), is a bug in your developed code.

To reader re: function

Stew Ashton, January 23, 2008 - 3:41 am UTC


"You mean all the recommendations by oracle books are false."

You quoted one recommendation from one book (without naming your source).

That would be like saying "all the recommendations by Tom are misunderstood" just because one reader didn't get one point...

PL/SQL exception handling is wonderful: it does a great job all by itself !
- It provides a meaningful Oracle error number and message that I can google on.
- It provides the actual line number of the code that got the exception.
- It stops execution of the whole PL/SQL procedure.
- It causes a rollback of the transaction.

It does so much more than just "print the error" : it keeps my data clean and tells me what I need to find my bug.

If you put in "when others", you still have to make sure all these things happen, and there are only two ways to do that :

- "When others then raise" is OK, but you lose the line number of the code that got the first exception.

- "When others then raise_application_error" loses the line number, the real error code and the real message.

function

A reader, January 23, 2008 - 7:41 am UTC

book is "oracle pl/sql interactive workbook" by rozenweig.

I am not arguing about using exceptions here. I think what you said about "WHEN OTHERS" is not correct above. It will tell you error code and error messaege without line number where error occurs.

try it yourself. add this to your code (no raise)

WHEN OTHERS THEN
v_err_Code :=SQLCODE;
v_err_msg := SUBSTR(SQLERRM,1,200);
DBMS_OUPTUT.PUT_LINE.('Error code: '||v_err_Code);
DBMS_OUTPUT.PUT_LINE ('Error message: '||v_err_msg);

when the program runs into it it will print this:

Error Code: -6502
Error Message: ORA-6502 : PL/SQL: numeric or value error

I think what Tom implies is that even if the program does not have this exception handler, it will print the error.


2. I found that using "raise_application_error" with built in expcetions is more just to customize the error message reported otherwise you can use the oracle error code and oracle message. It serves the same thing.

using raise_application_errr you have to maintain a list of all the customized error codes and messages in the application which can cause some confusion if developers did not stick to it.
Tom Kyte
January 23, 2008 - 8:01 am UTC

What I am saying is that when others you have coded here is HORRIBLE

I would erase it immediately

I would not permit it

I would argue long and hard against it

I would not permit that code into production

I would print that code out in a big font and circle it in red and put it on the wall next to all of the developers and write really big "do not do this, I will erase this code from source code control, no ifs and or buts about it"


forget about "printing an error" - that is useless.

get into the habit of dealing with errors properly. dbms_output - hah, that doesn't even have to 'print' anything - if I call your routine - what do I "see" - I see NOTHING because I'm another piece of code, I don't have eyes.


..to add 3 things to plsql in 11g

A reader, January 23, 2008 - 8:49 am UTC

".. was given the opportunity to add 3 things to plsql in 11g. The first choice on my list was simply:
REMOVE WHEN OTHERS FROM THE PLSQL LANGUAGE ENTIRELY"

I am now curious to know the last 2 things....
Tom Kyte
January 23, 2008 - 2:03 pm UTC

the other thing was to be able to go back and forth between a REF CURSOR and a DBMS_SQL cursor.

So, plsql can now dynamically process a ref cursor by casting as a dbms_sql cursor and using the procedural API to access it.

Which means you can use APEX to put a better easier to build and maintain interface on top of a bunch of stored procedures that you wrote that return ref cursors to java. Instead of returning them to the really complex java application you never got quite right - you repurpose them with APEX without recoding them.

And which means, if you didn't like what you were getting with APEX, you could take your existing APEX routines which generally used dbms_sql cursors - and invoke them from VB or something else that needed a ref cursor.


and one that hasn't made it yet :)

function

A reader, January 23, 2008 - 6:37 pm UTC

Tom:

OK I see your point. I guess most authors did not realize this yet.

Does not this also mean I do not need any exception handler unelss I have some action for that error or i want to change the error message oracle displays

I can have a pl/sql prorgam without any exceptions and the program will run and if it hits an error it will print it in the output.

correct.
Tom Kyte
January 23, 2008 - 8:34 pm UTC

... I guess most authors did not realize this yet. ...

unless they are good developers :)


...
Does not this also mean I do not need any exception handler unelss I have some
action for that error or i want to change the error message oracle displays
...



you need an exception handler when:

a) the exception is expected. EG: when no_data_found. You might be EXPECTING that, and upon getting in, supply a default (you can handle the error, the error is in fact NOT an error - example:

code;
code; 
code;
begin
   select x into l_var from t where ...;
exception
   when no_data_found then l_var := 'something';
end;
code;
code;


b) you want to log the exception using an autonomous transaction, example:
code;
code;
code;
exception 
  when others then
       log_the_error( .... );
       RAISE;
end;



those are the ONLY times you need an exception block, if you have them other times, they are probably wrong.



... I can have a pl/sql prorgam without any exceptions and the program will run and
if it hits an error it will print it in the output. ...


no, the ONLY THING that prints is the client application, sqlplus will by default display it, what other clients do is whatever the other clients do.




... and one that hasn't made it yet :)

A reader, January 24, 2008 - 4:13 am UTC

Thanks Tom... thank you very much for your efforts to help to improve Oracle and Oracle community.

function

A reader, January 25, 2008 - 8:51 pm UTC

Tom:

Part of the function has

select SUM(qty) into V_USED from TABLE;

I noticed that even I initaliaze V_USED to "0" it becomes NULL after I run the select and no records are found.

1. Should not the above rise an expcetion if no records are there.

2. DO i have to do this

If (V_USED is NULL)
V_USED := 0;
end if;

after the above statement since I can not use null in math formula.
Tom Kyte
January 28, 2008 - 7:08 am UTC

1) no, no_data_found it raised by a select into in a procedural language when there are no records.


select aggregate from table

ALWAYS RETURNS exactly zero rows. only when there is a group by would you expect zero, one or more. without a group by - aggregates return ONE ROW always.

  1  select count(*), sum(1), avg(1), min(1), max(1)
  2* from dual where 1=0
ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> /

  COUNT(*)     SUM(1)     AVG(1)     MIN(1)     MAX(1)
---------- ---------- ---------- ---------- ----------
         0

ops$tkyte%ORA11GR1> c/=0/=1
  2* from dual where 1=1
ops$tkyte%ORA11GR1> l
  1  select count(*), sum(1), avg(1), min(1), max(1)
  2* from dual where 1=1
ops$tkyte%ORA11GR1> /

  COUNT(*)     SUM(1)     AVG(1)     MIN(1)     MAX(1)
---------- ---------- ---------- ---------- ----------
         1          1          1          1          1



2) well, only you can answer that, for the result of sum(qty) for no records is not zero - but null.

you could:


ops$tkyte%ORA11GR1> select sum(1), nvl(sum(1), 0) from dual where 1=0;

    SUM(1) NVL(SUM(1),0)
---------- -------------
                       0



function in where clause for large table

Bhaskar, March 21, 2008 - 4:22 pm UTC

Hi Tom,
This is regarding the usage of functions in where clause.
I'm trying to create a table using CTAS using below SELECT query. This is running for more than 2 hr 40 mins to create the table.
Basically, it has a function to get rule id as
"rule.get_id (d.url_name, TO_DATE (SUBSTR ('M20080101', 2), 'YYYYMMDD') ) = p.rule_id(+)"
The table sizes are display = 27million, rule = 150K records.
This get_id function has lot of rules built in,so i could not write one inline query here.
I have explain plan and tkprof stats also as below.
As this select creates a table of around 27 million (same as display.. ).Any ideas how we go tuning below sql?.

SELECT 'M20080101', rn_id, surf_location_id, computer_id, ns_version,
log_id, d.local_time, receive_time,
DURATION, instance_id,
url_name, site_name, domain_name, is_https, referer_url,
ref_is_https, redirect_url, redirect_is_https, next_url,
next_is_https, webses_id, pcses_id, module_name, window_height,
window_width, focus_pid, origin_id, app_master_id,
app_master_instance_id, url_order, is_complete, content_type_id,
error_number, http_method, nbchar, is_first_page,
d.dsp_hash,
p.tree_id tree_id, p.rule_id rule_id, p.mv_cat_id mv_cat_id, NVL (d.xflag, 0)
FROM display d,
rule PARTITION (p20080101) p
WHERE rule.get_id (d.url_name, TO_DATE (SUBSTR ('M20080101', 2), 'YYYYMMDD') ) = p.rule_id(+)
AND d.local_time >= TO_DATE ('01-JAN-2008', 'DD-MON-YYYY')
AND d.local_time < TO_DATE ('21-JAN-2008', 'DD-MON-YYYY')
AND d.local_time >= TO_DATE ('01-JAN-2008', 'DD-MON-YYYY')
AND d.local_time < TO_DATE ('20-JAN-2008', 'DD-MON-YYYY') + 1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 1.07 9635.06 2849737 380010860 17614 26857754
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.08 9635.08 2849737 380010860 17614 26857754

Also, one interesting note is we do not collect stats on display table.

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

CREATE TABLE STATEMENT Optimizer Mode=CHOOSE 1 2
LOAD AS SELECT :Q96481000 P->S QC (RANDOM)
NESTED LOOPS OUTER 1 4 K 2 :Q96481000 PCWP
PARTITION LIST ALL :Q96481000 PCWP 1 31
TABLE ACCESS FULL DISPLAY 1 4 K 1 :Q96481000 PCWP 32 62
TABLE ACCESS BY LOCAL INDEX ROWID RULE 1 16 3 :Q96481000 PCWP 9 9
INDEX RANGE SCAN RULE_IDT 1 2 :Q96481000 PCWP 9 9

Thanks in Advance,
Bhaskar
Tom Kyte
March 24, 2008 - 10:57 am UTC

well, think about it - if d.URL_name is unique - meaning we'd be FORCED to call the function once per row - and the function is really fast - say like 1/1000th of a second....

ops$tkyte%ORA10GR2> select 27000000/1000/60/60 from dual;

27000000/1000/60/60
-------------------
                7.5


you are looking at 7.5 hours just to run the function.

Now, if URL_NAME has low distinct cardinality.... you can optimize this using a scalar subquery and/or deterministic in 10g and/or RESULT_CACHE in 11g....

consider - table t is a copy of ALL_OBJECTS, function F just counts how often it was called...

ops$tkyte%ORA10GR2> create or replace function f( x in varchar2 ) return number
  2  as
  3  begin
  4          dbms_application_info.set_client_info(userenv('client_info')+1 );
  5          return length(x);
  6  end;
  7  /

Function created.


now, we invoke f(x) for each row:


ops$tkyte%ORA10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select owner, f(owner) from t;

49772 rows selected.


Statistics
----------------------------------------------------------
        680  recursive calls
          0  db block gets
       4192  consistent gets
        684  physical reads
          0  redo size
     939132  bytes sent via SQL*Net to client
      36883  bytes received via SQL*Net from client
       3320  SQL*Net roundtrips to/from client
         12  sorts (memory)
          0  sorts (disk)
      49772  rows processed

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
49772



49,772 rows = 49,772 invocations - let us use a scalar subquery
http://asktom.oracle.com/pls/ask/search?p_string=%22scalar+subquery+caching%22



ops$tkyte%ORA10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select owner, (select f(owner) from dual) f from t;

49772 rows selected.


Statistics
----------------------------------------------------------
          8  recursive calls
          0  db block gets
       4097  consistent gets
          0  physical reads
          0  redo size
     939125  bytes sent via SQL*Net to client
      36883  bytes received via SQL*Net from client
       3320  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49772  rows processed

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
68


we are down to 68 function calls (click above link to read more about that), but we can do better, if we "presort" the data by the input into the function:




ops$tkyte%ORA10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select owner, (select f(owner) from dual) f
  2    from (select owner, rownum r from t order by owner);

49772 rows selected.


Statistics
----------------------------------------------------------
          7  recursive calls
          0  db block gets
        827  consistent gets
          0  physical reads
          0  redo size
     671024  bytes sent via SQL*Net to client
      36883  bytes received via SQL*Net from client
       3320  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      49772  rows processed

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
32


and now it is called only 32 times - surprisingly:

ops$tkyte%ORA10GR2> select count(distinct owner) from t;

COUNT(DISTINCTOWNER)
--------------------
                  32


that is the minimum number of times it has to be called for that query....

In 10g, you might just be able to make the function DETERMINISTIC if it is deterministic (make sure you understand that before using it!!!!)

ops$tkyte%ORA10GR2> create or replace function f( x in varchar2 ) return number
  2  DETERMINISTIC
  3  as
  4  begin
  5          dbms_application_info.set_client_info(userenv('client_info')+1 );
  6          return length(x);
  7  end;
  8  /

Function created.

ops$tkyte%ORA10GR2> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> set autotrace traceonly statistics;
ops$tkyte%ORA10GR2> select owner, f(owner) from t;

49772 rows selected.


Statistics
----------------------------------------------------------
         20  recursive calls
          0  db block gets
       4051  consistent gets
          0  physical reads
          0  redo size
     939132  bytes sent via SQL*Net to client
      36883  bytes received via SQL*Net from client
       3320  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      49772  rows processed

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> select userenv('client_info') from dual;

USERENV('CLIENT_INFO')
----------------------------------------------------------------
5801





so, there I got it down to 5,801 - from 49,772 - pretty good, but not as good as it could be.

function

sam, May 18, 2008 - 11:52 pm UTC

i have a function that computes available credit for a given month and year.


Now it is formatted like

function CAC
(p_cust_id IN varchar2,
p_month IN varchar2,
p_year In varchar2)

return number
is
...variables

begin

cur_month:=to_number(to_Char(to_Date(p_month,'MONTH'),'MM'));


SELECt cr_qty ino into allowed_Qty from credits where cust_id=p_cust_id and
effective_Date = to_date('1-'||cur_month||'-'||p_year,''DD-MM-YYYY') and cr_year = to_number(p_year)


SELECt SUM(ord_qty) into Tot_qty from ORDERED_ITEMS
where cust_id=p_cust_id and order_date=(SELECT max(order_Date) from ordered_items where
order_date < to_date('1-'||cur_month||'-'||p_year,'DD-MM-YYYY')
and to_char(order_date,'YYYY') = p_year;


.......other code

end;


order_items
-----------
order_Date date PK
cust_id varchar2(5) PK
book_no varcahr2(8) PK
ord_qty number(5)

credits
-----------
cust_id varchar2(5) PK
effective_date date PK
allowed_qty number(5)
cr_year number(4)


1. is it better to pass a date type parameter. If yes, do you just pass any date for that month/year (ie DD-MON-YYYY) and then read the month and year from it.

2. Is there more efficient way to write the above.
Tom Kyte
May 19, 2008 - 4:21 pm UTC

1) just pass the date, yes.

2) I've said over and over - I think your logic is entirely botched, please tell me why it isn't (go up a couple to see where I first said that, if you have only a january record you'll add it up over and over ...)

function

A reader, May 19, 2008 - 5:36 pm UTC

Tom:

1. Is it really a big issue to leave it as p_month and p_year as the code is in test and several procedures call it.

2. I missed the "<" sign.
Where do you see an issue in the logic. I not summing up anything anymore. I am only pickin up the last active record.

If there is record keyed in january for the whole year then in May i want to pick it up to determine the credit. If they added a revised one in March then I will pick that one for May since it is the last active one.

SELECt cr_qty ino into allowed_Qty from credits where cust_id=p_cust_id and
effective_Date <= to_date('1-'||cur_month||'-'||p_year,''DD-MM-YYYY') and cr_year =
to_number(p_year)


Tom Kyte
May 20, 2008 - 10:15 am UTC

1) I am pointing out that you've done it "not correctly". Do what you want with this information. I'll just keep saying it everytime I see it.

2) what "<" sign?!? Not sure what you meant by that.

If you decided to make a major change in the logic, it would have been nice to have you say "I change the logic, I corrected the bug, it is now doing this: .... for this reason"

It is funny, I don't think you know what this procedure would/should do... This 'logic'


cur_month:=to_number(to_Char(to_Date(p_month,'MONTH'),'MM'));


SELECt cr_qty ino into allowed_Qty from credits where cust_id=p_cust_id and 
effective_Date = to_date('1-'||cur_month||'-'||p_year,''DD-MM-YYYY') and cr_year = 
to_number(p_year)


SELECt SUM(ord_qty) into Tot_qty from ORDERED_ITEMS
   where cust_id=p_cust_id and order_date=(SELECT max(order_Date) from ordered_items where
                    order_date < to_date('1-'||cur_month||'-'||p_year,'DD-MM-YYYY')
     and to_char(order_date,'YYYY') = p_year;




is so far and away from the original, it is hard to comprehend we are talking about the same procedure!

and - since you've moved pages here, we cannot even see the original logic.

I cannot comment on the correctness of your code, I have no idea what the entire specification is

function

A reader, May 19, 2008 - 5:38 pm UTC

Tom:

If you are refeing to this query, i am summing up th Total Used year to date. do you see an issue here.

SELECt SUM(ord_qty) into Tot_qty from ORDERED_ITEMS
where cust_id=p_cust_id and order_date=(SELECT max(order_Date) from ordered_items where
order_date < to_date('1-'||cur_month||'-'||p_year,'DD-MM-YYYY')
and to_char(order_date,'YYYY') = p_year;


Array Size and Recursive calls with Functions

Steven Cooper, June 17, 2008 - 10:58 am UTC

Tom,

You said way back up at the top of this thread 

<QUOTE>
ops$tkyte@ORA920> set arraysize 1000
ops$tkyte@ORA920> exec dbms_application_info.set_client_info(0);

PL/SQL procedure successfully completed.

ops$tkyte@ORA920> set autotrace traceonly
ops$tkyte@ORA920> select b
  2    from (select your_bonus(empno) b
  3            from emp
  4                   where rownum > 0 ) EMP
  5   where b > 0;

500 rows selected.

ops$tkyte@ORA920> set autotrace off
ops$tkyte@ORA920> select sys_context( 'userenv', 'client_info') sys_ctx from dual;

SYS_CTX
--------------------
1002

You can see it is a function of the number of times we fetched from this result set.  Larger array 
fetches will reduce this number
</QUOTE>

I have a similar situation where i was incurring more recusive calls than i expected and i tracked it down to the array size where my recursive calls seemed to be Round(Rows Returned / ArraySize) * 2 ...can you please explain why the arraysize effects the number of times the function is called? 

I thought that it would just be 1000, not 1002, i dont get why the number of times we fetch from the result set would effect the number of times we called the function. 

I can provide my example if you would like, but i think yours from above is the same behaviour mine is showing.

Thanks in advance
Steven

Tom Kyte
June 17, 2008 - 2:54 pm UTC

...
I thought that it would just be 1000, not 1002, i dont get why the number of
times we fetch from the result set would effect the number of times we called
the function...

it basically boils down to "because we process the way we process, the way we want to"

when you single row or small arraysize fetch, we need to restart, pick up where we left off - the less often you come to us, the less often we need to pick up where we left off.

Decode Limits

Saravanan, June 18, 2008 - 6:43 am UTC

Hi Tom,
1)what's the maximum no.of expressions that a decode function can take?
2)what is the maximun no.of Nested decodes with in a decode
function?

Any documentation links available for this?
would be grateful if you can provide.

Thanks for your time.
Tom Kyte
June 18, 2008 - 2:40 pm UTC

1) see documentation

http://docs.oracle.com/docs/cd/B19306_01/server.102/b14200/functions040.htm#i1017437

of course there is documentation for this... (and it really wasn't hard to find... just looked up the DECODE function...)


And tell you what - if you are worried about hitting any limits in #2, you are not really wanting to use decode anymore, you are wanting to use a lookup table - while decode has physical limits, if you find you are hitting them - it is time to stop using decode and start using the database - you obviously at that point have a need for a lookup table, not a decode function.

RE: decode limits

Duke Ganote, June 18, 2008 - 11:55 am UTC

Saravanan-- It's in the documentation:
"The maximum number of components in the DECODE function, including expr, searches, results, and default, is 255."
Oracle® Database SQL Reference 10g Release 2 (10.2)
http://pages.citebite.com/k5t7l7o1fyqu

And it's easy enough to verify empirically:

DECLARE
sql_statement VARCHAR2(32000) := '';
cnt NUMBER := 0;
BEGIN
sql_statement := 'select count(*) from dba_objects'
||' where DECODE(object_id';
FOR rec IN ( select ','||object_id||',''Y''' as someterm
from dba_objects
where rownum <= 127 ) LOOP
sql_statement := sql_statement||rec.someterm;
END LOOP;
sql_statement := sql_statement||') = ''Y''';
execute immediate sql_statement into cnt;
-- DBMS_OUTPUT.PUT_LINE(sql_statement);
DBMS_OUTPUT.PUT_LINE(cnt);
END;
/
127 -- 127*2 = 254. Add one for object_id to get 255.

PL/SQL procedure successfully completed.

DECLARE
sql_statement VARCHAR2(32000) := '';
cnt NUMBER := 0;
BEGIN
sql_statement := 'select count(*) from dba_objects'
||' where DECODE(object_id';
FOR rec IN ( select ','||object_id||',''Y''' as someterm
from dba_objects
where rownum <= 128 ) LOOP
sql_statement := sql_statement||rec.someterm;
END LOOP;
sql_statement := sql_statement||') = ''Y''';
execute immediate sql_statement into cnt;
-- DBMS_OUTPUT.PUT_LINE(sql_statement);
DBMS_OUTPUT.PUT_LINE(cnt);
END;
/
ORA-00939: too many arguments for function

A reader, June 19, 2008 - 12:43 am UTC


RE: nested DECODE limits

Duke Ganote, June 19, 2008 - 12:29 pm UTC

"Tom's right. Again." regarding using a lookup table instead of sprawling DECODEs.

However, the question on nested DECODEs is interesting, so I experimented with that. There's no 255 limit on doing it that way:

DECLARE
sql_statement VARCHAR2(32000) := '';
sql_nested_decode VARCHAR2(32000) := '';
cnt NUMBER := 0;
BEGIN
sql_statement := 'select count(*) from dba_objects'
||' where ';
FOR rec IN ( select rownum r#
, 'DECODE(object_id,'
||object_id||',''Y''' as someterm
from dba_objects
where rownum <= 900 ) LOOP
sql_nested_decode
:= rec.someterm
||CASE rec.r# WHEN 1 THEN NULL
ELSE ',' END
||sql_nested_decode||')';
END LOOP;
sql_statement := sql_statement||sql_nested_decode||' = ''Y''';
execute immediate sql_statement into cnt;
-- DBMS_OUTPUT.PUT_LINE(sql_nested_decode);
-- DBMS_OUTPUT.PUT_LINE(sql_statement);
DBMS_OUTPUT.PUT_LINE(cnt);
END;
/
900

PL/SQL procedure successfully completed.

NOTE: you can make it barf by upping it to 1000 terms (at least in version 10.2.0.3.0):

*
ERROR at line 1:
ORA-03113: end-of-file on communication channel

Or it'll overflow the buffer if you make it way too long, say, asking for 2000 values:

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 15

So, again, use a lookup table (or at least a virtual lookup table by UNION ALLing lots of SELECT...FROM DUAL).

ODCIStatsSelectivity for functions that return varchar2

Andrew Markiewicz, February 19, 2009 - 11:54 am UTC

Tom,
I am trying to use the extensible optimizer data cartridge to customize the statistics for a plsql package function that returns a varchar2. All the examples I have seen and successful tests I have done have only been with functions that return a number.
Is this a purposeful limitation of the extensibility or do I have an error somewhere?

The following test case has two functions with the exact same signature except for the return data type, one number and one varchar2. The function returning a number calls the ODCIStatsSelectivity method when parsed but the function returning a varchar2 does not.

Is there any way to use the extensible optimizer with a function that returns something other than a number?

Any help appreciated.
Thanks.
Andrew

sql->@ODCISel.tys
sql->CREATE OR REPLACE TYPE ODCISel AS object
2 (
3 dummy NUMBER,
4 static FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER
5 ,static FUNCTION BaseSelectivity(
6 pred sys.ODCIPredInfo
7 , sel OUT NUMBER
8 , args sys.ODCIArgDescList
9 , p_start INTEGER
10 , stop INTEGER
11 , env sys.ODCIEnv
12 ) return NUMBER
13 ,static FUNCTION ODCIStatsSelectivity(
14 pred sys.ODCIPredInfo
15 , sel OUT NUMBER
16 , args sys.ODCIArgDescList
17 , p_start INTEGER
18 , stop INTEGER
19 , p1 NUMBER
20 , p2 DATE
21 , env sys.ODCIEnv
22 ) RETURN NUMBER
23 )
24 NOT FINAL
25 /

Type created.

Elapsed: 00:00:00.54
sql->show err
No errors.
sql->
sql->
sql->@ODCISel.tyb
sql->CREATE OR REPLACE TYPE body ODCISel
2 AS
3
4
5 ---------------------------------------------------------------------------------------------------------------
6 static FUNCTION BaseSelectivity(
7 pred sys.ODCIPredInfo
8 , sel OUT NUMBER
9 , args sys.ODCIArgDescList
10 , p_start INTEGER
11 , stop INTEGER
12 , env sys.ODCIEnv
13 ) return NUMBER
14 IS
15 BEGIN
16 dbms_output.put_line(rpad('-',80,'-'));
17 dbms_output.put_line('Pred : ' || pred.ObjectName);
18 dbms_output.put_line('Meth : ' || pred.MethodName);
19 dbms_output.put_line('Flags : ' || pred.flags);
20
21 dbms_output.put_line('envflags : ' || env.envflags);
22 dbms_output.put_line('callproperty : ' || env.callproperty);
23 dbms_output.put_line('debuglevel : ' || env.debuglevel);
24 dbms_output.put_line('cursornum : ' || env.cursornum);
25
26 for idx in args.FIRST .. args.LAST
27 loop
28 dbms_output.put_line('--------- arg = ' || idx || ' -----------');
29 dbms_output.put_line('argtype : ' || args(idx).ARGTYPE);
30 dbms_output.put_line('tabname : ' || args(idx).TABLENAME);
31 dbms_output.put_line('colname : ' || args(idx).COLNAME);
32 dbms_output.put_line('partlower : ' || args(idx).TABLEPARTITIONLOWER);
33 dbms_output.put_line('partupper : ' || args(idx).TABLEPARTITIONUPPER);
34 dbms_output.put_line('cardinality : ' || args(idx).CARDINALITY);
35 end loop;
36
37 sel :=
38 case
39 when pred.MethodName = 'F6' then 42
40 when pred.MethodName = 'F7' then 75
41 else 1
42 end;
43
44 return ODCIConst.Success;
45 END;
46
47 ---------------------------------------------------------------------------------------------------------------
48 static FUNCTION ODCIStatsSelectivity(
49 pred sys.ODCIPredInfo
50 , sel OUT NUMBER
51 , args sys.ODCIArgDescList
52 , p_start INTEGER
53 , stop INTEGER
54 , p1 NUMBER
55 , p2 DATE
56 , env sys.ODCIEnv
57 ) RETURN NUMBER
58 IS
59 BEGIN
60 dbms_output.put_line('NUMBER');
61 return BaseSelectivity(pred, sel, args, p_start, stop, env);
62 END;
63 ---------------------------------------------------------------------------------------------------------------
64 static FUNCTION ODCIGetInterfaces(ifclist OUT SYS.ODCIObjectList) RETURN NUMBER
65 IS
66 begin
67 ifclist := sys.odciobjectlist();
68 ifclist.extend;
69 ifclist(1):= sys.odciobject('SYS','ODCISTATS2');
70 RETURN sys.odciconst.success;
71 END;
72
73 END;
74 /

Type body created.

Elapsed: 00:00:00.28
sql->show err
No errors.



sql->disassociate statistics from packages amapkg3;

Statistics disassociated.

Elapsed: 00:00:00.01
sql->
sql->create or replace package amapkg3
2 as
3
4 function f6(p1 integer, p2 date) return integer;
5 function f7(p1 integer, p2 date) return varchar2;
6
7 pragma restrict_references (amapkg3, WNDS,WNPS);
8 end;
9 /

Package created.

Elapsed: 00:00:00.01
sql->show err
No errors.
sql->
sql->create or replace package body amapkg3
2 as
3 ------------------------------------------------------
4 function f6(p1 integer, p2 date) return integer
5 is
6 begin
7 return 1;
8 end;
9
10 ------------------------------------------------------
11 function f7(p1 integer, p2 date) return varchar2
12 is
13 begin
14 return 'Y';
15 end;
16
17 end;
18 /

Package body created.

Elapsed: 00:00:00.01
sql->show err
No errors.
sql->ASSOCIATE STATISTICS WITH PACKAGES amapkg3 USING ODCISel;

Statistics associated.

Elapsed: 00:00:00.13
sql->
sql->
sql->-- Run tests
sql->DROP TABLE amat1;

Table dropped.

Elapsed: 00:00:00.12
sql->create table amat1(x int);

Table created.

Elapsed: 00:00:00.04
sql->exec dbms_stats.set_table_stats(user, 'amat1',numrows=>1000000)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
sql->
sql->set echo on
sql->col PLAN_TABLE_OUTPUT format a100
sql->
sql->explain plan for
2 select /*+ &&tag */ * from amat1 where amapkg3.f6(x, sysdate) = 1
3 /
Enter value for tag: ama13
NUMBER
--------------------------------------------------------------------------------
Pred : AMAPKG3
Meth : F6
Flags : 45
envflags : 0
callproperty : 0
debuglevel : 0
cursornum : 13
--------- arg = 1 -----------
argtype : 3
tabname :
colname :
partlower :
partupper :
cardinality :
--------- arg = 2 -----------
argtype : 3
tabname :
colname :
partlower :
partupper :
cardinality :
--------- arg = 3 -----------
argtype : 2
tabname : AMAT1
colname : "X"
partlower :
partupper :
cardinality :
--------- arg = 4 -----------
argtype : 1
tabname :
colname :
partlower :
partupper :
cardinality :

Explained.

Elapsed: 00:00:00.12
sql->select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2618211286

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 420K| 5332K| 1605 (94)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| AMAT1 | 420K| 5332K| 1605 (94)| 00:00:08 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("AMAPKG3"."F6"("X",SYSDATE@!)=1)

13 rows selected.

Elapsed: 00:00:00.07
sql->
sql->
sql->explain plan for
2 select /*+ &&tag */ * from amat1 where amapkg3.f7(x, sysdate) = 'Y'
3 /

Explained.

Elapsed: 00:00:00.11
sql->select plan_table_output from table(dbms_xplan.display('plan_table',null,'serial'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2618211286

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10000 | 126K| 1605 (94)| 00:00:08 |
|* 1 | TABLE ACCESS FULL| AMAT1 | 10000 | 126K| 1605 (94)| 00:00:08 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("AMAPKG3"."F7"("X",SYSDATE@!)='Y')

13 rows selected.

Elapsed: 00:00:00.04
sql->
sql->

ODCI return value

Andrew Markiewicz, March 10, 2009 - 10:11 am UTC

My last post on this thread may have been a bit off the original topic. But in the event someone else has this same problem with ODCIStatsSelectivity I will post the solution here.
I created a SR with Oracle and they figured it out after consulting the development team.

The problem was to get the ODCIStatsSelectivity method to be invoked by the CBO when parsing a SQL with a function that returns a value that is varchar2. All the documentation and examples I found only showed functions that return a number.

Oracle support indicated that in addition to being used as bounds in range scans, the p_start and stop parameters implicitly indicate what the return value of the function is.

For instance, the following method can only be used for functions that return a numeric value.

13 ,static FUNCTION ODCIStatsSelectivity(
14 pred sys.ODCIPredInfo
15 , sel OUT NUMBER
16 , args sys.ODCIArgDescList
17 , p_start INTEGER
18 , stop INTEGER
19 , p1 NUMBER
20 , p2 DATE
21 , env sys.ODCIEnv
22 ) RETURN NUMBER


Changing the parameter datatypes of p_start and stop to varchar2 allows the ODCIStatsSelectivity to be invoked for functions that return a varchar2.


13 ,static FUNCTION ODCIStatsSelectivity(
14 pred sys.ODCIPredInfo
15 , sel OUT NUMBER
16 , args sys.ODCIArgDescList
17 , p_start VARCHAR2
18 , stop VARCHAR2
19 , p1 NUMBER
20 , p2 DATE
21 , env sys.ODCIEnv
22 ) RETURN NUMBER


Andrew

Deterministic or Non-Deterministic

Maverick, May 24, 2010 - 2:44 pm UTC

I was reading your thread from above [very old ones] regarding Deterministic and non-deterministic functions.

user asked about this function:
Create or Replace Function Det_or_Not(p_emp_no in emp.emp_no%type) return emp.emp_name%type is
v_emp_name emp.emp_name%type;
begin
select emp_name into v_emp_name
from emp
where emp_no = p_emp_no;

return v_emp_name;
end;


[your statement for this question]
"
It is non-deterministic.

A simple "update emp set emp_name = 'foo' " will change the return value for a given p_emp_no.
Given the input 12345 -- this function may return a different value at two different points in
time.
"

and in next question [scroll down further] you wrote this function

create or replace function your_bonus( p_empno in number ) return number
2 deterministic
3 as
4 l_comm number;
5 begin
6 dbms_application_info.set_client_info( sys_context( 'userenv', 'client_info')+1 );
7
8 select comm
9 into l_comm
10 from emp
11 where empno = p_empno;
12
13 return l_comm;
14 exception
15 when no_data_found then
16 raise program_error;
17 end;
18 /
Function created.


So, my question is how can this be deterministic? if i update comm value in emp table from 20% to 30%.
the return value changes, correct?
Everytie I read this thread iget stuck at this point and nto able to understand how can this be determnistic.
Can you explain it again [based on what I mentioned above]?

Thanks a bunch.
Tom Kyte
May 24, 2010 - 3:10 pm UTC

good catch - I don't know why I put deterministic there at all in that example - it doesn't have anything to do with the question really.

It is a bad example because of that - the function based index is not a good idea there.

I made a mistake, I should not have marked that function as deterministic - unfortunately, I cannot remember what I was thinking about way back then.

Thanks a lot :-)

A reader, May 24, 2010 - 3:29 pm UTC

I am so releaved..I have been breaking my head ever since..but I am glad I asked atleast now. Thanks.

so what is a dterministic function then??

Maverick, May 25, 2010 - 8:50 am UTC

Tom, since we cannot use any function with a database column as deterministic function[ as this column value gets updated]..what do you consider [real life example] as a deterministic function?
Tom Kyte
May 25, 2010 - 12:39 pm UTC

substr( x, 1, 10 )

that is deterministic.


to_date( x, 'yyyy-mm' )

that is deterministic


substr( x, 1, instr(x,',') )

is not deterministic

to_date( x, 'yyyy' )

is not deterministic



Deterministic means given a specific input - you will return the same output in that database forever and ever.

user defined functions

A reader, May 25, 2010 - 12:46 pm UTC

Tom, those are all Oracle inbuilt functions..what about user defined functions? any real life examples of those?
Tom Kyte
May 25, 2010 - 1:03 pm UTC

pretend I wrote substr.

There is one...


here is one, from Expert Oracle Database Architecture:

ops$tkyte@ORA11GR2> create or replace
  2  function my_soundex( p_string in varchar2 ) return varchar2
  3  deterministic
  4  as
  5      l_return_string varchar2(6) default substr( p_string, 1, 1 );
  6      l_char      varchar2(1);
  7      l_last_digit    number default 0;
  8
  9      type vcArray is table of varchar2(10) index by binary_integer;
 10      l_code_table    vcArray;
 11
 12  begin
 13      stats.cnt := stats.cnt+1;
 14
 15      l_code_table(1) := 'BPFV';
 16      l_code_table(2) := 'CSKGJQXZ';
 17      l_code_table(3) := 'DT';
 18      l_code_table(4) := 'L';
 19      l_code_table(5) := 'MN';
 20      l_code_table(6) := 'R';
 21
 22
 23      for i in 1 .. length(p_string)
 24      loop
 25          exit when (length(l_return_string) = 6);
 26          l_char := upper(substr( p_string, i, 1 ) );
 27
 28          for j in 1 .. l_code_table.count
 29          loop
 30          if (instr(l_code_table(j), l_char ) > 0 AND j <> l_last_digit)
 31          then
 32              l_return_string := l_return_string || to_char(j,'fm9');
 33              l_last_digit := j;
 34          end if;
 35          end loop;
 36      end loop;
 37
 38      return rpad( l_return_string, 6, '0' );
 39  end;
 40  /


Functions Handling in SQL Queries

Anand, August 25, 2010 - 7:28 am UTC

Hi Tom, using the below piece of code I'm not getting the required value 2 at the row num 56,74 and 92. How to get the required value 2 at the mentione positions. Please tell me what is wrong in my code?

declare
v_int number;
begin
for i in 1..100
loop
select MOD(
CASE WHEN MOD(i + FLOOR(i / 19), 19) = 0 THEN 1
ELSE MOD(i + FLOOR(i / 19), 19)END, 19)
into v_int
from dual;
dbms_output.put_line('Iteration Value :'||i||'-----------'||'Mod Value :'||v_int);
end loop;
exception
when others then
dbms_output.put_line(sqlerrm);
end;

Tom Kyte
August 26, 2010 - 12:08 pm UTC

exception
when others then
dbms_output.put_line(sqlerrm);
end;

http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22

I hate your code. why did you do that? please do not say "it is just a demo", it is a horrible practice that must be outlawed right now - for 'demos' for real code - for everything.


I don't know what to say to you.

when I is 56 then

i/19 = 2.94736842
floor(i/19) = 2
i+floor(i/19) = 58
mod(i+floor(i/19),19) = 1

1 is NOT equal to zero. therefore we do the else
the else just does the same evaluation - hence the answer is 1


It is doing exactly what you programmed. Since I have no clue what you are trying to program (because - well - you sort of didn't tell us!!!!!!!) I cannot "fix" this.


specification - write a specification - do not post code that doesn't work without a detailed explanation of what it was supposed to do!!!!

Can't solve your riddle

Kevin Kirkpatrick, September 04, 2012 - 10:11 pm UTC

Hi Tom,

You'd written:

substr( x, 1, 10 )

that is deterministic.


to_date( x, 'yyyy-mm' )

that is deterministic


substr( x, 1, instr(x,',') )

is not deterministic

to_date( x, 'yyyy' )

is not deterministic

Not sure what I'm missing, but could you supply a value of "x" which can return different results at different times for the functions you've labeled "not deterministic"?

Thanks!
Tom Kyte
September 10, 2012 - 7:26 pm UTC

that should have been instrb() - sorry. When using instr (characters) it would be deterministic.


ops$tkyte%ORA11GR2> select to_date( '2012', 'yyyy' ) from dual;

TO_DATE('
---------
01-SEP-12


Next month, that will return oct-1st, the default month is the current month for YYYYY without a month specified

The right answer

Bilal, March 05, 2015 - 6:47 am UTC

use
pragma AUTONOMOUS_TRANSACTION;
it will allow you to insert the record.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.