Create and delete an index on oracle table columns

Suppose we have the following table, and we often execute queries to display the bookmarks belonging to a certain category:

CREATE TABLE bookmark (
    id           NUMBER(10, 0), -- number 10 digits before the decimal and 0 digits after the decimal
    title        VARCHAR2(255 CHAR) NOT NULL, -- String with a maximum length of 255 charachters
    url          VARCHAR2(500 CHAR) UNIQUE NOT NULL, -- holds unique values across the table data
    category     VARCHAR2(500 CHAR) NOT NULL, -- holds unique values across the table data
    is_public    NUMBER(1, 0) NOT NULL, -- plays the role of a boolean '0'-false, '1'-true ,
    created_at   DATE NOT NULL, --  when the entry is created
    PRIMARY KEY ( id )

Then an index on the column category would make the query more performant. To create it, issue the following command (creates by default a B-Tree index):

CREATE INDEX category_i ON bookmark (category);

--confirm its creation by querying the existing index on the table
SELECT * FROM all_indexes WHERE table_name = 'BOOKMARK';

Now, maybe retrieving the latest bookmarks with a category is also a common scenario, so it might be worth creating then an index on multiple columns, in our case category and created_at. The command would look something like the following:

CREATE INDEX category_created_at_i ON bookmark (category, created_at DESC);

-- this would be a perfect fit for the following query
SELECT * FROM bookmark WHERE category='blog' ORDER BY created_at DESC;

To delete the created indexes use the DROP INDEX commands:

DROP INDEX category_i;
DROP INDEX category_created_at_i;

Reference -

Shared with from 👉 Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Subscribe to our newsletter for more code resources and news

Adrian Matei

Adrian Matei
Life force expressing itself as a coding capable human being