The SQL Results Grid is found in the Data tab in the lower portion of the Editor, and the Data tab in the right hand detail panel of the Schema Browser. Results are displayed in the Editor Data grid. There are many things you can do with the results of a query.
Auto Trace
Auto Trace is a mini version of SQL Trace that displays quick results directly on the client. In Toad, the results are displayed beneath the Editor or the Query Builder window. From the Auto Trace results area you can sort columns, print the grid, and copy the results to the clipboard. Auto Trace is one of many optimization features Toad provides.
Notes:
l Auto Trace forces a read of all data from the result of the query. This can take some time.
If a query may return a large number of rows and time is a factor, you should not use Auto Trace.
l This feature requires access to some V$ tables.
To enable/disable Auto Trace and SQL Trace
» Right-click in theEditorand select theAuto Tracemenu option. A checkmark indicates it is enabled. Or, in theEditorresults area, click theTracetab and then theAuto Trace orSQL Tracetab and select or clear theEnabledcheckbox.
To view Auto Trace results
» In theEditorresults area, click theTracetab. If the Trace tab is not visible, right-click and then selectDesktop | Trace.
Optimization
Toad offers several features to help you optimize queries or view the performance statistics for the server. Although Toad provides access to these statistics and/or Oracle utilities, this section describes only how to use the features within Toad, not how to interpret the results.
For an excellent guide on SQL tuning, we suggestOracle SQL - High Performance Tuningby Guy Harrison available from Prentice Hall Press.
Feature Description Optimize
Current SQL
Use Auto Optimize SQL to quickly optimize a single SQL statement. Toad searches for faster alternatives and allows you to compare them to
Feature Description
the original statement and each other.
SQL Optimizer If you have a Toad Edition that includes the SQL Optimizer package, you can use it to help you optimize your code.
Explain Plan Explain Plan shows the path and order in which Oracle will process your statement. By processing Explain Plan on variations of a statement, you can see how the adjustments will affect the execution.
SQL Trace SQL Trace is a server-side trace utility that displays CPU, IO
requirements, and resource usage for a statement. SQL Trace is a much more complete utility than Auto Trace; however, viewing the results can be difficult because the output file is created on the server.
Auto Trace Auto Trace is a mini version of SQL Trace that displays quick results directly on the client. In Toad, the results are displayed beneath the Editor window.
Optimizer Mode
You can set the optimizer mode for the current session. This will affect all queries (including Toad's own) for the duration of the session or optimizer setting.
Note:Optimizer mode is not available in Oracle 10g databases. Therefore Toad disables this option when it is connected to a 10g database. Row Numbers
Toad will display row numbers in the Editor data grid if the option to do so is enabled. Make sure theView | Options | Data Grids tab | Show row numbers in all grids(applies to data grid on Browser also) checkbox is selected.
The total number of rows returned in the resultset will display in the status bar at the bottom of the window only after you have scrolled to the end of the resultset. This is because the resultset is fetched only as required, to improve overall performance. When the last row is fetched, Toad will display the total count.
To return the Oracle pseudo-column ROWNUM in the SQL Results grid, add "ROWNUM" to the query:
select rownum, emp.* from employee emp
Remember that rownum is an Oracle pseudo-column, not stored with the table definition or data, but derived when queried.
To return the ROWID in the query, specify the column and the datatype: select rowidtochar(rowid), emp.* from employee emp
Note:You can also enable "Show ROWID in data grids" fromView | Options | Data Gridstab by clicking in the checkbox. This will accomplish the same thing without resorting to coding. Saving Toad Query Results
Any of Toad's window query results can be saved to the Windows Clipboard or to a file by the procedure below. Some dialog boxes do NOT have a "Copy to Clipboard" or "Save to Disk" function. This duplicates that functionality.
To save query results
1. Turn on spooled output to disk file:Database | Spool SQL | Spool SQL To File. 2. Run the desired Toad window (for example, the Schema Differences window) select each
desired tab.
3. From the User Files folder, open DEBUG.SQL. 4. Copy each SQLinto the Editor window.
5. Run eachSQLin the Editor window, substituting hard coded values for the bind parameter variables, or just enter them when prompted in the Variables dialog box. 6. Save the grid contents to clipboard or disk file, usingright-click, Export Dataset. Time Values
When displaying times with dates, Delphi, and thus, Toad, will suppress the time values if they are 12:00:00 AM (midnight). The time portion of the date fraction is zero, so Toad adds no value to the display of the date.
Remember that Oracle stores dates as a big fraction number offset from January 1, 4712 B.C. It is then converted to a complete date and time. It is also good well past Y2K.
Performing the query "Select sysdate from dual" will display the time, and similarly, queries of DATE datatype columns will display the time if it is not midnight.
The time drop-down inView | Toad Options | Data Gridsdoes not affect this output of time.