A simple example of creating an index for an Oracle data table

WhenOracle data tableWhen there are many records, in order to improve the query efficiency, we oftenCreate an indexIn this way, the query speed can be greatly improved during the query. This article introduces the process of creating an index in the form of an instance. Next let's take a look at this process.

First, check the existing indexes.

 
 

  
  
select index_name from all_indexes where table_name = ‘PICTURE’; 
 
 

Sure enough, the system only built the index for the primary key: SYS_C55001

An error is also made. In general, oracle is case-insensitive, but table_name in the preceding query statement must be in uppercase. If it is written as 'picture ', there is no record.

Again, create an index.

 
 

  
  
create index picture_album_idx on picture aid）;  
  
  
 
  
  
create index picture_user_idx on picture userid）;  
  
  
 
  
  
create index picture_cat_idx on picture cid）; 
 
 

Third, repeat the first step to verify whether the operation is successful.

Fourth, let's see if the speed is faster when you browse the album.

In theory, it should be faster.

 
 

  
  
select index_name from all_indexes where table_name = ‘TB_PUT_APPROVE’;  
  
  
 
  
  
select * from TB_PUT_APPROVE t  
  
  
 
  
  
create index TB_PUT_APPROVE_approveid on TB_PUT_APPROVEapproveid） 
 
 

This article introduces how to create an index for an Oracle data table. I hope this article will help you with some benefits. Thank you!