Home » SQL & PL/SQL » SQL & PL/SQL » Query to store the results - Runs for long time (Oracle 10g, 10.2.0, OS-Linux)
Query to store the results - Runs for long time [message #444590] Mon, 22 February 2010 16:56 Go to next message
sivakumar121
Messages: 27
Registered: April 2009
Junior Member
Hi

I have a query like this -

SELECT
FIELD_A,
FN_FUNCTION(CARVE_ID, 1) FIELD_B,
FN_FUNCTION(CARVE_ID, 2) FIELD_C,
FN_FUNCTION(CARVE_ID, 3) FIELD_D,
FN_FUNCTION(CARVE_ID, 4) FIELD_E,
FN_FUNCTION(CARVE_ID, 5) FIELD_F,
FN_FUNCTION(CARVE_ID, 6) FIELD_G
FROM TB_CARVE;


When I execute the query, it returns the data (approx - 40,000 rows) in 1 min.
But when I try to insert this data into another table (or create a table of this data) it takes me about 2 hours.

Tried using Materialized view, its again the same the refresh takes 2 hours.

Basically here, what I am trying to do is the data from the above query is used to update the values in another table.
What ever the procedure I am trying it takes 2 hours.

Any thoughts?
Re: Query to store the results - Runs for long time [message #444591 is a reply to message #444590] Mon, 22 February 2010 17:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does it return ALL the data in a minute, or does it just return the first rows in that time?
Re: Query to store the results - Runs for long time [message #444592 is a reply to message #444590] Mon, 22 February 2010 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>Any thoughts?

illusion of FIRST ROWS?
Re: Query to store the results - Runs for long time [message #444593 is a reply to message #444591] Mon, 22 February 2010 17:13 Go to previous messageGo to next message
sivakumar121
Messages: 27
Registered: April 2009
Junior Member
It is not displaying ALL data for sure, When I am trying to count the records, the query hangs.
Re: Query to store the results - Runs for long time [message #444594 is a reply to message #444593] Mon, 22 February 2010 17:15 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>When I am trying to count the records, the query hangs.

query does not "hang".
Session is busy working behind the scenes.

Just be patient!
Re: Query to store the results - Runs for long time [message #444595 is a reply to message #444590] Mon, 22 February 2010 17:28 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
>Any thoughts?
If those are really PL/SQL function, each will require processing time.

due to missing WHERE cause, SELECT returns every row in table.

So why are you surprised that it takes a while to complete?
Re: Query to store the results - Runs for long time [message #444596 is a reply to message #444593] Mon, 22 February 2010 17:29 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Right - as @BlackSwan points out, what you're getting is the illusion of the data being returned - the query returns the first rows in a minute - probably 100 rows and keeps on working to return the rest of them.

The key to improving the performance of this query is going to be the function FN_FUNCTION - can you post details of what it does?
Previous Topic: GLOBAL TEMPORARY TABLE
Next Topic: need to create colums from distinct rows in reporting
Goto Forum:
  


Current Time: Fri Sep 30 22:47:03 CDT 2016

Total time taken to generate the page: 0.05923 seconds