In this segment, we will take a look at the demonstration part of indexing and learn how to create indices. You will understand how it reduces the query time.
So, in the video, you learnt how to create indices. You need to understand the following points while creating indices:
- Compact Keyword: Using this keyword, you can specify the type of indexing that you are going to use for a particular column. For bitmap indexing, you need to use the BITMAP keyword.
- Deferred Rebuild: This is used to defer the process of indexing for the future stage, which means indexing is not active currently. If you want to make the indexing active, then you need to write the following query separately:
alter index i1 on user_info rebuild ;
Now, let’s do a comparative analysis of the time taken to process a query with and without indexing.
Vishwa wrote a query for printing the data of all the male doctors. As you saw in the video, without indexing, it took around 0.174 seconds to process the query, whereas with indexing, it took approximately 0.162 seconds. Therefore, you can conclude that indexing reduces the latency.
In order to create a bitmap index, you need to use the ‘BITMAP’ keyword. To print a list of all the indices that have been created on the table ‘user_info’, you can use the following query:
So, the three indices created on the profession column are named i1, i2 and i3. Out of these, i3 is a bitmap index. Now, let’s take a look at the query time when you are using ‘BITMAP’ indexing instead of ‘COMPACT’ indexing.
Vishwa has written a query to calculate the average ratings of the profession ‘doctor’.
The query is:
Example
select avg(ratings) as avg_ratings from user_info where profession = ‘doctor’ ;Output
By default, the i1 index will be executed, as it was created first. And the query time was 28.121 seconds.
Now, to check the query time in the case of Bitmap indexing, you need to first drop the i1 and i2 indexes. The query to drop the indexes is:
Example
drop index i1 on user_info ;Output
Now, Vishwa ran the same query to calculate the average ratings of the ‘doctor’ profession using Bitmap indexing. And the query time was 29.024 seconds.
Therefore, compact indexing gave the lesser query time on this data set.