CODEWITHSUNDEEP
oracle-databasemetadata
Doug
Doug

Reputation: 1738

How To Check for an Index in Oracle

I am writing a schema upgrade script for a product that depends on an Oracle database. In one area, I need to create an index on a table - if that index does not already exist. Is there an easy way to check for the existence of an index that I know the name of in an Oracle script?

It would be similar to this in SQL Server: IF NOT EXISTS (SELECT * FROM SYSINDEXES WHERE NAME = 'myIndex') // Then create my myIndex

Upvotes: 20

Views: 76061

Answers (1)

erikkallen
erikkallen

Reputation: 34391

select count(*) from user_indexes where index_name = 'myIndex'

sqlplus won't support IF..., though, so you'll have to use anonymous PL/SQL blocks, which means EXECUTE IMMEDIATE to do DDL.

DECLARE
    i INTEGER;
BEGIN
    SELECT COUNT(*) INTO i FROM user_indexes WHERE index_name = 'MYINDEX';
    IF i = 0 THEN
        EXECUTE IMMEDIATE 'CREATE INDEX myIndex ...';
    END IF;
END;
/

Edit: as pointed out, Oracle stores unquoted object names in all uppercase.

Upvotes: 40

Related Questions