Understanding Oracle Indexes

Simple Example

Let's create a really basic table and populate it with about 9 million rows of random data.  You can find the PL/SQL script I used at the bottom of this article.

 CREATE TABLE "EMPLOYEE" (	
  "NAME" VARCHAR2(64 BYTE), 
	"TITLE" VARCHAR2(128 BYTE), 
	"EMAIL" VARCHAR2(256 BYTE)
);

With 9 million rows of data, let's do a brute for lookup of of an employee by their name.

select * from employee where name = 'John 1240320967';
All Rows Fetched: 1 in 0.867 seconds

That seems reasonably fast, but what if we add an index to the name column like so? 

CREATE INDEX EMPLOYEE_NAME_INDEX1 ON EMPLOYEE ("NAME");

And try again.

select * from employee where name = 'John 1240320967';
All Rows Fetched: 1 in 0.002 seconds

We can see the table retrieved the results in milliseconds!

Using Explain to Illustrate

Let's see how fast a select is on an index table.

EXPLAIN PLAN FOR
select * from employee where name = 'John 1563943545';
SELECT plan_table_output FROM TABLE(sys.dbms_xplan.display());

Output...

Plan hash value: 3461153608
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |    52 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEE             |     1 |    52 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EMPLOYEE_NAME_INDEX1 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("NAME"='John 1563943545') 

Cool, the cost is nice and low.  What about searching on an unindexed table.

EXPLAIN PLAN FOR
select * from employee where title = 'Manager 1563943545';
SELECT plan_table_output FROM TABLE(sys.dbms_xplan.display());

Output...

Plan hash value: 2119105728
 
------------------------------------------------------------------------------
| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |     1 |    52 | 19942   (1)| 00:04:00 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEE |     1 |    52 | 19942   (1)| 00:04:00 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("TITLE"='Manager 1563943545')

Notice the significant cost increase as well as time for this query.  

How Indexs Work

Applying an index to a column creates a separate table which simply orders the data, and contains a pointer to it, in the original (unsorted) table.  

By default Oracle creates B-tree indexes.  Although there are other types of indexes, most of them are used for special cases.  You can find a list of them here.  This data structure will improve access to a sorted list, by grouping data into branches and leaves.

Doing It Right the First Time

Let's create a CD catalog, notice this time we put a Primary Key on it (which you should always do).  By default this will create an index on the table.  We also added another on album title since were likely to search on this field.  This avoids a full table scan when querying.  

CREATE TABLE ALBUM (
  ALBUM_ID NUMBER NOT NULL,
  PRODUCER VARCHAR2(256), 
  TITLE VARCHAR2(256), 
  CONSTRAINT ALBUM_PK PRIMARY KEY ( ALBUM_ID ) ENABLE 
);
CREATE INDEX ALBUM_TITLE_INDEX1 ON ALBUM (TITLE);
CREATE SEQUENCE ALBUM_SEQUENCE;

Take a look at the objects created from this.

Now let's create the song table.

CREATE TABLE SONG (
  SONG_ID NUMBER NOT NULL, 
  ALBUM_ID NUMBER, 
  TITLE VARCHAR2(256), 
  LENGTH NUMBER, 
  CONSTRAINT SONG_PK PRIMARY KEY ( SONG_ID )ENABLE 
);
CREATE INDEX SONG_TITLE_INDEX1 ON SONG (TITLE);
CREATE SEQUENCE SONG_SEQUENCE;

Forgien Keys

Forgien keys define a relationship between parent and child.  The child table contains the FK which links to a PK on the parent.  Let's make song a child to album.  

ALTER TABLE SONG
ADD CONSTRAINT SONG_ALBUM_FK1
FOREIGN KEY (ALBUM_ID)
REFERENCES ALBUM (ALBUM_ID)
ENABLE;

Foreign keys are important because the enforce the integrity of your data.  Not only that, they also describe how your data relates to each other.  This makes describing your data model much easier.  Think of the poor developers who need to understand our work 5 years from now.

Creating and ERD with SQL Developer

Now that your FK are setup properly, let's visualize it.

File > Data Modeler > Import > Data Dictionary > (Select your connection and tables)

 

Other 

Here's the PL/SQL script I used to populate this table with data.

declare

v_id  number := 1 ;
v_name varchar2(64) ;
v_title varchar2(128);
v_email varchar2(128);
v_rand number := 1;

begin

while (v_id < 9000000)

loop

v_rand := abs(dbms_random.random);

--dbms_output.put_line('creating emp:' || v_rand);

v_name    := 'John ' || v_rand;
v_title  := 'Manager ' || v_rand;
v_email     := v_rand || '@asdf.com';

INSERT INTO EMPLOYEE
(NAME,TITLE,EMAIL)
 values(v_name,v_title,v_email);

v_id := v_id +1;

end loop;

commit;

end; 

/

 

---