New knowledge of Oracle Indexes

A new understanding of Oracle indexes first involves Oracle indexes. Www.2cto.com general index B * tree, BitMap index, function index, composite index, reverse index. The common indexes are described here. B * tree indexes are common indexes. The usage is as follows: SQL code create index idx_a on my_table (a); B * tree composite index usage: SQL code create index idx_a_ B on my_table (a, B ); composite indexes are an extension of common indexes. For each query, CBO selects an index for the query. Therefore, even if multiple single-column indexes are created, only one index is selected for use. Note that the order of the composite index varies greatly. According to the preceding composite index, if this is the case, the SQL code select * from my_table where a = * can use the index idx_a_ B. However, if the SQL code select * from my_table where B = *, the index idx_a_ B is not used. Therefore, when using composite indexes, pay attention to the order before and after. Indexes also occupy tablespaces. As for the amount occupied, you can find the index. SQL code -- (1) Check the number of extents in the INDEX segment: select segment_name, count (*) from dba_extents where segment_type = 'index' and owner = UPPER ('& owner ') -- schema name group by segment_name -- (2) Check the number of blocks in the index segment: select segment_name, sum (blocks) from dba_extents where segment_name = 'sys _ C005736 '-- Object Name group by segment_name; Oracle allocates logical structure space for all data in the database. The unit of the database space is data block, range (extent), and segment (segment ). Oracle data blocks are the minimum storage units used and allocated by Oracle. It is determined by the DB_BLOCK_SIZE set during database creation. Once the database is generated, the size of the data block cannot be changed. To change the database, you must create a new database. (There are some differences in Oracle9i, but this is not covered in this article .) Extent is composed of a group of continuous blocks. One or more extents form a segment. When all space in a segment is used up, Oracle assigns it a new extent.
Segment is composed of one or more extent. It contains all data in a specific logical storage structure in a tablespace. The extent in a segment can be discontinuous or even in different data files. An object can only correspond to one segment of logical storage. We can view the extent in this segment to see the storage of the corresponding object. When you select an appropriate index for a large number of data tables, you can reduce the storage size, costs, and query efficiency.