You plan to bulk load data INSERT INTO . . . SELECT FROM statements.
Which two situations benefit from parallel INSERT operations on tables that have no materialized
views defined on them?
A.
Direct path insert of a million rows into a partitioned, index-organized table containing one
million rows and a conventional B*tree secondary index.
B.
Direct path insert of a million rows into a partitioned, index-organized table containing 10 rows
and a bitmapped secondary index.
C.
Direct path insert of 10 rows into a partitioned, index-organized table containing one million
rows and conventional B* tree secondary index.
D.
Direct path insert of 10 rows into a partitioned, index-organized table containing 10 rows and a
bitmapped secondary index
E.
Conventional path insert of a million rows into a nonpartitioned, heap-organized containing 10
rows and having a conventional B* tree index.
F.
Conventional path insert of 10 rows into a nonpartitioned, heap-organized table one million rows
and a bitmapped index.
Explanation:
Note:
* A materialized view is a database object that contains the results of a query.
* You can use the INSERT statement to insert data into a table, partition, or view in two ways:
conventional INSERTand direct-path INSERT.
* With direct-path INSERT, the database appends the inserted data after existing data in the table.
Data is written directly into datafiles, bypassing the buffer cache. Free space in the existing data is
not reused. This alternative enhances performance during insert operations and is similar to the
functionality of the Oracle direct-path loader utility, SQL*Loader. When you insert into a table that
has been created in parallel mode, direct-path INSERT is the default.
* Direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it
has a mapping table, or if it is reference by a materialized view.
* When you issue a conventional INSERT statement, Oracle Database reuses free space in the
table into which you are inserting and maintains referential integrity constraints
* Conventional INSERT always generates maximal redo and undo for changes to both data and
metadata, regardless of the logging setting of the table and the archivelog and force logging
settings of the database
E,F
In my opinion
A is correct
B is not correct for me. An IOT with a bitmapped secondary index as automatically a mapping table (see oracle doc). And // direct path insert is not supporting if IOT has a mapping table.
C is not correct: direct path is not faster than conventional insert for tiny set
D is not correct: same reason than B and C
E is correct
F is not correct: same reason than C
To be confirmed.
imo A,C
conventional can’t be done parallel
resume
no B, no D – because direct-path INSERT is not supported for an index-organized table (IOT) if it is not partitioned, if it has a mapping table, or if it is reference by a materialized view
no E, no F – because conventional insert can be parallelized only with NOAPPEND hint (no such hint is assumed in the question)
so, A and C should be correct.
E would be better than C if PARALLEL DML is enabled.
AE
A, E
C, D and F is wrong because using parallel INSERT operation on small volume of data has no benefit
B is wrong because table has bitmapped secondary index and it is a problem (parallel operation on that index)
“If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) and not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows.”
Source: https://docs.oracle.com/cd/E11882_01/server.112/e40540/indexiot.htm#CNCPT1182
A, E are correct.
Explanation for E:
You should use a conventional path load in the following situations:
When loading a relatively small number of rows into a large indexed table
During a direct path load, the existing index is copied when it is merged with the new index keys. If the existing index is very large and the number of new keys is very small, then the index copy time can offset the time saved by a direct path load.
http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_modes.htm