Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql code for a VIEW (9.0.4.0)
pl/sql code for a VIEW [message #316424] Fri, 25 April 2008 02:01 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,
I have created view.The query in view is taking more than 30 mins for execution.So I want to write a pl/sql code for reducing the time period.Is it possible? I f possible how to write the code..Please guide me ..

Thank you.
Re: pl/sql code for a VIEW [message #316429 is a reply to message #316424] Fri, 25 April 2008 02:08 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
So I want to write a pl/sql code for reducing the time period

Of what?

Regards
Michel
Re: pl/sql code for a VIEW [message #316432 is a reply to message #316429] Fri, 25 April 2008 02:14 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Michel,
if I put that view in Pl/SQL .if I am executing the code that execution time should reduced? Is it possible?

Thank you
Re: pl/sql code for a VIEW [message #316435 is a reply to message #316432] Fri, 25 April 2008 02:24 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In other words, this is what you have now:
CREATE VIEW v_dept AS
  SELECT * FROM dept;
and it takes half an hour to execute.

In order to shorten this time, you'd like to put it this way:
BEGIN
  CREATE VIEW v_dept AS
    SELECT * FROM dept;
END;

(Forget about Oracle not being able to execute this PL/SQL block; one should use EXECUTE IMMEDIATE) Why do you think that something like this would execute FASTER than pure SQL statement?
Re: pl/sql code for a VIEW [message #316438 is a reply to message #316435] Fri, 25 April 2008 02:30 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,
I wrote as follows
craete or replace procedure proc
as
begin
  execute immediate
   'craete or replace view
...
....
....
'
exception
  when others then
    dbms_output.put_line(sqlerrm);
end proc

prcedure was created.But my requirement is when I am executing
the query only it's taking very long time.I have done
Optimaztion.I am not that much good in DBA concepts.So now I
want ot write a pl/sql code for the view to increase the
performance of the view..Is it possible using pl/sql?
if possible please let me know how to write the code for this
issue..

Thank you.
Re: pl/sql code for a VIEW [message #316442 is a reply to message #316438] Fri, 25 April 2008 02:39 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
To cut a long story short: no, it won't work any faster than SQL statement.

As a view is (only) a stored SELECT statement, it is expected that querying the view might take a long time. However, moving code from SQL to PL/SQL is not a way to optimize a statement. Just for your convenience, we have the Performance Tuning forum. Did you visit it? Search for a way to optimize your query? Read the documentation?

Just a blind guess: did you properly index relevant columns in tables that are used in view creation? Did you properly join all tables? Is there an unwanted Cartesian join?
Re: pl/sql code for a VIEW [message #316444 is a reply to message #316442] Fri, 25 April 2008 02:49 Go to previous message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

I have added hints in the query and I indexes are created.the query is giving the required result.But only execution prbolem.I will post the information what u required.So please let me know for further information.

Thank you
Previous Topic: update table columns depending on other table column values
Next Topic: How to sort this?
Goto Forum:
  


Current Time: Thu Dec 08 04:01:14 CST 2016

Total time taken to generate the page: 0.82485 seconds