Partition pruning not happening when inserting

The select statement runs fine with proper partition pruning.
But as soon as I am trying to insert the data into another table, the execution plan changes and no partition pruning happens:

Execution plan for SELECT:
-----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                             |    36 | 15444 |       |  1749K  (1)| 05:49:53 |       |       |
|*  1 |  HASH JOIN                  |                             |    36 | 15444 |    56M|  1749K  (1)| 05:49:53 |       |       |
|   2 |   PART JOIN FILTER CREATE   | :BF0000                     |   914K|    46M|       |   311   (5)| 00:00:04 |       |       |
|   3 |    NESTED LOOPS             |                             |   914K|    46M|       |   311   (5)| 00:00:04 |       |       |
|   4 |     TABLE ACCESS FULL       | SMALL_TABLE        |    18 |   108 |       |     3   (0)| 00:00:01 |       |       |
|   5 |     PARTITION LIST ITERATOR |                             | 50804 |  2331K|       |    17   (6)| 00:00:01 |   KEY |   KEY |
|*  6 |      TABLE ACCESS FULL      | BIG_TABLE1         | 50804 |  2331K|       |    17   (6)| 00:00:01 |   KEY |   KEY |
|   7 |   PARTITION LIST JOIN-FILTER|                             |   399K|   143M|       |  1738K  (1)| 05:47:48 |:BF0000|:BF0000|
|*  8 |    TABLE ACCESS FULL        | BIG_TABLE2 |   399K|   143M|       |  1738K  (1)| 05:47:48 |:BF0000|:BF0000|
-----------------------------------------------------------------------------------------------------------------------------------


Execution plan for INSERT INTO TARGET_TABLE SELECT....:
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT           |                             |    36 | 15444 |       |  1749K  (1)| 05:49:53 |       |       |
|   1 |  LOAD TABLE CONVENTIONAL   | TARGET_TABLE            |       |       |       |            |          |       |       |
|*  2 |   HASH JOIN                |                             |    36 | 15444 |    56M|  1749K  (1)| 05:49:53 |       |       |
|   3 |    NESTED LOOPS            |                             |   914K|    46M|       |   311   (5)| 00:00:04 |       |       |
|   4 |     TABLE ACCESS FULL      | SMALL_TABLE        |    18 |   108 |       |     3   (0)| 00:00:01 |       |       |
|   5 |     PARTITION LIST ITERATOR|                             | 50804 |  2331K|       |    17   (6)| 00:00:01 |   KEY |   KEY |
|*  6 |      TABLE ACCESS FULL     | BIG_TABLE1         | 50804 |  2331K|       |    17   (6)| 00:00:01 |   KEY |   KEY |
|   7 |    PARTITION LIST ALL      |                             |   399K|   143M|       |  1738K  (1)| 05:47:48 |     1 |166332 |
|*  8 |     TABLE ACCESS FULL      | BIG_TABLE2 |   399K|   143M|       |  1738K  (1)| 05:47:48 |     1 |166332 |
----------------------------------------------------------------------------------------------------------------------------------

The data dictionary says statistics are not stale but I am still trying to gather statistics again but it will take several hours.
Any idea what could be going wrong?
Why does the execution  plan change when inserting data?
subrato chatterjeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
can you post some ddl and the actual statements?
0
subrato chatterjeeAuthor Commented:
--partitioned by file_id(150,000 partitions, 150,000,000 rows)
CREATE TABLE BIG_TABLE1
(
   file_id number,
   product_id number,
   id number
)


--partitioned by file_id(35,000 partitions, 10,000,000 rows)
create table BIG_TABLE2
(
file_id number,
statistic VARCHAR2(500),
result_value number,
id number
)


--Only 30 rows
create table SMALL_TABLE
(
file_id number
)


INSERT INTO t_rm_rec_results
   SELECT file_id,
          statistic,
          product_id,
          id,
          statistic,
          result_value
     FROM BIG_TABLE1 bt1, BIG_TABLE2 bt2, SMALL_TABLE st
    WHERE     bt1.file_id = st.file_id
          AND bt2.file_id = bt1.file_id
          AND bt2.id = bt1.id
          AND bt2.product_id IN (12, 13)
0
sdstuberCommented:
what do the predicate conditions look like in the plan?

Have you tried explicit transitivity on the file id ?

WHERE     bt1.file_id = st.file_id
           AND bt2.file_id = st.file_id      ---  shouldn't be needed, but try it and see if it changes the plan
           AND bt2.file_id = bt1.file_id

Oracle "should" and "often" will figure out the transitivity on its own, sometimes it won't.

This article provides a more detailed set of examples

https://seanstuber.wordpress.com/2014/12/18/oracle-optimizer-doesnt-understand-elementary-math/
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

subrato chatterjeeAuthor Commented:
Found the following on web:

"Bloom filters are not used in DML statements: Bug 13801198 says that a simple two table join shows use of Bloom filter/pruning when running as a query and not when running as part of a INSERT as SELECT. The performance delta is 4:24 vs 0:24."

https://juliandontcheff.wordpress.com/2012/08/28/bloom-filters-for-dbas/

My select explain plan uses bloom filters (|:BF0000|:BF0000|) but the Insert does not.
May be this explains the partition pruning issue.
0
sdstuberCommented:
yes, it would change the plan, because the bloom filters aren't getting pushed to the lower conditions.

Did you try forcing transitivity yourself?

What are the plans, including the predicate portions  before and after?
0
subrato chatterjeeAuthor Commented:
Hi sdstuber, you are right - the plan changes after forcing the transitivity.

Using Bloom filter (without transitivity) - select takes 50 seconds.

Using transitivity - insert into select takes 10 minutes. Partition pruning happens
                                 but the plan does a nested loop between results returned by BIG_TABLE1 and
BIG_TABLE2.

Without using transitivity - insert into select takes 50 minutes. Partition pruning does not happen but the plan does a hash join between the two big tables.

So, thanks a lot sdstuber for helping me out. But I could not open your blog I getting a 'page cannot be displayed message'
0
sdstuberCommented:
Glad I could help.  I have no idea about the blog though.  I just checked and it opened for me.

Hopefully just something temporary.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.