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 - https://blogs.oracle.com/sql/how-to-create-and-use-indexes-in-oracle-database
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 ⭐🙏
