Home » SQL & PL/SQL » SQL & PL/SQL » Query Vs Procedure on materialized view (Oracle 9i)
Query Vs Procedure on materialized view [message #344882] Mon, 01 September 2008 09:52 Go to next message
pointers
Messages: 410
Registered: May 2008
Senior Member
Hi,

I have a materialized view (mine_all) which has some 20 columns and 20000 rows.
If I query
select * from mine_all;


It is taking 1 min to get all the rows. Here, I am using PLSQL developer to execute this.

I have created a package which has one procedure which uses a ref cursor.

The package and procedure are as follows.
Here I have included the same query in procedure.

create package pkg as
type ref_type is ref cursor;
procedure mine(rc out ref_type);
end pkg;


create package body pkg as
procedure mine(rc out ref_type)as
begin

--Here I have included the same query in procedure.
open rc for select * from mine_all;
end pkg;




I am executing it from the PLSQL developer by using 'TEST'. The code has the following. But it is taking lot of time (4 mints) compared to the earlier select query.
begin
 pkg.mine_all(rc=> :rc);
end;



Why is the same query on the materialized view under the procedure using ref cursor on materialized view taking lot of time compared to the simple select query on the materialized view.

How can we tune this.

Regards,
Pointers.
Re: Query Vs Procedure on materialized view [message #344894 is a reply to message #344882] Mon, 01 September 2008 11:56 Go to previous messageGo to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Quote:
begin
pkg.mine_all(rc=> :rc);
end;


This confuses, the procedure you defined in the package is MINE not MINE_ALL.

By thumb rule static SQL will run faster than PL/SQL. If you want to find out what is the problem with the speed then try EXPLAN PLAN and check the stats, this might be a good place to start with as this will also help you to tune the query
Re: Query Vs Procedure on materialized view [message #344897 is a reply to message #344882] Mon, 01 September 2008 12:16 Go to previous message
pointers
Messages: 410
Registered: May 2008
Senior Member
It was a typo....
Please read it as 'pkg.mine'.

Regards,
Pointers.
Previous Topic: System Integration
Next Topic: procedure with refcursor
Goto Forum:
  


Current Time: Sat Dec 10 14:48:49 CST 2016

Total time taken to generate the page: 0.11860 seconds