hey everyone,
i have huge dataset ( 600.000 row)
im using windows 10 and toad for oracle 12.1
i have to import that to excel.
in my last attempt it took 2 hours but i have less time for that.
what should i do?
urgently, thanks for your support.
Be sure to check the "Use Array DML" option on the last step before you start it. That makes a big difference.
i couldnt see array dml section?
Oh, you are exporting. You said "Importing" before.
Uncheck "Display all results in grid".
Sorry its my fault.
Is there another solution?
Does that not help? It should help a lot.
Didnt unfortunately
what does the table definition look like? You can send it to me in an email if you don't want to post it publicly. john.dorlon@quest.com
Sales numbers, customer infos, barcodes etc
By the way whats the difference between excel file and excel instance
Sorry, I should have been more clear. I am asking for the "create table" statement.
Excel File creates the file by our own code.
Excel Instance starts in instance of Excel.exe and sends data to it. You may have better luck with that.
Which version of Toad are you using?
Im using data tables from SAP . I didnt create myself
You can go to Schema Browser-Tables-Script to get the Create Table script. I am trying to get an idea of how long it should take. The more columns, the more data there is, the longer it is going to take. Please send the script so I can create the table here, fill it with fake data, and give you an idea of how fast I think it should go.
My toad version 12.1
I will try create table script tomorrow.
Oh 12.1, you said that at the beginning, sorry. I just saw "oracle 12.1" 
Regarding statistics, is this a slow running query? I was thinking at first that you were just exporting from one table, but if it's a slow running query, then yes, maybe statistics will help.
If you are just exporting from a single table, or if the query executes quickly but exports slowly, then statistics probably will not help.
Im using a lot of inner join, left join, group by, order by from 7-8 different tables 
When you start exporting....
How long until you start to see the exported rowcounts increasing?
And once they start, how much longer until export is done?
I should say that im using view for this export. Im right click to view on object palette and im clicking to describe. After im using export
I think my problem is when export to excel
I ask again:
When you start exporting....
How long until you start to see the exported rowcounts increasing?
And once they start, how much longer until export is done?
Rowcount increasing starting in 2 minutes but exporting continues 1 hour
Thanks Jon, you are the great person. I will send you tomorrow morning.
As a test, you could try:
Create table test_table as select * from your_view_name;
Then try to export test_table. Again, make sure that "Display all results in grid" is unchecked.
If it is suddenly fast, the problem is in the view. Updated statistics might help.
If it is still slow, then it is either the volume of data (do you have a lot of columns?) or maybe Toad is having a problem turning that data into an Excel file, or some combination of the two.
If you can create that table and export it and send it to me, I will try to reproduce the problem and fix if there is something to fix.
As a frame of reference, if I make a copy of DBA_TABLES and put 750,000 rows in it, I can export to Excel in about 8 minutes using Toad 13.3. I also tried this in 12.1 but there I ran out of memory and got an error. I forgot to mention that with a dataset of this size, you may need to use 64 bit Toad.
Another option that will not use as much memory - (as long as Display all results in grid" is unchecked) is exporting to CSV and then open the CSV file with Excel. CSV is the same as "Delimited Text" with a comma delimiter and string fields enclosed with quotes.
-John
1 Like