When a table grows in size, it's very difficult and time-consuming to find the data we need by scanning the entire table data.
The well-known solution to this problem is indexing. We can build an index, which is a particular storage structure, to identify quickly where data is stored in the table. In the real world, indexes are often used, for example, in a book like this, so we are accustomed to using them.
In this recipe, we will see how indexes work and when to use them, and we'll also avoid over-indexing; we will introduce the B-tree indexes, and then look at other types of indexes and more details on their use.
In this recipe, we will use the CUSTOMERS table of SH schema. There are more than 55000 rows in the table. We will create several indexes on this table, and after the creation of each inde we will execute the following queries (we will call them TEST CASE onwards):
SET AUTOT TRACE EXP SELECT CUST_FIRST_NAME, CUST_LAST_NAME, CUST_YEAR_OF_BIRTH...
