Home » SQL & PL/SQL » SQL & PL/SQL » Spool time in query
Spool time in query [message #288640] Tue, 18 December 2007 04:14 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi Experts,

I have one question regarding spool time in the query.

1. In one query

select TAB1.a, TAB2.b, TAB3.c, TAB4.d
from TAB1, TAB2, TAB3, TAB4
where TAB1.a = TAB2.a
and TAB2.b = TAB3.b
and TAB3.c = TAB4.c

And spool the data.

1. Create a temoporary table
Insert record of above mentioned select query.

And fire the query in the temporary table.
And spool the data.

I want to ask that which will be faster?

Thanks in advance.




Re: Spool time in query [message #288650 is a reply to message #288640] Tue, 18 December 2007 04:27 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
First one
Re: Spool time in query [message #288655 is a reply to message #288650] Tue, 18 December 2007 04:31 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks for your answer.
But i heard that from temporary table we can have faster access?

Am i right.

Also please clarify your answer.

Thanks again.
Re: Spool time in query [message #288656 is a reply to message #288655] Tue, 18 December 2007 04:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

But i heard that from temporary table we can have faster access?

From where?

Quote:

Also please clarify your answer

Why don't you ask the same for those who said the opposite?

There is nothing to clarify.
First one is faster.

Regards
Michel
Re: Spool time in query [message #288662 is a reply to message #288656] Tue, 18 December 2007 04:43 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks again for your reply.

As far as my small oracle knowledge is concerned retriveing data through join and retriving data from a temporary table i think retriving data from a single table is faster.

And if you are saying retriving from join will faster then please tell me why it is faster?

Thanks in advance.
Re: Spool time in query [message #288667 is a reply to message #288662] Tue, 18 December 2007 04:49 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Ask yourself following question: How will the records be inserted in your temporary table? Where would you get that data?

Re: Spool time in query [message #288783 is a reply to message #288640] Tue, 18 December 2007 11:43 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Or to put it another way, answer the following, which would be faster

1) run a select to a spool file?


2) run the EXACT same query into a temporary table and then run a second query to dump the data to a spool file.

If you answered 2, then return to school.
Previous Topic: PL/SQL Performance
Next Topic: seconds in day must be between 0 and 86399
Goto Forum:
  


Current Time: Thu Dec 08 20:01:01 CST 2016

Total time taken to generate the page: 0.23895 seconds