oracle索引介绍与高性能sql优化（The Oracle index and the high performance SQL optimization）.docVIP

oracle索引介绍与高性能sql优化（The Oracle index and the high performance SQL optimization） The ORACLE index and the high performance SQL optimization.Txt12 missing is a poem to let you in ordinary days to read out the rhythm; missing is a shower, let you dry up the moist days; missing is a sun, so that your days of gloomy clear up. What is an index? An index is a secondary object built on one or more columns of a table to speed up access to data in the table; The data structure of the Oracle storage index is the B* tree, and the bitmap index is the same as the B* index of the leaf nodes; The index consists of the root node, the branch node and the leaf node. The higher index block contains the index data of the lower index block, and the leaf node contains the index data and the ROWID that determines the actual position of the line. The purpose of using indexes Faster query speed Reduce I/O operations Remove disk sorting When to use index The number of records returned by the query Sort table 40% Non sorted table 7% Tables have more fragmentation (frequent addition and deletion) Types of indexes Non unique index (most commonly used) unique index Bitmap index Locally prefixed partitioned index Local prefix free partitioned index Global prefix partitioned index Hash partitioned index Function based index Guidelines for managing indexes Creates an index after inserting data into a table . After inserting or loading data with SQL*Loader or import tools, indexing is more effective; Index the correct tables and columns . Often retrieves rows from 40% or non sorted table 7% in a large table; . In order to improve multi table associations, index columns are used for linking; . The value in the column is relatively unique; . Range of values (large: B* tree index, small: bitmap index); . Type Date columns are generally suitable for function based indexes; . There are many null values in columns that are not suitable for indexing Arrange index columns for performance . Multiple field ret