Well. It depends.
If you only access the table, then you'll get parallel:
create table t (x not null, y not null, z not null
) parallel 2
as
select level x, lpad('x', 200, 'x') y, ceil(level/1000) z from dual
connect by level <= 10000;
create index ix on t (x);
alter session set parallel_degree_policy = manual;
set serveroutput off
set feed only
select * from t;
set feed on
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC +PARALLEL LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t
Plan hash value: 2866799186
----------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM)| :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 4 | TABLE ACCESS FULL| T | Q1,00 | PCWP | |
----------------------------------------------------------------------
16 rows selected.But if you only scan the index, you'll get serial:
set feed only
select x from t;
set feed on
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC +PARALLEL LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select x from t
Plan hash value: 671578345
-------------------------------------
| Id | Operation | Name |
-------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | INDEX FAST FULL SCAN| IX |
-------------------------------------
13 rows selected.
Switch the parallel settings over and you see the opposite:
alter index ix parallel 2;
alter table t noparallel;
set feed only
select * from t;
set feed on
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC +PARALLEL LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select * from t
Plan hash value: 2498539100
----------------------------------
| Id | Operation | Name |
----------------------------------
| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| T |
----------------------------------
13 rows selected.
set feed only
select x from t;
set feed on
select *
from table(dbms_xplan.display_cursor(null, null, 'BASIC +PARALLEL LAST'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select x from t
Plan hash value: 1782433779
-------------------------------------------------------------------------
| Id | Operation | Name | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | |
| 1 | PX COORDINATOR | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | Q1,00 | P->S | QC (RAND) |
| 3 | PX BLOCK ITERATOR | | Q1,00 | PCWC | |
| 4 | INDEX FAST FULL SCAN| IX | Q1,00 | PCWP | |
-------------------------------------------------------------------------
16 rows selected.
Things get a bit more complicated when you're accessing the table AND the index and they have different settings. You could end up with serial or parallel
for the same query, as this example from Tanel Poder shows:
http://blog.tanelpoder.com/2007/06/23/a-gotcha-with-parallel-index-builds-parallel-degree-and-query-plans/ But this all assumes you're using manual parallel. If you have auto parallelism enabled, the database can override the parallel settings at the table/index level. For more on this, read:
https://blogs.oracle.com/datawarehousing/what-is-auto-dop https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9530821800346264450 https://asktom.oracle.com/pls/apex/asktom.search?tag=parallel-dml-update-without-any-parallel-hint-or-parallel-degree