-- -- simple performance tests with External Tables -- set timing on set serveroutput on set autotrace traceonly DROP DIRECTORY ext_tab_dir ; CREATE DIRECTORY ext_tab_dir AS '/s01/abinitio/data/prod/mfs/parts/ext_tab_tst_tonguc'; drop table EXT_CON_CONTRACT PURGE ; create table EXT_CON_CONTRACT ( CONTRACT_SK NUMBER(16), CONTRACT_TP_SK NUMBER(16), CONTRACT_NK NUMBER(20), SOURCE_SYSTEM_SK NUMBER(16), START_DATE DATE, END_DATE DATE, INITIAL_ETL_DATE DATE, MODIFICATION_SYSTIME DATE, UPDATE_ETL_DATE DATE, DELETE_FLAG VARCHAR2(2 BYTE), CONTRACT_EXPIRATION_DATE DATE ) organization external ( default directory ext_tab_dir access parameters ( RECORDS DELIMITED BY NEWLINE BADFILE ext_tab_dir:'CON_CONTRACT_bad_file.bad' DISCARDFILE ext_tab_dir:'CON_CONTRACT_discard_file.dis' LOGFILE ext_tab_dir:'CON_CONTRACT_log_file.log' FIELDS TERMINATED BY '¨' missing field values are null ( CONTRACT_SK , CONTRACT_TP_SK , CONTRACT_NK , SOURCE_SYSTEM_SK , START_DATE char date_format date mask "yyyymmddhh24miss" , END_DATE char date_format date mask "yyyymmddhh24miss" , INITIAL_ETL_DATE char date_format date mask "yyyymmddhh24miss" , MODIFICATION_SYSTIME char date_format date mask "yyyymmddhh24miss" , UPDATE_ETL_DATE char date_format date mask "yyyymmddhh24miss" , DELETE_FLAG , CONTRACT_EXPIRATION_DATE char date_format date mask "yyyymmddhh24miss" ) ) location ( 'mfs_16way_001.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_002.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_003.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_004.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_005.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_006.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_007.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_008.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_009.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_010.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_011.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_012.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_013.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_014.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_015.CON_xfrm_contract_to_20080302.dat', 'mfs_16way_016.CON_xfrm_contract_to_20080302.dat' ) ) parallel 16 REJECT LIMIT UNLIMITED ; alter session enable parallel dml; alter session enable parallel ddl; -- with external tables you have to compute statistics :( exec dbms_stats.gather_table_stats(user, tabname => 'EXT_CON_CONTRACT', degree => 16, estimate_percent => NULL); create table TAB_CON_CONTRACT nologging parallel 16 as select /*+ parallel(t) */ * from EXT_CON_CONTRACT t ; exec dbms_stats.gather_table_stats(user, tabname => 'TAB_CON_CONTRACT', degree => 16); select /*+ parallel(t) */ count(*) from TAB_CON_CONTRACT t ; select /*+ parallel(t) */ count(*) from EXT_CON_CONTRACT t ; select /*+ parallel(t) */ * from EXT_CON_CONTRACT t where CONTRACT_NK = 907223888 ; select /*+ parallel(t) */ * from TAB_CON_CONTRACT t where CONTRACT_NK = 907223888 ; -- one external table, one heap organized table with aggregate functions HASH GROUP BY and HASH JOIN SELECT b.delete_flag, COUNT(a.source_system_sk), AVG(a.source_system_sk), SUM(a.source_system_sk), MIN(a.source_system_sk), MAX(a.source_system_sk) FROM tab_con_contract a, ext_con_contract b WHERE a.contract_sk = b.contract_sk AND a.source_system_sk = b.source_system_sk GROUP BY b.delete_flag ; -- two external tables HASH JOIN BUFFERED for equality explain plan for SELECT /* parallel(a) parallel(b) use_hash(a,b) */ a.status_reason_action_sk, b.contract_sk FROM ext_con_subs_status a, ext_con_contract b WHERE a.contract_sk = b.contract_sk AND a.source_system_sk = b.source_system_sk ; SELECT * FROM TABLE(dbms_xplan.display); Plan hash value: 1464569502 ------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 222M| 4662M| 68680 (2)| 00:16:02 | | | | | 1 | PX COORDINATOR | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10002 | 222M| 4662M| 68680 (2)| 00:16:02 | Q1,02 | P->S | QC (RAND) | |* 3 | HASH JOIN BUFFERED | | 222M| 4662M| 68680 (2)| 00:16:02 | Q1,02 | PCWP | | | 4 | PX RECEIVE | | 62M| 539M| 9405 (1)| 00:02:12 | Q1,02 | PCWP | | | 5 | PX SEND HASH | :TQ10000 | 62M| 539M| 9405 (1)| 00:02:12 | Q1,00 | P->P | HASH | | 6 | PX BLOCK ITERATOR | | 62M| 539M| 9405 (1)| 00:02:12 | Q1,00 | PCWC | | | 7 | EXTERNAL TABLE ACCESS FULL| EXT_CON_CONTRACT | 62M| 539M| 9405 (1)| 00:02:12 | Q1,00 | PCWP | | | 8 | PX RECEIVE | | 444M| 5507M| 59017 (1)| 00:13:47 | Q1,02 | PCWP | | | 9 | PX SEND HASH | :TQ10001 | 444M| 5507M| 59017 (1)| 00:13:47 | Q1,01 | P->P | HASH | | 10 | PX BLOCK ITERATOR | | 444M| 5507M| 59017 (1)| 00:13:47 | Q1,01 | PCWC | | | 11 | EXTERNAL TABLE ACCESS FULL| EXT_CON_SUBS_STATUS | 444M| 5507M| 59017 (1)| 00:13:47 | Q1,01 | PCWP | | ------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - access("A"."CONTRACT_SK"="B"."CONTRACT_SK" AND "A"."SOURCE_SYSTEM_SK"="B"."SOURCE_SYSTEM_SK") -- SORT&MERGE JOIN for non-equality explain plan for SELECT /* parallel(a) parallel(b) use_hash(a,b) */ a.status_reason_action_sk, b.contract_sk FROM ext_con_subs_status a, ext_con_contract b WHERE a.contract_sk > b.contract_sk AND a.source_system_sk < b.source_system_sk ; SELECT * FROM TABLE(dbms_xplan.display); Plan hash value: 853031183 ---------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib | ---------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 69T| 1397T| | 743M(100)|999:59:59 | | | | | 1 | PX COORDINATOR | | | | | | | | | | | 2 | PX SEND QC (RANDOM) | :TQ10001 | 69T| 1397T| | 743M(100)|999:59:59 | Q1,01 | P->S | QC (RAND) | | 3 | MERGE JOIN | | 69T| 1397T| | 743M(100)|999:59:59 | Q1,01 | PCWP | | | 4 | SORT JOIN | | 62M| 539M| 2402M| 20057 (3)| 00:04:41 | Q1,01 | PCWP | | | 5 | PX RECEIVE | | 62M| 539M| | 9405 (1)| 00:02:12 | Q1,01 | PCWP | | | 6 | PX SEND BROADCAST | :TQ10000 | 62M| 539M| | 9405 (1)| 00:02:12 | Q1,00 | P->P | BROADCAST | | 7 | PX BLOCK ITERATOR | | 62M| 539M| | 9405 (1)| 00:02:12 | Q1,00 | PCWC | | | 8 | EXTERNAL TABLE ACCESS FULL| EXT_CON_CONTRACT | 62M| 539M| | 9405 (1)| 00:02:12 | Q1,00 | PCWP | | |* 9 | FILTER | | | | | | | Q1,01 | PCWP | | |* 10 | SORT JOIN | | 444M| 5507M| 19G| 152K (3)| 00:35:39 | Q1,01 | PCWP | | | 11 | PX BLOCK ITERATOR | | 444M| 5507M| | 59017 (1)| 00:13:47 | Q1,01 | PCWC | | | 12 | EXTERNAL TABLE ACCESS FULL | EXT_CON_SUBS_STATUS | 444M| 5507M| | 59017 (1)| 00:13:47 | Q1,01 | PCWP | | ---------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 9 - filter("A"."SOURCE_SYSTEM_SK"<"B"."SOURCE_SYSTEM_SK") 10 - access("A"."CONTRACT_SK">"B"."CONTRACT_SK") filter("A"."CONTRACT_SK">"B"."CONTRACT_SK")