Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Question Unanswered: Extraction of Entire table...

    Hey,
    just wondering what the quickest way of extracting all of the data from a table is: it needs to be in CSV format...

    currently i am doing;

    Code:
    set hea off
    set pagesize 0
    spool c:\XXX.csv
    select x || ',' || y || ',' || z from blahtable order by blahfield
    /
    spool off
    but this still displays the data on the screen; which i guess is slowing it down rather alot.

    Any ways of turning this off; or better still a new ingenious way in which to do this which i can't think of...


    p.s. there are -alot- of rows -> 50million in one table and 105million in another.

  2. #2
    Join Date
    Feb 2001
    Location
    NC, USA
    Posts
    200
    Have a look at the all the SET options available in SQL*Plus. 'SET TERM OFF' should do what you want.

    Also, scroll down a few posts. I posted a Pro*C utility that will do this extraction. If you're on NT and can't compile it. I can post a precompiled version if you like.

    Alternatively, roll your own PL/SQL version with UTL_FILE for full control and flexibility.

  3. #3
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    posting the pro*c version would be good to look at; and unfortunatly i am on nt

    don't know any pl/sql (or enough) - could do it in java; though this will be significatly slower than pro*C or pl/sql, no?

  4. #4
    Join Date
    Feb 2002
    Location
    British Columbia
    Posts
    13
    Setting feedback and term off may help but the real culprit may be your order by clause. Do you have an index on the fields your are ordering by? If not your explain plan may show that you are performing multiple full table scans. An analyzed index on those columns will have a huge impact in reducing your query time even if you create, analyze and drop the index just for this query.

  5. #5
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441

    Unhappy

    yeah i do have an alanalysed (spelling??!?) index of the column i ordered it by; but it still takes AGES-> Also set term off does not do what i expected - i still get output in the sqlplus window? - taking processor.

    It still takes ages with just spooling select * from blahtable!

    <SIGH>
    <MOAN>Need a better computer</MOAN>


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •