SQL & PL/SQL

Hi:
I have been fighting with this odd issue for whole day now, anybody who can shed some light for me is greatly appreciated.

Trying to migrate my program from 10.2.0.2.0 to 11.1.0.7.0, found one key query was runing 10 times slower than before. turns out to be
"insert into" changes the plan.

Here is the query:
INSERT INTO current_ci_facility_all#
/* -- actual query here -- */

current_ci_facility_all# is a global temporary table to hold the transit data result.
For Plan1, step 10 is using the correct index sales.price_shadow_idx03, however, if I use insert into ,
for plan2, step 10 is NOT using index, but doing full table scan, thus make the whole thing much slower.
I have tried ctas way , it uses the index correctly. The problem only exists in insert into, and only in Oracle 11, the same query works great in oracle 10.2.
anybody can tell me how to force insert into ... using the original plan as the original query?






Plan1(for actual query only)
SELECT STATEMENT ALL_ROWSCost: 2,050 Bytes: 1,115,485 Cardinality: 1,015
1 FAST DUAL Cost: 2 Cardinality: 1
17 NESTED LOOPS OUTER Cost: 2,050 Bytes: 1,115,485 Cardinality: 1,015
13 NESTED LOOPS OUTER Cost: 1,543 Bytes: 149,903 Cardinality: 169
9 HASH JOIN OUTER Cost: 1,205 Bytes: 145,847 Cardinality: 169
4 TABLE ACCESS BY INDEX ROWID TABLE SALES.FCST_ROOT_FACILITY_ALL Cost: 53 Bytes: 139,763 Cardinality: 169
3 BITMAP CONVERSION TO ROWIDS
2 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) SALES.FCST_ROOT_FACILITY_ALL_IDX01
8 VIEW VIEW SALES.ITEM_FACTOR_RAW_VW Cost: 1,149 Bytes: 18,186,120 Cardinality: 505,170
7 UNION-ALL
5 MAT_VIEW ACCESS FULL MAT_VIEW SALES.ITEM_FACTOR_MV Cost: 575 Bytes: 10,007,445 Cardinality: 476,545
6 MAT_VIEW ACCESS FULL MAT_VIEW SALES.ITEM_FACTOR_MV Cost: 575 Bytes: 601,125 Cardinality: 28,625
12 VIEW PUSHED PREDICATE VIEW SALES.PRICE_VW_CURRENT_UOM# Cost: 2 Bytes: 24 Cardinality: 1
11 TABLE ACCESS BY INDEX ROWID TABLE SALES.PRICE_SHADOW Cost: 2 Bytes: 35 Cardinality: 1
10 INDEX RANGE SCAN INDEX SALES.PRICE_SHADOW_IDX03 Cost: 1 Cardinality: 1
16 VIEW PUSHED PREDICATE VIEW SALES.ITEM_COST_PIVOT_VW Cost: 3 Bytes: 212 Cardinality: 1
15 SORT GROUP BY Cost: 3 Bytes: 72 Cardinality: 3
14 INDEX RANGE SCAN INDEX SALES.ITEM_COST_DETAIL_MV_IDX01 Cost: 2 Bytes: 144 Cardinality: 6


Plan2(after the insert into is used)
INSERT STATEMENT ALL_ROWSCost: 2,751 Bytes: 1,160,145 Cardinality: 1,015
17 LOAD TABLE CONVENTIONAL SALES.CURRENT_CI_FACILITY_ALL#
1 FAST DUAL Cost: 2 Cardinality: 1
16 HASH JOIN OUTER Cost: 2,751 Bytes: 1,160,145 Cardinality: 1,015
12 HASH JOIN OUTER Cost: 1,942 Bytes: 156,494 Cardinality: 169
9 HASH JOIN OUTER Cost: 1,205 Bytes: 145,847 Cardinality: 169
4 TABLE ACCESS BY INDEX ROWID TABLE SALES.FCST_ROOT_FACILITY_ALL Cost: 53 Bytes: 139,763 Cardinality: 169
3 BITMAP CONVERSION TO ROWIDS
2 BITMAP INDEX SINGLE VALUE INDEX (BITMAP) SALES.FCST_ROOT_FACILITY_ALL_IDX01
8 VIEW VIEW SALES.ITEM_FACTOR_RAW_VW Cost: 1,149 Bytes: 18,186,120 Cardinality: 505,170
7 UNION-ALL
5 MAT_VIEW ACCESS FULL MAT_VIEW SALES.ITEM_FACTOR_MV Cost: 575 Bytes: 10,007,445 Cardinality: 476,545
6 MAT_VIEW ACCESS FULL MAT_VIEW SALES.ITEM_FACTOR_MV Cost: 575 Bytes: 601,125 Cardinality: 28,625
11 VIEW VIEW SALES.PRICE_VW_CURRENT_UOM# Cost: 737 Bytes: 384,489 Cardinality: 6,103
10 TABLE ACCESS FULL TABLE SALES.PRICE_SHADOW Cost: 737 Bytes: 213,605 Cardinality: 6,103
15 VIEW VIEW SALES.ITEM_COST_PIVOT_VW Cost: 807 Bytes: 32,523,526 Cardinality: 149,878
14 SORT GROUP BY Cost: 807 Bytes: 3,597,072 Cardinality: 149,878
13 INDEX FAST FULL SCAN INDEX SALES.ITEM_COST_DETAIL_MV_IDX01 Cost: 146 Bytes: 3,604,080 Cardinality: 150,170



thanks