Home » SQL & PL/SQL » SQL & PL/SQL » Create Inline Function (Oracle 11g)
Create Inline Function [message #313092] Thu, 10 April 2008 12:07 Go to next message
oe123
Messages: 5
Registered: April 2008
Junior Member
I have a function defined as

CREATE OR REPLACE FUNCTION YEAR(val IN TIMESTAMP) RETURN INTEGER DETERMINISTIC IS
BEGIN
RETURN EXTRACT(year FROM val);
END;

Only reason is so I can have backwards compatibility in my application which currently supports another DBMS, and that DBMS happens to have functions like that(year/month/day (timestamp))

Anyway, this is all great but the performance of this function is just horrible.

Running a
SELECT YEAR(someTimestampColumn) vs
SELECT EXTRACT(year FROM someTimestampColumn)
is an order of magnitude slower.

I'd expect Oracle to be able to inline this function, and I've read that setting the plsql_optimize_level session option to "3" will help with inlining certain functions but that hasn't helped any.

Am I just out of luck? and need to case out the sql depending on the dbms. I'd like to avoid that as much as possible.

Thanks
Oguzhan Eris
Re: Create Inline Function [message #313095 is a reply to message #313092] Thu, 10 April 2008 12:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I've read that setting the plsql_optimize_level session option to "3" will help with inlining certain functions

From doc:
Quote:
The INLINE pragma specifies that a subprogram call is, or is not, to be inlined. Inlining replaces a subprogram call (to a subprogram in the same program unit) with a copy of the called subprogram.


By the way, just replacing function by another is not the way to port an application from a DBMS to another. There are much more important things and that will far more modify your application than recoding query with the correct function calls.

Regards
Michel
Re: Create Inline Function [message #313100 is a reply to message #313095] Thu, 10 April 2008 12:36 Go to previous messageGo to next message
oe123
Messages: 5
Registered: April 2008
Junior Member
The PRAGMA INLINE directive cannot be used in functions directly it's to be used by pl/sql blocks. I don't know howto use that directive to be able to run that simple select statement from jdbc.

I am well aware of what needs to be done to port an application to use another dbms, so no need to educate me.
Re: Create Inline Function [message #313101 is a reply to message #313100] Thu, 10 April 2008 12:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I don't know howto use that directive to be able to run that simple select statement from jdbc.

You can't.

Regards
Michel
Re: Create Inline Function [message #313103 is a reply to message #313092] Thu, 10 April 2008 13:55 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
Why cant you create a view in oracle that uses the EXTRACT function, but you dont need to code it in your application. Just select it.



Re: Create Inline Function [message #313104 is a reply to message #313103] Thu, 10 April 2008 13:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The issue is that the code ALREADY contains this function call otherwise there would be no problem.

Regards
Michel
Re: Create Inline Function [message #313107 is a reply to message #313092] Thu, 10 April 2008 14:16 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
No, as I read it, the issue is that the other DBMS doesnt support the EXTRACT function, but already has a YEAR function. No-where does it say that the code cannot be changed. All I read was that he wanted to ensure backwards compatibility with another dbms.

anyway, if you cant change the code, look up dbms_advanced_rewrite package.

Basically you can take

select year(value) from table;


and rewrite in in oracle as:-

select extract(year from value) from table;


But beware, making the database fit the app is not a good idea. Your app might not have privs on other databases. dbms_advanced_rewrite can cause havoc if misused.

More Info
http://www.dba-oracle.com/t_dbms_advanced_rewrite.htm

[Updated on: Thu, 10 April 2008 14:18]

Report message to a moderator

Re: Create Inline Function [message #313111 is a reply to message #313107] Thu, 10 April 2008 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the code can be changed there is no question just replace one function by the other one.
But as I said this is just a small detail compare to all that have to be changed.

Regards
Michel
Re: Create Inline Function [message #313113 is a reply to message #313092] Thu, 10 April 2008 14:40 Go to previous messageGo to next message
oe123
Messages: 5
Registered: April 2008
Junior Member
The application in question is a reporting application which can create some pretty complicated queries on the fly depending on user input.

The code is as sql compliant as it can be, but I'm sure as you all know, date functions vary greatly between different dbms's, and so the queries the application auto-creates rely on such functions provided by the other dbms (Sybase ASA)

As a developer, I would hate to case out the db level differences in application code. I know it's impossible to accomplish that abstraction fully, but I want to do as much as I can.

Thanks for the dbms_advanced_rewrite pointer, but there are millions of permutations in the sql we can create, so I doubt it'll fit into the role.

I would normally agree with you that making the db fit the app is a bad idea, but in this situation, making the app fit the db is actually worse.

Having such helper functions would've been great, but can't do it if it's at the cost of such performance degradation.

Thanks for the tips, but as far as the original question goes, answer seems to be no, so I'll just have to deal with it.

Thanks
Re: Create Inline Function [message #313114 is a reply to message #313113] Thu, 10 April 2008 14:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
as far as the original question goes, answer seems to be no, so I'll just have to deal with it.

This is correct. Either you use built-in function and hav good performances, either you use your own function and suffer performances degradation.

Regards
Michel
Re: Create Inline Function [message #313139 is a reply to message #313113] Thu, 10 April 2008 17:38 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Have you tried native compilation? Tom Kyte demonstrates the performance difference between a function with native compilation versus the same function with normal compilation here:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4243161825986
Previous Topic: IF-then instruction
Next Topic: UTL_FILE Package Used::ORA-29280: invalid directory path Error Occurred
Goto Forum:
  


Current Time: Thu Dec 08 02:18:13 CST 2016

Total time taken to generate the page: 0.05649 seconds