This discussion is archived
10 Replies Latest reply: Dec 10, 2007 7:14 AM by SueHarper RSS

How to fetch all the records

520568 Newbie
Currently Being Moderated
Hello everybody,

I am currently working on SQL Developer for the couple of weeks. I have specific need to check, how much time one query take to fetch all the records. Currently it can fetch only 50 records and when scroll then next 50 records and so on.
Can anybody tell me, how to set this to fetch full records

Regards,
Martand M Joshi
  • 1. Re: How to fetch all the records
    -K- Guru
    Currently Being Moderated
    Martand,

    There's no setting to fetch all records. You wouldn't like SQL Developer to fetch for minutes on big tables anyway.
    If, for 1 specific table, you want to fetch all records, you can do Control-End in the results pane to go to the last record. You could time the fetching time yourself, but that will vary on the network speed and congestion, the program (SQL*Plus will be quicker than SQL Dev because it's more simple), etc.
    Now, what I think you really want here, is getting the execution time. If you really want to analyze the query's performance, you'd have to use the explain plan, autotrace and tkprof (see the manual for tuning and performance).
    To make us life easier, the makers of SQL Developer included support of the explain plan, and as of v1.1, autotrace!

    I'm sure you've got enough to work things out from here...

    Regards,
    K.
  • 2. Re: How to fetch all the records
    SueHarper Oracle ACE
    Currently Being Moderated
    The F9 key return the set of 50 records at a time. You can use the F5 key to return all your records. For those of you not wanting to return all records there is a preference in 1.1, for the Script Output, that allows you to control how many records returned. i.e. you might only want to return the first 10 records, for really big tables.

    In terms of assessing performance, follow the suggestions provided above.

    Sue
  • 3. Re: How to fetch all the records
    580608 Newbie
    Currently Being Moderated
    Hi!

    I guess that the mentioned option changed name in 1.2.1: 'Sql Array Fetch Size'. Available from Tools -> Preferences -> Database -> Advanced parameters.

    Is this correct?

    Thanks,

    Radu
  • 4. Re: How to fetch all the records
    -K- Guru
    Currently Being Moderated
    No and no:
    The mentioned option is Tools - Preferences - Database - Worksheet Parameters - Max Rows to print in a script, and hasn't changed name.

    The option you mention is for the data grid and normally executed statements (F9), but is broken in the current version.

    Regards,
    K.
  • 5. Re: How to fetch all the records
    580608 Newbie
    Currently Being Moderated
    Hi,

    Thanks for the input.

    In this case let's consider for example this simple use case:
    in data grid or in SQL worksheet with a normally executed statement (F9) I wish to provide a report of the data (Excel, csv or HTML). How can I make sure that all the rows are exported?
    In SQL Navigator there is an option to select all rows...

    Thanks,

    Radu
  • 6. Re: How to fetch all the records
    -K- Guru
    Currently Being Moderated
    The Export Data function exports everything; if not all rows are fetched already, sqldev fetches them now. If the table's big enough, you'll see the record counter advance in the status bar.
    Mind you've got an extra Where tab in the dialog. By default, you'll always get the same results you get in the data grid.

    Regards,
    K.
  • 7. Re: How to fetch all the records
    580608 Newbie
    Currently Being Moderated
    Hi!

    OK, thanks.

    What about browsing the data: went into data grid and pressed Ctrl+End for a table with 11 million rows. The SQL Developer GUI just froze and never came back. Is there any buffer size I can increase in order to accommodate these bigger tables? In SQL Navigator I have no problem for the same table.

    Thanks,

    Radu
  • 8. Re: How to fetch all the records
    -K- Guru
    Currently Being Moderated
    Sqldev vs. other tools has some serious performance issues. Fetching 11 million rows may take ages.
    Additionally, for exporting, sqldev may fetch the whole lot again. In previous versions up to 3 times!
    The GUI freezing up until the action is complete is a known and annoying issue.

    So, you can be patient for sqldev to finish fetching, or use sqlnav.
    FWIW, the team promised to look at the performance issues for next release...

    K.
  • 9. Re: How to fetch all the records
    580608 Newbie
    Currently Being Moderated
    Hi!

    Thanks for the answer. Yes, it would be a good idea to concentrate on performance and bug fixing: there are so many errors that I really have difficulties recommending this tool for serious use.

    Thanks for all the info,

    Radu
  • 10. Re: How to fetch all the records
    SueHarper Oracle ACE
    Currently Being Moderated
    In the next release, the export does not fetch the records again, unless you change the query in the export dialog.

    Sue