Oracle change index to zero index

Question

Unfortunately, one column of a huge table has zero half of the data, so when you query

select count(*) from huge_table where half_null_col is null; 

will be a performance error, even if it is already indexed:

 create index half_null_col_idx on huge_table(half_null_col asc); 

There are two questions:

• Oracle 11g should support a constant expression for indexing by null values , but unfortunately I went through the oracle doc but could not find an explicit white paper about this. Please share the link if anyone knows

• how to change the index instead of drop and create again to avoid performance issues.

Answer 1

I currently have at least four options:

• Create a constant expression index ...

   create index half_null_col_idx on huge_table (half_null_col, 1); 

• Create a raster image index in the table. Raster indexes also allow indexing NULL ...

   create bitmap index half_null_col_idx on huge_table (half_null_col); 

• Create an index for NULL-reassigned k-functions based on functions, and use reassigned NULL in your queries instead of a query for NULL ...

   create index half_null_col_idx on huge_table (nvl(half_null_col, '<a value that does not appear in the values of the column>')); select * from huge_table where nvl(half_null_col, '<a value that does not appear in the values of the column>') = '<a value that does not appear in the values of the column>' ; 

• Redistribute the table so that NULL values ​​go to one section and the rest of the values ​​to different sections / sections ...

   create table huge_table_2 partition by list (half_null_col) ( partition pt_nulls values (null), partition pt_others values (default) ) as select * from huge_table; 

---

If you select only count(*) from the table, then your raster index is the best option.

If you want to use full rows of data from a table in another place (in conjunction with another table or for export to CSV or something else), then redistribution may be your best option.

If you cannot / do not want to redistribute the table, and you cannot create a raster image index (for example, due to heavy simultaneous DML activity in the table), then the "constant expression" or "NULL-reassigned-to-something" index may be your best option.

---

To answer your original questions:

• Oracle internally treats them as "functional indexes." Perhaps search for this term.
• You can not. But there is no performance overhead for online indexing + creating afaik. Oracle DB is good enough to take care of the "multitasking" here - create your index with your application, which works almost as fast as before. If you need to recreate the index, just do it. And if you select the option "constant expression", you can first create a new index, and then delete the old one.