Oracle Management Index (VI) Oracle Rebuild Index

(1) Check the index that needs to be rebuilt

Check in the following areas to determine which indexes need to be rebuilt.

First, view the user index in the system table space

To avoid fragmentation of the data dictionary, try to avoid the presence of users ' tables and indexes in the system table space.

Select Index_name from dba_indexes where tablespace_name= ' system ' and ' owner not ' in (' SYS ', ' system ')

Second, make sure that the user's tables and indexes are not in the same table space

The first rule for tables and index objects is to separate tables and indexes. The tables and corresponding indexes are built in different table spaces, preferably on different disks. This avoids many I/O conflicts that occur during data management and querying.

/* Formatted on 2010/6/19 21:00:08 (QP5 v5.115.810.9015) * *

Select I.owner "Owner",

I.index_name "Index",

T.table_name "Table",

I.tablespace_name "Tablespace"

From dba_indexes I, Dba_tables t

WHERE I.owner = T.owner

and i.table_name = T.table_name

and i.tablespace_name = T.tablespace_name

and I.owner not in (' SYS ', ' SYSTEM ')

Third, see what indexes are in the datasheet space

The user's default tablespace should not be the system table space, but the datasheet space. When indexing is established, the index is built into the datasheet space if the corresponding index table space name is not specified. This is a problem that programmers often overlook. When indexing should be in place, explicitly indicate the corresponding index tablespace.

/* Formatted on 2010/6/19 21:05:01 (QP5 v5.115.810.9015) * *

SELECT owner, segment_name, SUM (bytes)

From Dba_segments

WHERE tablespace_name = ' SYSTEM ' and segment_type = ' INDEX '

GROUP by Owner, Segment_name

Four, see which index has been expanded more than 10 times

As the table records increase, the corresponding index increases. If the next extent value of an index is set unreasonably (too small), the extension of the index segment becomes very frequent. There are too many extent in the index, and the speed and efficiency of the retrieval will be reduced.

(1) View the number of index extensions

/* Formatted on 2010/6/19 21:13:41 (QP5 v5.115.810.9015) * *

SELECT COUNT (*),

Owner

Segment_name,

Tablespace_name

From Dba_extents

WHERE segment_type = ' INDEX ' and owner not in (' SYS ', ' SYSTEM ')

GROUP by Owner, Segment_name, Tablespace_name

Having COUNT (*) > 10

ORDER BY COUNT (*) DESC

(2) After you find the index that needs to be rebuilt, you need to determine the size of the index to set reasonable index storage parameters.

/* Formatted on 2010/6/19 21:19:32 (QP5 v5.115.810.9015) * *

SELECT owner "owner",

Segment_name "INDEX",

Tablespace_name "Tablespace",

Bytes "Bytes/count",

SUM (bytes) "Total bytes",

ROUND (SUM (bytes)/(1024 * 1024), 0) "Total M",

Count (bytes) "Total COUNT"

From Dba_extents

WHERE segment_type = ' INDEX '

and Segment_name in (' index_name1 ', ' index_name2 ')

GROUP by Owner,

Segment_name,

Segment_type,

Tablespace_name,

bytes

Order by Owner, Segment_name

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

(3) To determine the index table space and enough space left

Determine which index table space to rebuild the index to. Make sure that the corresponding index table space has enough space left.

/* Formatted on 2010/6/19 21:27:50 (QP5 v5.115.810.9015) * *

SELECT ROUND (Bytes/(1024 * 1024), 2) "Free (M)"

From Sm$ts_free

WHERE tablespace_name = ' Table space name '

(4) Rebuilding index

The following points should be noted when rebuilding an index:

A. If you do not specify a tablespace name, the index is built in the user's default tablespace.

B. If you do not specify nologging, the log will be written, resulting in slower speeds. Because the rebuild of the index is not necessary to restore, you can not write the log.

C. If a resource is busy, it indicates that the process is using the index and waits for a later submission.

ALTER index name Rebuild tablespace Index Table space name storage (initial initial value next extended value) nologging

(5) Check the index

Check for a rebuilt index.

SELECT * from dba_extents where segment_name= "index name"

(6) query according to the index, check whether the index is valid

Use the appropriate where condition for the query to ensure that the index is used. See what happens when you use an index.

SELECT * from dba_ind_columns where index_name= ' index name '

Then, the query is based on the corresponding index entry.

SELECT * from "table name" where ...

(7) Locate the fragmented tablespace and collect its fragments.

Once the index is rebuilt, the original index is deleted, which can result in fragmentation of the table space.

/* Formatted on 2010/6/19 21:40:45 (QP5 v5.115.810.9015) * *

SELECT ' alter tablespace ' | | Tablespace_name | | ' Coalesce; '

From dba_free_space_coalesced

WHERE percent_blocks_coalesced!= 100

To view the size of the index footprint:

Select (sum (bytes)/1024/1024) | | MB ' from dba_segments where segment_name = ' indbilllog5_callend ';

To view the size of a table footprint

Select (sum (bytes)/1024/1024) | | MB ' from dba_segments where segment_name = ' TBILLLOG5 ';