hi
when I execute following queries see completely different result where is problem?
here I execute a simple select query which takes a few seconds to complete:
SELECT * FROM
PRODUCTION.VERY_SMALL_TABLE L
INNER JOIN PRODUCTION.BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX R
on R.ID_1 = L.ID or R.ID_2 = L.ID
and its execution plan is:
-----------------------------------------------------------------------------
| Id | Operation | Name
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | VIEW | VW_ORE_65071C6B
| 2 | UNION-ALL |
| 3 | NESTED LOOPS |
| 4 | NESTED LOOPS |
| 5 | TABLE ACCESS FULL | VERY_SMALL_TABLE
| 6 | PARTITION RANGE ALL |
|* 7 | INDEX RANGE SCAN | ID_2_INDX
| 8 | TABLE ACCESS BY LOCAL INDEX ROWID | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX
| 9 | NESTED LOOPS |
| 10 | NESTED LOOPS |
| 11 | TABLE ACCESS FULL | VERY_SMALL_TABLE
| 12 | PARTITION RANGE ALL |
|* 13 | INDEX RANGE SCAN | ID_1_INDX
|* 14 | TABLE ACCESS BY LOCAL INDEX ROWID | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX
--------------------------------------------------------------------------------------
---------------------------------------------------------
| Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------
| 72M| 9554M| 11M (1)| 00:07:16 | | |
| 72M| 9554M| 11M (1)| 00:07:16 | | |
| | | | | | |
| 50M| 6821M| 6056K (1)| 00:03:57 | | |
| 50M| 6821M| 6056K (1)| 00:03:57 | | |
| 7 | 98 | 3 (0)| 00:00:01 | | |
| 7246K| | 621 (0)| 00:00:01 | 1 |1048575|
| 7246K| | 621 (0)| 00:00:01 | 1 |1048575|
| 7246K| 877M| 865K (1)| 00:00:34 | 1 | 1 |
| 21M| 2870M| 5097K (1)| 00:03:20 | | |
| 42M| 2870M| 5097K (1)| 00:03:20 | | |
| 7 | 98 | 3 (0)| 00:00:01 | | |
| 6098K| | 621 (0)| 00:00:01 | 1 |1048575|
| 6098K| | 621 (0)| 00:00:01 | 1 |1048575|
| 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.ID_1 = L.ID or R.ID_2 = L.ID);
execution plan changes to this:
----------------------------------------------------------------------------
| Id | Operation | Name
----------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT |
| 1 | LOAD AS SELECT | DUMMY_TABLE
| 2 | NESTED LOOPS |
| 3 | TABLE ACCESS FULL | VERY_SMALL_TABLE
| 4 | PARTITION RANGE ALL |
|* 5 | TABLE ACCESS FULL | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX
----------------------------------------------------------------------------
------------------------------------------------------
Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop
------------------------------------------------------
222G| 28T| 1971M (1)| 21:23:21 | |
| | | | |
222G| 28T| 1200M (1)| 13:01:53 | |
7 | 98 | 3 (0)| 00:00:01 | |
31G| 3756G| 171M (1)| 01:51:42 | 1 |1048575
31G| 3756G| 171M (1)| 01:51:42 | 1 |1048575
------------------------------------------------------
and create table query takes too too long to complete !!