Oracle SQL Script to Retrieve Index Information - with Description and Sample Output

advertisements

_____________________________________________________________________________________________________________________

1. Query to list all indexes on a specific table or schema.

This script lists all indexes on a specific table. It queries the all_indexes data dictionary view based on the table name and displays the index name, table name, uniqueness (UNIQUE or NONUNIQUE), and status (VALID or INVALID) of each index.

-- List all indexes on a specific table
SELECT index_name, table_name, uniqueness, status
FROM all_indexes
WHERE table_name = 'YOUR_TABLE_NAME';

-- Sample Output:
-- INDEX_NAME         TABLE_NAME     UNIQUENESS  STATUS
-- -----------------  -------------  ----------  -------
-- IDX_CUSTOMER_ID    CUSTOMERS      NONUNIQUE   VALID
-- IDX_ORDER_ID       ORDERS         UNIQUE      VALID

advertisements

 

2. Query to display the columns included in an index.

This script displays the columns included in a specific index. It queries the all_ind_columns data dictionary view based on the table name and index name, and returns the index name, column name, and column position of each column in the index.

-- Display columns included in an index
SELECT index_name, column_name, column_position
FROM all_ind_columns
WHERE table_name = 'YOUR_TABLE_NAME' AND index_name = 'YOUR_INDEX_NAME'
ORDER BY column_position;

-- Sample Output:
-- INDEX_NAME         COLUMN_NAME  COLUMN_POSITION
-- -----------------  -----------  ----------------
-- IDX_CUSTOMER_ID    CUSTOMER_ID   1
-- IDX_CUSTOMER_ID    LAST_NAME     2
-- IDX_CUSTOMER_ID    FIRST_NAME    3

3. Query to show the index size and storage parameters.

This script shows the size and storage parameters of a specific index. It queries the all_indexes data dictionary view based on the table name and index name, and retrieves information such as the index name, table name, number of leaf blocks, number of rows, index size in bytes, compression status, and tablespace name.

Please replace 'YOUR_TABLE_NAME' and 'YOUR_INDEX_NAME' with the actual names of the table and index you want to query in the scripts. Adjust the queries as needed for schema-specific information or additional details.

-- Show index size and storage parameters
SELECT index_name, table_name, leaf_blocks, num_rows, bytes, compression, tablespace_name
FROM all_indexes
WHERE table_name = 'YOUR_TABLE_NAME' AND index_name = 'YOUR_INDEX_NAME';

-- Sample Output:
-- INDEX_NAME         TABLE_NAME     LEAF_BLOCKS  NUM_ROWS  BYTES     COMPRESSION  TABLESPACE_NAME
-- -----------------  -------------  ------------ --------- --------- ------------ ------------------
-- IDX_CUSTOMER_ID    CUSTOMERS      100          1000      32768     ENABLED      USERS

_____________________________________________________________________________________________________________________

Website Stats