Hello Toad users.
TLDR: Use F9 instead of F5.
In the Editor, there are 2 "run" buttons. One of them has F5 as a shortcut, the other has F9 as a shortcut. Their intended uses are very different and they produce results in different ways. If you use them "wrong" you could experience a lot of frustration. It is my intent by writing this to prevent that frustration.
[Edit: I spent most of this rant talking about SELECT statements, but F9 also has benefits and is generally faster than F5 for DML and DDL as well. Make it a habit to use F9 unless your editor has several statements, one after another, in the same tab, and you want to run all of them.]
The first way, the best way, the way you should always run single statements, is F9. F9 sends your SQL to Oracle, and returns the open cursor in the grid below. The first 500 rows are retrieved by default, and more will retrieved if you scroll down in the grid. Even if your query returns millions of rows, the initial fetch will be fast and won't use much memory because we're just pulling a little at a time. You can CTRL+End in the grid if you want to retrieve all rows. And that will still be faster than running with F5.
The other way, the way that is NOT Intended for single SQL statements, although it does support them, is F5. When you run a SQL with F5 - Toad sends the SQL to Oracle, then retrieves ALL OF THE ROWS and you watch in agony as they are processed while they come in. For large result sets, this is slow and uses a lot of memory. Toad does not return control to you until all rows are retrieved (or you cancel). The data is displayed in two ways - SQL*Plus style, as shown below....
...and in a grid, as shown below:
The impact of F5 can be huge if your SQL returns a lot of rows.
- F5 is slower and uses more memory because it fetches all data at once, processes each row as it comes in, and then show it to you in two different ways.
- If you ran with F5, and your output used most, but not all of your free memory - you may not have enough free memory to save the data. This problem is much less likely to occur with F9.
- F9 is fast because it only retrieves a little bit at a time, does minimal processing, and presents the data in one place.
- The F9 grid represents the data using the datatype it is defined as in the database.
- The F5 grid represents all data as strings.
- If the editor contains multiple statements, F5 will execute all of them (unless you select only the one(s) that you want to execute. F9 will only execute the statement where the cursor is.
Other benefits of running SELECT statements with F9 are:
- If you include ROWID in your select statement, then you can edit the data in the grid, and Toad will translate your edits into Insert/update/delete statements to make corresponding changes in the database.
- If you want to export data (by right-click -> export dataset), then Toad will re-execute your query and run it in a way that will not keep all rows in memory at the same time. This will allow you to export as much data as you need to, without running out of memory.
- F9 will add your SQL to SQL Recall. F5 will not.
- F9 obeys the substitution variable prompting options on Options -> Execute/Compile. Script execution is intended to mimic SQL*Plus, so you must use SET DEFINE commands to control substitution variable prompting in scripts.
Right-click -> Eport Dataset:
Just make sure the "Display all results in grid" option is unchecked.
So....when should you use F5?
The ONLY time you should use F5 is when you want to run a script that contains multiple statements that you wish to run one after another. For example, a script to create objects in the database, or perform a bunch of DML (insert/update/delete statements), or maybe run some procedures. The point here is that you have multiple SQL statements that you want to run one after the other. That is the only time that you should be using F5.
If you made it this far, thanks for reading. I hope this is helpful.
