How to quickly build an index for a large Oracle table

This article mainly introduces "Oracle large table how to quickly build an index". In daily operation, I believe many people have doubts about how to quickly build an index on a large Oracle table. The editor consulted all kinds of data and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubt of "how to quickly build an index in Oracle large table". Next, please follow the editor to study!

-- Note: please back up before making changes

Step 1: show parameter workarea_size_policy

Alter session set workarea_size_policy=manual; / / set manual management pga

Step 2: show parameter sort_area_size

Set up a pga that uses 1G:

Alter session set sort_area_size=1073741824

Step 3: show parameter db_file_multiblock_read_count

Alter session set db_file_multiblock_read_count=128; / / sets multiple chunks to read to 128. that is, io wants him to read as many chunks as possible.

Step 4: create index index1 on table_name (index_field1 [, index_field2]) nologging parallel 4 tablespace xxx_index;-- parallel-depending on the number of CPU, for a single CPU, it is best not to use parallel

Step 5: get rid of parallelism and change the index to write journal alter index xxx noparallel

Alter index xxx logging

Step 6: set up automatic management PGA

Alter session set workarea_size_policy=AUTO

Finally, after the index is established, restore the above changes.

At this point, the study on "how to quickly index large Oracle tables" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!