Which three options are true about parallel queries when PARALLEL_DEGREE_POLICY is set to MANUAL and the session is using the default settings for parallel query, DDL, and DML?
A. A subquery in a parallel DML isparallelizedonly if it includes a parallel hint.
B. The number of parallel execution servers requested for a cursor is based on the greatest degree of parallelism associated with any object accessed by the cursor.
C. A SELECT statement can be executed in parallel only if no scalar subqueries are contained in the SELECT list.
D. In a CREATE TABLE . . . AS SELECT (CTAS) statement, SELECT is parallelized only if create TABLE is parallelized.
E. In an INSERT INTO . . . SELECT FROM statement, INSERT is parallelized if select is parallelized.
F. Single row inserts are never executed is parallel.
Correct Answer: CEF
Explanation/Reference:
*Decision to Parallelize
A SELECT statement can be parallelized only if the following conditions are satisfied:
/The query includes a parallel hint specification (PARALLEL or PARALLEL_INDEX) or the schema objects referred to in the query have a PARALLEL declaration associated with them.
/At least one of the tables specified in the query requires one of the following:
A full table scan
An index range scan spanning multiple partitions
/ (C)No scalar subqueries are in the SELECT list.
*By default, the system only uses parallel execution when a parallel degree has been explicitly set on an object or if a parallel hint is specified in the SQL statement.
*CREATE TABLE … AS SELECT in Parallel
Parallel execution lets you parallelize the query and create operations of creating a table as a subquery from another table or set of tables. This can be extremely useful in the creation of summary or rollup tables.
Clustered tables cannot be created and populated in parallel.
*PARALLEL_DEGREE_POLICY specifies whether or not automatic degree of Parallelism, statement queuing, and in-memory parallel execution will be enabled.
MANUAL
Disables automatic degree of parallelism, statement queuing, and in-memory parallel execution.
This reverts the behavior of parallel execution to what it was prior to Oracle Database 11g Release
2 (11.2). This is the default.
Incorrect:
A:
*For parallel DML (INSERT, UPDATE, MERGE, and DELETE), the reference object that determines the DOP(degree of parallelism)is the table being modified by and insert, update, or delete operation. Parallel DML also adds some limits to the DOP to prevent deadlock. If the parallel DML statement includes a subquery, the subquery’s DOP is the same as the DML operation.
*For parallel DDL, the reference object that determines the DOP is the table, index, or partition being created, rebuilt, split, or moved. If the parallel DDL statement includes a subquery, the subquery’s DOP is the same as the DDL operation.
D:The CREATE TABLE … AS SELECT statement contains two parts: a CREATE part (DDL) and a SELECT part (query). Oracle Database can parallelize both parts of the statement.
The query part of a CREATE TABLE … AS SELECT statement can be parallelized only if the following conditions are satisfied:
Reference: OracleDatabase VLDB and Partitioning Guide,Using Parallel Execution
|
Download Printable PDF. VALID exam to help you PASS.
|
 |
