SQL Step-by-Step (18) Index

Index

Indexes allow the DBMS to access data more quickly. The system creates this internal data structure (that is, the index), which causes the query to search for rows when the query is indexed, so that queries are much faster. This index notifies the DBMS to find a row in the table for a given column index value, which is somewhat like the index of the book that tells you which page you can find for a given word. Now let's create an index for ownerid in the antiqueowners column:

CREATE INDEX oid_idx on Antiqueowners (ownerid);

The following statement is created for the name, so:

CREATE INDEX name_idx on Antiqueowners (Ownerlastname, ownerfirstname);

To delete an index, you can use the drop:

DROP INDEX Oid_idx;

As in the previous tutorial, we can also "drop" (delete) a table. In the second example above, the index is created on two columns.

Some DBMS do not force a primary key, in other words, the uniqueness of the class is not enforced automatically. What does it mean, as if it sounds foggy. Well, for example, if you're inserting another line into the Antiqueowners table, such as this ownerid is 02, some systems can let you do this even if we require that all rows have different values. To avoid two rows having the same value, we have a way to overcome it by creating a unique index on the column, where we need it to be the primary key so that the system does not replicate:

CREATE UNIQUE INDEX oid_idx on Antiqueowners (ownerid);