Skip to Main Content
  • Questions

  • How can I create a table with a parallel option

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Derek.

Asked: November 01, 2018 - 6:23 pm UTC

Last updated: September 25, 2019 - 4:15 am UTC

Version: 12.1.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I will create a table with 1000M records. Can I create the table with some level parallel option setting. Because later on, some people(end user) will be accessing the table with kinds of queries with different conditions, my purpose is to make those end users select the table with a parallel select processing without any hints in their queries. Is this doable for create table or we have to add hint in their queries? And the table has different indexes on different columns.

Kind Regards,

Derek


and Connor said...

Very easily.

create table t ( x int ) parallel;


This makes the table eligible for parallel queries (even if not specified in the SQL).


SQL> create table t parallel as
  2  select * from dba_objects;

Table created.

SQL>
SQL> select owner, count(*) from t
  2  group by owner;

OWNER                            COUNT(*)
------------------------------ ----------
ORDDATA                               292
XDB                                  1030
SYS                                 51853
PUBLIC                              12542
SI_INFORMTN_SCHEMA                      8
DBSFWUSER                               8
OLAPSYS                                25
MCDONAC                              1139
CTXSYS                                412
GSMADMIN_INTERNAL                     204
APEX_180200                          3923
APPQOSSYS                               6
WMSYS                                 394
APEX_LISTENER                          22
SYSTEM                                460
ASKTOM                                279
LBACSYS                               239
ORDPLUGINS                             10
DBSNMP                                 55
REMOTE_SCHEDULER_AGENT                 12
DVSYS                                 373
APEX_180100                          3894
ORACLE_OCM                              8
DEMO1                                   4
BAR                                     1
FLOWS_FILES                            13
ORDS_METADATA                         221
OJVMSYS                                23
DEMO                                    2
OUTLN                                  10
DVF                                    22
MDSYS                                2550
HR                                     34
AUDSYS                                 56
SCOTT                                  63
ORDSYS                               3187

36 rows selected.

SQL> @ex

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  1gh8vvyq91903, child number 0
-------------------------------------
select owner, count(*) from t group by owner

Plan hash value: 129087698

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |          |       |       |    21 (100)|          |        |      |            |
|   1 |  PX COORDINATOR          |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)    | :TQ10001 |    36 |   216 |    21   (5)| 00:00:01 |  Q1,01 | P->S | QC (RAND)  |
|   3 |    HASH GROUP BY         |          |    36 |   216 |    21   (5)| 00:00:01 |  Q1,01 | PCWP |            |
|   4 |     PX RECEIVE           |          |    36 |   216 |    21   (5)| 00:00:01 |  Q1,01 | PCWP |            |
|   5 |      PX SEND HASH        | :TQ10000 |    36 |   216 |    21   (5)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   6 |       HASH GROUP BY      |          |    36 |   216 |    21   (5)| 00:00:01 |  Q1,00 | PCWP |            |
|   7 |        PX BLOCK ITERATOR |          | 83374 |   488K|    20   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  8 |         TABLE ACCESS FULL| T        | 83374 |   488K|    20   (0)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   8 - access(:Z>=:Z AND :Z<=:Z)

Note
-----
   - Degree of Parallelism is 24 because of table property


29 rows selected.

SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

disadvantage parallel table

A reader, September 24, 2019 - 1:55 pm UTC

1) Is there any disadvantage , if we create the table in parallel mode

2)should we define the number of DOP at the time of table creation

Connor McDonald
September 25, 2019 - 4:15 am UTC

1) "maybe"

If your machine is swamped by I/O, a parallel operation might make things worse rapidly.

2) the automatic selection is typically fine in most cases. Don't forget that when you create a table in parallel mode, is *stays* in parallel mode. You might want to reset it to non-parallel once created.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.