RDBMS

What is an Index?

An Oracle index works like the index at the end of a textbook: topics in alphabetical order, each followed by a page number, it adds a few pages to the book, and you use it only when it helps.

• An index is used to search for required rows quickly.
• It occupies extra space and is stored separately from the table.
• It holds the values of the key (indexed column) in ascending order, each with the ROWID of its row (like the page number).
• Internally it is stored as a self-balancing (B-)tree, so searching is fast.
• Oracle uses it only when it improves performance – automatically; you never refer to it in a query.

Two purposes: (1) speed up searching (without an index Oracle reads the table from the first row to the end; with one it finds the key in the tree and fetches the row by ROWID) and (2) enforce uniqueness – a UNIQUE index is created automatically for PRIMARY KEY and UNIQUE constraints.

create index students_name_idx on students (name);
create unique index students_name_idx on students (name);
create index batches_ccode_fcode_idx on batches (ccode, fcode);   -- most used column first

create index students_name_idx on students ( upper(name) );       -- function-based (8i)
create index batches_duration_idx on batches ( enddate - stdate );
create index students_name_idx on students (name) online;         -- table not locked

• Function-based index (Oracle8i): an index on an expression; used when the query uses the same expression (e.g. where upper(name) = 'RICHARD MARX'). Requires the QUERY REWRITE system privilege.
• Online indexing (Oracle8i): the table is not locked while the index is built – higher availability, important for Internet databases.
• USER_INDEXES describes your indexes.

When Oracle Does Not Use an Index

Drawbacks of indexes

Indexes speed up queries but slow down DML: with 5 indexes, one INSERT becomes six insertions, and every change to an indexed column changes the index. Many indexes suit tables used mainly for querying. Dropping an index affects only performance, never functionality. (Users can influence index usage with hints.)

Clustering

A cluster stores tables that are often joined together in one area of the disk, so joins are faster. The tables must share a common column – the cluster key – and each key value is stored only once, saving space.

create cluster course_batches (ccode varchar2(5));        -- 1. cluster + key

create index cl_ccode_index on cluster course_batches;    -- 2. cluster index (before any DML)

create table courses ( ccode varchar2(5), name varchar2(30), ... )
cluster course_batches (ccode);                           -- 3. place tables at creation
create table batches ( bcode varchar2(5), ccode varchar2(5), ... )
cluster course_batches (ccode);

A table can be placed in a cluster only when it is created (CLUSTER option of CREATE TABLE). Clustering is transparent to users and applications.

Sequence

A sequence is a database object used by multiple users to generate unique numbers – typically primary keys such as account or employee numbers.

create sequence rollno start with 100 increment by 1;

select rollno.nextval from dual;      -- 100
select rollno.nextval from dual;      -- 101
select rollno.currval from dual;      -- 101 (value of the most recent NEXTVAL)

insert into students values (rollno.nextval, ...);
insert into payments values (rollno.currval, ...);     -- same roll number

alter sequence rollno maxvalue 1000;
drop sequence rollno;

• NEXTVAL returns the next value and increments the sequence; CURRVAL returns the value of the most recent NEXTVAL and cannot be used before NEXTVAL.
• Sequences guarantee uniqueness, not consecutiveness: a number taken by a rolled-back INSERT is lost, leaving a gap.
• ALTER SEQUENCE can change INCREMENT BY, MAXVALUE and MINVALUE.

Pseudo Columns

A pseudo column is used like a table column but is not a column of any table and cannot be modified.

select user, uid from dual;
select rowid, ccode from courses;
select rownum, ccode from courses where rownum <= 5;      -- first five rows
select rownum, ccode from courses where rownum = 5;       -- no rows selected
select rownum, ccode, fee from courses order by fee;      -- ROWNUM assigned before sorting

Chapter 12 Notes (PDF)

Question / Answer (15 questions)

Moderate-level questions based on the Chapter 12 notes (PDF). Click a question to see the answer.

MCQ Test : Check Your Understanding (5 questions)

Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.

Bibliography/References

The chapter notes PDF is the course study material and is credited to its author below. The explanations, questions and MCQs on this page are original study aids written for this course from the topics covered in that PDF. The other works are cited for verification and further reading. Oracle, SQL*Plus and PL/SQL are trademarks of Oracle Corporation; other names belong to their respective owners.

1. Srikanth, P. (n.d.). Oracle For Beginners (chapter notes – the PDF shown on each chapter page). Srikanth Technologies. http://www.srikanthtechnologies.com/
2. Oracle Corporation. (n.d.). Oracle Database SQL Language Reference, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/
3. Oracle Corporation. (n.d.). Oracle Database Concepts, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/cncpt/
4. Oracle Corporation. (n.d.). Oracle Database Administrator’s Guide, 19c. https://docs.oracle.com/en/database/oracle/oracle-database/19/admin/
5. Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill Education.