1Z0-117 · Question #101
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?
The correct answer is A. Direct path insert of a million rows into a partitioned, index-organized table containing one million rows B. Direct path insert of a million rows into a partitioned, index-organized table containing 10 rows and a. 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…
Question
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?
Options
- ADirect path insert of a million rows into a partitioned, index-organized table containing one million rows
- BDirect path insert of a million rows into a partitioned, index-organized table containing 10 rows and a
- CDirect path insert of 10 rows into a partitioned, index-organized table containing one million rows and
- DDirect path insert of 10 rows into a partitioned, index-organized table containing 10 rows and a bitmapped
- EConventional path insert of a million rows into a nonpartitioned, heap-organized containing 10 rows
- FConventional path insert of 10 rows into a nonpartitioned, heap-organized table one million rows and
How the community answered
(40 responses)- A83% (33)
- C3% (1)
- D5% (2)
- E10% (4)
Explanation
- 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
Topics
Community Discussion
4
Mateus R.Feb 2, 2026A and B are your winners here. Think of direct path insert like a moving truck that bypasses the loading dock entirely and drops furniture straight into the room, so when you are hauling a massive load (a million rows) into a partitioned table, that truck approach shines because Oracle writes data above the high water mark without touching indexes or row-by-row overhead, and partitioning gives the parallel workers separate rooms to fill at the same time, which is exactly the combination Oracle parallel DML was built to exploit. Conventional path (E and F) is like carrying boxes one at a time through the front door, and a tiny 10-row load (C and D) does not give the workers enough furniture to justify parking multiple trucks outside.
Toby R.Feb 27, 2026Saw almost this exact scenario worded on mine, and I almost second-guessed myself on B because the existing table only had 10 rows and I thought that mattered. It does not, what matters is the volume being inserted and whether you are using direct path, so A and B both clear that bar with a million rows going in via direct path, while C and D are inserting only 10 rows which gives parallel nothing to chew on, and E and F are conventional path which bypasses the whole direct-path parallel benefit entirely.
Marisol N.Jan 17, 2026A and B, direct path with a million rows triggers parallel benefit.
Ingrid P.Feb 13, 2026E has to be right because a conventional path insert on a heap-organized table is the baseline scenario where parallel DML actually kicks in without the restrictions that IOTs impose, and a million rows is exactly the volume where the parallelism overhead pays off versus single-stream inserts.