1z0-071 Oracle Database SQL – ADDITIONAL KNOWLEDGE BASE – DATABASE OBJECTS, DBA ARTICLES, SCRIPTS etc

1. INDEXES

Indexes are like indexes at the end of your book. They are used to access data faster. For example, select start from tab one where call one equals to one will scan the entire table to bring the rows where call one equals to one. Instead, if we have an index on that column, it will use the index which stores the value in an order and corresponding pointers to the table to find out the data faster. This way, a full table scan is not needed. Of course, it will be useful if the table is large. However, let me show you how to create an index. Create index tab one idx one on tab one, curl one here tab one idx one is the name of the index that I am giving. It can be any name.

Let’s see if the index is there by querying user indexes. It shows all the indexes owned by this schema, which is HR. Remember, we have connected as HR. Let me do a filter. Select star from user indexes where table name equals to tab one. We have the index. Now if we do select star from Tar tab one where call one equals to one, that should run faster. But however, since the table is very small, it doesn’t make any difference. The purpose of this lesson is just to show you how to create an index. And let’s drop that index. Verify it no indexes for the table. Tab one.