Problem solve Get help with specific problems with your technologies, process and projects.

Sequences and indexes explained

What is a sequence in a database? Please explain in easy words, as I'm totally new to databases. Also, what are indexes?

A sequence is basically a number counter. It starts at one number and then gets incremented as time goes on. For instance, suppose my application needs to handle my company's invoices. The invoice number is typically started at a specific value (say 100000) and incremented by 1 for each successive invoice. So I want to create a sequence to generate my invoice numbers for me. The following SQL statement will create such a sequence:

CREATE SEQUENCE invoice_num_seq 
Above, you can see the sequence named INVOICE_NUM_SEQ which starts with the number 100,000 and gets incremented by 1 each time.

When my application generates an invoice, it first needs to get the next available invoice number. This is done by querying the sequence's next value psuedo-column called NEXTVAL. This is done by just selecting the next value in a SELECT statement:

SELECT invoice_num_seq.nextval FROM dual;
The sequence automatically determines (by incrementing) which number comes next and returns that value to the application.

By using sequences in this method, I've generated increasing invoice numbers. As long as my application is coded to get the next number in the sequence at the time the invoice is created, each invoice will receive a unique invoice number.

Database indexes help speed up database queries. The easiest way to think about this is to relate it to a book. Let's suppose that you have a car repair manual. And suppose you wanted to look up information on changing your cars oil in that manual. One method is to start at the beginning of the book and read through until the end until you find every page that covers changing oil. Or, you could look in the book's index and find only those pages that cover the topic. The index tells you exactly where to go in the book!

Database indexes are the same. Suppose I have a table which has two columns, ID and NAME. Now I ask you to tell me the name of the person who's id is '1001'. Without an index, you would have to look at every single row to find that name. This is called a full table scan. Now a full table scan isn't a big deal if your table only contains 12 rows of data. But what if it contains 12 million rows? I don't want to read 12 million rows here. So we add an index on the ID column of the table. When I ask for the name of the person who's id is '1001', I look up in the index first. It quickly tells me which row (or rows) contain the required information!

For More Information

  • What do you think about this answer? E-mail the editors at [email protected] with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
  • Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
  • Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle database design and architecture