Editor: F5 (Execute as Script) vs F9 (Execute/Compile)

Hello Toad users.

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.

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.

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 AT ONCE. 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.
  • The F5 grid represents all data as string
  • 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.

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.

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. 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.