0

I have a table with 70000 rows MYPROMPT varchar2(100), Mystring1 varchar2(100), Mystring2 varchar2(100)

So, data might look like this:

 'GREETING','Hello there','Joe' 
 'GREETING','Hello there','Mike'
 'GREETING','Hi','Steve' 
 'FIRING','You are fired','Dave'
 'FIRING','Outta here','Bob'

I need to spool this to a file like

GREETING
Hello there Joe
Hello there Mike
Hi Steve
FIRING
You are fired Dave
Outta here Bob

I have a script that handles this, doing a select for each MYPROMPT, and this works fine. It runs a series of queries based on the various values for MYPROMPT. The problem is that it takes a long time to spool to the output file I need (well over an hour). Is there something I can set, either in SQLPlus or at the database that will speed this up?

moleboy
  • 884
  • 1
  • 10
  • 17
  • 4
    When you're spooling the data to a file, you can SET TERMOUT OFF, this will be much faster as it doesn't have to spend the time to write it to the screen,or you can have a look to this http://stackoverflow.com/questions/2560398/oracle-sql-plus-spool – Gaurav Soni Aug 09 '12 at 17:29
  • THanks much! TERMOUT has helped markedly and I'll check out the other advice as well. – moleboy Aug 09 '12 at 17:46
  • Sadly, none of this helped. BUT, its possible there's more of a system issue at the moment. Working on that now. – moleboy Aug 09 '12 at 19:16
  • 1
    You mention multiple queries. What are they? If could easily be that they are slowing you down. – Ben Aug 09 '12 at 20:44
  • @Ben, actually, everything is running slow on that database (it takes me several seconds just to sort 70k records). So I don't think I can point fingers until I get that resolved. – moleboy Aug 10 '12 at 12:40
  • You do have indexes right? People would be more able to help you if you posted the relevant parts of your table creation script, your queries and their explain plans. – Ben Aug 10 '12 at 12:42
  • Sorry, I was not clear. My point was I do not need any more help because it is actually NOT the problem I thought it was. My apologies for the confusion. – moleboy Aug 14 '12 at 15:20

1 Answers1

0

My bad. It wasn't SQLPlus causing the problem, but rather a problem with my query.

moleboy
  • 884
  • 1
  • 10
  • 17