Oracle function-based index

Oracle function-based indexes, such as executing the following SQL statement: select * from emp where upper (ename) = 'King', even if an index is created on the ename, the emp table will be scanned in the entire table, And the ename field in the table will be changed to uppercase and compared with the constant KING. If we create a function-based index, for example, create index emp_upper_idx on emp (upper (ename); at this time, we only need to scan a small part of data by range, then obtain the rowid to access the data in the table, which is faster. A function-based index is similar to a common index, but a common index is created on a column, and it is created on a function. Of course this will have a certain impact on data insertion, because we need to use function compute to generate an index. However, insert data is usually inserted in a small amount, while query data usually has a large amount of data. To optimize the query speed, it is sufficient to slightly reduce the point insertion speed. The function index www.2cto.com also provides a function to index only some rows. Suppose there is a large table with a column named FLAG, which can only be Y and N. Assume that most of the data is Y and a small part is N. We need to change N to Y. If a common index is created, the index will be very large, and the overhead for maintaining this index will be large when N is changed to Y. However, this table sounds more suitable for bitmap indexing, but it is a transaction system (OLTP). Many people may insert or modify records at the same time. Therefore, bitmap indexes are not suitable. Therefore, it would be easier to create an index on a row with a value of N. Create index SQL: create index flag_index on big_table (case flag when 'n' then 'n' end); this index will be greatly reduced, and the maintenance cost will be very low. The premise is that we are only interested in rows with a value of N.