Toad World Blog

Generate Indexes in Optimize SQL

Jan 10, 2013 11:00:00 PM by Steve Hilker

SQL Optimizer identifies columns to use as index alternatives for a SQL statement after it analyzes SQL syntax, relationships between tables, and selectivity of data. SQL Optimizer then combines identified alternatives into index sets.

To generate an index alternative

  1. Select the Optimize SQL tab in the main window.

  2. Enter a SQL statement in the Alternative Details pane.

  3. Click B_Index. The Select Connection and Schema window displays.

  4. Select a connection and schema to use.

  5. Select Index Details in the SQL Information pane to view index generation information.

  6. Select an index set to test in the Alternatives pane.

  7. Click TB_Execute.

    Note: The Execute function allows you to test an index set SQL Optimizer generated. It physically creates the indexes on the database, runs the SQL statement, retrieves execution statistics, and drops the indexes. Since this process physically creates indexes on your database, it may impact performance of other SQL statements.

Tags: Toad for Oracle SQL Optimizer for Oracle Wiki Tutorial

Steve Hilker

Written by Steve Hilker

Steve Hilker was a Product Manager for Quest Software. Steve has over 35 years technical experience spanning application development, system administration, database management and various management positions at several software companies. Steve was the founder of RevealNet, best known for its desktop knowledge bases and unique database tools such as PL/Formatter. RevealNet was acquired by Quest Software in 2001. He's had the pleasure of being the product manager for many of Quest's database tools.

In his private life, Steve enjoys oil painting and living on a small farm in a pre-Civil war manor home in Fredericksburg, Virginia.