Show indexes in Oracle
One of my students asked how you could show index from table_name;
in Oracle. They were chagrined when I told them there wasn’t an equivalent command. Outside of using Quest’s Toad or Oracle SQL*Developer, you can query the data catalog, like so:
-- SQL*Plus formatting commands. COLUMN index_name FORMAT A32 COLUMN column_position FORMAT 999 HEADING "COLUMN|POSITION" COLUMN column_name FORMAT A32 -- Ordinary query with a substitution variable. SELECT i.index_name , ic.column_position , ic.column_name FROM user_indexes i JOIN user_ind_columns ic ON i.index_name = ic.index_name WHERE i.table_name = UPPER('&input') |
Naturally, this is a subset of what’s returned by the show index from table_name
; syntax. There is much more information in these tables but I only wanted to show an example.
The UPPER
function command ensures that the table name is found in the database. Unless you’ve created a case sensitive object, they’re stored in uppercase strings.
While a single SQL statement works well, a little organization in PL/SQL makes it more readable. A display_indexes
function provides that organization. It only displays normal indexes, not LOB indexes, and it depends on a schema-level collection of strings. This is the user-defined type (UDT) that I used for the collection.
CREATE OR REPLACE TYPE index_table AS TABLE OF VARCHAR2(200); / |
The following is the definition of the function:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 | CREATE OR REPLACE FUNCTION display_indexes ( pv_table_name VARCHAR2 ) RETURN INDEX_TABLE IS -- Declare an iterator for the collection return variable. index_counter NUMBER := 1; column_counter NUMBER; -- Declare and initialize local collection variable as return type. index_desc INDEX_TABLE := index_table(); -- Get indexes. CURSOR index_name (cv_table_name VARCHAR2) IS SELECT i.index_name FROM user_indexes i WHERE i.table_name = cv_table_name AND i.index_type = 'NORMAL' ORDER BY 1; -- Get index columns. CURSOR index_columns (cv_index_name VARCHAR2) IS SELECT ic.column_position , ic.column_name FROM user_ind_columns ic WHERE ic.index_name = cv_index_name ORDER BY 1; BEGIN -- Assign the table name to the collection. index_desc.EXTEND; index_desc(index_counter) := UPPER(pv_table_name); index_counter := index_counter + 1; FOR i IN index_name(UPPER(pv_table_name)) LOOP -- Assign the index name to the collection. index_desc.EXTEND; index_desc(index_counter) := LPAD(i.index_name,2 + LENGTH(i.index_name),' '); -- Set column counter on entry to nested loop. column_counter := 1; FOR j IN index_columns(i.index_name) LOOP IF column_counter = 1 THEN -- Increment the column counter, extend space, and concatenate to string. column_counter := column_counter + 1; index_desc.EXTEND; index_desc(index_counter) := index_desc(index_counter) || '(' || LOWER(j.column_name); ELSE -- Add a subsequent column to the list. index_desc(index_counter) := index_desc(index_counter) || ',' || LOWER(j.column_name); END IF; END LOOP; -- Append a close parenthesis and incredment index counter. index_desc(index_counter) := index_desc(index_counter) || ')'; index_counter := index_counter + 1; END LOOP; -- Return the array. RETURN index_desc; END; / |
You can call the function with this syntax:
SELECT column_value AS "TRANSACTION INDEXES" FROM TABLE(display_indexes('TRANSACTION')); |
It returns the following formatted output for the TRANSACTION
table, which is much nicer than the SQL output. Unfortunately, it will take more effort to place it on par with the show index from table_name;
in MySQL.
TRANSACTION INDEXES ------------------------------------------------------------------------------------------------------------------------------ TRANSACTION PK_TRANSACTION(transaction_id) UQ_TRANSACTION(rental_id,transaction_type,transaction_date,payment_method_type,payment_account_number,transaction_account) |
As always, I hope it helps folks.