| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> (no subject)
Hello,
I have a question regarding method of index access(full vs. range) that
oracle uses when I use different join methods (Hash vs. Nested loop).
Here are the some details about the environment.
Server : Oracle Server EE ver 8.1.7.2 OS : HP UX 11.0 hash_area_size : 4194304 hash_join_enabled : TRUE hash_multiblock_io_count : 64
The tables, indexes are all analyzed fully (no estimates) with dbms_stat. The following two scenarios are executed back-to-back and are reproducable. The parameter value to the SQL could be any month in a format like 200207, 200201 etc..
When I use nested loop join the index on large table is ranged scaned, but when I use the hash join the full scan on the index is done. My question is why is the behviour of index scan different? Why there is no partition pruning when the table is hash-joined?
The details related to the objects is at the end of the message.
Thanks for your help.
Regards
Viral.
NESTED LOOP SQL
,fact_dmnsn_q3 fd
where fd.srce_sys_id = cm.srce_sys_id
and cm.mdo_id = 'NA'
and fd.due_perd = LAST_DAY(TO_DATE('&1', 'YYYYMM'))
Execution Plan
HASH JOIN SQL
select --+use_hash(cm fd) index(fd)
fd.*
from copa_mdo_srce_sys_map cm
,fact_dmnsn_q3 fd
where fd.srce_sys_id = cm.srce_sys_id
and cm.mdo_id = 'NA'
and fd.due_perd = LAST_DAY(TO_DATE('&1', 'YYYYMM'));
Execution Plan
Table/index designs
Name Null? Type ----------------------------------------- -------- --------------------- FACT_ID NOT NULL NUMBER(15) CAPTL_ID VARCHAR2(15) CUST_ID VARCHAR2(15) FUNC_ID VARCHAR2(15) GENRC_DMNSN_1_ID VARCHAR2(15) GENRC_DMNSN_1_TYPE_ID NUMBER(4) GENRC_DMNSN_2_ID VARCHAR2(15) GENRC_DMNSN_2_TYPE_ID NUMBER(4) GENRC_DMNSN_3_ID VARCHAR2(15) GENRC_DMNSN_3_TYPE_ID NUMBER(4) GEO_ID VARCHAR2(15) LEGAL_ENT_ID VARCHAR2(15) MEASR_ID VARCHAR2(15) MM_HYBRD_ID VARCHAR2(15) ORG_ID VARCHAR2(15) PROD_ID VARCHAR2(15) PROFT_CTR_ID VARCHAR2(15) SITE_ID VARCHAR2(15) TIME_PERD_ID NOT NULL VARCHAR2(15) TRADE_CHANL_ID VARCHAR2(15) FACT_TYPE_CODE NOT NULL VARCHAR2(2) ISO_CRNCY_CODE_CHAR VARCHAR2(3) SRCE_SYS_ID NOT NULL NUMBER(15) LYOUT_ID NUMBER(4) FACT_QLTY_CODE VARCHAR2(1) MKT_CLASS_CODE VARCHAR2(1) DEMND_PLAN_CUST_GRP_CODE VARCHAR2(10) TIME_PERD_TYPE_CODE NOT NULL VARCHAR2(4) TIME_PERD_END_DATE NOT NULL DATE DUE_PERD NOT NULL DATE ROW_ORIGN VARCHAR2(1) PRTTN_CODE VARCHAR2(15) PARNT_FACT_ID NUMBER(15) DELIV_RCVD_ID NUMBER(10) ORIG_UNIT_ID NUMBER(11) SQL:idwsp3 -> desc copa_mdo_srce_sys_map Name Null? Type ----------------------------------------- -------- -------------------- SRCE_SYS_ID NOT NULL NUMBER(15) AGG_SRCE_SYS_ID NOT NULL NUMBER(15) MDO_ID NOT NULL VARCHAR2(15)
Indexes
Table Name Index Name Column Name Col Pos. ========== ========== ============ ======== COPA_MDO_SRCE_SYS_MAP COPA_MDO_SRCE_SYS_MAP_PK SRCE_SYS_ID 1 FACT_DMNSN_Q3 FACT_DMNSN_Q3_IDX1 SRCE_SYS_ID 1 FACT_DMNSN_Q3 FACT_DMNSN_Q3_IDX1 DUE_PERD 2 FACT_DMNSN_Q3 FACT_DMNSN_Q3_IDX1 FACT_ID 3
FACT_DMNSN_Q3_IDX1 is a local index on FACT_DMNSN_Q3 and the table is partitioned by SRCE_SYS_ID, DUE_PERD, FACT_ID.
Here are some queries to give you feel about the contents of the objects
select count(*) from copa_mdo_srce_sys_map; COUNT(*)
54
select count(*) from copa_mdo_srce_sys_map where mdo_id='NA';
COUNT(*)
10
select srce_sys_id, due_perd, count(*)
from fact_dmnsn_q3 group by srce_sys_id, due_perd
SRCE_SYS_ID DUE_PERD COUNT(*)
----------- ----------- ----------
178 31-JUL-2000 2465
178 31-AUG-2000 2587
178 30-SEP-2000 2733
178 31-OCT-2000 2753
178 30-NOV-2000 2697
178 31-DEC-2000 2660
178 31-JAN-2001 2621
178 28-FEB-2001 2899
178 31-MAR-2001 2959
178 30-APR-2001 2917
178 31-MAY-2001 2855
178 30-JUN-2001 2934
178 31-JUL-2001 2803
178 31-AUG-2001 2787
178 30-SEP-2001 2964
178 31-OCT-2001 2903
178 30-NOV-2001 2969
178 31-DEC-2001 3029
178 31-JAN-2002 2997
178 28-FEB-2002 3011
178 31-MAR-2002 2884
178 30-APR-2002 116455
178 31-MAY-2002 141384
178 30-JUN-2002 174043
178 31-JUL-2002 168288
179 30-APR-2001 1935
179 31-MAY-2001 2536
179 30-JUN-2001 2663
179 31-JUL-2001 2563
179 31-AUG-2001 2569
179 30-SEP-2001 2571
179 31-OCT-2001 2696
179 30-NOV-2001 2552
179 31-DEC-2001 2382
179 31-JAN-2002 2728
179 28-FEB-2002 2560
179 31-MAR-2002 2879
179 30-APR-2002 17544
179 31-MAY-2002 17841
179 30-JUN-2002 14750
179 31-JUL-2002 17122
180 31-AUG-1999 1357
180 31-OCT-1999 1462
180 30-NOV-1999 1494
180 31-DEC-1999 1379
180 31-JAN-2000 1362
180 29-FEB-2000 1343
180 31-MAR-2000 1391
180 30-APR-2000 1350
180 31-MAY-2000 1394
180 30-JUN-2000 1277
180 31-JUL-2000 1294
180 31-AUG-2000 1251
180 30-SEP-2000 1499
180 31-OCT-2000 1341
180 30-NOV-2000 1297
180 31-DEC-2000 1261
180 31-JAN-2001 1429
180 28-FEB-2001 1581
180 31-MAR-2001 1649
180 30-APR-2001 1517
180 31-MAY-2001 1475
180 30-JUN-2001 1546
180 31-JUL-2001 1495
180 31-AUG-2001 1542
180 30-SEP-2001 1610
180 31-OCT-2001 1516
180 30-NOV-2001 1692
180 31-DEC-2001 1379
180 31-JAN-2002 1412
180 28-FEB-2002 1265
180 31-MAR-2002 1373
180 30-APR-2002 990
180 31-MAY-2002 1266
180 30-JUN-2002 1071
180 31-JUL-2002 1176
181 31-AUG-1999 763
181 31-OCT-1999 1203
181 30-NOV-1999 1275
181 31-DEC-1999 1217
181 31-JAN-2000 1022
181 29-FEB-2000 1111
181 31-MAR-2000 926
181 30-APR-2000 895
181 31-MAY-2000 870
181 30-JUN-2000 994
181 31-JUL-2000 993
181 31-AUG-2000 932
181 30-SEP-2000 916
181 31-OCT-2000 908
181 30-NOV-2000 986
181 31-DEC-2000 940
181 31-JAN-2001 924
181 28-FEB-2001 831
181 31-MAR-2001 845
181 30-APR-2001 919
181 31-MAY-2001 827
SRCE_SYS_ID DUE_PERD COUNT(*)
----------- ----------- ----------
181 30-JUN-2001 900
181 31-JUL-2001 809
181 31-AUG-2001 771
181 30-SEP-2001 773
181 31-OCT-2001 751
181 30-NOV-2001 749
181 31-DEC-2001 1204
181 31-JAN-2002 1291
181 28-FEB-2002 1157
181 31-MAR-2002 1310
181 30-APR-2002 1278
181 31-MAY-2002 1284
181 30-JUN-2002 1253
181 31-JUL-2002 1190
182 31-AUG-1999 733
182 31-OCT-1999 792
182 30-NOV-1999 682
182 31-DEC-1999 642
182 31-JAN-2000 571
182 29-FEB-2000 500
182 31-MAR-2000 483
182 30-APR-2000 475
182 31-MAY-2000 478
182 30-JUN-2000 468
182 31-JUL-2000 417
182 31-AUG-2000 447
182 30-SEP-2000 519
182 31-OCT-2000 487
182 30-NOV-2000 486
182 31-DEC-2000 500
182 31-JAN-2001 484
182 28-FEB-2001 536
182 31-MAR-2001 560
182 30-APR-2001 539
182 31-MAY-2001 620
182 30-JUN-2001 554
182 31-JUL-2001 564
182 31-AUG-2001 543
182 30-SEP-2001 544
182 31-OCT-2001 579
182 30-NOV-2001 564
182 31-DEC-2001 537
182 31-JAN-2002 523
182 28-FEB-2002 518
182 31-MAR-2002 784
182 30-APR-2002 22928
182 31-MAY-2002 20778
182 30-JUN-2002 19950
182 31-JUL-2002 19063
183 31-AUG-1999 1282
183 31-OCT-1999 1496
183 30-NOV-1999 1390
183 31-DEC-1999 1658
183 31-JAN-2000 1517
183 29-FEB-2000 1363
183 31-MAR-2000 1151
183 30-APR-2000 1094
183 31-MAY-2000 1152
183 30-JUN-2000 1190
183 31-JUL-2000 1247
183 31-AUG-2000 1112
183 30-SEP-2000 1095
183 31-OCT-2000 1225
183 30-NOV-2000 1253
183 31-DEC-2000 1259
183 31-JAN-2001 1325
183 28-FEB-2001 1264
183 31-MAR-2001 1383
183 30-APR-2001 1355
183 31-MAY-2001 1156
183 30-JUN-2001 1262
183 31-JUL-2001 1309
183 31-AUG-2001 1294
183 30-SEP-2001 1579
183 31-OCT-2001 1648
183 30-NOV-2001 1460
183 31-DEC-2001 1418
183 31-JAN-2002 1449
183 28-FEB-2002 1359
183 31-MAR-2002 1715
183 30-APR-2002 1519
183 31-MAY-2002 1563
183 30-JUN-2002 1341
183 31-JUL-2002 1456
184 31-OCT-1999 846
184 30-NOV-1999 873
184 31-DEC-1999 742
184 31-JAN-2000 780
184 29-FEB-2000 834
184 31-MAR-2000 846
184 30-APR-2000 808
184 31-MAY-2000 793
184 30-JUN-2000 812
184 31-JUL-2000 859
184 31-AUG-2000 806
184 30-SEP-2000 882
184 31-OCT-2000 741
SRCE_SYS_ID DUE_PERD COUNT(*)
----------- ----------- ----------
184 30-NOV-2000 745
184 31-DEC-2000 761
184 31-JAN-2001 629
184 28-FEB-2001 663
184 31-MAR-2001 833
184 30-APR-2001 653
184 31-MAY-2001 741
184 30-JUN-2001 738
184 31-JUL-2001 705
184 31-AUG-2001 748
184 30-SEP-2001 739
184 31-OCT-2001 781
184 30-NOV-2001 751
184 31-DEC-2001 778
184 31-JAN-2002 646
184 28-FEB-2002 795
184 31-MAR-2002 818
184 30-APR-2002 19185
184 31-MAY-2002 19151
184 30-JUN-2002 18582
184 31-JUL-2002 16628
186 31-AUG-1999 1859
186 31-OCT-1999 1925
186 30-NOV-1999 1922
186 31-DEC-1999 1755
186 31-JAN-2000 1653
186 29-FEB-2000 1855
186 31-MAR-2000 1834
186 30-APR-2000 1808
186 31-MAY-2000 1988
186 30-JUN-2000 1838
186 31-JUL-2000 1874
186 31-AUG-2000 1839
186 30-SEP-2000 1873
186 31-OCT-2000 1766
186 30-NOV-2000 1843
186 31-DEC-2000 1814
186 31-JAN-2001 1830
186 28-FEB-2001 1765
186 31-MAR-2001 2010
186 30-APR-2001 1933
186 31-MAY-2001 2005
186 30-JUN-2001 2142
186 31-JUL-2001 1707
186 31-AUG-2001 1854
186 30-SEP-2001 1890
186 31-OCT-2001 1847
186 30-NOV-2001 2008
186 31-DEC-2001 1901
186 31-JAN-2002 1869
186 28-FEB-2002 2120
186 31-MAR-2002 2674
186 30-APR-2002 98676
186 31-MAY-2002 106429
186 30-JUN-2002 98260
186 31-JUL-2002 88069
478 31-JUL-2002 889841
479 31-JUL-2002 132331
1008 31-JUL-2002 532
1010 31-JUL-2002 2244
----------
sum 2560503
_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail.
http://www.hotmail.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: viral desai INET: viral303_at_hotmail.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Mon Aug 12 2002 - 18:03:21 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |