How Does Oracle create indexes in hundreds of millions of records?

How Does Oracle create an index for the big data database in a hundred million-level record table? 1. in the previous phase, a project was created, and a data in the production system needs to be exported to the backup machine. This table contains nearly 40 fields and 1.4 billion pieces of data. Data Extraction uses an open-source ETL Tool. After comparison, the txt file is first exported and imported to the backup machine at the fastest speed. 2. analyze the problems encountered during data import: Problem 1. Do not create a tablespace automatically. Because the tablespace is large, only one tablespace file is created at the beginning. As a result, the tablespace cannot be automatically expanded after it is fully used. Create multiple tablespace files. The file size cannot exceed 25 GB. The advantage of setting a large tablespace is that the tablespace is continuous in the magnetic standby sector and the query performance is high. Problem 2: parameter adjustment 1. Change pga_aggregate_target = 2048 m when workarea_size_policy = AUTO. For a serial operation, a session can use pga = MIN (5% PGA_AGGREGATE_TARGET, 100 MB), so that the maximum value of pga can be used:

alter system set pga_aggregate_target=2048m;

 

2. use parallel parameters. Four CPUs can be used with parallel 2, which can occupy up to two CPUs. Normally, no problem is found (provided that other applications do not occupy resources with more than two CPUs. nologging must be used to increase the speed. (Reduce a large number of redo logs) 4. PGA, the normal auto pga is only 100 MB at the maximum, apparently not achieving the best performance, you should use manual pga

Alter session set workarea_size_policy = manual; alter session set hash_area_size = 100000; -- hash_area_size is automatically adjusted according to ort_area_size * 2 by default. As a result, sort_area_size cannot exceed 1 GB, if it is set manually, no problem will occur. Alter session set sort_area_size = 2000000000; -- when the system has enough available memory, the maximum size is 2 GB.

 

5. Setting a higher value of db_file_multiblock_read_count also improves I/O performance. Make sure that db_cache_size is large enough to avoid the emergence of free_buffer_wait. 6. Analyze the index after creation. 7. You can monitor the progress through v $ session_longops.