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

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.

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.

3 Likes

Thank you John!

If you must use F5, you can greatly save memory by starting your script with these two lines: Your output will look much cleaner, too.

set linesize 4096;
SET PAGESIZE 50000;

1 Like

@JohnDorlon !!! Great Article!

Could you also put in the differences / similarities of the Shift+F9 and Ctrl+Enter and how they fit in? I have used Ctrl+Enter 99% of the time to run a script, but it appears there is something different than just F9.

Hi @UtahToad - Sorry I am just now noticing your question.

I don't have much to say on Shift+F9 vs CTRL+Enter, but here's what I can tell you.

Shift+F9 and CTRL+Enter are the same.

If you select a SQL, or part of a SQL, then use either of those shortcuts, Toad will run it. If you don't select anything, Toad will run whatever is under the caret, stopping at a blank line (instead of a semicolon).

If you Shift+F9 or CTRL+Enter here, we leave out the where clause.
image

if you Shift+F9 or CTRL+Enter here, we run the same thing as above (select * from emp)
image

So, how do Shift+F9 and CTRL+Enter differ from F9?

F9 will also run what is selected, but if you don't select anything, F9 will parse through blank lines and whatever else looking for the semicolon to mark the end of your statement. In my first screen shot, F9 would run the entire SQL, including the where clause. In my second screen shot, F9 would run just what is selected.

Hey, John,

I have only just discovered that I can use the sqlplus 'show' command with F5, which is a great shortcut within sqlplus itself. I thought TOAD just would not do those, but it does with F5. For example:

image

F9 gives:

image

Cheers,
Russ

Hi Russ.

Hm, yeah that's true.

I guess for F9 we could convert that to

select * from v$parameter where name = 'db_flashback_retention_target'

...but F5 is meant to, in some ways, emulate SQL*Plus. F9 is not.

-John

Yeah. You'd expect that as a 20+ year DBA, I would have templates for that stuff, but I don't. It is so quick to type that I just type the whole show command. I was always frustrated that TOAD would not do the show with F9, and it is helpful that it does with F5. But, there is a gotcha: It must done with care to prevent unintended consequences. F5 executes everything in the editor page unless the desired command/statement is highlighted. I have known devs who have come to grief not realizing that.

Cheers,
Russ

That's a good point. I added it to main post above.