Using Parallelism in Oracle Database 12C

=> Parallelism is a technique used in Oracle database to speed up the processing of data. With parallelism, a single task is divided into smaller subtasks that can be performed concurrently by multiple processes or threads. This means that the work is split up among multiple CPUs, which can significantly reduce the amount of time required to complete the task.

=> In the context of Oracle database, parallelism can be used to speed up the retrieval of data from a database. For example, when querying a large table, the query can be executed using parallelism, with the data being retrieved in parallel by multiple processes. This can make the query run faster in terms of wall-clock time than if only one process were used.

=> Parallelism can also be used to speed up other operations in Oracle database, such as index creation and data loading. However, it is important to note that not all database operations can be parallelized, and that there are some overheads associated with parallelism, such as the need to coordinate the work being performed by multiple processes.

=> To use parallelism in Oracle database, you can use various hints and configuration settings to control the degree of parallelism and the way in which the work is divided among multiple processes.

=> To use parallelism, there are two steps. First, you need to decide how many processes (or "degree of parallelism") you want to use. This tells the database how many processes it should use to retrieve the data. Second, you need to include a hint in the query that tells the database which table(s) to retrieve data from using parallelism and how many processes to use.

=> For example, let's say you want to retrieve data from a table called "EMP". You can include a hint in your query like this: "/*+ parallel(emp,4) /". This tells the database to retrieve data from the "EMP" table using parallelism with 4 processes. If you use an alias for the table in your query (for example, "FROM emp e"), you need to use the alias in the hint as well (like "/+ parallel(e,4) */").

=> Using parallelism means that the work of reading rows from the table is split up among multiple processes. This can make the query run faster in terms of wall-clock time than if only one process were used.

=> You can also omit specifying a degree of parallelism in the hint, and the database will automatically determine how many processes to use based on its initialization parameters. However, this may not always give you the desired degree of parallelism.

=> Sample Code :

SELECT /*+ parallel(emp,4) */ empno, ename FROM emp;

SELECT/*+ parallel(e,4) */ empno, ename FROM emp e;

SELECT/*+ parallel(e) */ empno, ename FROM emp e;

=> An index is a data structure that helps the database retrieve data more quickly. When the database retrieves data using an index, it can sometimes use parallelism to speed up the process.

=> To use parallelism when accessing an index, you need to use a hint called "parallel_index". You can only use this hint when the index is a locally partitioned index. In this case, you can add the "parallel_index" hint to your query to specify how many processes the database should use to retrieve data from the index.

=> For example, let's say you have an index called "emp_i4" on a table called "EMP". You can add the "parallel_index" hint to your query like this: "/*+ parallel_index(emp, emp_i4 ,4) */". This tells the database to use parallelism to retrieve data from the "emp_i4" index on the "EMP" table using 4 processes.

=> There are two arguments to the "parallel_index" hint: the table name and the index name. If you omit the degree of parallelism from the hint, the database will automatically compute the degree of parallelism for the query.

=> If you use aliases for your tables in your query (like "FROM emp e"), you need to use the alias names in your hints as well.

=> Sample Code 

SELECT /*+ parallel_index(emp, emp_i4 ,4) */ empno, ename FROM emp WHERE deptno = 10;