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 Codever. 👉 Use the Copy to mine functionality to copy this snippet to your own personal collection and easy manage your code snippets.

Codever is open source on Github ⭐🙏

Subscribe to our newsletter for more code resources and news

Adrian Matei (aka adixchen)

Adrian Matei (aka adixchen)
Life force expressing itself as a coding capable human being

routerLink with query params in Angular html template

routerLink with query params in Angular html template code snippet Continue reading