Shown below is a SQL*Plus script that describes just one index. The first part of the script resembles the other scripts shown in this chapter. It looks at the input argument to see whether or not it was qualified by an owner name, defaults it to the current user’s userid if necessary, then stores the results in the substitution variables s_owner_name and s_index_name. Next, a SELECT is done on the ALL_INDEXES table to retrieve information that is not column-specific about the index into a set of substitution variables. These substitution variables are then used to define the page title. So far, nothing has been displayed to the user. Finally, the index columns themselves are selected, causing the page title to print.

If the index does not exist, no columns will be selected and the page title will not be printed. The result will be that there is no output for the user to see. To avoid leaving the user in doubt about why there is no output, an extra SELECT is done to cover this case. This final SELECT statement only succeeds if the index does not exist, and it outputs a message informing the user that is the case.

The last thing the script does is reset anything it changed back to the default values. Here is the script:

--DESCRIPTION
--Displays information about an index. The index name
--is passed as a parameter to this script.
--
--INPUTS
--  param 1   Name of the index to be described.
--
SET ECHO OFF
SET RECSEP OFF
SET NEWPAGE NONE
SET VERIFY OFF
SET PAGESIZE 9999
SET HEADING OFF
SET LINESIZE 80
SET FEEDBACK OFF
CLEAR COMPUTES
CLEAR COLUMNS
CLEAR BREAKS

--Turn off terminal output to avoid spurious blank lines
--caused by the SELECTS that are done only to load the 
--substitution variables.
SET TERMOUT OFF

--Dissect the input argument, and get the owner name and
--table name into two separate substitution variables.
--The owner name defaults to the current user.
DEFINE s_owner_name = ' '
DEFINE s_index_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN index_name NOPRINT NEW_VALUE s_index_name
SELECT
  DECODE(INSTR('&&1','.'),
         0,USER,  /*Default to current user.*/
         UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
  DECODE(INSTR('&&1','.'),
         0,UPPER('&&1'),  /*Only the index name was passed in.*/
         UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) index_name
  FROM dual;

--Retrieve information about the index as a whole.
DEFINE s_table_owner
DEFINE s_table_name 
DEFINE s_index_type
DEFINE s_uniqueness
DEFINE s_tablespace_name
COLUMN table_owner NOPRINT NEW_VALUE s_table_owner
COLUMN table_name NOPRINT NEW_VALUE s_table_name
COLUMN index_type NOPRINT NEW_VALUE s_index_type
COLUMN uniqueness NOPRINT NEW_VALUE s_uniqueness
COLUMN tablespace_name NOPRINT NEW_VALUE s_tablespace_name
SELECT table_owner, table_name, index_type, uniqueness, tablespace_name
  FROM all_indexes
 WHERE owner = '&&s_owner_name' 
   AND index_name = '&&s_index_name';

--Information about the index as a whole is printed in 
--the page title.
TTITLE SKIP 1 LEFT 'INDEX ' &&s_owner_name..&&s_index_name -
       ' ' &&s_index_type ' ' &&s_uniqueness SKIP 1 -
       'DEFINED ON TABLE ' &&s_table_owner..&s_table_name SKIP 1 -
       'STORED IN TABLESPACE ' &&s_tablespace_name SKIP 1 -
       'CONTAINING COLUMNS: '

--List the columns that make up the index.
--The indent column moves the column list over to the
--right so that it comes after the 'CONTAINING COLUMNS:' 
--portion of the header.
SET TERMOUT ON
COLUMN indent FORMAT A19
COLUMN column_name FORMAT A30
SELECT ' ' indent, 
       column_name
  FROM all_ind_columns
 WHERE index_owner = '&&s_owner_name'
   AND index_name = '&&s_index_name'
ORDER BY column_position;

--Spit out an error message if the index does not exist.
TTITLE SKIP 1
SELECT 'Index &&s_owner_name..&&s_table_name does not exist.'
  FROM DUAL
 WHERE NOT EXISTS (
                    SELECT * 
                      FROM all_indexes
                     WHERE owner = '&&s_owner_name'
                       AND index_name = '&&s_index_name'
                  );

--Change all settings back to defaults
CLEAR COLUMNS
CLEAR BREAKS
UNDEFINE s_owner_name
UNDEFINE s_index_name
UNDEFINE s_index_type
UNDEFINE s_uniqueness
UNDEFINE s_tablespace_name
SET PAGESIZE 14
SET HEADING ON
SET NEWPAGE 1
SET FEEDBACK ON

To describe an index, simply invoke SHOW_INDEX and pass the index name as a parameter. Here’s an example:

SQL> @show_index project_hours_pk

INDEX JEFF.PROJECT_HOURS_PK NORMAL UNIQUE
DEFINED ON TABLE JEFF.PROJECT_HOURS
STORED IN TABLESPACE USERS
CONTAINING COLUMNS:
                    PROJECT_ID
                    EMPLOYEE_ID
                    TIME_LOG_DATE

If the specified index does not exist, you will get a message telling you that. For example:

SQL> @show_index this_does_not_exist

Index JEFF.THIS_DOES_NOT_EXIST does not exist.

Next you will see how you can write another script that uses SHOW_INDEX to describe all the indexes on a table.

You now have a script that describes one specific index. If you wanted to describe all the indexes for a particular table, the obvious solution would be to run SHOW_INDEX for each of those indexes. The only thing that makes this at all difficult is that SQL*Plus has no built-in looping construct.

You can’t just write a simple loop to display each index for a table. However, you do have a view, the ALL_INDEXES view, from which you can select the names of all the indexes on a table. Instead of selecting just the index names, it’s not too big a leap to select an expression such as this:

SELECT '@SHOW_INDEX ' || owner || '.' || index_name

Executing a SELECT such as the previous one against the ALL_INDEXES view results in output that looks like this:

@SHOW_INDEX JEFF.PROJECT_HOURS_PK
@SHOW_INDEX JEFF.PROJECT_HOURS_UNIQUE

Using the SPOOL command, you can send this output to a file, which will then contain commands to describe each index on a table. Then you execute that file. The following LIST_INDEXES.SQL script does this:

--DESCRIPTION
--Describe all the indexes that are defined on 
--the specified table.
--
--INPUTS
--  param 1   The table name, optionally qualified by
--            an owner name using the standard dot notation.
--
SET ECHO OFF
SET TERMOUT OFF
SET FEEDBACK OFF
SET VERIFY OFF
CLEAR COLUMNS

--Dissect the input argument, and get the owner name and
--table name into two separate substitution variables.
--The owner name defaults to the current user.
DEFINE s_owner_name = ' '
DEFINE s_table_name = ' '
COLUMN owner_name NOPRINT NEW_VALUE s_owner_name
COLUMN table_name NOPRINT NEW_VALUE s_table_name
SELECT
  DECODE(INSTR('&&1','.'),
         0,USER,  /*Default to current user.*/
         UPPER(SUBSTR('&&1',1,INSTR('&&1','.')-1))) owner_name,
  DECODE(INSTR('&&1','.'),
         0,UPPER('&&1'),  /*Only the table name was passed in.*/
         UPPER(SUBSTR('&&1',INSTR('&&1','.')+1))) table_name
  FROM dual;

--
--Create a file of commands that will use the SHOW_INDEX script
--to describe each index defined on the table.
SET PAGESIZE 0
SET HEADING OFF
SPOOL list_indexes_1.sql

--Generate the show index commands.
SELECT '@SHOW_INDEX ' || owner || '.' || index_name
  FROM all_indexes
 WHERE table_owner = '&&s_owner_name'
   AND table_name = '&&s_table_name';

--Generate an error message if the table does not exist.
SELECT 'PROMPT Table &&s_owner_name..&&s_table_name does not exist.'
  FROM DUAL
 WHERE NOT EXISTS (
                    SELECT * 
                      FROM all_tables
                     WHERE owner = '&&s_owner_name'
                       AND table_name = '&&s_table_name'
                   );

--Generate a message if no indexes at all exist for the table.
SELECT 'PROMPT Table &&s_owner_name..&&s_table_name has no indexes defined.'
  FROM all_tables
 WHERE owner = '&&s_owner_name'
   AND table_name = '&&s_table_name'
   AND NOT EXISTS (
                    SELECT * 
                      FROM all_indexes
                     WHERE table_owner = '&&s_owner_name'
                       AND table_name = '&&s_table_name'
                   );

SPOOL OFF

--Reset everything back to its default.
SET PAGESIZE 14
SET TERMOUT ON
SET FEEDBACK ON
SET VERIFY ON
CLEAR COLUMNS

--Tell the user what she is going to see.
PROMPT
PROMPT INDEXES DEFINED ON TABLE &&s_owner_name..&&s_table_name

--Execute the script that was just created to 
--display all the indexes on the table.
@list_indexes_1

This script builds a file named LIST_INDEXES_1, which runs the SHOW_INDEX script for each index on the specified table. There are two extra SELECTs to cover the cases where the table does not exist, or where there are no indexes defined on the table. This file is then executed in order to display information about each of the table’s indexes.

Here’s what it looks like to run the LIST_INDEXES script on the PROJECT_HOURS table:

SQL> @list_indexes project_hours

INDEXES DEFINED ON TABLE JEFF.PROJECT_HOURS

INDEX JEFF.PROJECT_HOURS_UNIQUE NORMAL UNIQUE
DEFINED ON TABLE JEFF.PROJECT_HOURS
STORED IN TABLESPACE USERS
CONTAINING COLUMNS:
                    PROJECT_ID
                    EMPLOYEE_ID
                    TIME_LOG_DATE
                    HOURS_LOGGED
                    DOLLARS_CHARGED

INDEX JEFF.PROJECT_HOURS_PK NORMAL UNIQUE
DEFINED ON TABLE JEFF.PROJECT_HOURS
STORED IN TABLESPACE USERS
CONTAINING COLUMNS:
                    PROJECT_ID
                    EMPLOYEE_ID
                    TIME_LOG_DATE

As you can see, information about each index on the PROJECT_HOURS table is displayed. The next two examples show the results of running LIST_INDEXES with an invalid table name and for a table that does not have any indexes:

SQL> @list_indexes bad_table_name

INDEXES DEFINED ON TABLE JEFF.BAD_TABLE_NAME
Table JEFF.BAD_TABLE_NAME does not exist.
SQL>
SQL> @list_indexes x

INDEXES DEFINED ON TABLE JEFF.X
Table JEFF.X has no indexes defined.