We help IT Professionals succeed at work.

how to select a large numebr ofo rows in sql developer

itortu
itortu asked
on
hi,

i am querying the database for a total number of 146,407 rows.

everytime i run my query, sql developr freezes up due to the large number of records i assume.

i would like to find out a way that i can retrieve all the records that i need, avoiding sql developer freezing up, and at the same time been able to obtain the total number of rows, that is 146,407

i think i do not need to retreive the whole 146,407 rows at once, i just need to make sure i get them all, even if it in parts, and not repeating or skipping any.

please any help on how to achieve this would be awesome.

i am providing the query i am using currently.

thank you so much!
set define off;
SELECT '' AS "Internal Field Name", r.dDocName AS "System ID" , r.dDocTitle AS "Title", r.dRevLabel AS "Revision", m.XCOMMENTS AS "Description"
, m.XIDENTIFIER AS "Identifier", r.dDocType AS "Content Type" , m.XSUBTYPE AS "Content Sub Type", r.dSecurityGroup AS "Data Classification"
, r.dDocAccount AS "Security Account", m.XISCONFIDENTIAL AS "Is Confidential", m.xSupplementalMarkings AS "Confidential Group", m.xIsExternal AS "Is External"
, m.xExternalLocation AS "External Location", m.xExternalLocation2 AS "External Container", d.dOriginalName AS "Native File", '' AS "Alternate File"
, m.XLEGALENTITY AS "Legal Entity", 'Chief Financial Office & Treas' AS "Business Unit", 'Corporate Controller' As "Department", m.XCREATOR As "Creator"
, m.XCONTRIBUTOR AS "Contributor", r.dDocAuthor AS "User ID", m.xIsVital AS "Subject to Review", m.xVitalReviewer AS "Reviewer", m.xVitalPeriod AS "Review Period"
, m.xVitalPeriodUnits AS "Review Period Units", m.XSUBJECT1 AS "Subject 1", m.XSUBJECT2 AS "Subject 2", m.XSUBJECT3 AS "Subject 3", m.XSUBJECT4 AS "Subject 4", m.XSUBJECT5 AS "Subject 5"
, m.xIsRecord AS "Is Record" , m.xCategoryID AS "FRS" , TO_CHAR(m.xRecordActivationDate,'MM/DD/YYYY HH:MI AM')  AS "Record Activation Date", TO_CHAR(m.xRecordExpirationDate,'MM/DD/YYYY HH:MI AM')  AS "Record Expiration Date"
, TO_CHAR(m.xPublicationDate,'MM/DD/YYYY HH:MI AM')  AS "Creation Date", TO_CHAR(r.dInDate,'MM/DD/YYYY HH:MI:SS AM')  AS "Release Date", r.dID As "dID"
FROM REVISIONS r, DOCMETA m, DOCUMENTS d
WHERE r.dID = m.dID AND r.dID = d.dID AND d.dIsPrimary = 1 AND r.dreleasestate = 'Y'
AND m.XBUSINESSUNIT = 'Chief Financial Officer' AND m.XDEPARTMENT = 'Corporate Controller'
ORDER BY r.dDocName ASC, r.dRevLabel ASC;

Open in new window

Comment
Watch Question

Expert of the Quarter 2010
Expert of the Year 2010

Commented:
You could get 10000 rows at a time, e.g.

FROM REVISIONS r, DOCMETA m, DOCUMENTS d
WHERE r.dID = m.dID AND r.dID = d.dID AND d.dIsPrimary = 1 AND r.dreleasestate = 'Y'
AND m.XBUSINESSUNIT = 'Chief Financial Officer' AND m.XDEPARTMENT = 'Corporate Controller'
AND ROWNUM BETWEEN 1 and 10000;
ORDER BY r.dDocName ASC, r.dRevLabel ASC;

Now this assumes the data is static from when you start 1-10000, 10001-20000 all the way until the last record.
Richard OlutolaConsultant

Commented:
What do mean by freezing up? Do you check processes in task manager and you're sure it has frozen and not just taking a while to retrieve the rows?
How long do you allow before giving up?
R.

Author

Commented:
i will give that a try cyberkiwi, thank you.

rolutola, i have waited about an hour to retrieve the records, and they do come up after a long time waiting. then the issue comes again when i try to export the records to xls, or text. it freezes again, i have waited more than an hour and is just goes all blank. I have also checked all the processes, and killed most of them to free up so memory. thank you.
Richard OlutolaConsultant

Commented:
This could be due to lack (or use) of indexes. It appears that SQL Developer is accessing the server again during the export (to excel) process. Have you tried running this in SQL*Plus for comparison? Set timing on for the test.

R.

Author

Commented:
is there a way i can select in ranges of 25000 items at a time?

Author

Commented:
i was able to run AND ROWNUM BETWEEN 1 and 25000;

but when i run

AND ROWNUM BETWEEN 25001 and 50001;

i get nothing.

Author

Commented:
yep i have to confirm that i am not able to retrieve any more rows when i do

from 10001 to 20000
Expert of the Quarter 2010
Expert of the Year 2010
Commented:
You have to first alias the rownum in the inner query before you can filter it.

If the base query is

select a,b,c,d
from e,f,g
where e.i=f.j and f.k=g.l
order by e.d asc

The the paging works like this

select a,b,c,d from (
select a,b,c,d, rownum as rn
from e,f,g
where e.i=f.j and f.k=g.l
  and rownum <= 50000
order by e.d asc
) sq
where rn between 25001 and 50000
order by d asc

Rownum is interesting because you can limit it, say <=50000, but if is try to skip the first row, say rownum=2, it does not work because without the first row, there is no 2nd row.

Commented:
What version of SQL Dev are you using?

You have to realize that returning 150k records can put a severe strain on your system, which is why it freezes up. However, you can still change the number of rows returned in this manner:

Tools / Preferences / Database / Advanced / SQL Array Fetch Size


If you need to export to Excel, why not just run the query directly from Excel instead of copy/paste from SQL Dev?
Mark GeerlingsDatabase Administrator

Commented:
I agree with gatorvip, there are are better ways to get data into Excel than by querying all of the rows in SQL Developer first.  Having Excel use an ODBC connection to query Oracle directly is one option.  Another option is to use the supplied PL\SQL package: utl_file, to have a procedure write a comma-delimitted or fixed-length ASCII file that Excel can then easily import.  (Note that utl_file can only write to a disk on the server, so you may need to copy the ASCII file from the database server to a client before Excel can open it, if you use this option.)

Author

Commented:
I would like to query the records directly from excel, the problem is that these are over 100,000 records, and I think excel has a limit in the number of rows, isn't?
Mark GeerlingsDatabase Administrator

Commented:
Older versions of Excel (like the version with Windows95) had a much smaller limit (I think it was 16,000 records).  I don't know which version of Excel you have now, and i don't know what the current Excel limits are.

If you use a PL\SQL procedure to create an ASCII file, you could have it limit the number of records to whatever number you want, then continue in a second file, etc. if the database has more records than Excel can handle in a single file.

Author

Commented:
the problem is that i am not sure how to change the current query i use to retrieve the records, to a stored procedure and how to write the ascii file. is there any way i could get an example on how to do this? thank you.

Commented:
Excel 2007
http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HP010073849.aspx
Worksheet size        1,048,576 rows by 16,384 columns

Prior versions (through 97) could only handle 64k rows
http://support.microsoft.com/kb/120596


Information Technology Specialist
Commented:
See attached.
comments.txt
Mark GeerlingsDatabase Administrator
Commented:
Using rownum with a subquery is way to force an Oracle database to return a "page" of records, but this approach can cause performance problems, since it forces a full-table scan of the underlying records.

Here are two examples PL\SQL procedures, first one that uses that uses utl_file, then one that uses a cursor loop.

create or replace procedure sample (path_nm in varchar2, file_nm in varchar2,
 lines_to_read in number default 3, chars_per_record in number default 999,
 output_path in varchar2 default 'C:\temp') as
-- This procedure opens a text file in any directory, reads three lines from it
--  and writes them to C:\temp\temp.txt.
-- This is intended for large text files, to quickly get a small section of the file that can
--  be opened and browsed more easily in a text editor.
  in_file   utl_file.file_type;
  out_file  utl_file.file_type;
  text_str  varchar2(4000);
  lines     pls_integer;
  err_text  varchar2(200);
begin
  lines := 0;
  out_file := utl_file.fopen(output_path,'temp.txt','W');
  in_file := utl_file.fopen(path_nm,file_nm,'R',chars_per_record);
  while lines < lines_to_read loop
    begin
      utl_file.get_line(in_file, text_str);
      utl_file.put_line(out_file,nvl(text_str,to_char(lines)));
      lines := lines +1;
    exception
      when others then
        err_text := substr(sqlerrm,1,200);
        lines := lines +1;
        utl_file.put_line(out_file,err_text);
    end;
  end loop;
  utl_file.fclose(in_file);
  utl_file.fclose(out_file);
end;
/

--replace the square brackets [] and the text between them with meaningful values
create or replace procedure [procedure_name] as
  cursor c1 is select [column(s)]
    from ...
    where ...
-- set up variables here to hold values to be selected
begin
  open c1;
  loop
    fetch c1 into [variable(s)];
    exit when c1%notfound;
    counter := counter +1;
    total := total +1;
--  put main logic here
  end loop;
  close c1;
end;
/