Indexes are among the most useful and underutilized components of SQL. The user can create an Oracle index and store values along with their location in it.
Similar to the index at the end of a book, an index enables the user to go straight to the data they are interested in. Indexes are most useful when a user has to find a few rows. Therefore, they can use an index in statements that return a handful of rows – after creating one, of course!
Simple Techniques to Create an Index in Oracle Database
Creating an index is a simple task in MySQL query optimization as you only need to know two things:
- The columns that require indexing
- The name you will give the index
Here’s how to create one:
create index <indexname> on <tablename> ( <col1>, <col2>, <col3>, … <coln> );
Eg. create index cars_colour_metallic on cars (colour);
However, there are a few things to know about indexes before you begin:
- You can place several columns in a single index, which then becomes a composite or compound index.
For instance, in the above example, you could also add the types of cars in the index like this: create index cars_colour_metallic on cars (colour, type);
- The order in which you set columns in the index affects its use by the optimizer.
Next, let’s take a look at two of the most important index types users create in Oracle.
Two Major Index Types – and When to Pick Each
There are several kinds of indexes in the Oracle database that can improve your SQL. However, one of the most significant decisions you’ll have to make is likely to involve choosing between B-trees and bitmaps.
Create Index Oracle: B-tree Versus Bitmap Indexes
B-trees:– Indexes are in balanced B-tree format by default, which means all the leaf nodes are located at the same depth. It takes equal effort (O(log n)) to access any value, and one leaf index entry contains one row of data.
Bitmap:- Bitmaps also store indexed values, but in a completely different manner as compared to B-trees. In it, one value entry is associated with a range of row values. A bitmap has a series of 1s (yes) and 0s (no) to indicate whether any of the range rows contains the value or not.
One major difference between these two index types is that a B-tree doesn’t include null indexed values; a bitmap does. A bitmap can, therefore, answer some statements during MySQL query optimization, such as targeted index searches in which the column has a null value.
Although this won’t work for a B-tree, the user can add a constant at the end of an index to turn it into a composite index.
Bitmaps are also helpful because compressing the bits is simpler, which is why a bitmap index is generally smaller as compared to a B-tree index with identical data.
Why You Need to Keep a Check on the Indexes You Create
With all the benefits an index provides, it is important to create as few of them as possible. This is because you may end up creating one for every specific requirement and forget about them over time. The same goes for other users who may come and go on your team. And no one will have a clue why Brad needed to create that six-column function-based nightmare.
Since you don’t know if the index in question is only used for year-end reporting or never used, you cannot drop an index whenever you want. This can result in awkward situations where a table contains more indexes than columns!
So, if you’re unsure between two excellent indexes and one “good enough” index, it is better to choose the latter. And don’t forget to test!