Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm connected to the oracle server remotely. I need to export a table data(the whole table) with millions of records but due the hosting restrictions I can only use SQL (no exp , expdp) . Therefore I'm wondering what's the sql query that with the best performance. My table structure is as following :

  CREATE TABLE "AUTO"."AUTO" 
   (    
    "AUTOJ" NUMBER(16,0), 
    "AUTOK" NUMBER(5,0), 
    "AUTOL" NUMBER(38,0), 
    "AUTOM" NUMBER(4,0), 
    "AUTON" NUMBER(4,0), 
"AUTOZ" NUMBER(8,0), 
    "AUTOAA" NUMBER(32,0), 
    "AUTOBB" NUMBER(38,0), 
    "AUTOBC" NUMBER(38,0), 
    "AUTOBD" NUMBER(38,0), 
    "AUTOBW" DATE, 

    "V" NUMBER(32,0), 
    "AUTOT" VARCHAR2(128), 
    "AUTOU" NUMBER(10,0), 
"MK" NUMBER(38,0), 
    "ID" NUMBER(38,0) NOT NULL ENABLE, 
    "AUTOA" VARCHAR2(64) NOT NULL ENABLE, 
    "AUTOB" NUMBER(38,0) NOT NULL ENABLE, 
    "AUTOC" VARCHAR2(68), 
    "AUTOD" VARCHAR2(64), 
    "AUTOE" DATE NOT NULL ENABLE, 
    "AUTOF" DATE, 
    "AUTOG" NUMBER(16,0), 
    "AUTOH" VARCHAR2(128) NOT NULL ENABLE, 
    "AUTOI" NUMBER(10,0), 
    "AUTOW" NUMBER(10,0), 
    "AUTOX" NUMBER(15,2), 
    "AUTOY" NUMBER(15,2), 

    "AUTOMK" DATE, 
    "AUTOPO" VARCHAR2(128), 
    "AUTOCV" NUMBER(10,0), 
    "AUTOON" NUMBER(10,0), 
    "AUTOXA" NUMBER(1,0),
"AUTOBY" NUMBER(8,0), 
    "AUTOCC" NUMBER(38,0), 
    "AUTODA" DATE, 
    "AUTOO" NUMBER(3,0), 
    "AUTOP" VARCHAR2(1), 
    "AUTOR" NUMBER(3,0), 
    "AUTOS" NUMBER(3,0),  
    "AUTOIWN" NUMBER(10,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 104857600 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT)
  TABLESPACE "AUTODATA01" 

I was thinking about to select blocks of records (e.g. of 100.000), concatenate them internally and after it finishes to "export them". However this seems to take long too . Based on my estimation it would take about 20 hours.

  OS : Solaris 
  DB : Oracle 11g  
share|improve this question
1  
If you need all the table, the fastest query is simply select * from your_tab, there's no going faster than that (parallel might or might not help - depends what your bottleneck is). What are you using to do the export? –  Mat May 20 '13 at 14:19
    
Theoretically you can speedup the export simply by executing it at the "right" time. If there is a huge DML against this table the full table scan can be slower. First of all you have to find what is your bottleneck. –  ibre5041 May 20 '13 at 15:44
    
@Mat select * from your_tab is not the fastest query due the network issues. I'm using oracle sql developer –  user2072671 May 20 '13 at 19:25
    
@Ivan I run it at the right time. I think the query itself is slow. Some 'parallel' stuff would help. –  user2072671 May 20 '13 at 19:28
2  
If the issue is the network then bear in mind that sql*net has a fairly naive compression built in. jonathanlewis.wordpress.com/2010/05/07/sqlnet-compression If there was a way of ordering (or clustering) the result without too much overhead then it could be worth pursuing. –  David Aldridge May 21 '13 at 15:13

1 Answer 1

Tom Kyte (of http://asktom.oracle.com fame) maintains links to several different methods of "unloading" a table (or query) to CSV.

(expdp comment redacted.)

share|improve this answer
    
expdp has a command-line client that can run from a remote machine, but expdp requires a directory local to the host to which to export. –  David Aldridge May 21 '13 at 15:08
    
You can do an impdp over a network link though, @David. (But indeed, expdp needs a directory on the server.) –  Mat May 23 '13 at 14:00
    
I had forgotten that expdp writes server side (as it can run parallel). –  Adam Musch May 23 '13 at 19:06

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.