limitations of calling functions from sql
January 4, 2002 - 2pm Central time zone
Reviewer: Kulguru
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.
Followup January 4, 2002 - 8pm Central time zone:
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
January 4, 2002 - 10pm Central time zone
Reviewer: Kulguru
A function called in a sql cannot return a boolean. This was my doubt. Why is it so.
Followup January 5, 2002 - 10am Central time zone:
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
January 5, 2002 - 11am Central time zone
Reviewer: Kulguru
Tom
Can you give the link to the documents where I can find a list of sql types and pl/sql types.
Thanks
What could be the effect when it is in RBO mode
March 24, 2002 - 5am Central time zone
Reviewer: lakshmi narasimhan from Dubai, UAE
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?
Followup March 24, 2002 - 3pm Central time zone:
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
March 24, 2002 - 11pm Central time zone
Reviewer: A reader
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
Followup March 25, 2002 - 7am Central time zone:
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
March 25, 2002 - 2am Central time zone
Reviewer: Lakshmi narasimhan R from Dubai, UAE
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.
Followup March 25, 2002 - 8am Central time zone:
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.
March 25, 2002 - 8am Central time zone
Reviewer: A reader
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 ?
Followup March 25, 2002 - 12pm Central time zone:
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
March 26, 2002 - 12am Central time zone
Reviewer: Momen from Riyadh
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. http://asktom.oracle.com/~tkyte/
Thanks
Followup March 26, 2002 - 7am Central time zone:
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 ?
March 26, 2002 - 1am Central time zone
Reviewer: Charanjiv from India
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?.
Followup March 26, 2002 - 7am Central time zone:
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
July 7, 2003 - 6am Central time zone
Reviewer: john
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?
Followup July 7, 2003 - 7am Central time zone:
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.
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
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.
that function will let us easily count how many times it's called due to the
dbms_application_info stuff...
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
that is to be expected -- 1,000 rows -- 1,000 calls
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
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.
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
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
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>
you cannot beat 0 times
CHAR Datatype question
July 7, 2003 - 12pm Central time zone
Reviewer: A reader from MN, USA
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
Followup July 7, 2003 - 2pm Central time zone:
seems to be a doc bug, I've logged on on it as of reading this.
calling the same function twice
July 9, 2003 - 6am Central time zone
Reviewer: John
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?
Followup July 9, 2003 - 11am Central time zone:
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
August 6, 2003 - 8am Central time zone
Reviewer: Chakravarthi from Chennai,India
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
Followup August 6, 2003 - 8am Central time zone:
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
September 25, 2003 - 6pm Central time zone
Reviewer: Frank from US
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!
Followup September 25, 2003 - 11pm Central time zone:
other then using SUBSTR, no
function based index in rule
September 29, 2003 - 3pm Central time zone
Reviewer: A reader from Buffalo, NY
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.
Followup September 29, 2003 - 4pm Central time zone:
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
October 9, 2003 - 1am Central time zone
Reviewer: Anil Pant from Hyderabad, India
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
Followup October 9, 2003 - 4pm Central time zone:
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
October 9, 2003 - 11pm Central time zone
Reviewer: Naina from New York
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."
Followup October 10, 2003 - 8am Central time zone:
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>
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"
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
October 13, 2003 - 7am Central time zone
Reviewer: Naina from New York
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
Followup October 13, 2003 - 7am Central time zone:
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
February 2, 2004 - 9pm Central time zone
Reviewer: Matt from Australia
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
Followup February 3, 2004 - 7am Central time zone:
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....
February 2, 2004 - 11pm Central time zone
Reviewer: Matt from Australia
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 )...
February 3, 2004 - 10pm Central time zone
Reviewer: Matt from Australia
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
February 4, 2004 - 9am Central time zone
Reviewer: Olga from Vienna
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
Followup February 4, 2004 - 4pm Central time zone:
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;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
1000
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)
2 1 VIEW (Cost=3 Card=10 Bytes=260)
3 2 COUNT
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;
USERENV('CLIENT_INFO')
----------------------------------------------------------------
10
Wow!
February 4, 2004 - 4pm Central time zone
Reviewer: Olga from Vienna
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?
April 6, 2004 - 1pm Central time zone
Reviewer: Gabriel from Montreal, Canada
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,
Followup April 7, 2004 - 8am Central time zone:
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!
June 23, 2004 - 5pm Central time zone
Reviewer: A reader
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
Followup June 23, 2004 - 9pm Central time zone:
ok, append the TKPROF report as well......
tkprofs
June 23, 2004 - 11pm Central time zone
Reviewer: A reader
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
Followup June 24, 2004 - 9am Central time zone:
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
June 23, 2004 - 11pm Central time zone
Reviewer: A reader
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
June 25, 2004 - 10am Central time zone
Reviewer: A reader
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
Followup June 25, 2004 - 2pm Central time zone:
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.
June 25, 2004 - 3pm Central time zone
Reviewer: A reader
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
Followup June 25, 2004 - 4pm Central time zone:
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!
June 25, 2004 - 4pm Central time zone
Reviewer: A reader
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
June 27, 2004 - 10am Central time zone
Reviewer: A reader
<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
Followup June 27, 2004 - 11am Central time zone:
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.
June 27, 2004 - 6pm Central time zone
Reviewer: A reader
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
Followup June 28, 2004 - 8am Central time zone:
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
June 28, 2004 - 7am Central time zone
Reviewer: Matthias Rogel from Kaiserslautern, Germany
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?
June 28, 2004 - 7pm Central time zone
Reviewer: A reader
"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
Followup June 28, 2004 - 8pm Central time zone:
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
June 28, 2004 - 8pm Central time zone
Reviewer: Gary from Sydney, Aus
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.
Followup June 28, 2004 - 8pm Central time zone:
when you create an operator... like "contains" is.
but yes, good pointer, thanks -- appreciate the followup.
Thanks
June 28, 2004 - 9pm Central time zone
Reviewer: A reader
"it is guessing "about 1% of the rows"
Thanks!
To Gary
June 28, 2004 - 9pm Central time zone
Reviewer: A reader
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?
July 23, 2004 - 11am Central time zone
Reviewer: A reader from Boston, MA USA
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)
Followup July 23, 2004 - 3pm Central time zone:
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
August 4, 2004 - 5am Central time zone
Reviewer: A reader from Aus
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
Followup August 4, 2004 - 10am Central time zone:
create table t
( ....,
bool varchar2(1) not null check ( bool in ('Y','N') ),
....
)
/
would be one approach.
PLSQL functions in queries
August 5, 2004 - 3pm Central time zone
Reviewer: A reader
"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?
Followup August 5, 2004 - 8pm Central time zone:
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
September 22, 2004 - 9am Central time zone
Reviewer: Oliver Schoett from Germany
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
November 7, 2004 - 7pm Central time zone
Reviewer: Robert from CT
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
Followup November 8, 2004 - 9am Central time zone:
named notation is not supported in plsql called from SQL that is correct.
1% cardinality
November 19, 2004 - 2pm Central time zone
Reviewer: A reader
"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
Followup November 19, 2004 - 7pm Central time zone:
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.
November 19, 2004 - 10pm Central time zone
Reviewer: A reader
"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?
Followup November 20, 2004 - 8am Central time zone:
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"
November 20, 2004 - 9am Central time zone
Reviewer: A reader
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
Followup November 20, 2004 - 10am Central time zone:
or, you can tell us what the default for that function should be....
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)
ops$tkyte@ORA9IR2> ASSOCIATE STATISTICS WITH FUNCTIONS f DEFAULT SELECTIVITY 50;
Statistics associated.
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>
November 20, 2004 - 10am Central time zone
Reviewer: A reader
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
Followup November 20, 2004 - 11am Central time zone:
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:
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96595/toc.htm
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.
November 20, 2004 - 2pm Central time zone
Reviewer: A reader
"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
Followup November 20, 2004 - 3pm Central time zone:
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
November 20, 2004 - 9pm Central time zone
Reviewer: A reader
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
Followup November 21, 2004 - 8am Central time zone:
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
November 21, 2004 - 9am Central time zone
Reviewer: A reader
Can you please give a example of using ODCIStatsSelectivity given my example? None of the docs have
a example
Followup November 21, 2004 - 9am Central time zone:
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 )
November 21, 2004 - 4pm Central time zone
Reviewer: A reader
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
Followup November 21, 2004 - 7pm Central time zone:
you have to allocate the array -- ifclist is NULL.
ifclist := odciobjectList( odciobject('SYS','ODCISTATS2') );
for example, or
ifclist := odciobjetList();
ifclist.extend .......
November 21, 2004 - 5pm Central time zone
Reviewer: A reader
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
sel := 5; -- 5% selectivity
return ODCIConst.Success;
END;
static FUNCTION ODCIGetInterfaces(ifclist OUT ODCIObjectList) RETURN NUMBER
IS
begin
ifclist := odciobjectlist();
ifclist.extend;
ifclist(1):= odciobject('SYS','ODCISTATS2');
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 Card=50000 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 Card=5 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
Followup November 21, 2004 - 7pm Central time zone:
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
December 16, 2004 - 3am Central time zone
Reviewer: a reader from Russia
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.
“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”
Tom, does this mean that our optimized this way queries (I mean adding rownum) will be not
optimized in 10g and we’ll have to remove rownum, in other words - return these queries to
their source state?
Thank you.
Followup December 16, 2004 - 8am Central time zone:
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
January 10, 2005 - 11pm Central time zone
Reviewer: Shreelakshmi from Bangalore (India)
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?
Followup January 11, 2005 - 8am Central time zone:
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
January 13, 2005 - 8am Central time zone
Reviewer: Shreelakshmi from Bangalore (India)
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
Followup January 13, 2005 - 9am Central time zone:
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)
http://download-west.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://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96612/u_file.htm#998101
4) that would, well, be documented as well.
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96624/08_subs.htm#591
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
January 24, 2005 - 7am Central time zone
Reviewer: Shreelakshmi from Bangalore ( India)
Dear Tom,
Please Give tell me whats the difference between oracle 7i,8i,9i,10g .
Thanks In Advance
regards
Followup January 24, 2005 - 8am Central time zone:
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
January 25, 2005 - 2am Central time zone
Reviewer: Shreelakshmi from Bangalore(India)
What Are The Record Groups And What Is The Use Of It
Thanks in Advance
Followup January 25, 2005 - 9am Central time zone:
they are like 2 dimensional arrays -- rows and columns. a result set cached in a client.
Named parameter notation
March 9, 2005 - 10pm Central time zone
Reviewer: A reader
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
Followup March 10, 2005 - 7am Central time zone:
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!
March 10, 2005 - 9am Central time zone
Reviewer: A reader
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
March 11, 2005 - 5pm Central time zone
Reviewer: Andrew Markiewicz from Madison, WI USA
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)
Followup March 12, 2005 - 9am Central time zone:
thanks!
June 29, 2005 - 11am Central time zone
Reviewer: A reader
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.
Followup June 29, 2005 - 11am Central time zone:
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
February 9, 2006 - 12pm Central time zone
Reviewer: Krous from Chicago, IL USA
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?
Followup February 10, 2006 - 11am Central time zone:
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....
March 9, 2006 - 11am Central time zone
Reviewer: A reader
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;
Followup March 9, 2006 - 3pm Central time zone:
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
March 10, 2006 - 5am Central time zone
Reviewer: Narendra from India
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
Followup March 10, 2006 - 12pm Central time zone:
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.
http://dizwell.com/main/index.php?option=com_jd-wiki&Itemid=112&id=deterministicfunctions
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
March 11, 2006 - 3am Central time zone
Reviewer: Narendra from India
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.
Followup March 11, 2006 - 3pm Central time zone:
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
April 11, 2006 - 11pm Central time zone
Reviewer: Sinan Topuz from NYC USA
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
Followup April 12, 2006 - 8am Central time zone:
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?
April 12, 2006 - 12pm Central time zone
Reviewer: Yves from USA
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;
Followup April 12, 2006 - 7pm Central time zone:
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
April 12, 2006 - 8pm Central time zone
Reviewer: Yves from MD USA
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.
Followup April 12, 2006 - 9pm Central time zone:
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
April 13, 2006 - 1am Central time zone
Reviewer: Yves from USA
I did not know that the NVL function itself could take an SQL.
Thanks.
Followup April 13, 2006 - 7am Central time zone:
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
June 12, 2006 - 2pm Central time zone
Reviewer: A reader from USA
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.
Followup June 13, 2006 - 10am Central time zone:
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
June 13, 2006 - 6am Central time zone
Reviewer: A reader from USA
Tom, Any feedback on the above?
Thanks.
Followup June 13, 2006 - 12pm Central time zone:
yes, *give me some time*.
I have a day job see :)
And sometimes I take a night off...
Trying to find a better way..
June 23, 2006 - 1pm Central time zone
Reviewer: A reader
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?
Followup June 23, 2006 - 1pm Central time zone:
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
June 23, 2006 - 11pm Central time zone
Reviewer: A reader
Clarification
June 27, 2006 - 10am Central time zone
Reviewer: Craig
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?
Followup June 27, 2006 - 11am Central time zone:
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.
June 27, 2006 - 11am Central time zone
Reviewer: Carig from Austin
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.
Followup June 27, 2006 - 2pm Central time zone:
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
November 6, 2006 - 7am Central time zone
Reviewer: abz
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?
Followup November 6, 2006 - 10am Central time zone:
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?
November 6, 2006 - 10am Central time zone
Reviewer: abz
Ok, you are right, but how can there be performance problems due to this?
Followup November 6, 2006 - 10am Central time zone:
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
November 6, 2006 - 11am Central time zone
Reviewer: abz
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.
Followup November 6, 2006 - 11am Central time zone:
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
November 9, 2006 - 10am Central time zone
Reviewer: abz
I have created public synonym for a user defined function, but it still requires to GRANT EXECUTE
on the function to every user.
Followup November 9, 2006 - 2pm Central time zone:
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.
December 19, 2006 - 9am Central time zone
Reviewer: A reader
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?
Followup December 19, 2006 - 10am Central time zone:
"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
April 15, 2007 - 2pm Central time zone
Reviewer: Dawar from Los Angeles, CA USA
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
December 3, 2007 - 11am Central time zone
Reviewer: David Rodrgiuez
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
Followup December 3, 2007 - 11am Central time zone:
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
December 3, 2007 - 12pm Central time zone
Reviewer: David Rodrgiuez
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
December 3, 2007 - 1pm Central time zone
Reviewer: David Rodrgiuez
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
January 21, 2008 - 10pm Central time zone
Reviewer: A reader
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.
Followup January 21, 2008 - 10pm Central time zone:
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
January 21, 2008 - 10pm Central time zone
Reviewer: A reader
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;
Followup January 22, 2008 - 6am Central time zone:
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..."
January 22, 2008 - 3am Central time zone
Reviewer: Duke Ganote from Sleepless in Anderson Township, Ohio USA
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.
Followup January 22, 2008 - 7am Central time zone:
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"
January 22, 2008 - 4pm Central time zone
Reviewer: Duke Ganote from soggy cold Amelia, Ohio USA
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
January 22, 2008 - 4pm Central time zone
Reviewer: A reader
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.
Followup January 22, 2008 - 6pm Central time zone:
... --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
January 22, 2008 - 7pm Central time zone
Reviewer: A reader
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
Followup January 22, 2008 - 7pm Central time zone:
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
January 22, 2008 - 7pm Central time zone
Reviewer: A reader
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);
Followup January 22, 2008 - 7pm Central time zone:
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
January 22, 2008 - 9pm Central time zone
Reviewer: A reader
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.
Followup January 23, 2008 - 7am Central time zone:
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:
http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.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
January 23, 2008 - 3am Central time zone
Reviewer: Stew Ashton from Paris, France
"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
January 23, 2008 - 7am Central time zone
Reviewer: A reader
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.
Followup January 23, 2008 - 8am Central time zone:
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
January 23, 2008 - 8am Central time zone
Reviewer: A reader from London
".. 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....
Followup January 23, 2008 - 2pm Central time zone:
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
January 23, 2008 - 6pm Central time zone
Reviewer: A reader
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.
Followup January 23, 2008 - 8pm Central time zone:
... 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 :)
January 24, 2008 - 4am Central time zone
Reviewer: A reader
Thanks Tom... thank you very much for your efforts to help to improve Oracle and Oracle community.
function
January 25, 2008 - 8pm Central time zone
Reviewer: A reader
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.
Followup January 28, 2008 - 7am Central time zone:
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
March 21, 2008 - 4pm Central time zone
Reviewer: Bhaskar from Sunnyvale,ca
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
Followup March 24, 2008 - 10am Central time zone:
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
May 18, 2008 - 11pm Central time zone
Reviewer: sam
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.
Followup May 19, 2008 - 4pm Central time zone:
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
May 19, 2008 - 5pm Central time zone
Reviewer: A reader
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)
Followup May 20, 2008 - 10am Central time zone:
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
May 19, 2008 - 5pm Central time zone
Reviewer: A reader
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
June 17, 2008 - 10am Central time zone
Reviewer: Steven Cooper from England
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
Followup June 17, 2008 - 2pm Central time zone:
...
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
June 18, 2008 - 6am Central time zone
Reviewer: Saravanan
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.
Followup June 18, 2008 - 2pm Central time zone:
1) see documentation
http://download.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
June 18, 2008 - 11am Central time zone
Reviewer: Duke Ganote from Amelia, Ohio USA
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
June 19, 2008 - 12am Central time zone
Reviewer: A reader
RE: nested DECODE limits
June 19, 2008 - 12pm Central time zone
Reviewer: Duke Ganote from Amelia, Ohio USA
"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
February 19, 2009 - 11am Central time zone
Reviewer: Andrew Markiewicz from Madison, WI USA
<code>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 |
-----------
ODCI return value
March 10, 2009 - 10am Central time zone
Reviewer: Andrew Markiewicz from Madison, WI
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
|