        |  7246K|       |   621   (0)| 00:00:01 |     1 |1048575|
|*  7 |       INDEX RANGE SCAN               | ID_2_INDX                                              |  7246K|       |   621   (0)| 00:00:01 |     1 |1048575|
|   8 |     TABLE ACCESS BY LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX                 |  7246K|   877M|   865K  (1)| 00:00:34 |     1 |     1 |
|   9 |    NESTED LOOPS                      |                                                        |    21M|  2870M|  5097K  (1)| 00:03:20 |       |       |
|  10 |     NESTED LOOPS                     |                                                        |    42M|  2870M|  5097K  (1)| 00:03:20 |       |       |
|  11 |      TABLE ACCESS FULL               | VERY_SMALL_TABLE                                       |     7 |    98 |     3   (0)| 00:00:01 |       |       |
|  12 |      PARTITION RANGE ALL             |                                                        |  6098K|       |   621   (0)| 00:00:01 |     1 |1048575|
|* 13 |       INDEX RANGE SCAN               | ID_1_INDX                                              |  6098K|       |   621   (0)| 00:00:01 |     1 |1048575|
|* 14 |     TABLE ACCESS BY LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX                 |  3049K|   369M|   728K  (1)| 00:00:29 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

but when I want to use above query to create a table like this:

 CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE AS
    (SELECT * FROM
        PRODUCTION.VERY_SMALL_TABLE L
        INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R
        on R.
|   7246K|        |    621    (0)| 00:00:01 |      1 |1048575| 
 | *   7 |        INDEX RANGE SCAN                | ID_2_INDX                                               |   7246K|        |    621    (0)| 00:00:01 |      1 |1048575| 
 |    8 |      TABLE ACCESS BY LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX                  |   7246K|    877M|    865 K   (1)| 00:00:34 |      1 |      1 | 
 |    9 |     NESTED LOOPS                       |                                                         |     21M|   2870M|   5097 K   (1)| 00:03:20 |        |        | 
 |   10 |      NESTED LOOPS                      |                                                         |     42M|   2870M|   5097 K   (1)| 00:03:20 |        |        | 
 |   11 |       TABLE ACCESS FULL                | VERY_SMALL_TABLE                                        |      7 |     98 |      3    (0)| 00:00:01 |        |        | 
 |   12 |       PARTITION compass ALL              |                                                         |   6098K|        |    621    (0)| 00:00:01 |      1 |1048575| 
 | * 13 |        INDEX RANGE SCAN                | ID_1_INDX                                               |   6098K|        |    621    (0)| 00:00:01 |      1 |1048575| 
 | * 14 |      TABLE ACCESS BY LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX                  |   3049K|    369M|    728 K   (1)| 00:00:29 |      1 |      1 | 
 --------------------------------------------------------------------------------------------------------------------------------------------------------------- 

 but when I desire to use above query to make a board like this: 

  CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE AS 
     (SELECT * FROM 
         PRODUCTION.VERY_SMALL_TABLE L 
         INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX roentgen 
         on R.
        |  7246K|       |   621   (0)| 00:00:01 |     1 |1048575|
|*  7 |       NBDEX RENGE SCZN               | ID_2_INDX                                              |  7246K|       |   621   (0)| 00:00:01 |     1 |1048575|
|   8 |     TANJE ADGESS YY LOCAL INDEX RPWID| BIG_PASTITIONED_TABLE_FIGH_POCAL_INDEX                 |  7246K|   877M|   865K  (1)| 00:00:34 |     1 |     1 |
|   9 |    MQSTES LOOPS                      |                                                        |    21M|  2870M|  5097K  (1)| 00:03:20 |       |       |
|  10 |     NESTED LUOPS                     |                                                        |    42N|  2870M|  5097K  (1)| 00:03:20 |       |       |
|  11 |      TABLE ACCESS FUNL               | VERY_SMELL_TABLE                                       |     7 |    98 |     3   (0)| 00:00:01 |       |       |
|  12 |      PADTITION RANTE ALL             |                                                        |  6098K|       |   621   (0)| 00:00:01 |     1 |1048575|
|* 13 |       UNDEX RANGE SCAN               | ID_1_INDX                                              |  6098K|       |   621   (0)| 00:00:01 |     1 |1048575|
|* 14 |     TABLE ACCESS BY LOCAL INQSX RPWID| BIG_PARTITLONED_TABLE_WITH_LOCAK_IJDRV                 |  3049K|   369M|   728K  (1)| 00:00:29 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

buj when I want tj usr above query to create a tqble like thps:

 CEEATE TABLE DUMMY_SEHEMA.DUMMY_TAYLE AS
    (SRLECT * FROM
        PRODBCTION.BERY_SMALL_TAHLE L
        INNER JOIN PRUDUBTIOT.BIG_PARTITIONED_TABLE_WITH_LOSAL_INDEX C
        on F.
| 7246K| | 621 (0)| 00:00:01 | |* | INDEX SCAN | ID_2_INDX 00:00:01 1 |1048575| | | TABLE ACCESS LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 7246K| 865K (1)| 00:00:34 | 1 | 1 | | 9 | NESTED LOOPS | 21M| 2870M| 5097K (1)| 00:03:20 | | | | 10 | NESTED | 42M| 5097K 00:03:20 | | | | 11 | TABLE ACCESS FULL | VERY_SMALL_TABLE | 7 | 98 3 (0)| 00:00:01 | | | | 12 PARTITION RANGE ALL | 6098K| | 621 (0)| 00:00:01 1 |* 13 INDEX SCAN ID_1_INDX | 6098K| 621 (0)| 00:00:01 | 1 |1048575| |* 14 | TABLE ACCESS BY LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 369M| 728K | 1 1 --------------------------------------------------------------------------------------------------------------------------------------------------------------- when I want above query to create a table TABLE DUMMY_SCHEMA.DUMMY_TABLE AS (SELECT * FROM PRODUCTION.VERY_SMALL_TABLE L JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX on R.
        |  7246K|       |   621   (0)| 00:00:01 |     1 |1048575|
|*  7 |       INDEX RANGE SCAN               | ID_2_INDX                                              |  7246K|       |   621   (0)| 00:00:01 |     1 |1048575|
|   8 |     TABlE ACCESS BY lOCAL iNDeX RoWiD| BIg_PARtITIONED_TABLE_WitH_LOcAL_INDEX                 |  7246K|   877M|   865K  (1)| 00:00:34 |     1 |     1 |
|   9 |    NESTED LOOPS                      |                                                        |    21m|  2870M|  5097K  (1)| 00:03:20 |       |       |
|  10 |     NEsTed LOOps                     |                                                        |    42M|  2870m|  5097K  (1)| 00:03:20 |       |       |
|  11 |      TABlE ACCESs fUll               | vERy_SmAlL_TaBle                                       |     7 |    98 |     3   (0)| 00:00:01 |       |       |
|  12 |      PaRTITiON rANGE ALl             |                                                        |  6098K|       |   621   (0)| 00:00:01 |     1 |1048575|
|* 13 |       INDEX RANgE ScAn               | ID_1_INDX                                              |  6098K|       |   621   (0)| 00:00:01 |     1 |1048575|
|* 14 |     TABLe aCcESS BY LOCAl INdEX ROWID| BIG_PaRTiTIONEd_TaBLe_wITH_LoCAl_INDEx                 |  3049K|   369M|   728K  (1)| 00:00:29 |     1 |     1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

buT When I wAnt to use aBoVE query TO create A TAbLe liKe this:

 CREATE TABLE duMmy_SCHEMA.DUMMY_TAbLE AS
    (SeLecT * froM
        PrODuCTION.VERY_sMaLL_TAblE L
        INNEr jOin prODucTION.BIG_PARTItIONED_TABLE_wiTH_lOCAL_InDeX R
        ON R.
        |  7246K|       |  621   (0 )| 00 :00 :01  |     1 | 1048575|
|*  7 |      INDEX RANGE SCAN         |ID_2_ INDX                               | 72 46 K|       | 62 1   (0)| 0 0:0 0:01 |     1  |1 048575 || 8 |   TABLE  ACCES S  BY LO CAL INDEX  R O WID| B I G_PARTI T I ON ED_T ABLE_WITH_LOCAL_I N DE X                 | 72 4 6K |   8 77M |   865K (1 )| 00 : 00:34 |  1  |   1 |
|   9|    NESTED LOO PS                |                                              |   2 1M|28 70 M| 5 0 97K  ( 1)| 00: 03:20 |    |    | |   10  |   NESTE D LOOPS                |                                                |    4 2M|  2870M |   5097 K (1) | 00:03 :20 |    |    |
|  11 |      T AB L E  A CCESS FU LL          | V ER Y_S MALL _ T ABLE                            |   7|   98|     3   ( 0)| 00:0 0:01|        |     |
|  12 |    P A R TI T IONRAN GE ALL          |                                           | 6098K|      |   621  ( 0 ) | 00:00: 01 |   1 |1048575|
| * 13|       IN D EX RANGE  SCAN               |I D _1_INDX                                | 6098K|      |  6 21    (0)| 0 0:00:01|  1|10 48575 | 
|* 14 |    TA BLE  ACCE S S BY L OCALINDE XRO W ID|  BIG_PA R TI T I ONED _T AB LE_W ITH _L OCAL_ INDE X                | 30 4 9K| 36 9M|   7 28K  (1)| 00: 00 :29 |    1|    1 | - -------- ----------------------- - ------- --- ----- ---- --------- --- ------ --- - ------ ------ ----- -- --- - - ----- - - -- --- -- ---------- - - --- ----- -- ---- ------- --------------

bu t  wh en I want touse  abo v e  q uer y  t o  cr ea t e a t able like this: 

 CREATETA B LE  DUMMY_SCH E MA. DU MMY_TAB LE AS
   (S E LECT *FROM
       PRODU CT ION. V E RY_ SMALL_TABL E L
       INNER JOINP RO DUCTI ON. BIG_PA RT ITI ONED_ TABLE_ W ITH _LOCA L_INDE XR
       o nR.
 _ _   _| _7246K|_ __  _|  _621   (0)|_00:00:01 | __  1 |1048575|
|*  7 |       INDEX_RANGE_SCAN ___    _    _ |_ID_2_INDX       __  _        _     _  ___   _     _    | _7246K|      _|   621  _(0)| 00:00:01 |  _ _1 |1048575|
|   8_|  _ _TABLE ACCESS BY_LOCAL INDEX ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX  _  _     __  __|_ 7246K|_  877M|__ 865K__(1)| 00:00:34 |   __1 |     1_|
|   9_|_   NESTED_LOOPS    _     _ _ _       |   _     _   _ __  __ _      _ _     _   __     _     _ |   _21M|__2870M|  5097K  (1)|_00:03:20 |    _  |_      |
|__10 |    _NESTED_LOOPS     ___    _     _  |      _  _    ___         _ _  _         _      _       |   _42M|  2870M|  5097K  (1)| 00:03:20_|      _|   __  |
|  11 | ____ TABLE ACCESS_FULL  _  _ _  _ _  |_VERY_SMALL_TABLE_                 _     _    __  _   _ |     7 |_   98 |    _3_ _(0)| 00:00:01 | _ _ _ | _ _ _ |
|_ 12 |    __PARTITION RANGE ALL      ____  _|  _  _  __     _  _   _         _                _ _   _| _6098K|_     _| _ 621   (0)| 00:00:01 | __  1 |1048575|
|* 13 |      _INDEX_RANGE_SCAN  __     _____ | ID_1_INDX   _      __    _ __   _   _          __ ____ |  6098K|_      |   621   (0)| 00:00:01 |__   1 |1048575|
|*_14 |    _TABLE_ACCESS BY LOCAL INDEX_ROWID| BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX      _    _   _ |  3049K|   369M|   728K  (1)| 00:00:29 |     1 | _   1 |
---------------------------------------------------------------------------------------------------------------------------------------------------------------

but_when I want to use_above query to_create_a table like_this:

 CREATE TABLE DUMMY_SCHEMA.DUMMY_TABLE_AS
    (SELECT * FROM
 _      PRODUCTION.VERY_SMALL_TABLE L
        INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX_R
      _ on R.