Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2009
    Posts
    10

    Unanswered: How To Save Data Quickly

    I have a query that retrieves 7 lakh rows of data in say 3 minutes but when i go to save this data in comma sepatrated file using toad's save as feature .it takes almost 3 hrs to save the data.

    This query looks like:

    SELECT circle_name,cluster_name,rsnnumber ,mdnnumber,hs_rsnnumber ,otafdate,
    (case when old_mdn is null then 'INACTIVE' else 'ACTIVE' end)
    FROM GSK_INVENTORYDETAILS
    WHERE OTAFDATE>='1-MAR-2009'
    AND OTAFDATE<='31-MAR-2009'
    AND GSKPLAN LIKE 'PREPAID%'
    AND CIRCLE_NAME='UTTAR PRADESH (WEST)'


    GSK_INVENTORYDETAILS has over 20 cr data

    I would like to know a way to save my data faster

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    save to a Solid State Disk.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    USE SQLPLUS, NOT TOAD.

    Code:
    set pagesize 0
    set linesize 2000
    set trimspool on
    spool c:\temp\myfile.csv
    
    SELECT circle_name||','||cluster_name||','||rsnnumber||','||
    mdnnumber||','||hs_rsnnumber||','||otafdate||','|| 
    (case when old_mdn is null then 'INACTIVE' else 'ACTIVE' end) 
    FROM GSK_INVENTORYDETAILS 
    WHERE OTAFDATE between to_date('1-MAR-2009')  AND to_date('31-MAR-2009') 
    AND GSKPLAN LIKE 'PREPAID%' 
    AND CIRCLE_NAME='UTTAR PRADESH (WEST)';
    
    spool off
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT circle_name 
           ||',' 
           ||cluster_name 
           ||',' 
           ||rsnnumber 
           ||',' 
           ||mdnnumber 
           ||',' 
           ||hs_rsnnumber 
           ||',' 
           ||otafdate 
           ||',' 
           ||(CASE 
                WHEN old_mdn IS NULL THEN 'INACTIVE' 
                ELSE 'ACTIVE' 
              END) 
    FROM   gsk_inventorydetails 
    WHERE  otafdate BETWEEN To_date('1-MAR-2009') AND To_date('31-MAR-2009') 
           AND gskplan LIKE 'PREPAID%' 
           AND circle_name = 'UTTAR PRADESH (WEST)';
    using TO_DATE without mask is as bad as not using TO_DATE at all, IMO.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    true, just being lazy and showing the originator that to_date should be used.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Apr 2009
    Posts
    10
    Thanks All For The Reply...

    I Tried The Step Even Tried The Mask But The Data Still Takes Almost Same Time To Save .

    Can This Query Or Data Structure Be Modified Anymore, Or May I Have Some Other Tool To Save Data?

  7. #7
    Join Date
    Apr 2009
    Posts
    10
    I have no ssd with me, but found out on wiki that writing to a hard disk is faster than saving on ssd,retrieving the saved data is however faster in case of ssd.is it true? or again i m fooled by wiki?

Posting Permissions

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