一次500行SQL的优化-CSDN博客

OBIEE Developer 发来邮件，叫我看一下下面SQL，说跑30分钟不出结果。
SELECT DISTINCT D1.C4 AS C1,
D1.C5 AS C2,
D1.C6 AS C3,
D1.C7 AS C4,
D1.C8 AS C5,
D1.C9 AS C6,
D1.C10 AS C7,
0 AS C8,
D1.C3 AS C9,
D1.C2 AS C10,
D1.C1 AS C11,
D1.C11 AS C12,
D1.C12 AS C13,
D1.C13 AS C14
FROM (SELECT SUM(T2075740.TOT_PLAN_NOS_AMT) AS C1,
SUM(T2075740.TOT_PLAN_NIV_AMT) AS C2,
SUM(T2075740.TOT_PLAN_GIV_AMT) AS C3,
T2076898.CHANL_TYPE_DESC AS C4,
T2076898.ACCT_GRP_NAME AS C5,
T2073345.ASSOC_ACCT_NAME AS C6,
T2073345.ASSOC_NAME AS C7,
T2076296.PROD_DESC AS C8,
T2074757.MTH_NAME AS C9,
T2074757.MTH_NUM AS C10,
T2074456.BUS_UNIT_NAME AS C11,
T2074456.LOCAL_CRNCY_CODE AS C12,
T2074456.BUS_UNIT_SKID AS C13
FROM ADWG_OPTIMA_AP11.OPT_ACCT_ASDN_TYPE2_DIM T2073345,
ADWG_OPTIMA_AP11.OPT_ACCT_FDIM T2076898 /* OPT_ACCT_PRMTN_FDIM */,
ADWG_OPTIMA_AP11.OPT_PROD_BRAND_ASDN_DIM T2076296,
ADWG_OPTIMA_AP11.OPT_BUS_UNIT_FDIM T2074456,
ADWG_OPTIMA_AP11.OPT_CAL_MASTR_DIM T2074757 /* OPT_CAL_MASTR_DIM01 */,
ADWG_OPTIMA_AP11.OPT_BRAND_BASLN_IFCT T2075740
WHERE (T2073345.ACCT_SKID = T2075740.PRMTN_ACCT_SKID AND
T2073345.BUS_UNIT_SKID = T2075740.BUS_UNIT_SKID AND
T2075740.PROD_SKID = T2076296.BRAND_SKID AND
T2075740.BUS_UNIT_SKID = T2076296.BUS_UNIT_SKID AND
T2074456.BUS_UNIT_SKID = T2075740.BUS_UNIT_SKID AND
T2074757.CAL_MASTR_SKID = T2075740.DATE_SKID AND
T2075740.BUS_UNIT_SKID = T2076898.BUS_UNIT_SKID AND
T2075740.PRMTN_ACCT_SKID = T2076898.ACCT_SKID AND
T2074456.BUS_UNIT_NAME = 'Australia' AND
T2074757.FISC_YR_ABBR_NAME = 'FY10/11' AND
T2075740.FY_DATE_SKID = T2076296.FY_DATE_SKID AND
T2076296.PROD_LVL_DESC = 'Company' AND
INSTR(T2073345.ASSOC_ACCT_LONG_NAME, T2073345.ACCT_LONG_NAME) = 1 AND
(T2075740.DATE_SKID - T2073345.ASDN_EFF_END_DATE_SKID) *
(T2075740.DATE_SKID - T2073345.ASDN_EFF_START_DATE_SKID) <= 0 AND
(T2073345.ASSOC_ACCT_LONG_NAME IN
('A1 BATTERY PRO - 2001417551',
'AAA FNQ BATTERY WHOLESALES - 2001417545',
'ABCO PRODUCTS - 2001666045',
'ABCOE DISTRIBUTORS - 2002157346',
'ADMIN & MARKETING SOLUTIONS - 2000371945',
'ALL-WAYS FOODS - 2002127142',
'AMWAY CASTLE HILL - 2001416668',
'ANCOL SA - 2001415575',
'ANSPEC PTY LTD - 2001013611',
'API AUSTRALIA - 2000404011',
'API NATIONAL - 2000404012',
'ARNOTTS ANZ - 2000905941',
'ARNOTTS AUS - 2000905943',
'AU R11 TESTING DUMMY XX12 2 - 2333333355',
'AU ROOF ACCOUNT - 1900001790',
'AUSTRALIA ALL OTHER - 2000342300',
'AUSTRALIA POST - 2001417362',
'AUSTRALIA POST - 2001829802',
'BATTERIES PLUS - 2001417738',
'BATTERY PACKS PLUS AUST PTY LTD - 2001414925',
'BATTERY SPECIALTIES - 2000405342',
'BATTERY SPECIALTIES GENERAL - 2000405340',
'BATTERY SPECIALTIES HARDWARE - 2000405341',
'BEACON IMPORT EXPORT PTY LTD - 2001880326',
'BI-RITE ELECTRICAL - 2001418600',
'BIDVEST PTY LTD - 2001414732',
'BIG W - HO - 2000335689',
'BIG W AUSTRALIA - 2000403935',
'BIG W HEAD OFFICE PAYER - 2000335691',
'BING LEE - 2000404130',
'BING LEE BELCONNEN - 2001588102',
'BING LEE BURWOOD - 2001414789',
'BING LEE ELECTRICS PTY LTD - 2001414689',
'BONFECT SNACKFOOD SERVICES - 2002127139',
'BOYD & MAJOR LTD - 2000299023',
'BSR - 2001630602',
'BUNZL - 2000405345',
'BUY RITE STORES - 2000404982',
'CASEY''S BATTERY CENTRE - 2001552661',
'CASEY''S BATTERY CENTRE TOWNSVILLE - 2001976857',
'CASTLE HAMPERS PTY LTD - 2001418202',
'CHATSWOOD CHASE PHARMACY - 2001417709',
'CHEMPLUS - 2000404979',
'CHRISCO HAMPERS - 2001834670',
'CHRISCO HAMPERS AUSTRALIA LTD - 2001417711',
'CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518',
'CLIFFORD HALLAM PHARMACEUTICAL - 2000351986',
'CLIVE ANTHONYS - 2000352007',
'CLIVE PEETERS - 2001418601',
'CMO Account - 1200000000',
'CNW - 2000403937',
'COLES 9711 LAUNCESTON DC - 2000351935',
'COLES GROUP LTD - 2000352009',
'COLES S/MARKETS AUSTRALIA - 2000404618',
'COLES SUPERMARKETS - 2000352006',
'COMMERCIAL - 2000342628',
'COMPLETE OFFICE SUPPLIES - 2000335710',
'CONFECT EXPRESS - 2002127159',
'CONFECTIONERY - 2000405343',
'CONFECTIONERY OTHER - 2001811825',
'CORNETTS - 2000277498',
'CORPORATE EXPRESS - 2000335711',
'CORPORATE EXPRESS - AUS - 2002144899',
'CORPORATE EXPRESS - AUS - 2002166867',
'COSTCO AUSTRALIA - 2002122626',
'COSTCO WHOLESALE AUSTRALIA - 2002093908',
'CUMBERLAND INDUSTRIES - 2001451140',
'D A & P A CRETAN -*SOLD-TO - 2000352254',
'DAVID JONES AUSTRALIA - 2001418604',
'DENTAVISION PTY LTD - 2001692729',
'DEPARTMENT STORES - 2000342629',
'DERNI GROUP - 2002249580',
'DICK SMITH ELECTRONICS - 2001418606',
'DICK SMITH ELECTRONICS PTY LTD - 2001415347',
'DISTRESSED STOCK. ST PETERS - 2001833533',
'DISTRESSED STOCK.COM PTY LTD - PAYE - 2001826497',
'DISTRIBUTORS - 2000335713',
'DOMAYNE - 2001418607',
'DOMAYNE NSW - 2000291617',
'DOWNTOWN & DYER - 2002188640',
'DRAKES - 2000335599',
'DUMMY AU TEST CASE 42193 - 2333333380',
'DUMMY AU XX12 - 2333333458',
'DUMMY AU XXHC - 2333333431',
'DUMMY AU XXHC - 2333333432',
'DUMMY AU XXHC - 2333333433',
'DUMMY AU XXHC - 2333333434',
'DUMMY AU XXHC - 2333333435',
'DUMMY AU XXHC - 2333333436',
'DUMMY AU XXHC - 2333333437',
'DUMMY AU XXHC - 2333333438',
'DUMMY AU XXHC - 2333333439',
'DUMMY AU XXHC - 2333333440',
'DUMMY AU XXHC - 2333333441',
'DUMMY AU XXHC - 2333333442',
'DUMMY AU XXHC - 2333333477',
'DUMMY AU XXHC - 2333333478',
'DUMMY AU XXHC - 2333333479',
'DUMMY D4_XXHC - 2333333357',
'DUMMY XXHC - 2333333381',
'DUMMY XXHC - 2333333382',
'DUMMY XXHC - 2333333384',
'DUMMY XXHC - 2333333385',
'DUMMY XXHC - 2333333386',
'DUMMY XXHC - 2333333387',
'DUMMY XXHC - 2333333388',
'DURA-SALES - 2000335696',
'EAGLE VISION PRODUCTS - 2002201116',
'ELECTRICAL - 2001027491',
'ELEMENT 14 PTY LTD - 2001417690',
'EXPORTS - 2001803538',
'FAR NORTH WHOLESALERS - SHIP-TO - 2000370233',
'FISHERS - 2000335600',
'FOODWORKS - 2000335598',
'FRANKLINS AUSTRALIA - 2000403727',
'GENERAL STATIONERY SUPPLIES PTY LTD - 2001417836',
'GNS - 2000335707',
'GROCERY HOLDINGS PTY LTD - 2000351938',
'GUNZ DENTAL PTY LTD - 2001692730',
'HAIRCARE DISTRIBUTION SERVICES DC - 2001379487',
'HARRIS SCARFE - 2001418611',
'HARVEY NORMAN - 2001418612',
'HARVEY NORMAN HOLDINGS - 2000404321',
'HARVEY NORMAN OFIS - 2001762720',
'HARVEY NORMAN QLD - 2000335701',
'HENRY SCHEIN - 2001418613',
'HOLLYHOCK BATTERIES - 2000405347',
'HOLMANS COMMERCIAL - 2001593794',
'HOLMANS COMMERCIAL - 2002211269',
'HOSPITALITY DEPOT - 2001952491',
'IGA - 2000277144',
'IGA WA - 2000404758',
'IGA WA ROMEO''S - 2000352996',
'INDEPENDENT ELECTRICAL STORES - 2001418615',
'INDEPENDENT PHARMACEUTICAL SUPPLIES - 2001417515',
'ISC GROCERY - 2000335601',
'ISLAND WHOLESALE TRADING - 2002064592',
'J BLACKWOOD - 2001418617',
'JA APPLIANCES SALES & SERVICE - 2001416700',
'JB HI FI - 2001418618',
'JB HI FI 001 GROUP HEAD OFFICE - 2001417600',
'JENNJAK PTY LTD - 2001631705',
'JOHN BARRY GROUP PTY LTD - PAYER - 2001417571',
'JOHN DANKS - 2000405349',
'JOYCE MAYNE - 2000404320',
'KEITH BOWDEN ELECTRICAL - 2001416697',
'KEY CONFECTIONERY - 2001811794',
'KMART - ANZ - 2001570396',
'KMART - AUSTRALIA - 2000404612',
'KMART 1118 SUNNYBANK HILL - 2000277087',
'KMART 9250 SMEATON GRANGE DC - 2000277143',
'KMART 9601 CANNING VALE DC - 2000277140',
'KMART AUSTRALIA (PAYER) - 2000300012',
'KMART AUSTRALIA PTY LTD - 2000334989',
'KOSMOS FOODS DARWIN - 2001417543',
'LAYBY SERVICES AUSTRALIA - 2002314284',
'LEISURE COAST W/SALE CONFECT.(DO NO - 2000342568',
'LYRECO - 2000373775',
'MASTER INSTRUMENTS PTY LTD - 2001417549',
'MAXIM STATIONERY - 2001673338',
'METCASH AUSTRALIA - 2000277145',
'MISC COMMERCIAL CUSTOMERS - 2000335712',
'MISC PHARMACY AUSTRALIA - 2000343070',
'MNB VARIETY IMPORTS PTY LTD - 2001835878',
'MONEY SAVER FOODS - 2001663227',
'MORNINGTON BETTA ELECTRICAL - 2001537022',
'MOTOR TRADERS - 2001417727',
'MR SHAVER - 2001417596',
'MR SHAVER WA - 2001417595',
'MUIRS ELECTRICAL - 2001418620',
'MY CHEMIST (NODE) - 2002277899',
'MY CHEMIST HEALTH & BEAUTY T/A - 2001417574',
'MYER - AUSTRALIA - 2000404615',
'MYER 203 BONDI JUNCTION - 2001086627',
'MYER LTD - 2001074314',
'NATIONAL CONFECTIONERY WHOLESALERS - 2001811793',
'NATIONAL PHARMACIES - 2000404989',
'NBD TEAM - 2000345756',
'NON-REVENUE ORDER CUSTOMERS - 2000377884',
'NQR SUPERMARKET CLEARANCE OUTLET - 2001837782',
'NUANCE GROUP - 2001418622',
'OFFICEMAX CONTRACT-AUSTRALIA - 2000403936',
'OFFICEMAX CONTRACT-AUSTRALIA - 2002141032',
'OFFICEWORKS - 2001418623',
'OFFICEWORKS AUSTRALIA - 2001811634',
'OLYMPIC BATTERIES PTY LTD - 2001416331',
'OMEGA POWER EQUIPMENT - 2000335715',
'OZE PHARMACY - 2001807542',
'OZE PHARMACY PENRITH - 2001417689',
'OZSALE BROOKVALE - 2002212164',
'OZWORKZ - 2002249813',
'P&G DIST NEW ZEALAND LTD - 0000010392',
'PETERS OF KENSINGTON - 2001415345',
'PETERS OF KENSINGTON - 2002328920',
'PFD FOOD SERVICES - 2002160907',
'PHARMACY AUSTRALIA - 2001079729',
'PHARMACY DIRECT - 2001417675',
'PHARMACY OTHER - 2000335570',
'POWER DC PTY LTD - 2001416493',
'POWER DC-TO BE DELIVERED TO REJECT SHOP - 2002208671',
'PREMIER BATTERIES PTY LTD - 2001414844',
'PRICELINE AUSTRALIA - 2000404976',
'PRICELINE PTY LTD - HO - 2000291612',
'PROCTER & GAMBLE AUSTRALIA PTY LTD - 0000010255',
'PROCTER & GAMBLE INTERNATIONAL - 0000010828',
'PROFESSIONAL - 2000342535',
'PROFESSIONAL - AU - 2001692776',
'R S COMPONENTS PTY LTD - 2001418218',
'RADIO PARTS GROUP - 2001416494',
'RADIO PARTS GROUP - DNU - 2001416495',
'RAIN ONE AUSTRALIA PTY LTD - 2001417857',
'RALERU LTD - 2001414733',
'RAWCO PTY LTD - DNU - 2001416383',
'RDT TECHNOLOGIES & COMMUNICATIONS - 2001418154',
'REMNANT AUSTRALIA - 2000905947',
'REMNANTS - 2000291615',
'REPCO - 2001418625',
'RETRAVISION - 2001418626',
'RETRAVISION VIC - 2000342521',
'RITCHIES - 2000335597',
'ROAD RUNNER WHOLESALE - 2002290470',
'ROY YOUNG CHEMIST - CHATSWOOD - 2001417583',
'RUSHMORE DISTRIBUTORS - 2001415346',
'RUSHMORE MM CONTRACT ACCOUNT - 2001437471',
'SDS QUEENSLANDS GOVERNMENT - 2001415925',
'SHAVER SHOP - 2001418627',
'SIGMA - AUSTRALIA - 2000404350',
'SMALL TRADERS OTHER - 2000354069',
'SMIMAC FOODSERVICE - 2002126452',
'SNACK FOODS - 2002127160',
'SOUL PATTINSON KINGSGROVE -DC - 2000258460',
'SOUNDY''S ELEC & COMPUTERS BARRABA - 2001586778',
'SOUNDY''S ELEC & COMPUTERS QUIRINDI - 2001416894',
'SOUNDY''S ELEC & COMPUTERS TAMWORTH - 2001663417',
'SOUNDY''S ELEC & COMPUTERS TAMWORTH DNU - 2001416870',
'SPAR AUSTRALIA LIMITED - 2000403934',
'SPAR AUSTRALIA LIMITED - 2000875618',
'SPC ARDMONA FACTORY SALES - 2001833534',
'SPELEAN PTY LTD - 2001416659',
'SSA CUSTOMER NODES - 2000354095',
'STATEWIDE INDEPEND WHOLESALERS LTD - 2000335524',
'STATEWIDE INDEPENDENT WHOLESALERS - 2001782010',
'STATIONERS SUPPLY - 2000335708',
'SUPER CHEAP AUTO - 2000335702',
'SUPPLY SA - 2001416666',
'SYDNEY CITY BETTA ELECTRICAL - 2001418079',
'SYMBION AUSTRALIA - 2000342519',
'T&E DISTRIBUTION & EXPORT (SHIP-TO) - 2002244042',
'TARGET - AUSTRALIA - 2000404613',
'TARGET 5098 FRANKSTON - 2000277419',
'TARGET 5137 DANDENONG PLAZA - 2000277403',
'TARGET 5157 CHIRNSIDE PARK - 2000277401',
'TARGET 5163 QUEANBEYAN - 2000277454',
'TARGET 9399 ALTONA NORTH DC - 2000277495',
'TARGET AUSTRALIA - 2000277497',
'TARGET AUSTRALIA PTY LTD - 2000335249')) AND
T2073345.ASSOC_NAME NOT IN
(SELECT DISTINCT T2073345.NAME AS C1
FROM ADWG_OPTIMA_AP11.OPT_ACCT_ASDN_TYPE2_DIM T2073345
WHERE (INSTR(T2073345.ASSOC_NAME, T2073345.NAME) = 0 AND
(T2073345.ASSOC_ACCT_LONG_NAME IN
('A1 BATTERY PRO - 2001417551',
'AAA FNQ BATTERY WHOLESALES - 2001417545',
'ABCO PRODUCTS - 2001666045',
'ABCOE DISTRIBUTORS - 2002157346',
'ADMIN & MARKETING SOLUTIONS - 2000371945',
'ALL-WAYS FOODS - 2002127142',
'AMWAY CASTLE HILL - 2001416668',
'ANCOL SA - 2001415575',
'ANSPEC PTY LTD - 2001013611',
'API AUSTRALIA - 2000404011',
'API NATIONAL - 2000404012',
'ARNOTTS ANZ - 2000905941',
'ARNOTTS AUS - 2000905943',
'AU R11 TESTING DUMMY XX12 2 - 2333333355',
'AU ROOF ACCOUNT - 1900001790',
'AUSTRALIA ALL OTHER - 2000342300',
'AUSTRALIA POST - 2001417362',
'AUSTRALIA POST - 2001829802',
'BATTERIES PLUS - 2001417738',
'BATTERY PACKS PLUS AUST PTY LTD - 2001414925',
'BATTERY SPECIALTIES - 2000405342',
'BATTERY SPECIALTIES GENERAL - 2000405340',
'BATTERY SPECIALTIES HARDWARE - 2000405341',
'BEACON IMPORT EXPORT PTY LTD - 2001880326',
'BI-RITE ELECTRICAL - 2001418600',
'BIDVEST PTY LTD - 2001414732',
'BIG W - HO - 2000335689',
'BIG W AUSTRALIA - 2000403935',
'BIG W HEAD OFFICE PAYER - 2000335691',
'BING LEE - 2000404130',
'BING LEE BELCONNEN - 2001588102',
'BING LEE BURWOOD - 2001414789',
'BING LEE ELECTRICS PTY LTD - 2001414689',
'BONFECT SNACKFOOD SERVICES - 2002127139',
'BOYD & MAJOR LTD - 2000299023',
'BSR - 2001630602',
'BUNZL - 2000405345',
'BUY RITE STORES - 2000404982',
'CASEY''S BATTERY CENTRE - 2001552661',
'CASEY''S BATTERY CENTRE TOWNSVILLE - 2001976857',
'CASTLE HAMPERS PTY LTD - 2001418202',
'CHATSWOOD CHASE PHARMACY - 2001417709',
'CHEMPLUS - 2000404979',
'CHRISCO HAMPERS - 2001834670',
'CHRISCO HAMPERS AUSTRALIA LTD - 2001417711',
'CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518',
'CLIFFORD HALLAM PHARMACEUTICAL - 2000351986',
'CLIVE ANTHONYS - 2000352007',
'CLIVE PEETERS - 2001418601',
'CMO Account - 1200000000',
'CNW - 2000403937',
'COLES 9711 LAUNCESTON DC - 2000351935',
'COLES GROUP LTD - 2000352009',
'COLES S/MARKETS AUSTRALIA - 2000404618',
'COLES SUPERMARKETS - 2000352006',
'COMMERCIAL - 2000342628',
'COMPLETE OFFICE SUPPLIES - 2000335710',
'CONFECT EXPRESS - 2002127159',
'CONFECTIONERY - 2000405343',
'CONFECTIONERY OTHER - 2001811825',
'CORNETTS - 2000277498',
'CORPORATE EXPRESS - 2000335711',
'CORPORATE EXPRESS - AUS - 2002144899',
'CORPORATE EXPRESS - AUS - 2002166867',
'COSTCO AUSTRALIA - 2002122626',
'COSTCO WHOLESALE AUSTRALIA - 2002093908',
'CUMBERLAND INDUSTRIES - 2001451140',
'D A & P A CRETAN -*SOLD-TO - 2000352254',
'DAVID JONES AUSTRALIA - 2001418604',
'DENTAVISION PTY LTD - 2001692729',
'DEPARTMENT STORES - 2000342629',
'DERNI GROUP - 2002249580',
'DICK SMITH ELECTRONICS - 2001418606',
'DICK SMITH ELECTRONICS PTY LTD - 2001415347',
'DISTRESSED STOCK. ST PETERS - 2001833533',
'DISTRESSED STOCK.COM PTY LTD - PAYE - 2001826497',
'DISTRIBUTORS - 2000335713',
'DOMAYNE - 2001418607',
'DOMAYNE NSW - 2000291617',
'DOWNTOWN & DYER - 2002188640',
'DRAKES - 2000335599',
'DUMMY AU TEST CASE 42193 - 2333333380',
'DUMMY AU XX12 - 2333333458',
'DUMMY AU XXHC - 2333333431',
'DUMMY AU XXHC - 2333333432',
'DUMMY AU XXHC - 2333333433',
'DUMMY AU XXHC - 2333333434',
'DUMMY AU XXHC - 2333333435',
'DUMMY AU XXHC - 2333333436',
'DUMMY AU XXHC - 2333333437',
'DUMMY AU XXHC - 2333333438',
'DUMMY AU XXHC - 2333333439',
'DUMMY AU XXHC - 2333333440',
'DUMMY AU XXHC - 2333333441',
'DUMMY AU XXHC - 2333333442',
'DUMMY AU XXHC - 2333333477',
'DUMMY AU XXHC - 2333333478',
'DUMMY AU XXHC - 2333333479',
'DUMMY D4_XXHC - 2333333357',
'DUMMY XXHC - 2333333381',
'DUMMY XXHC - 2333333382',
'DUMMY XXHC - 2333333384',
'DUMMY XXHC - 2333333385',
'DUMMY XXHC - 2333333386',
'DUMMY XXHC - 2333333387',
'DUMMY XXHC - 2333333388',
'DURA-SALES - 2000335696',
'EAGLE VISION PRODUCTS - 2002201116',
'ELECTRICAL - 2001027491',
'ELEMENT 14 PTY LTD - 2001417690',
'EXPORTS - 2001803538',
'FAR NORTH WHOLESALERS - SHIP-TO - 2000370233',
'FISHERS - 2000335600',
'FOODWORKS - 2000335598',
'FRANKLINS AUSTRALIA - 2000403727',
'GENERAL STATIONERY SUPPLIES PTY LTD - 2001417836',
'GNS - 2000335707',
'GROCERY HOLDINGS PTY LTD - 2000351938',
'GUNZ DENTAL PTY LTD - 2001692730',
'HAIRCARE DISTRIBUTION SERVICES DC - 2001379487',
'HARRIS SCARFE - 2001418611',
'HARVEY NORMAN - 2001418612',
'HARVEY NORMAN HOLDINGS - 2000404321',
'HARVEY NORMAN OFIS - 2001762720',
'HARVEY NORMAN QLD - 2000335701',
'HENRY SCHEIN - 2001418613',
'HOLLYHOCK BATTERIES - 2000405347',
'HOLMANS COMMERCIAL - 2001593794',
'HOLMANS COMMERCIAL - 2002211269',
'HOSPITALITY DEPOT - 2001952491',
'IGA - 2000277144',
'IGA WA - 2000404758',
'IGA WA ROMEO''S - 2000352996',
'INDEPENDENT ELECTRICAL STORES - 2001418615',
'INDEPENDENT PHARMACEUTICAL SUPPLIES - 2001417515',
'ISC GROCERY - 2000335601',
'ISLAND WHOLESALE TRADING - 2002064592',
'J BLACKWOOD - 2001418617',
'JA APPLIANCES SALES & SERVICE - 2001416700',
'JB HI FI - 2001418618',
'JB HI FI 001 GROUP HEAD OFFICE - 2001417600',
'JENNJAK PTY LTD - 2001631705',
'JOHN BARRY GROUP PTY LTD - PAYER - 2001417571',
'JOHN DANKS - 2000405349',
'JOYCE MAYNE - 2000404320',
'KEITH BOWDEN ELECTRICAL - 2001416697',
'KEY CONFECTIONERY - 2001811794',
'KMART - ANZ - 2001570396',
'KMART - AUSTRALIA - 2000404612',
'KMART 1118 SUNNYBANK HILL - 2000277087',
'KMART 9250 SMEATON GRANGE DC - 2000277143',
'KMART 9601 CANNING VALE DC - 2000277140',
'KMART AUSTRALIA (PAYER) - 2000300012',
'KMART AUSTRALIA PTY LTD - 2000334989',
'KOSMOS FOODS DARWIN - 2001417543',
'LAYBY SERVICES AUSTRALIA - 2002314284',
'LEISURE COAST W/SALE CONFECT.(DO NO - 2000342568',
'LYRECO - 2000373775',
'MASTER INSTRUMENTS PTY LTD - 2001417549',
'MAXIM STATIONERY - 2001673338',
'METCASH AUSTRALIA - 2000277145',
'MISC COMMERCIAL CUSTOMERS - 2000335712',
'MISC PHARMACY AUSTRALIA - 2000343070',
'MNB VARIETY IMPORTS PTY LTD - 2001835878',
'MONEY SAVER FOODS - 2001663227',
'MORNINGTON BETTA ELECTRICAL - 2001537022',
'MOTOR TRADERS - 2001417727',
'MR SHAVER - 2001417596',
'MR SHAVER WA - 2001417595',
'MUIRS ELECTRICAL - 2001418620',
'MY CHEMIST (NODE) - 2002277899',
'MY CHEMIST HEALTH & BEAUTY T/A - 2001417574',
'MYER - AUSTRALIA - 2000404615',
'MYER 203 BONDI JUNCTION - 2001086627',
'MYER LTD - 2001074314',
'NATIONAL CONFECTIONERY WHOLESALERS - 2001811793',
'NATIONAL PHARMACIES - 2000404989',
'NBD TEAM - 2000345756',
'NON-REVENUE ORDER CUSTOMERS - 2000377884',
'NQR SUPERMARKET CLEARANCE OUTLET - 2001837782',
'NUANCE GROUP - 2001418622',
'OFFICEMAX CONTRACT-AUSTRALIA - 2000403936',
'OFFICEMAX CONTRACT-AUSTRALIA - 2002141032',
'OFFICEWORKS - 2001418623',
'OFFICEWORKS AUSTRALIA - 2001811634',
'OLYMPIC BATTERIES PTY LTD - 2001416331',
'OMEGA POWER EQUIPMENT - 2000335715',
'OZE PHARMACY - 2001807542',
'OZE PHARMACY PENRITH - 2001417689',
'OZSALE BROOKVALE - 2002212164',
'OZWORKZ - 2002249813',
'P&G DIST NEW ZEALAND LTD - 0000010392',
'PETERS OF KENSINGTON - 2001415345',
'PETERS OF KENSINGTON - 2002328920',
'PFD FOOD SERVICES - 2002160907',
'PHARMACY AUSTRALIA - 2001079729',
'PHARMACY DIRECT - 2001417675',
'PHARMACY OTHER - 2000335570',
'POWER DC PTY LTD - 2001416493',
'POWER DC-TO BE DELIVERED TO REJECT SHOP - 2002208671',
'PREMIER BATTERIES PTY LTD - 2001414844',
'PRICELINE AUSTRALIA - 2000404976',
'PRICELINE PTY LTD - HO - 2000291612',
'PROCTER & GAMBLE AUSTRALIA PTY LTD - 0000010255',
'PROCTER & GAMBLE INTERNATIONAL - 0000010828',
'PROFESSIONAL - 2000342535',
'PROFESSIONAL - AU - 2001692776',
'R S COMPONENTS PTY LTD - 2001418218',
'RADIO PARTS GROUP - 2001416494',
'RADIO PARTS GROUP - DNU - 2001416495',
'RAIN ONE AUSTRALIA PTY LTD - 2001417857',
'RALERU LTD - 2001414733',
'RAWCO PTY LTD - DNU - 2001416383',
'RDT TECHNOLOGIES & COMMUNICATIONS - 2001418154',
'REMNANT AUSTRALIA - 2000905947',
'REMNANTS - 2000291615',
'REPCO - 2001418625',
'RETRAVISION - 2001418626',
'RETRAVISION VIC - 2000342521',
'RITCHIES - 2000335597',
'ROAD RUNNER WHOLESALE - 2002290470',
'ROY YOUNG CHEMIST - CHATSWOOD - 2001417583',
'RUSHMORE DISTRIBUTORS - 2001415346',
'RUSHMORE MM CONTRACT ACCOUNT - 2001437471',
'SDS QUEENSLANDS GOVERNMENT - 2001415925',
'SHAVER SHOP - 2001418627',
'SIGMA - AUSTRALIA - 2000404350',
'SMALL TRADERS OTHER - 2000354069',
'SMIMAC FOODSERVICE - 2002126452',
'SNACK FOODS - 2002127160',
'SOUL PATTINSON KINGSGROVE -DC - 2000258460',
'SOUNDY''S ELEC & COMPUTERS BARRABA - 2001586778',
'SOUNDY''S ELEC & COMPUTERS QUIRINDI - 2001416894',
'SOUNDY''S ELEC & COMPUTERS TAMWORTH - 2001663417',
'SOUNDY''S ELEC & COMPUTERS TAMWORTH DNU - 2001416870',
'SPAR AUSTRALIA LIMITED - 2000403934',
'SPAR AUSTRALIA LIMITED - 2000875618',
'SPC ARDMONA FACTORY SALES - 2001833534',
'SPELEAN PTY LTD - 2001416659',
'SSA CUSTOMER NODES - 2000354095',
'STATEWIDE INDEPEND WHOLESALERS LTD - 2000335524',
'STATEWIDE INDEPENDENT WHOLESALERS - 2001782010',
'STATIONERS SUPPLY - 2000335708',
'SUPER CHEAP AUTO - 2000335702',
'SUPPLY SA - 2001416666',
'SYDNEY CITY BETTA ELECTRICAL - 2001418079',
'SYMBION AUSTRALIA - 2000342519',
'T&E DISTRIBUTION & EXPORT (SHIP-TO) - 2002244042',
'TARGET - AUSTRALIA - 2000404613',
'TARGET 5098 FRANKSTON - 2000277419',
'TARGET 5137 DANDENONG PLAZA - 2000277403',
'TARGET 5157 CHIRNSIDE PARK - 2000277401',
'TARGET 5163 QUEANBEYAN - 2000277454',
'TARGET 9399 ALTONA NORTH DC - 2000277495',
'TARGET AUSTRALIA - 2000277497',
'TARGET AUSTRALIA PTY LTD - 2000335249')))))
GROUP BY T2073345.ASSOC_NAME,
T2073345.ASSOC_ACCT_NAME,
T2074456.BUS_UNIT_SKID,
T2074456.BUS_UNIT_NAME,
T2074456.LOCAL_CRNCY_CODE,
T2074757.MTH_NAME,
T2074757.MTH_NUM,
T2076296.PROD_DESC,
T2076898.CHANL_TYPE_DESC,
T2076898.ACCT_GRP_NAME) D1
ORDER BY C1, C2, C3, C4, C5, C6, C7, C12, C13, C14;

在跑这个SQL之前，需要跑如下的SQL
alter session set star_transformation_enabled=false;
alter session disable parallel query;
Alter session set "_optimizer_null_aware_antijoin"=false;
Alter session set "_OPTIMIZER_PUSH_PRED_COST_BASED"=false;

因为我们数据库版本是11.1.0.7 上面有非常多的BUG，所以设置上面的隐含参数可以避免某个bug.
上面的SQL是不会诱发那个BUG,但是由于OBIEE不能/不好控制代码，也就是不能为单个SQL修改隐含参数，所以所有的OBIEE的SQL都会RUN上面的CODE因为是报表，所以不能开并行，报表用户多了，开并行DB就扛不住了，因为报表数据是ETL汇总抽取过来的，表相对都比较小，而且统计信息也不会出什么问题所以这里我就不交代表大小，统计信息问题了。

1.如果不更改环境变量，执行计划如下

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2329744498

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 370 | 4114 (20)| 00:00:18 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10006 | 1 | 370 | 4114 (20)| 00:00:18 | | | Q1,06 | P->S | QC (ORDER) |
| 3 | SORT ORDER BY | | 1 | 370 | 4114 (20)| 00:00:18 | | | Q1,06 | PCWP | |
| 4 | PX RECEIVE | | 1 | 370 | 4114 (20)| 00:00:18 | | | Q1,06 | PCWP | |
| 5 | PX SEND RANGE | :TQ10005 | 1 | 370 | 4114 (20)| 00:00:18 | | | Q1,05 | P->P | RANGE |
| 6 | SORT GROUP BY | | 1 | 370 | 4114 (20)| 00:00:18 | | | Q1,05 | PCWP | |
|* 7 | HASH JOIN ANTI SNA | | 1 | 370 | 4114 (20)| 00:00:18 | | | Q1,05 | PCWP | |
| 8 | PX RECEIVE | | | | | | | | Q1,05 | PCWP | |
| 9 | PX SEND HASH | :TQ10003 | | | | | | | Q1,03 | P->P | HASH |
| 10 | NESTED LOOPS | | | | | | | | Q1,03 | PCWP | |
| 11 | NESTED LOOPS | | 1 | 316 | 2431 (25)| 00:00:11 | | | Q1,03 | PCWP | |
| 12 | NESTED LOOPS | | 1 | 264 | 2431 (25)| 00:00:11 | | | Q1,03 | PCWP | |
|* 13 | HASH JOIN | | 249 | 36105 | 2014 (29)| 00:00:09 | | | Q1,03 | PCWP | |
|* 14 | HASH JOIN | | 3397 | 285K| 1969 (29)| 00:00:09 | | | Q1,03 | PCWP | |
| 15 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | |
| 16 | PX RECEIVE | | 365 | 10950 | 511 (11)| 00:00:03 | | | Q1,03 | PCWP | |
| 17 | PX SEND BROADCAST | :TQ10000 | 365 | 10950 | 511 (11)| 00:00:03 | | | | S->P | BROADCAST |
|* 18 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 365 | 10950 | 511 (11)| 00:00:03 | | | | | |
| 19 | NESTED LOOPS | | 346K| 18M| 1450 (35)| 00:00:07 | | | Q1,03 | PCWP | |
| 20 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | |
| 21 | PX RECEIVE | | | | | | | | Q1,03 | PCWP | |
| 22 | PX SEND BROADCAST | :TQ10001 | | | | | | | | S->P | BROADCAST |
| 23 | TABLE ACCESS BY INDEX ROWID | OPT_BUS_UNIT_FDIM | 1 | 18 | 1 (0)| 00:00:01 | | | | | |
|* 24 | INDEX UNIQUE SCAN | OPT_BUS_UNIT_FDIM_UX1 | 1 | | 0 (0)| 00:00:01 | | | | | |
| 25 | PX BLOCK ITERATOR | | 346K| 18M| 1449 (35)| 00:00:07 | KEY | KEY | Q1,03 | PCWC | |
|* 26 | TABLE ACCESS FULL | OPT_BRAND_BASLN_IFCT | 346K| 18M| 1449 (35)| 00:00:07 | KEY | KEY | Q1,03 | PCWP | |
| 27 | BUFFER SORT | | | | | | | | Q1,03 | PCWC | |
| 28 | PX RECEIVE | | 16160 | 931K| 43 (33)| 00:00:01 | | | Q1,03 | PCWP | |
| 29 | PX SEND BROADCAST | :TQ10002 | 16160 | 931K| 43 (33)| 00:00:01 | | | | S->P | BROADCAST |
| 30 | PARTITION LIST SUBQUERY | | 16160 | 931K| 43 (33)| 00:00:01 |KEY(SQ)|KEY(SQ)| | | |
|* 31 | TABLE ACCESS FULL | OPT_PROD_BRAND_ASDN_DIM | 16160 | 931K| 43 (33)| 00:00:01 |KEY(SQ)|KEY(SQ)| | | |
|* 32 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_ACCT_ASDN_TYPE2_DIM | 1 | 119 | 3 (0)| 00:00:01 | ROWID | ROWID | Q1,03 | PCWP | |
|* 33 | INDEX RANGE SCAN | OPT_ACCT_ASDN_TYPE2_DIM_PK | 4 | | 2 (0)| 00:00:01 | | | Q1,03 | PCWP | |
|* 34 | INDEX UNIQUE SCAN | OPT_ACCT_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | | Q1,03 | PCWP | |
| 35 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 52 | 1 (0)| 00:00:01 | ROWID | ROWID | Q1,03 | PCWP | |
| 36 | PX RECEIVE | | 14 | 756 | 1682 (14)| 00:00:08 | | | Q1,05 | PCWP | |
| 37 | PX SEND HASH | :TQ10004 | 14 | 756 | 1682 (14)| 00:00:08 | | | Q1,04 | P->P | HASH |
| 38 | PX BLOCK ITERATOR | | 14 | 756 | 1682 (14)| 00:00:08 | 1 | 16 | Q1,04 | PCWC | |
|* 39 | TABLE ACCESS FULL | OPT_ACCT_ASDN_TYPE2_DIM | 14 | 756 | 1682 (14)| 00:00:08 | 1 | 16 | Q1,04 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

7 - access("T2073345"."ASSOC_NAME"="T2073345"."NAME")
13 - access("T2075740"."PROD_SKID"="T2076296"."BRAND_SKID" AND "T2075740"."BUS_UNIT_SKID"="T2076296"."BUS_UNIT_SKID" AND
"T2075740"."FY_DATE_SKID"="T2076296"."FY_DATE_SKID")
14 - access("T2074757"."CAL_MASTR_SKID"="T2075740"."DATE_SKID")
18 - filter("T2074757"."FISC_YR_ABBR_NAME"='FY10/11')
24 - access("T2074456"."BUS_UNIT_NAME"='Australia')
26 - filter("T2074456"."BUS_UNIT_SKID"="T2075740"."BUS_UNIT_SKID")
31 - filter("T2076296"."PROD_LVL_DESC"='Company')
32 - filter(INSTR("T2073345"."ASSOC_ACCT_LONG_NAME","T2073345"."ACCT_LONG_NAME")=1 AND ("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO - 2001417551' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ BATTERY WHOLESALES - 2001417545' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS - 2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING SOLUTIONS - 2000371945' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL - 2001416668' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA - 2001415575' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD - 2001013611' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='API AUSTRALIA - 2000404011' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API NATIONAL - 2000404012' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS - 2000905943' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12 2 - 2333333355' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF ACCOUNT - 1900001790' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA ALL OTHER - 2000342300' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001417362' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001829802' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY PACKS PLUS AUST PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES - 2000405342' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES HARDWARE - 2000405341' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT EXPORT PTY LTD - 2001880326' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD - 2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER - 2000335691' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN - 2001588102' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE ELECTRICS PTY LTD - 2001414689' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE
STORES - 2000404982' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE
TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE
PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS - 2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L -
2001417518' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE ANTHONYS - 2000352007'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS - 2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO Account - 1200000000' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CNW - 2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711 LAUNCESTON DC - 2000351935' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS AUSTRALIA - 2000404618' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES SUPERMARKETS - 2000352006' OR "T2073345"."ASS)
33 - access("T2073345"."ACCT_SKID"="T2075740"."PRMTN_ACCT_SKID")
34 - access("T2075740"."PRMTN_ACCT_SKID"="T2076898"."ACCT_SKID" AND "T2075740"."BUS_UNIT_SKID"="T2076898"."BUS_UNIT_SKID")
39 - filter(INSTR("T2073345"."ASSOC_NAME","T2073345"."NAME")=0 AND ("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO - 2001417551' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ BATTERY WHOLESALES - 2001417545' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS - 2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING SOLUTIONS - 2000371945' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL - 2001416668' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA - 2001415575' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD - 2001013611' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='API AUSTRALIA - 2000404011' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API NATIONAL - 2000404012' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS - 2000905943' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12 2 - 2333333355' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF ACCOUNT - 1900001790' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA ALL OTHER - 2000342300' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001417362' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001829802' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY PACKS PLUS AUST PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES - 2000405342' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES HARDWARE - 2000405341' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT EXPORT PTY LTD - 2001880326' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD - 2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER - 2000335691' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN - 2001588102' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE ELECTRICS PTY LTD - 2001414689' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE
STORES - 2000404982' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE
TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE
PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS - 2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L -
2001417518' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE ANTHONYS - 2000352007'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS - 2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO Account - 1200000000' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CNW - 2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711 LAUNCESTON DC - 2000351935' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS AUSTRALIA - 2000404618' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES SUPERMARKETS - 2000352006' OR "T2073345"."ASSOC_ACCT_LONG_NAME"=')

----RUN SQL-------
168 rows selected.

Elapsed: 00:00:17.62

SQL可以在18秒内跑完，现在禁止并行

SQL> alter session disable parallel query;

Session altered.

SQL> set define off

执行计划如下：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 921854253

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 370 | 7069 (22)| 00:00:31 | | |
| 1 | SORT GROUP BY | | 1 | 370 | 7069 (22)| 00:00:31 | | |
|* 2 | HASH JOIN ANTI SNA | | 1 | 370 | 7068 (22)| 00:00:31 | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 316 | 3975 (27)| 00:00:18 | | |
| 5 | NESTED LOOPS | | 1 | 264 | 3974 (27)| 00:00:18 | | |
|* 6 | HASH JOIN | | 249 | 36105 | 3223 (33)| 00:00:14 | | |
| 7 | PART JOIN FILTER CREATE | :BF0000 | 3397 | 285K| 3178 (33)| 00:00:14 | | |
|* 8 | HASH JOIN | | 3397 | 285K| 3178 (33)| 00:00:14 | | |
|* 9 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 365 | 10950 | 511 (11)| 00:00:03 | | |
| 10 | NESTED LOOPS | | 346K| 18M| 2651 (36)| 00:00:12 | | |
| 11 | TABLE ACCESS BY INDEX ROWID | OPT_BUS_UNIT_FDIM | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 12 | INDEX UNIQUE SCAN | OPT_BUS_UNIT_FDIM_UX1 | 1 | | 0 (0)| 00:00:01 | | |
| 13 | PARTITION RANGE ALL | | 346K| 18M| 2650 (36)| 00:00:12 | 1 | 9 |
| 14 | PARTITION LIST ITERATOR | | 346K| 18M| 2650 (36)| 00:00:12 | KEY | KEY |
|* 15 | TABLE ACCESS FULL | OPT_BRAND_BASLN_IFCT | 346K| 18M| 2650 (36)| 00:00:12 | KEY | KEY |
| 16 | PARTITION LIST JOIN-FILTER | | 16160 | 931K| 43 (33)| 00:00:01 |:BF0000|:BF0000|
|* 17 | TABLE ACCESS FULL | OPT_PROD_BRAND_ASDN_DIM | 16160 | 931K| 43 (33)| 00:00:01 |:BF0000|:BF0000|
|* 18 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_ACCT_ASDN_TYPE2_DIM | 1 | 119 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 19 | INDEX RANGE SCAN | OPT_ACCT_ASDN_TYPE2_DIM_PK | 4 | | 2 (0)| 00:00:01 | | |
|* 20 | INDEX UNIQUE SCAN | OPT_ACCT_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | |
| 21 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 52 | 1 (0)| 00:00:01 | ROWID | ROWID |
| 22 | PARTITION LIST ALL | | 14 | 756 | 3093 (15)| 00:00:14 | 1 | 16 |
|* 23 | TABLE ACCESS FULL | OPT_ACCT_ASDN_TYPE2_DIM | 14 | 756 | 3093 (15)| 00:00:14 | 1 | 16 |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("T2073345"."ASSOC_NAME"="T2073345"."NAME")
6 - access("T2075740"."BUS_UNIT_SKID"="T2076296"."BUS_UNIT_SKID" AND "T2075740"."PROD_SKID"="T2076296"."BRAND_SKID" AND
"T2075740"."FY_DATE_SKID"="T2076296"."FY_DATE_SKID")
8 - access("T2074757"."CAL_MASTR_SKID"="T2075740"."DATE_SKID")
9 - filter("T2074757"."FISC_YR_ABBR_NAME"='FY10/11')
12 - access("T2074456"."BUS_UNIT_NAME"='Australia')
15 - filter("T2074456"."BUS_UNIT_SKID"="T2075740"."BUS_UNIT_SKID")
17 - filter("T2076296"."PROD_LVL_DESC"='Company')
18 - filter(INSTR("T2073345"."ASSOC_ACCT_LONG_NAME","T2073345"."ACCT_LONG_NAME")=1 AND
("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO - 2001417551' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ BATTERY
WHOLESALES - 2001417545' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS - 2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING
SOLUTIONS - 2000371945' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL - 2001416668' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA -
2001415575' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD - 2001013611' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API
AUSTRALIA - 2000404011' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API NATIONAL - 2000404012' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS - 2000905943'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12 2 - 2333333355' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF
ACCOUNT - 1900001790' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA ALL OTHER - 2000342300' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001417362' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST -
2001829802' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
PACKS PLUS AUST PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES - 2000405342' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
SPECIALTIES HARDWARE - 2000405341' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT EXPORT PTY LTD - 2001880326' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD -
2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W
AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER - 2000335691' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN -
2001588102' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING
LEE ELECTRICS PTY LTD - 2001414689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE STORES - 2000404982' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S
BATTERY CENTRE TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS -
2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO
HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE ANTHONYS - 2000352007' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS -
2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO Account - 1200000000' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CNW -
2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711 LAUNCESTON DC - 2000351935' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS
AUSTRALIA - 2000404618' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES SUPERMARKETS - 2000352006' OR "T2073345"."ASS)
19 - access("T2073345"."ACCT_SKID"="T2075740"."PRMTN_ACCT_SKID")
20 - access("T2075740"."PRMTN_ACCT_SKID"="T2076898"."ACCT_SKID" AND "T2075740"."BUS_UNIT_SKID"="T2076898"."BUS_UNIT_SKID")
23 - filter(INSTR("T2073345"."ASSOC_NAME","T2073345"."NAME")=0 AND ("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO -
2001417551' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ BATTERY WHOLESALES - 2001417545' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS -
2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING SOLUTIONS - 2000371945' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL -
2001416668' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA - 2001415575' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD
- 2001013611' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API AUSTRALIA - 2000404011' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API
NATIONAL - 2000404012' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS - 2000905943' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12
2 - 2333333355' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF ACCOUNT - 1900001790' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA ALL OTHER - 2000342300' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST -
2001417362' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001829802' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY PACKS PLUS AUST
PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES - 2000405342' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
SPECIALTIES HARDWARE - 2000405341' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT EXPORT PTY LTD - 2001880326' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD -
2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W
AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER - 2000335691' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN -
2001588102' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING
LEE ELECTRICS PTY LTD - 2001414689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE STORES - 2000404982' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S
BATTERY CENTRE TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS -
2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO
HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE ANTHONYS - 2000352007' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS -
2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO Account - 1200000000' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CNW -
2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711 LAUNCESTON DC - 2000351935' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS
AUSTRALIA - 2000404618' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES SUPERMARKETS - 2000352006' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"=')

115 rows selected.

----RUN SQL -------
168 rows selected.

Elapsed: 00:00:22.53

禁止并行之后，执行计划改变了，但是SQL可以在22秒内跑完，现在继续更改参数

SQL> Alter session set "_optimizer_null_aware_antijoin"=false;

Session altered.

执行计划如下：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 661205518

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | 7069 (22)| 00:00:31 | | |
| 1 | SORT GROUP BY | | 1 | 316 | 7069 (22)| 00:00:31 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 316 | 3975 (27)| 00:00:18 | | |
| 5 | NESTED LOOPS | | 1 | 264 | 3974 (27)| 00:00:18 | | |
|* 6 | HASH JOIN | | 249 | 36105 | 3223 (33)| 00:00:14 | | |
| 7 | PART JOIN FILTER CREATE | :BF0000 | 3397 | 285K| 3178 (33)| 00:00:14 | | |
|* 8 | HASH JOIN | | 3397 | 285K| 3178 (33)| 00:00:14 | | |
|* 9 | TABLE ACCESS FULL | OPT_CAL_MASTR_DIM | 365 | 10950 | 511 (11)| 00:00:03 | | |
| 10 | NESTED LOOPS | | 346K| 18M| 2651 (36)| 00:00:12 | | |
| 11 | TABLE ACCESS BY INDEX ROWID | OPT_BUS_UNIT_FDIM | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 12 | INDEX UNIQUE SCAN | OPT_BUS_UNIT_FDIM_UX1 | 1 | | 0 (0)| 00:00:01 | | |
| 13 | PARTITION RANGE ALL | | 346K| 18M| 2650 (36)| 00:00:12 | 1 | 9 |
| 14 | PARTITION LIST ITERATOR | | 346K| 18M| 2650 (36)| 00:00:12 | KEY | KEY |
|* 15 | TABLE ACCESS FULL | OPT_BRAND_BASLN_IFCT | 346K| 18M| 2650 (36)| 00:00:12 | KEY | KEY |
| 16 | PARTITION LIST JOIN-FILTER | | 16160 | 931K| 43 (33)| 00:00:01 |:BF0000|:BF0000|
|* 17 | TABLE ACCESS FULL | OPT_PROD_BRAND_ASDN_DIM | 16160 | 931K| 43 (33)| 00:00:01 |:BF0000|:BF0000|
|* 18 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_ACCT_ASDN_TYPE2_DIM | 1 | 119 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 19 | INDEX RANGE SCAN | OPT_ACCT_ASDN_TYPE2_DIM_PK | 4 | | 2 (0)| 00:00:01 | | |
|* 20 | INDEX UNIQUE SCAN | OPT_ACCT_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | |
| 21 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 52 | 1 (0)| 00:00:01 | ROWID | ROWID |
| 22 | PARTITION LIST ALL | | 1 | 54 | 3093 (15)| 00:00:14 | 1 | 16 |
|* 23 | TABLE ACCESS FULL | OPT_ACCT_ASDN_TYPE2_DIM | 1 | 54 | 3093 (15)| 00:00:14 | 1 | 16 |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( NOT EXISTS (SELECT 0 FROM "ADWG_OPTIMA_AP11"."OPT_ACCT_ASDN_TYPE2_DIM" "T2073345" WHERE
INSTR("T2073345"."ASSOC_NAME","T2073345"."NAME")=0 AND ("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO - 2001417551' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ BATTERY WHOLESALES - 2001417545' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO
PRODUCTS - 2001666045' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS - 2002157346' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING SOLUTIONS - 2000371945' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS
FOODS - 2002127142' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL - 2001416668' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA - 2001415575' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD - 2001013611'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API AUSTRALIA - 2000404011' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API NATIONAL -
2000404012' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS
- 2000905943' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12 2 - 2333333355' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF ACCOUNT - 1900001790' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA ALL OTHER -
2000342300' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001417362' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001829802' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS -
2001417738' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY PACKS PLUS AUST PTY LTD - 2001414925' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES - 2000405342' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
SPECIALTIES GENERAL - 2000405340' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES HARDWARE - 2000405341' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT EXPORT PTY LTD - 2001880326' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE
ELECTRICAL - 2001418600' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD - 2001414732' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W AUSTRALIA -
2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER - 2000335691' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN -
2001588102' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING
LEE ELECTRICS PTY LTD - 2001414689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE STORES - 2000404982' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S
BATTERY CENTRE TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS -
2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO
HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE ANTHONYS - 2000352007' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS -
2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO Account - 1200000000' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CNW -
2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711 LAUNCESTON DC - 2000351935' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS
AUSTRALIA - 2000404618' OR "T2073345"."ASSOC)
6 - access("T2075740"."BUS_UNIT_SKID"="T2076296"."BUS_UNIT_SKID" AND "T2075740"."PROD_SKID"="T2076296"."BRAND_SKID" AND
"T2075740"."FY_DATE_SKID"="T2076296"."FY_DATE_SKID")
8 - access("T2074757"."CAL_MASTR_SKID"="T2075740"."DATE_SKID")
9 - filter("T2074757"."FISC_YR_ABBR_NAME"='FY10/11')
12 - access("T2074456"."BUS_UNIT_NAME"='Australia')
15 - filter("T2074456"."BUS_UNIT_SKID"="T2075740"."BUS_UNIT_SKID")
17 - filter("T2076296"."PROD_LVL_DESC"='Company')
18 - filter(INSTR("T2073345"."ASSOC_ACCT_LONG_NAME","T2073345"."ACCT_LONG_NAME")=1 AND
("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO - 2001417551' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ BATTERY
WHOLESALES - 2001417545' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS - 2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING
SOLUTIONS - 2000371945' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL - 2001416668' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA -
2001415575' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD - 2001013611' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API
AUSTRALIA - 2000404011' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API NATIONAL - 2000404012' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS - 2000905943'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12 2 - 2333333355' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF
ACCOUNT - 1900001790' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA ALL OTHER - 2000342300' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001417362' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST -
2001829802' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
PACKS PLUS AUST PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES - 2000405342' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
SPECIALTIES HARDWARE - 2000405341' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT EXPORT PTY LTD - 2001880326' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD -
2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W
AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER - 2000335691' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN -
2001588102' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING
LEE ELECTRICS PTY LTD - 2001414689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE STORES - 2000404982' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S
BATTERY CENTRE TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS -
2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO
HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE ANTHONYS - 2000352007' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS -
2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO Account - 1200000000' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CNW -
2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711 LAUNCESTON DC - 2000351935' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS
AUSTRALIA - 2000404618' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES SUPERMARKETS - 2000352006' OR "T2073345"."ASS)
19 - access("T2073345"."ACCT_SKID"="T2075740"."PRMTN_ACCT_SKID")
20 - access("T2075740"."PRMTN_ACCT_SKID"="T2076898"."ACCT_SKID" AND "T2075740"."BUS_UNIT_SKID"="T2076898"."BUS_UNIT_SKID")
23 - filter(INSTR("T2073345"."ASSOC_NAME","T2073345"."NAME")=0 AND ("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO -
2001417551' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ BATTERY WHOLESALES - 2001417545' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS -
2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING SOLUTIONS - 2000371945' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL -
2001416668' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA - 2001415575' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD
- 2001013611' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API AUSTRALIA - 2000404011' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API
NATIONAL - 2000404012' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS - 2000905943' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12
2 - 2333333355' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF ACCOUNT - 1900001790' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA ALL OTHER - 2000342300' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST -
2001417362' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001829802' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY PACKS PLUS AUST
PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES - 2000405342' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
SPECIALTIES HARDWARE - 2000405341' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT EXPORT PTY LTD - 2001880326' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD -
2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W
AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER - 2000335691' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN -
2001588102' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING
LEE ELECTRICS PTY LTD - 2001414689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE STORES - 2000404982' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S
BATTERY CENTRE TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS -
2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO
HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE ANTHONYS - 2000352007' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS -
2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO Account - 1200000000' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CNW -
2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711 LAUNCESTON DC - 2000351935' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS
AUSTRALIA - 2000404618' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES SUPERMARKETS - 2000352006' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"=')

150 rows selected.

这个执行计划就是有问题的执行计划，它跑了30分钟还不能跑完。

注意分析执行计划由于设置了隐含参数 Alter session set "_optimizer_null_aware_antijoin"=false; 执行计划由
|* 2 | HASH JOIN ANTI SNA 变成了 |* 2 | FILTER

所以这个时候就要优化了，最终创建2个索引

SQL> create index OPT_ACCT_ASDN_TYPE2_DIM_NX1 ON OPT_ACCT_ASDN_TYPE2_DIM(ASSOC_ACCT_LONG_NAME);

Index created.

SQL> create index OPT_PROD_BRAND_ASDN_DIM_NX1 ON OPT_PROD_BRAND_ASDN_DIM(PROD_LVL_DESC,BRAND_SKID,BUS_UNIT_SKID,FY_DATE_SKID);

Index created.

执行计划如下：

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1944140796

-------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 316 | 4567 (1)| 00:00:20 | | |
| 1 | SORT GROUP BY | | 1 | 316 | 4567 (1)| 00:00:20 | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 316 | 3597 (1)| 00:00:16 | | |
| 5 | NESTED LOOPS | | 1 | 257 | 3595 (1)| 00:00:16 | | |
| 6 | NESTED LOOPS | | 1 | 205 | 3594 (1)| 00:00:16 | | |
| 7 | NESTED LOOPS | | 7 | 1225 | 3587 (1)| 00:00:16 | | |
| 8 | NESTED LOOPS | | 14 | 1918 | 968 (1)| 00:00:05 | | |
| 9 | TABLE ACCESS BY INDEX ROWID | OPT_BUS_UNIT_FDIM | 1 | 18 | 1 (0)| 00:00:01 | | |
|* 10 | INDEX UNIQUE SCAN | OPT_BUS_UNIT_FDIM_UX1 | 1 | | 0 (0)| 00:00:01 | | |
| 11 | INLIST ITERATOR | | | | | | | |
|* 12 | TABLE ACCESS BY GLOBAL INDEX ROWID| OPT_ACCT_ASDN_TYPE2_DIM | 14 | 1666 | 967 (1)| 00:00:05 | ROWID | ROWID |
|* 13 | INDEX RANGE SCAN | OPT_ACCT_ASDN_TYPE2_DIM_NX1 | 1374 | | 250 (1)| 00:00:02 | | |
| 14 | PARTITION RANGE ALL | | 1 | 56 | 3587 (1)| 00:00:16 | 1 | 9 |
| 15 | PARTITION LIST ITERATOR | | 1 | 56 | 3587 (1)| 00:00:16 | KEY | KEY |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | OPT_BRAND_BASLN_IFCT | 1 | 56 | 3587 (1)| 00:00:16 | KEY | KEY |
| 17 | BITMAP CONVERSION TO ROWIDS | | | | | | | |
| 18 | BITMAP AND | | | | | | | |
|* 19 | BITMAP INDEX SINGLE VALUE | OPT_BRAND_BASLN_IFCT_BX1 | | | | | KEY | KEY |
|* 20 | BITMAP INDEX SINGLE VALUE | BUS_UNIT_SKID_BX | | | | | KEY | KEY |
|* 21 | TABLE ACCESS BY INDEX ROWID | OPT_CAL_MASTR_DIM | 1 | 30 | 1 (0)| 00:00:01 | | |
|* 22 | INDEX UNIQUE SCAN | OPT_CAL_MASTR_DIM_PK | 1 | | 0 (0)| 00:00:01 | | |
| 23 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_FDIM | 1 | 52 | 1 (0)| 00:00:01 | ROWID | ROWID |
|* 24 | INDEX UNIQUE SCAN | OPT_ACCT_FDIM_PK | 1 | | 0 (0)| 00:00:01 | | |
|* 25 | INDEX RANGE SCAN | OPT_PROD_BRAND_ASDN_DIM_NX1 | 1 | | 1 (0)| 00:00:01 | | |
| 26 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_PROD_BRAND_ASDN_DIM | 1 | 59 | 2 (0)| 00:00:01 | ROWID | ROWID |
| 27 | INLIST ITERATOR | | | | | | | |
|* 28 | TABLE ACCESS BY GLOBAL INDEX ROWID | OPT_ACCT_ASDN_TYPE2_DIM | 1 | 54 | 969 (1)| 00:00:05 | ROWID | ROWID |
|* 29 | INDEX RANGE SCAN | OPT_ACCT_ASDN_TYPE2_DIM_NX1 | 1374 | | 252 (2)| 00:00:02 | | |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter( NOT EXISTS (SELECT 0 FROM "ADWG_OPTIMA_AP11"."OPT_ACCT_ASDN_TYPE2_DIM" "T2073345" WHERE
("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO - 2001417551' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ BATTERY WHOLESALES
- 2001417545' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE
DISTRIBUTORS - 2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING SOLUTIONS - 2000371945' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL -
2001416668' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA - 2001415575' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD -
2001013611' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API AUSTRALIA - 2000404011' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API NATIONAL -
2000404012' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS -
2000905943' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12 2 - 2333333355' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF ACCOUNT - 1900001790' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA ALL OTHER -
2000342300' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001417362' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA
POST - 2001829802' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY PACKS PLUS AUST PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
SPECIALTIES - 2000405342' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES HARDWARE - 2000405341' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT
EXPORT PTY LTD - 2001880326' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD - 2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER -
2000335691' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN
- 2001588102' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE
ELECTRICS PTY LTD - 2001414689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE STORES - 2000404982' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY
CENTRE TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS -
2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO
HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE
ANTHONYS - 2000352007' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS - 2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO
Account - 1200000000' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CNW - 2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711
LAUNCESTON DC - 2000351935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS AUSTRALIA - 2000404618' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES
SUPERMARKETS - 2000352006' OR ")
10 - access("T2074456"."BUS_UNIT_NAME"='Australia')
12 - filter(INSTR("T2073345"."ASSOC_ACCT_LONG_NAME","T2073345"."ACCT_LONG_NAME")=1)
13 - access("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO - 2001417551' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ
BATTERY WHOLESALES - 2001417545' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS - 2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING
SOLUTIONS - 2000371945' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL - 2001416668' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA - 2001415575' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD - 2001013611' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API AUSTRALIA - 2000404011'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API NATIONAL - 2000404012' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS - 2000905943' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12 2
- 2333333355' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF ACCOUNT - 1900001790' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA
ALL OTHER - 2000342300' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001417362' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001829802' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY PACKS PLUS AUST PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
SPECIALTIES - 2000405342' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES HARDWARE - 2000405341' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT
EXPORT PTY LTD - 2001880326' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD - 2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER -
2000335691' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN
- 2001588102' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE
ELECTRICS PTY LTD - 2001414689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE STORES - 2000404982' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY
CENTRE TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS -
2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO
HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE
ANTHONYS - 2000352007' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS - 2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO
Account - 1200000000' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CNW - 2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711
LAUNCESTON DC - 2000351935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS AUSTRALIA - 2000404618' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES
SUPERMARKETS - 2000352006' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COMMERCIAL - 2000342628' OR "T2073345"."ASSOC_ACCT_LONG_)
16 - filter(("T2075740"."DATE_SKID"-"T2073345"."ASDN_EFF_END_DATE_SKID")*("T2075740"."DATE_SKID"-"T2073345"."ASDN_EFF_START_DATE_
SKID")<=0)
19 - access("T2073345"."ACCT_SKID"="T2075740"."PRMTN_ACCT_SKID")
20 - access("T2073345"."BUS_UNIT_SKID"="T2075740"."BUS_UNIT_SKID")
filter("T2074456"."BUS_UNIT_SKID"="T2075740"."BUS_UNIT_SKID")
21 - filter("T2074757"."FISC_YR_ABBR_NAME"='FY10/11')
22 - access("T2074757"."CAL_MASTR_SKID"="T2075740"."DATE_SKID")
24 - access("T2075740"."PRMTN_ACCT_SKID"="T2076898"."ACCT_SKID" AND "T2075740"."BUS_UNIT_SKID"="T2076898"."BUS_UNIT_SKID")
25 - access("T2076296"."PROD_LVL_DESC"='Company' AND "T2075740"."PROD_SKID"="T2076296"."BRAND_SKID" AND
"T2075740"."BUS_UNIT_SKID"="T2076296"."BUS_UNIT_SKID" AND "T2075740"."FY_DATE_SKID"="T2076296"."FY_DATE_SKID")
28 - filter(INSTR("T2073345"."ASSOC_NAME","T2073345"."NAME")=0 AND LNNVL("T2073345"."NAME"<>:B1))
29 - access("T2073345"."ASSOC_ACCT_LONG_NAME"='A1 BATTERY PRO - 2001417551' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AAA FNQ
BATTERY WHOLESALES - 2001417545' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ABCO PRODUCTS - 2001666045' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ABCOE DISTRIBUTORS - 2002157346' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ADMIN & MARKETING
SOLUTIONS - 2000371945' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ALL-WAYS FOODS - 2002127142' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AMWAY CASTLE HILL - 2001416668' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ANCOL SA - 2001415575' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='ANSPEC PTY LTD - 2001013611' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API AUSTRALIA - 2000404011'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='API NATIONAL - 2000404012' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS ANZ - 2000905941'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='ARNOTTS AUS - 2000905943' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU R11 TESTING DUMMY XX12 2
- 2333333355' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AU ROOF ACCOUNT - 1900001790' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA
ALL OTHER - 2000342300' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001417362' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='AUSTRALIA POST - 2001829802' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERIES PLUS - 2001417738'
OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY PACKS PLUS AUST PTY LTD - 2001414925' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY
SPECIALTIES - 2000405342' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES GENERAL - 2000405340' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BATTERY SPECIALTIES HARDWARE - 2000405341' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BEACON IMPORT
EXPORT PTY LTD - 2001880326' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BI-RITE ELECTRICAL - 2001418600' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIDVEST PTY LTD - 2001414732' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W - HO - 2000335689' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W AUSTRALIA - 2000403935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BIG W HEAD OFFICE PAYER -
2000335691' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE - 2000404130' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BELCONNEN
- 2001588102' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE BURWOOD - 2001414789' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BING LEE
ELECTRICS PTY LTD - 2001414689' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BONFECT SNACKFOOD SERVICES - 2002127139' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BOYD & MAJOR LTD - 2000299023' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BSR - 2001630602' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='BUNZL - 2000405345' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='BUY RITE STORES - 2000404982' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY CENTRE - 2001552661' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASEY''S BATTERY
CENTRE TOWNSVILLE - 2001976857' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CASTLE HAMPERS PTY LTD - 2001418202' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CHATSWOOD CHASE PHARMACY - 2001417709' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHEMPLUS -
2000404979' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO HAMPERS - 2001834670' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CHRISCO
HAMPERS AUSTRALIA LTD - 2001417711' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CITY-COUNTRY SALES & DISTRIBUTION P/L - 2001417518' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='CLIFFORD HALLAM PHARMACEUTICAL - 2000351986' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE
ANTHONYS - 2000352007' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CLIVE PEETERS - 2001418601' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CMO
Account - 1200000000' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='CNW - 2000403937' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES 9711
LAUNCESTON DC - 2000351935' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES GROUP LTD - 2000352009' OR
"T2073345"."ASSOC_ACCT_LONG_NAME"='COLES S/MARKETS AUSTRALIA - 2000404618' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COLES
SUPERMARKETS - 2000352006' OR "T2073345"."ASSOC_ACCT_LONG_NAME"='COMMERCIAL - 2000342628' OR "T2073345"."ASSOC_ACCT_LONG_)