Managing hints
----------------------------
Metalink Note:221970.1
Some of the reasons to place hints in SQL are to change the access path to the database, change the join order or join type for queries that are doing joins, hints for DML, and hints for data warehouse–specific operations, to name a few.
Place your hint into the statement using the /*+ hint */ syntax—for example:
Specify the NO_INDEX hint to let the optimizer disallow the use of a certain index:
SQL> select /*+ NO_INDEX(employees emp_emp_id) */employee_id
from employees
where employee_id >200;
If you just specify the NO_INDEX hint without listing any indexes, the optimizer will ignore all indexes on the table you specify.
You can also explicitly state the name of the index you wish to bypass:
select /*+ no_index(emp emp_i2) */ empno, ename, deptno
from emp
where deptno = 1;
Be sure to leave a space following the plus sign. The /*+ sequence is exactly three characters long,
with no spaces.
It is good practice
in using hints to be as specific as possible when instructing the optimizer what to do.
Forcing index:
----
SQL> select /*+ index (employees emp_dept_idx) +/
employee_id, department_id from employees;
where department_id > 50;
You can also specify the INDEX hint without specifying an index, as shown here:
SQL> select /*+ index (employees) +/
employee_id, department_id from employees;
where department_id > 50;
Since the INDEX hint in this example doesn't specify an index to use, the optimizer has the leeway to
select the best index—the index resulting in the least cost.
If you want to specify more than one index within the INDEX hint, Oracle recommends that you
specify the INDEX_COMBINE hint rather than the INDEX hint. You specify the INDEX_COMBINE hint in the
following way:
SQL> select /*+ index_combine(e emp_manager_ix emp_department_ix) */ *
from employees e
where manager_id = 108
or department_id=110;
In this case, the optimizer will use the combination of the two indexes you specify that has the lowest
cost. If you specify the INDEX_COMBINE hint without providing a list of indexes, the optimizer will use the
best combination of indexes based on its cost estimates.
Use the FULL hint to instruct the optimizer to choose a full table scan instead of an index scan.
SELECT /*+ full(emp) */ * FROM emp;
The foregoing hint instructs the optimizer to bypass the use of any possible indexes on the EMP table,
and simply scan the entire table in order to retrieve the data for the query.
OR
SELECT /*+ FULL */ col1,col2....
FROM SMALL
WHERE ID = 2;
OR
select /*+ FULL(emp) */ empno, ename, deptno
from emp
where deptno = 1;
In this example, if there were an index on the DeptNo column, a full table scan would be performed.
it has the same effect as the
FULL hint:
select /*+ FULL(emp) */ empno, ename, deptno
from emp
where deptno = 1;
For example, if we want to get the names of all employees that received a commission, our query would look like this:
SELECT ename, comm FROM emp
WHERE comm > 0;
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
As noted in the explain plan, no index is used. We happen to know there is a composite index on the
SAL and COMM columns of our EMP table. We can add a hint to use this index to gain the benefit of having
an index on the COMM column, even though it is not the leading column of the index:
SELECT /*+ index_ss(emp emp_i3) */ ename, comm FROM emp
WHERE comm > 0;
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX SKIP SCAN | EMP_I3 |
----------------------------------------------
The FULL hint specifies that we want Oracle to perform a full table scan. A hint can be applied to SELECT, INSERT, UPDATE and DELETE statements. They are specified by placing the hint immediately after the SELECT, INSERT, UPDATE or DELETE statement in a comment. The comment must have a + (plus) sign immediately following the open comment indicator.
For example....
SELECT /*+ FULL */ *
SELECT /*+ ORDERED */ *
SELECT --+ INDEX( .. )
SELECT /*+ leading(dept, emp) */ ename, deptno
FROM emp JOIN dept USING(deptno);
SELECT /*+ use_nl(emp, dept) */ ename, dname
FROM emp JOIN dept USING (deptno); --To invoke a nested loops join, use the USE_NL hint
To invoke a hash join, use the USE_HASH hint, and place both tables needing the join within parentheses
inside the USE_HASH hint:
SELECT /*+ use_hash(emp_all, dept) */ ename, dname
FROM emp_all JOIN dept USING (deptno);
Join Methods and Their Hints
----------------------------
Nested loops USE_NL /NO_USE_NL /USE_NL_WITH_INDEX
Nested loops joins are efficient when processing a small
number of rows. The optimizer chooses a driving table, which
is the “outer” table in the join. For each row in the outer table,
each row in the inner table is searched.
Hash USE_HASH /NO_USE_HASH
Hash joins are efficient when processing a large number of
rows. Hash joins are used only for equijoins.
Sort merge USE_MERGE /NO_USE_MERGE
A sort merge join is ideal for pre-sorted rows and full table
scans. The sort merge join is used for non-equality joins. Both
tables are sorted on the join key, and then merged. It outperforms
nested loops joins for large sets of rows.
SQL> show parameter optimizer_mode
NAME TYPE VALUE
-------------------- -------------------- --------------------
optimizer_mode string ALL_ROWS
SELECT /*+ first_rows */ *
FROM employees NATURAL JOIN departments;
If we needed the reverse situation, and the database’s default optimizer_mode was set to FIRST_ROWS,
we can supply an ALL_ROWS hint to tell the optimizer to use that mode when determining the execution
plan:
SQL> alter system set optimizer_mode=first_rows scope=both;
SELECT /*+ all_rows */ *
FROM employees NATURAL JOIN departments;
Maximizing Data Loading Speeds
------------------------------
Use a combination of the following two features to maximize the speed of insert statements:
- Set the table’s logging attribute to NOLOGGING; this minimizes the generation redo for direct path operations (this feature has no effect on regular DML operations).(Use it when there is no DATAGUARD configured)
The NOLOGGING feature never affects redo generation for regular INSERT, UPDATE, MERGE, and DELETE statements.
If you’re using RMAN to back up your database, you can report on unrecoverable datafiles via the REPORT UNRECOVERABLE command.
SQL>SELECT
TABLE_NAME
,LOGGING
FROM USER_TABLES
WHERE TABLE_NAME = 'F_REGS';
SQL> ALTER TABLE F_REGS NOLOGGING;
You are doing a DML INSERT statement, and it is performing slower than needed. You want to optimize
the INSERT statement to use a direct-path insert technique.
- Use a direct path loading feature, such as the following:
- INSERT /*+ APPEND */ on queries that use a subquery for determining which records are inserted.
- INSERT /*+ APPEND_VALUES */ on queries that use a VALUES clause
- CREATE TABLE…AS SELECT
The APPEND hint works with an INSERT statement only with a subquery; it does not work with an
INSERT statement with a VALUES clause. For that, you need to use the APPEND_VALUES hint.
SQL>INSERT /*+ APPEND */ INTO F_REGS
SELECT * FROM REG_MASTER;
This is appropriate for when you need to copy a
large volume of rows between tables. By bypassing the Oracle database buffer cache blocks and
appending the data directly to the segment above the high water mark, it saves significant overhead.
This is a very popular method for inserting rows into a table very quickly.
When you specify one of these hints, Oracle will perform a direct-path insert. In a direct-path insert,
the data is appended at the end of a table, rather than using free space that is found within current
allocated blocks for that table.
INSERT /*+ append_values */ INTO emp_dept
VALUES (15867234,'Smith, JR','Sales',1359,'2010-01-01',200,5,20);
If you want to bypass performing direct-path operations, you can use the NOAPPEND hint.
Enabling Query Rewrite:
You have materialized views in your database environment, and want to have queries that access the
source tables that make up the materialized views go against the materialized views directly to retrieve
the results.
The REWRITE hint can be used to direct the optimizer to use a materialized view. The materialized view
must have query rewrite enabled, and statistics for the materialized view and the associated objects
should be current to increase the likelihood for a query to be rewritten. See the following example:
SELECT /*+ rewrite(dept_sal_mv) */ department_id,
sum(nvl(salary+(salary*commission_pct),salary)) total_compensation
FROM employees
GROUP BY department_id
having sum(nvl(salary+(salary*commission_pct),salary)) > 10000
ORDER by 2;
We can see here that the optimizer used the materialized view in the execution plan, rather than
processing the entire query and recalculating the summary.
Conversely, you can also use a NOREWRITE hint if, for some reason, you do not want the optimizer to
use the materialized view. One possible reason is that the data in the materialized view is stale compared
to the source table(s), and you want to ensure you are getting the most current data. Here we can see
that the optimizer bypassed the use of the materialized view and resummarized the data directly from
the EMPLOYEES table:
SELECT /*+ norewrite */ department_id,
sum(nvl(salary+(salary*commission_pct),salary)) total_compensation
FROM employees
GROUP BY department_id
having sum(nvl(salary+(salary*commission_pct),salary)) > 10000
ORDER by 2;
Enabling Parallelism for a Specific Query
-----
You have a slow-running query accessing data from a large table. You want to see if you can speed up the
query by instructing Oracle to use multiple processes to retrieve the data.
SELECT /*+ parallel(emp,4) */ empno, ename
FROM emp;
The hints in the preceding two queries result in four processes dividing the work of reading rows
from the EMP table. Four processes working together will get the job done faster in terms of wall-clock
time than one process doing all the work by itself.
Parallel Hints for Indexes:
Specify the parallel_index hint to control parallel access to indexes. You can generally access an index
in parallel only when the index is a locally partitioned index. In that case, you can apply the
parallel_index hint. Here’s an example:
SELECT /*+ parallel_index(emp, emp_i4 ,4) */ empno, ename
FROM emp
WHERE deptno = 10;
If parallelism isn’t being used, you can insert the parallel hint, and then rerun the explain plan to
verify that the optimizer will use parallelism in the execution plan—for example:
select /*+ parallel(emp,4) */ * from emp;
As of Oracle 11g Release 2, the NOPARALLEL and NOPARALLEL_INDEX hints have been deprecated.
Instead, use NO_PARALLEL and NO_PARALLEL_INDEX.
In the following example, we’ve specified a DOP of 4 on the EMP
table:
CREATE TABLE EMP
(
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
)
PARALLEL(DEGREE 4);
By placing a static DOP of 4 on the table, any user accessing the EMP table will get a DOP of 4 for each
query executed.
select * from emp;
CREATE TABLE EMP_COPY
PARALLEL(DEGREE 4)
AS
SELECT * FROM EMP;
The following example alters the default DOP for a table:
ALTER TABLE EMP
PARALLEL(DEGREE 4);
If, after a time, you wish to reset the DOP on your table, you can also do that with the ALTER
statement. See the following two examples on how to reset the DOP for a table:
ALTER TABLE EMP
PARALLEL(DEGREE 1);
OR
ALTER TABLE EMP
NOPARALLEL;
The following example shows how to
change the default DOP for an index:
ALTER INDEX EMP_I1
PARALLEL(DEGREE 4);
As with tables, you can reset the DOP on an index either of the following two ways:
ALTER INDEX EMP_I4
PARALLEL(DEGREE 1);
ALTER INDEX EMP_I4
NOPARALLEL;
Implementing Parallel DML:
You want to induce parallelism when performing DML operations (INSERT, UPDATE, MERGE, DELETE) in
order to speed performance and reduce transaction time.
Parallel DML is disabled by default on a database, and must be
explicitly enabled with the following statement:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT /*+ PARALLEL(DEPT_COPY,4) */ * FROM DEPT_COPY;
INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT * FROM DEPT_COPY;
In order to take full advantage of parallel capabilities, try to parallelize all portions of a statement. If
you parallelize the INSERT but not the SELECT, the SELECT portion becomes a bottleneck for the INSERT,
and the INSERT performance suffers.
UPDATE /*+ PARALLEL(EMP,4) */ EMP
SET SAL = SAL*1.01
WHERE DEPTNO=10;
ALTER INDEX EMP_COPY_I1
REBUILD
PARALLEL(DEGREE 4);
If you need to rebuild a partition of a large local index, you can also use parallelism to perform this
operation. See the following example:
ALTER INDEX emppart_i1
REBUILD PARTITION emppart2001_p
PARALLEL(DEGREE 4);
----------------------------
Metalink Note:221970.1
Some of the reasons to place hints in SQL are to change the access path to the database, change the join order or join type for queries that are doing joins, hints for DML, and hints for data warehouse–specific operations, to name a few.
Place your hint into the statement using the /*+ hint */ syntax—for example:
Specify the NO_INDEX hint to let the optimizer disallow the use of a certain index:
SQL> select /*+ NO_INDEX(employees emp_emp_id) */employee_id
from employees
where employee_id >200;
If you just specify the NO_INDEX hint without listing any indexes, the optimizer will ignore all indexes on the table you specify.
You can also explicitly state the name of the index you wish to bypass:
select /*+ no_index(emp emp_i2) */ empno, ename, deptno
from emp
where deptno = 1;
Be sure to leave a space following the plus sign. The /*+ sequence is exactly three characters long,
with no spaces.
It is good practice
in using hints to be as specific as possible when instructing the optimizer what to do.
Forcing index:
----
SQL> select /*+ index (employees emp_dept_idx) +/
employee_id, department_id from employees;
where department_id > 50;
You can also specify the INDEX hint without specifying an index, as shown here:
SQL> select /*+ index (employees) +/
employee_id, department_id from employees;
where department_id > 50;
Since the INDEX hint in this example doesn't specify an index to use, the optimizer has the leeway to
select the best index—the index resulting in the least cost.
If you want to specify more than one index within the INDEX hint, Oracle recommends that you
specify the INDEX_COMBINE hint rather than the INDEX hint. You specify the INDEX_COMBINE hint in the
following way:
SQL> select /*+ index_combine(e emp_manager_ix emp_department_ix) */ *
from employees e
where manager_id = 108
or department_id=110;
In this case, the optimizer will use the combination of the two indexes you specify that has the lowest
cost. If you specify the INDEX_COMBINE hint without providing a list of indexes, the optimizer will use the
best combination of indexes based on its cost estimates.
Use the FULL hint to instruct the optimizer to choose a full table scan instead of an index scan.
SELECT /*+ full(emp) */ * FROM emp;
The foregoing hint instructs the optimizer to bypass the use of any possible indexes on the EMP table,
and simply scan the entire table in order to retrieve the data for the query.
OR
SELECT /*+ FULL */ col1,col2....
FROM SMALL
WHERE ID = 2;
OR
select /*+ FULL(emp) */ empno, ename, deptno
from emp
where deptno = 1;
In this example, if there were an index on the DeptNo column, a full table scan would be performed.
it has the same effect as the
FULL hint:
select /*+ FULL(emp) */ empno, ename, deptno
from emp
where deptno = 1;
For example, if we want to get the names of all employees that received a commission, our query would look like this:
SELECT ename, comm FROM emp
WHERE comm > 0;
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| EMP |
----------------------------------
As noted in the explain plan, no index is used. We happen to know there is a composite index on the
SAL and COMM columns of our EMP table. We can add a hint to use this index to gain the benefit of having
an index on the COMM column, even though it is not the leading column of the index:
SELECT /*+ index_ss(emp emp_i3) */ ename, comm FROM emp
WHERE comm > 0;
----------------------------------------------
| Id | Operation | Name |
----------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP |
| 2 | INDEX SKIP SCAN | EMP_I3 |
----------------------------------------------
The FULL hint specifies that we want Oracle to perform a full table scan. A hint can be applied to SELECT, INSERT, UPDATE and DELETE statements. They are specified by placing the hint immediately after the SELECT, INSERT, UPDATE or DELETE statement in a comment. The comment must have a + (plus) sign immediately following the open comment indicator.
For example....
SELECT /*+ FULL */ *
SELECT /*+ ORDERED */ *
SELECT --+ INDEX( .. )
SELECT /*+ leading(dept, emp) */ ename, deptno
FROM emp JOIN dept USING(deptno);
SELECT /*+ use_nl(emp, dept) */ ename, dname
FROM emp JOIN dept USING (deptno); --To invoke a nested loops join, use the USE_NL hint
To invoke a hash join, use the USE_HASH hint, and place both tables needing the join within parentheses
inside the USE_HASH hint:
SELECT /*+ use_hash(emp_all, dept) */ ename, dname
FROM emp_all JOIN dept USING (deptno);
Join Methods and Their Hints
----------------------------
Nested loops USE_NL /NO_USE_NL /USE_NL_WITH_INDEX
Nested loops joins are efficient when processing a small
number of rows. The optimizer chooses a driving table, which
is the “outer” table in the join. For each row in the outer table,
each row in the inner table is searched.
Hash USE_HASH /NO_USE_HASH
Hash joins are efficient when processing a large number of
rows. Hash joins are used only for equijoins.
Sort merge USE_MERGE /NO_USE_MERGE
A sort merge join is ideal for pre-sorted rows and full table
scans. The sort merge join is used for non-equality joins. Both
tables are sorted on the join key, and then merged. It outperforms
nested loops joins for large sets of rows.
SQL> show parameter optimizer_mode
NAME TYPE VALUE
-------------------- -------------------- --------------------
optimizer_mode string ALL_ROWS
SELECT /*+ first_rows */ *
FROM employees NATURAL JOIN departments;
If we needed the reverse situation, and the database’s default optimizer_mode was set to FIRST_ROWS,
we can supply an ALL_ROWS hint to tell the optimizer to use that mode when determining the execution
plan:
SQL> alter system set optimizer_mode=first_rows scope=both;
SELECT /*+ all_rows */ *
FROM employees NATURAL JOIN departments;
Maximizing Data Loading Speeds
------------------------------
Use a combination of the following two features to maximize the speed of insert statements:
- Set the table’s logging attribute to NOLOGGING; this minimizes the generation redo for direct path operations (this feature has no effect on regular DML operations).(Use it when there is no DATAGUARD configured)
The NOLOGGING feature never affects redo generation for regular INSERT, UPDATE, MERGE, and DELETE statements.
If you’re using RMAN to back up your database, you can report on unrecoverable datafiles via the REPORT UNRECOVERABLE command.
SQL>SELECT
TABLE_NAME
,LOGGING
FROM USER_TABLES
WHERE TABLE_NAME = 'F_REGS';
SQL> ALTER TABLE F_REGS NOLOGGING;
You are doing a DML INSERT statement, and it is performing slower than needed. You want to optimize
the INSERT statement to use a direct-path insert technique.
- Use a direct path loading feature, such as the following:
- INSERT /*+ APPEND */ on queries that use a subquery for determining which records are inserted.
- INSERT /*+ APPEND_VALUES */ on queries that use a VALUES clause
- CREATE TABLE…AS SELECT
The APPEND hint works with an INSERT statement only with a subquery; it does not work with an
INSERT statement with a VALUES clause. For that, you need to use the APPEND_VALUES hint.
SQL>INSERT /*+ APPEND */ INTO F_REGS
SELECT * FROM REG_MASTER;
This is appropriate for when you need to copy a
large volume of rows between tables. By bypassing the Oracle database buffer cache blocks and
appending the data directly to the segment above the high water mark, it saves significant overhead.
This is a very popular method for inserting rows into a table very quickly.
When you specify one of these hints, Oracle will perform a direct-path insert. In a direct-path insert,
the data is appended at the end of a table, rather than using free space that is found within current
allocated blocks for that table.
INSERT /*+ append_values */ INTO emp_dept
VALUES (15867234,'Smith, JR','Sales',1359,'2010-01-01',200,5,20);
If you want to bypass performing direct-path operations, you can use the NOAPPEND hint.
Enabling Query Rewrite:
You have materialized views in your database environment, and want to have queries that access the
source tables that make up the materialized views go against the materialized views directly to retrieve
the results.
The REWRITE hint can be used to direct the optimizer to use a materialized view. The materialized view
must have query rewrite enabled, and statistics for the materialized view and the associated objects
should be current to increase the likelihood for a query to be rewritten. See the following example:
SELECT /*+ rewrite(dept_sal_mv) */ department_id,
sum(nvl(salary+(salary*commission_pct),salary)) total_compensation
FROM employees
GROUP BY department_id
having sum(nvl(salary+(salary*commission_pct),salary)) > 10000
ORDER by 2;
We can see here that the optimizer used the materialized view in the execution plan, rather than
processing the entire query and recalculating the summary.
Conversely, you can also use a NOREWRITE hint if, for some reason, you do not want the optimizer to
use the materialized view. One possible reason is that the data in the materialized view is stale compared
to the source table(s), and you want to ensure you are getting the most current data. Here we can see
that the optimizer bypassed the use of the materialized view and resummarized the data directly from
the EMPLOYEES table:
SELECT /*+ norewrite */ department_id,
sum(nvl(salary+(salary*commission_pct),salary)) total_compensation
FROM employees
GROUP BY department_id
having sum(nvl(salary+(salary*commission_pct),salary)) > 10000
ORDER by 2;
Enabling Parallelism for a Specific Query
-----
You have a slow-running query accessing data from a large table. You want to see if you can speed up the
query by instructing Oracle to use multiple processes to retrieve the data.
SELECT /*+ parallel(emp,4) */ empno, ename
FROM emp;
The hints in the preceding two queries result in four processes dividing the work of reading rows
from the EMP table. Four processes working together will get the job done faster in terms of wall-clock
time than one process doing all the work by itself.
Parallel Hints for Indexes:
Specify the parallel_index hint to control parallel access to indexes. You can generally access an index
in parallel only when the index is a locally partitioned index. In that case, you can apply the
parallel_index hint. Here’s an example:
SELECT /*+ parallel_index(emp, emp_i4 ,4) */ empno, ename
FROM emp
WHERE deptno = 10;
If parallelism isn’t being used, you can insert the parallel hint, and then rerun the explain plan to
verify that the optimizer will use parallelism in the execution plan—for example:
select /*+ parallel(emp,4) */ * from emp;
As of Oracle 11g Release 2, the NOPARALLEL and NOPARALLEL_INDEX hints have been deprecated.
Instead, use NO_PARALLEL and NO_PARALLEL_INDEX.
In the following example, we’ve specified a DOP of 4 on the EMP
table:
CREATE TABLE EMP
(
EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT
)
PARALLEL(DEGREE 4);
By placing a static DOP of 4 on the table, any user accessing the EMP table will get a DOP of 4 for each
query executed.
select * from emp;
CREATE TABLE EMP_COPY
PARALLEL(DEGREE 4)
AS
SELECT * FROM EMP;
The following example alters the default DOP for a table:
ALTER TABLE EMP
PARALLEL(DEGREE 4);
If, after a time, you wish to reset the DOP on your table, you can also do that with the ALTER
statement. See the following two examples on how to reset the DOP for a table:
ALTER TABLE EMP
PARALLEL(DEGREE 1);
OR
ALTER TABLE EMP
NOPARALLEL;
The following example shows how to
change the default DOP for an index:
ALTER INDEX EMP_I1
PARALLEL(DEGREE 4);
As with tables, you can reset the DOP on an index either of the following two ways:
ALTER INDEX EMP_I4
PARALLEL(DEGREE 1);
ALTER INDEX EMP_I4
NOPARALLEL;
Implementing Parallel DML:
You want to induce parallelism when performing DML operations (INSERT, UPDATE, MERGE, DELETE) in
order to speed performance and reduce transaction time.
Parallel DML is disabled by default on a database, and must be
explicitly enabled with the following statement:
ALTER SESSION ENABLE PARALLEL DML;
INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT /*+ PARALLEL(DEPT_COPY,4) */ * FROM DEPT_COPY;
INSERT /*+ PARALLEL(DEPT,4) */ INTO DEPT
SELECT * FROM DEPT_COPY;
In order to take full advantage of parallel capabilities, try to parallelize all portions of a statement. If
you parallelize the INSERT but not the SELECT, the SELECT portion becomes a bottleneck for the INSERT,
and the INSERT performance suffers.
UPDATE /*+ PARALLEL(EMP,4) */ EMP
SET SAL = SAL*1.01
WHERE DEPTNO=10;
ALTER INDEX EMP_COPY_I1
REBUILD
PARALLEL(DEGREE 4);
If you need to rebuild a partition of a large local index, you can also use parallelism to perform this
operation. See the following example:
ALTER INDEX emppart_i1
REBUILD PARTITION emppart2001_p
PARALLEL(DEGREE 4);
No comments:
Post a Comment