It may be easier to look at it this way:
the idea of a hash join is that you read the involved tables (or
indexes if they have all the columns you need) in their entirety
once and only once, and use hashing algorithms to match up the
records. So index range scan is really not applicable. The only
option is full scan.
viral303_at_hotmail.com (Viral Desai) wrote in message news:<aa3ca283.0208131041.73e478ed_at_posting.google.com>...
> Hi Herman,
>
> Here are the details.
> 1. I removed the hint, it still does the full scan on the index.
> 2. Yes, your guess is right on. The due_perd is not the leading column
> in the index. It is the second column in the index, the first column
> of the index (multiple srce_sys_id values ) are coming from
> copa_mdo_srce_sys_map table.
>
> Table/index designs
> ===================
> desc fact_dmnsn_q3
> 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 * from copa_mdo_srce_sys_map where mdo_id='NA';
>
> SRCE_SYS_ID AGG_SRCE_SYS_ID MDO_ID
> ----------- --------------- ---------------
> 478 488 NA
> 479 488 NA
> 482 488 NA
> 483 488 NA
> 1006 488 NA
> 1007 488 NA
> 1008 488 NA
> 1009 488 NA
> 1010 488 NA
> 1011 488 NA
>
>
> 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
>
> Herman de Boer <h.de.boer_at_itcg.nl> wrote in message news:<ajaveb$3n0$1_at_news1.xs4all.nl>...
> > Viral Desai wrote:
> >
> > >RDBMS Version: 8.1.7.2
> > >Operating System and Version: HP UX 11.0
> > >
> > >I need some help understanding the access method that Oracle uses with
> > >Partitioned tables.
> > >
> > >I have sql code (Please see below) that has a non-partitioned very
> > >small table "copa_mdo_srce_sys_map" and a very large partitioned table
> > >"FACT_DMNSN_Q3". Both tables, the index, and all partitions are
> > >analyzed using dbms_stats.
> > >
> > >When I used nested loop join, there is range-scan on the large tables
> > >index and partitions are pruned, where as when I use hash join then
> > >full index scan occurs. Can someone explain why this is so different?
> > >It makes a lot of impact in run time and hash join performance is very
> > >bad.
> > >
> > >(FYI - hash_area_size is 4194304 and hash_multiblock_io_count is 64. I
> > >have tried to change hash_area_size at session level but it has not
> > >impacted the method of index scan (full vs. range) on the large
> > >table.)
> > >
> > >Thanks
> > >Viral
> > >
> > >NESTED LOOP
> > >===========
> > >select --+use_nl(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
> > >----------------------------------------------------------
> > >0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1333 Card=54869
> > >Bytes=5925852)
> > >1 0 NESTED LOOPS (Cost=1333 Card=54869 Bytes=5925852)
> > >2 1 TABLE ACCESS (FULL) OF 'COPA_MDO_SRCE_SYS_MAP' (Cost=1 Card=9
> > >Bytes=54)
> > >3 1 PARTITION RANGE (ITERATOR)
> > >4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FACT_DMNSN_Q3' (Cost=148
> > >Card=73158 Bytes=7462116)
> > >5 4 INDEX (RANGE SCAN) OF 'FACT_DMNSN_Q3_IDX1' (NON-UNIQUE) (Cost=30
> > >Card=73158)
> > >
> > >HASH JOIN
> > >=========
> > >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
> > >----------------------------------------------------------
> > >0 SELECT STATEMENT Optimizer=CHOOSE (Cost=13103 Card=54869
> > >Bytes=5925852)
> > >1 0 HASH JOIN (Cost=13103 Card=54869 Bytes=5925852)
> > >2 1 TABLE ACCESS (FULL) OF 'COPA_MDO_SRCE_SYS_MAP' (Cost=1 Card=9
> > >Bytes=54)
> > >3 1 PARTITION RANGE (ALL)
> > >4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'FACT_DMNSN_Q3' (Cost=13101
> > >Card=73158 Bytes=7462116)
> > >5 4 INDEX (FULL SCAN) OF 'FACT_DMNSN_Q3_IDX1' (NON-UNIQUE) (Cost=11693
> > >Card=73158)
> >
> > Viral,
> >
> > I think you should remove the hint 'index(fd)'. It specifies the
> > access method for the fact_dmndn_q3 table. I guess that the due_perd
> > column is NOT the leading column of the index?
> > Please post index details.
> >
> > Kind Regards,
> >
> > Herman de Boer
> > sr consultant
> > IT Consultancy Group bv.
Received on Wed Aug 14 2002 - 09:55:02 CDT
