Check the notes in the execution plan..
SQL> create table PAR ( p int primary key );
Table created.
SQL> create table CHD ( c int primary key, p int references PAR(p));
Table created.
SQL>
SQL> insert into PAR values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> explain plan for
2 insert /*+ APPEND */ into CHD
3 select rownum, 1
4 from
5 ( select 1 from dual connect by level <= 1000 ),
6 ( select 1 from dual connect by level <= 1000 );
SQL> SELECT * from table(dbms_xplan.display())
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
Plan hash value: 4088680292
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | LOAD TABLE CONVENTIONAL | CHD | | | |
| 2 | COUNT | | | | |
| 3 | MERGE JOIN CARTESIAN | | 1 | 4 (0)| 00:00:01 |
| 4 | VIEW | | 1 | 2 (0)| 00:00:01 |
|* 5 | CONNECT BY WITHOUT FILTERING | | | | |
| 6 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
| 7 | BUFFER SORT | | 1 | 4 (0)| 00:00:01 |
| 8 | VIEW | | 1 | 2 (0)| 00:00:01 |
|* 9 | CONNECT BY WITHOUT FILTERING| | | | |
| 10 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(LEVEL<=1000)
9 - filter(LEVEL<=1000)
Note
-----
- Direct Load disabled because parent referential constraints are present
27 rows selected.
SQL>
No FK's will disable it.
If there is no FK, then you'll see the LOAD AS SELECT
SQL> drop table PAR cascade constraints purge;
Table dropped.
(then repeat above)
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1 | 4 (0)| 00:00:01 |
| 1 | LOAD AS SELECT | CHD | | | |
| 2 | OPTIMIZER STATISTICS GATHERING | | 1 | 4 (0)| 00:00:01 |
| 3 | COUNT | | | | |
| 4 | MERGE JOIN CARTESIAN | | 1 | 4 (0)| 00:00:01 |
| 5 | VIEW | | 1 | 2 (0)| 00:00:01 |
|* 6 | CONNECT BY WITHOUT FILTERING | | | | |
| 7 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
| 8 | BUFFER SORT | | 1 | 4 (0)| 00:00:01 |
| 9 | VIEW | | 1 | 2 (0)| 00:00:01 |
|* 10 | CONNECT BY WITHOUT FILTERING| | | | |
| 11 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
and then if I actually run the code
SQL> insert /*+ APPEND */ into CHD
2 select rownum, 1
3 from
4 ( select 1 from dual connect by level <= 1000 ),
5 ( select 1 from dual connect by level <= 1000 );
1000000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> select index_name, status
2 from user_indexes
3 where table_name = 'CHD';
INDEX_NAME STATUS
------------------------------ --------
SYS_C0010605 VALID
you can see the index remains valid. (Obviously the direct path load will run *best* with no indexes, but it still works with them there)