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 | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 72M| 9554M| 11M (1)| 00:07:16 | | | | 1 | VIEW | VW_ORE_65071C6B | 72M| 9554M| 11M (1)| 00:07:16 | | | | 2 | UNION-ALL | | | | | | | | | 3 | NESTED LOOPS | | 50M| 6821M| 6056K (1)| 00:03:57 | | | | 4 | NESTED LOOPS | | 50M| 6821M| 6056K (1)| 00:03:57 | | | | 5 | TABLE ACCESS FULL | VERY_SMALL_TABLE | 7 | 98 | 3 (0)| 00:00:01 | | | | 6 | PARTITION RANGE ALL | | 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.ID_1 = L.ID or R.ID_2 = L.ID);
execution plan changes to this:
------------------------------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ------------------------------------------------------------------------------------------------------------------------------------ | 0 | CREATE TABLE STATEMENT | | 222G| 28T| 1971M (1)| 21:23:21 | | | | 1 | LOAD AS SELECT | DUMMY_TABLE | | | | | | | | 2 | NESTED LOOPS | | 222G| 28T| 1200M (1)| 13:01:53 | | | | 3 | TABLE ACCESS FULL | VERY_SMALL_TABLE | 7 | 98 | 3 (0)| 00:00:01 | | | | 4 | PARTITION RANGE ALL | | 31G| 3756G| 171M (1)| 01:51:42 | 1 |1048575| |* 5 | TABLE ACCESS FULL | BIG_PARTITIONED_TABLE_WITH_LOCAL_INDEX | 31G| 3756G| 171M (1)| 01:51:42 | 1 |1048575| -------------------------------------------------------------------------------------------------------------------------------------
and create table query takes too too long to complete !!
Best Answer
Add a first row hint, that way oracle may respond the same way as to the datagrip query.
As an alternative you could analyze your tables, so that the optimizer can use up to date statistics.