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
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
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 ⭐🙏