hello tom:
we have a target table msisdn_ua1 which has more than 100 million rows,and we want to merge the rows in table file_temp with about one million rows into msisdn_ua,the sql we use as follow:
MERGE /*+parallel(t1,8)*/ INTO msisdn_ua t1
USING (SELECT t.msisdn, t.ua_code,
TO_DATE (t.DAY, 'yyyymmdd') record_time,
t.model_id
FROM file_temp t) t2
ON ( t1.msisdn = t2.msisdn
AND t1.ua_code = t2.ua_code
AND t1.model_id = t2.model_id)
WHEN NOT MATCHED THEN
INSERT (msisdn, ua_code, record_time, model_id)
VALUES (t2.msisdn, t2.ua_code, t2.record_time, t2.model_id);
we have a index IDX_MSISDN_UA on table msisdn_ua(msisdn,model_id,ua_code),now we find that the plan is to use hash join,and the operation is very slow;
i mean that ,if we can use fast full index scan on the msisdn_ua,we can use nested loop join,i think it will be faster;so i use the sql like this :
MERGE /*+index_ffs(t1 IDX_MSISDN_UA) use_nl(t2,t1) */ INTO msisdn_ua t1
USING (SELECT t.msisdn, t.ua_code,
TO_DATE (t.DAY, 'yyyymmdd') record_time,
t.model_id
FROM file_temp t) t2
ON ( t1.msisdn = t2.msisdn
AND t1.ua_code = t2.ua_code
AND t1.model_id = t2.model_id)
WHEN NOT MATCHED THEN
INSERT (msisdn, ua_code, record_time, model_id)
VALUES (t2.msisdn, t2.ua_code, t2.record_time, t2.model_id);but ,the plan shows that the merge operation will not use fast full index scan on msisdn_ua but index range scan ,so the operation will slower;
my question is :
1.why dosen't it use fast full index scan here,the operation just to test if the msisdn,ua_code,model_id which in the index not in the msisdn_ua table,then inert it,they don't use any other field in the msisdn_ua;
2.how can i make the operation faster?
thanks very much!
first, an index fast full scan is the ability of the database to use an INDEX instead of a TABLE - the index is read "not in order", but full scanned out of order.
second, if it used an index and got a nested loops join, it would be just about as slow as you can make it
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:6749454952894#6760861174154 an index is good to retrieve A SMALL NUMBER of rows from a table. 1,000,000 rows is *NOT* a small number. 100,000,000 rows is *NOT* a small number. Using an index would probably not make sense
Now, that said, it sounds like you want to add rows from FILE_TEMP that do not already exist in MSISDN_UA. Assuming there is a primary key in place, you would want to evaluate:
ops$tkyte%ORA11GR2> create table file_temp (msisdn number, ua_code number, model_id number, data varchar2(2000) );
Table created.
ops$tkyte%ORA11GR2> create table msisdn_ua (msisdn number, ua_code number, model_id number, data varchar2(2000), primary key(msisdn,ua_code,model_id) );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'FILE_TEMP', numrows => 1000000, numblks => 100000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2> exec dbms_stats.set_table_stats( user, 'MSISDN_UA', numrows => 10000000, numblks => 1000000 );
PL/SQL procedure successfully completed.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> set autotrace on explain
ops$tkyte%ORA11GR2> insert into msisdn_ua
2 select file_temp.*
3 from file_temp, msisdn_ua
4 where file_temp.msisdn = msisdn_ua.msisdn (+)
5 and file_temp.ua_code = msisdn_ua.ua_code (+)
6 and file_temp.model_id = msisdn_ua.model_id (+)
7 and msisdn_ua.rowid is null;
0 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 1644611145
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1000K| 144M| 27203 (1)| 00:05:27 |
| 1 | LOAD TABLE CONVENTIONAL | MSISDN_UA | | | | |
|* 2 | FILTER | | | | | |
| 3 | NESTED LOOPS OUTER | | 1000K| 144M| 27203 (1)| 00:05:27 |
| 4 | TABLE ACCESS FULL | FILE_TEMP | 1000K| 95M| 27123 (1)| 00:05:26 |
|* 5 | INDEX UNIQUE SCAN | SYS_C0032619 | 1 | 51 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("MSISDN_UA".ROWID IS NULL)
5 - access("FILE_TEMP"."MSISDN"="MSISDN_UA"."MSISDN"(+) AND
"FILE_TEMP"."UA_CODE"="MSISDN_UA"."UA_CODE"(+) AND
"FILE_TEMP"."MODEL_ID"="MSISDN_UA"."MODEL_ID"(+))
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into msisdn_ua
2 select * from file_temp
3 where (msisdn,ua_code,model_id) not in (select msisdn, ua_code, model_id from msisdn_ua);
0 rows created.
Execution Plan
----------------------------------------------------------
Plan hash value: 2572901664
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | 1000K| 132M| | 150K (1)| 00:30:05 |
| 1 | LOAD TABLE CONVENTIONAL | MSISDN_UA | | | | | |
| 2 | MERGE JOIN ANTI SNA | | 1000K| 132M| | 150K (1)| 00:30:05 |
| 3 | SORT JOIN | | 1000K| 95M| 217M| 49829 (1)| 00:09:58 |
| 4 | TABLE ACCESS FULL | FILE_TEMP | 1000K| 95M| | 27123 (1)| 00:05:26 |
|* 5 | SORT UNIQUE | | 10M| 371M| 995M| 100K (1)| 00:20:07 |
| 6 | INDEX FAST FULL SCAN | SYS_C0032619 | 10M| 371M| | 9 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access(INTERNAL_FUNCTION("MSISDN")=INTERNAL_FUNCTION("MSISDN") AND
INTERNAL_FUNCTION("UA_CODE")=INTERNAL_FUNCTION("UA_CODE") AND
INTERNAL_FUNCTION("MODEL_ID")=INTERNAL_FUNCTION("MODEL_ID"))
filter(INTERNAL_FUNCTION("MODEL_ID")=INTERNAL_FUNCTION("MODEL_ID") AND
INTERNAL_FUNCTION("UA_CODE")=INTERNAL_FUNCTION("UA_CODE") AND
INTERNAL_FUNCTION("MSISDN")=INTERNAL_FUNCTION("MSISDN"))
ops$tkyte%ORA11GR2> set autotrace off
I don't have valid stats on my indexes and tables - so I would expect your plans to be a little different.