Home » RDBMS Server » Performance Tuning » Urgent reply needed
Urgent reply needed [message #132694] Mon, 15 August 2005 14:03 Go to next message
RB
Messages: 11
Registered: April 2002
Junior Member
Is it possibe to run explain plan for a query that uses user-defined function ?

ex:
select getdate('xxx'),
col1,
col2
from xyz
where <conditions>;

when i run explain plan for this then i get the error
ORA-00904: invalid column name

what change shd i to get rid of this error !
Re: Urgent reply needed [message #132696 is a reply to message #132694] Mon, 15 August 2005 14:27 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Are you sure it is an issue with explain plan and not with the syntax of your query itself?
Re: Urgent reply needed [message #132698 is a reply to message #132694] Mon, 15 August 2005 14:30 Go to previous messageGo to next message
RB
Messages: 11
Registered: April 2002
Junior Member
yes because this query is a part of pl/sql code and the code is already compiled.
Re: Urgent reply needed [message #132699 is a reply to message #132694] Mon, 15 August 2005 14:40 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Hmm, what am I missing?

MYDBA@ORCL >
MYDBA@ORCL > start explain_function;
MYDBA@ORCL >
MYDBA@ORCL > create table test(a) as select rownum from all_objects where rownum <= 10;

Table created.

MYDBA@ORCL >
MYDBA@ORCL > create or replace function f return number
  2  as
  3          l_count number;
  4  begin
  5          select count(*) into l_count from test;
  6          return l_count;
  7  end;
  8  /

Function created.

MYDBA@ORCL > show errors
No errors.
MYDBA@ORCL >
MYDBA@ORCL > select f from dual;

         F
----------
        10

1 row selected.

MYDBA@ORCL >
MYDBA@ORCL > explain plan for select f from dual;

Explained.

MYDBA@ORCL >
MYDBA@ORCL > select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------

Plan hash value: 1388734953

-------------------------------------------------------------------------
| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |     1 |       |     2   (0)| 00:00:01 |
|   1 |  FAST DUAL       |      |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------

8 rows selected.

MYDBA@ORCL >
MYDBA@ORCL > drop function f;

Function dropped.

MYDBA@ORCL > drop table test;

Table dropped.

MYDBA@ORCL >
MYDBA@ORCL > set echo off;
MYDBA@ORCL >

Re: Urgent reply needed [message #132701 is a reply to message #132694] Mon, 15 August 2005 14:53 Go to previous message
RB
Messages: 11
Registered: April 2002
Junior Member
My Bad.
I forgot to specify the package name which contains this function.

It gives the explain plan now.
Thanks so much for your help.
Previous Topic: Solaris V880 W/32 G ram Oracle 10.1.2 - parameters?
Next Topic: Foreign Keys vs. custom scripts
Goto Forum:
  


Current Time: Mon Nov 28 06:27:05 CST 2022