Index type of Oracle database and its Application scenario ___ database

Logically:
Single Column Single-line index
Concatenated multiple rows Index
Unique Unique index
Nonunique Non-Unique index
function-based Function Index
Domain Field Index
In physics:
Partitioned Partitioning Index
nonpartitioned Non-partitioned index
B-tree:
Normal B-Tree
Rever Key Reverse Transformation B-Tree
Bitmap Bitmap Index

1 The most common index type in the B-tree index Oracle database is the B-tree index, which is the B-tree index, named after the computational science structure with the same name. Every time you publish a basic CREATE INDEX statement that has not been further modified, you are creating a B-tree index. There is no intention to delve more deeply into the B-tree index, which can be understood by the users themselves. Basically these indexes store the column values of the index you created and the pointers to the actual data tables that you use to find your own rows. Remember, this means a multiple query, one that queries each node and index's leaf nodes, and then the row itself of the table. This is why the Oracle optimizer in some cases chooses to perform a full table scan without performing an index lookup because a full table scan might actually be quicker to execute. Also note that if your index is created on multiple columns, then the first column (leading column) is very important. Suppose you have a multiple-column index (also known as a cascading index) in which the indexed columns are arranged in columns C to D, and you can do a single query on the C column using the index, but you cannot use the index to do a separate query on the D-row metallurgical line.


2 based on the function index
If you read a lot of rows during a search, or if your index is not very selective, or if you use a column other than the first column in a cascading index, the Oracle database sometimes chooses not to use the index. So what if you want to perform a case-insensitive search? Like the following instruction: WHERE UPPER (first_name) = ' JOHN '.

This also does not use the index on the First_Name field. Why? Because Oracle has to use the upper function on all values of the index, so it's better to do a full table scan. So, many times Oracle creates a function based index for that purpose.
3) Inversion of keyword index
You can also see these inverted keyword indices, and they are also used occasionally. Suppose there is a list of "restaurant a", "Restaurant B", "Restaurant C" and other similar names. This may not be a good example, but the key point is that there are many unique values, but the previous part of the keyword does not change much. Because Oracle simplifies the reverse string before assigning the reverse keyword to b-tree, it may be best to use a reverse keyword index. Such an index might be more balanced, useful, and faster to search.


Oracle also provides a number of more complex index types. Note, however, that you'd better read the relevant documentation and then use the indexes as they each have their own specific scope of application.
1 Bitmap Index (bitmap index)
Suppose there is a column in the database table with a very narrow selectivity, such as the sex column, what type of index should you use? You might consider using a bitmap index for it. Because bitmap indexes are created for columns with very few values. But the factors that need to be considered are more than that. In general, bitmap indexing is useful only if you use bitmap indexes for several different columns with a lower value in the table, because you can use them together to make the column more selective, or you will need to perform a full table scan of the columns. For example, for a gender column, the index can have only two unique values, and any search of the table with this index may return half of the records. Second, these indexes are designed for the data warehouse, so it is assumed that the data will not change much. These indexes cannot be used to satisfy transactional databases or frequently updated databases. It should be said that updating a table on a bitmap index is not efficient at all.
2 Bitmap Connection index (bitmap join index)
Bitmap connection indexes are a step closer than bitmap indexing. These indexes extract the bitmap columns completely from the table data and store them in the index. It is assumed that these column collections must be queried together. Again, this is designed for the Data Warehouse database. In addition to having a WHERE clause at the end of the syntax, the bitmap connection index creates a directive just like create BITMAP index, which creates a bitmap index.
3) Compressed Index
Compressing an index is actually an option for a standard B-tree index. Compressed indexes have fewer leaf nodes, so the total number of I/O and the cache needed are less. All this means that Oracle's optimizer is more likely to use these compressed indexes than to use standard uncompressed indexes. However, these benefits are also costly, and when you access these compressed indexes, you need to consume more CPU to decompress. Also, when you read about how the optimizer uses these indexes and how to choose the appropriate compression level, it becomes obscure. The benefits of different user settings from a compressed index may vary.
4) Descending index (descending index)
This is a special type based on the function index. Descending indexes can significantly optimize the order by x, y, z desc clause queries.
5) partition index (partitioned index)
If you have a partitioned table in your database, you will have a chance to experience several new types of indexes, from global partitioning indexes across all partitions, and local partition indexes that are focused on individual partitions. Here no longer repeat, want to know the details of the relevant documents can be queried.
6 Index Organization table (index organized Table,iot)
This is a new type of table introduced in Oracle 9i. Oracle will use the index of the cascading index and its extended type for all columns in the table. When all the data is loaded into the index structure, the table becomes redundant and you can delete the table itself. This is the Index organization table.
7) cluster index (cluster index)
Basically, a clustered index puts the same columns of multiple tables together and uses a clustered index for that column. This index is less in practical use because there are still a variety of performance issues to be resolved.
8) field index (in domain index)
The domain index is used when we create a user-defined index type (indextype) for user-defined data types (datatype).
9 Hidden Indexes (invisible Index)
This is the new feature introduced in Oracle 11g. The creation process is the same as the standard index, but is not visible to the cost-based optimizer (CBO) after it is created. This allows you to perform large test queries on performance without affecting existing running applications.
10 Virtual Index
This is another tool for testers and developers. A virtual index (no segment space) allows you to test the new index and its impact on the query plan without actually creating an index. For GB tables, building indexes is very resource-intensive and takes a lot of time.
11 Other Index types
Oracle databases also provide many other types of indexes, such as Oracle Text,oracle Spatial, which is used to build indexes on large-character binary objects (CLOB) or other large text data. Interested readers can find out the relevant information themselves.
It's all for the optimizer.
If you've ever had extensive access to MySQL and other databases, you'll find that Oracle is the world's leading database provider, but their databases aren't really easy to use for users. It may be a bit out of the question to mention the optimizer, but the basic feed of the Oracle database is the optimizer, which is a very special kind of seasoning and is becoming more and more delicious. There are a number of books on the subject of Oracle cost Based OPTIMIZER,CBO, which specifically describe the techniques and strategies for analyzing tables and indexes.
For a database, you may need to continually test new queries in addition to constantly updating your statistics. Use the resolution planning mechanism and optimize to reduce total I/O and calculate the amount of data to be sorted and merged, only so you can achieve better performance.
Summarize
Although the Oracle Database index world is a bit scary, in fact, you usually use the index is only a few. And, regardless of how the naysayers are vilified, Oracle's optimizer has been designed quite well; Overall, Oracle is good at making your database run more efficiently. While this doesn't mean you don't need to tune your own SQL, if you keep up with the latest statistics and have Oracle organize the minimum data set you need, it will be able to meet your needs at a very fast rate.