| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Wild query
Can I assume that you meant to ask for suggestions on what colour to paint the go-faster stripes ?
It looks as if you might need to get something working with bitmap star transformations, possibly splitting the query (logically) into two stages so that the star is used for the small dimension tables, and then simple PK joins used to move out through the customer, account, meter, and premises tables.
-- Jonathan Lewis http://www.jlcomp.demon.co.uk Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases Screen saver or Life saver: http://www.ud.com Use spare CPU to assist in cancer research. James A. Williams wrote in message <5003a2b9.0111190800.5dfb9219_at_posting.google.com>...Received on Mon Nov 19 2001 - 10:58:07 CST
>I have a warehouse user joining some large tables together. Retrieves
>data after 30 or so minutes.
>
>SELECT dim_account.cis_account_number,
>dim_customer.customer_status_desc,
> dim_delivery_group.delivery_group_code,
>dim_marketer.marketer_name,
> dim_office.company_name, dim_premise.cis_premise_number,
> dim_tariff_schedule.tariff_type_code, dim_date.YEAR,
> dim_date2_fiscal.fiscal_year, dim_meter.meter_type_desc,
> dim_meter_activity.activity_type_desc,
> COUNT (fact_meter_activity.activity_id)
> FROM dim_account,
> dim_customer,
> dim_delivery_group,
> dim_marketer,
> dim_office,
> dim_premise,
> dim_tariff_schedule,
> dim_date,
> dim_date dim_date2_fiscal,
> dim_meter,
> dim_meter_activity,
> fact_meter_activity
> WHERE (dim_office.office_id = fact_meter_activity.office_id)
> AND (fact_meter_activity.customer_id = dim_customer.customer_id)
> AND (fact_meter_activity.account_id = dim_account.account_id)
> AND (dim_premise.premise_id = fact_meter_activity.premise_id)
> AND (fact_meter_activity.tariff_schedule_id =
>
>dim_tariff_schedule.tariff_schedule_id
> )
> AND (dim_delivery_group.delivery_group_id =
>
>fact_meter_activity.delivery_group_id
> )
> AND (dim_meter.meter_id = fact_meter_activity.meter_id)
> AND (fact_meter_activity.marketer_id = dim_marketer.marketer_id)
> AND (fact_meter_activity.activity_date_id = dim_date.date_id)
> AND (fact_meter_activity.activity_id =
>dim_meter_activity.activity_id)
> AND (dim_date2_fiscal.date_id =
>fact_meter_activity.activity_date_id)
> AND ( (dim_account.account_status_desc IN
> ('ACTIVE', 'FINAL', 'PEND ACTIVE',
>'UNCOLLECLTABLE', 'VOID')
> )
> AND (dim_customer.customer_status_desc IN
> ('ACTIVE', 'FINAL', 'PEND
>ACTIV', 'VOID')
> )
> AND (dim_delivery_group.delivery_group_code IN (' ',
> 'ATL',
> 'AUG',
> 'xxx,
> 'xxx',
> 'xxx',
> 'xxx',
> 'xxx',
> 'xxx',
> 'xxx',
> 'VAL'
> )
> )
> AND (dim_marketer.marketer_name IN ('xxx ',
> 'xxx ',
> 'xxx ',
> 'xxxx ',
> 'Cxxx ',
> 'xxxx ',
> 'xxx ',
> 'xxxx ',
> 'xxx',
> 'xxx ',
> 'xxx ',
> 'xxx ',
> 'xxx ',
> 'xxxx ',
> 'xxxx ',
> 'xxx ',
> 'xxx ',
> 'xxxx',
> 'Uxx MGMT ',
> 'WILxx '
> )
> )
> AND (dim_office.office_name IN ('ATsxxS',
> 'ATLAxx',
> 'WExxT'
> )
> )
> AND (dim_office.company_name IN ('AxxC', 'CxC'))
> AND (dim_tariff_schedule.tariff_type_code IN ('C', 'R'))
> AND (dim_tariff_schedule.tariff_schedule_code IN ('101',
> '102',
> '103',
> '121',
> '122',
> '123',
> '311',
> '312',
> '313',
> '347',
> '348',
> '365',
> '367',
> '371',
> '511',
> '512',
> '513',
> '547',
> '548',
> '571',
> '601',
> '602',
> '999'
> )
> )
> AND (dim_date.gregorian_date BETWEEN TO_DATE (
> '01-01-1976',
> 'dd-mm-yyyy'
> )
> AND TO_DATE (
> '01-01-2001',
> 'dd-mm-yyyy'
> )
> )
> AND (dim_meter_activity.activity_type_desc IN ('Inactive
>Remove',
> 'Remove',
> 'SUPR',
> 'Set',
> 'Turn Off',
> 'Turn On'
> )
> )
> AND (dim_meter_activity.activity_reason_desc IN
> ('Consumption on an Inactive Met',
> 'Deposit Nonpayment Denial',
> 'Deposit and service Nonpayment',
> 'Investigation Denial',
> 'Investigation and Returned Che',
> 'Investigation and Service Nonp',
> 'Miscellaneous Nonpayment Denia',
> 'Regular Termination',
> 'Returned Check Denial',
> 'Returned check and Service Non',
> 'Service Nonpayment Denial'
> )
> )
> )
>GROUP BY dim_account.cis_account_number,
> dim_customer.customer_status_desc,
> dim_delivery_group.delivery_group_code,
> dim_marketer.marketer_name,
> dim_office.company_name,
> dim_premise.cis_premise_number,
> dim_tariff_schedule.tariff_type_code,
> dim_date.YEAR,
> dim_date2_fiscal.fiscal_year,
> dim_meter.meter_type_desc,
> dim_meter_activity.activity_type_desc
>
>
>
>
>
>
>SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=69448 Card=1936427
>Bytes=447314637)
> SORT (GROUP BY) (Cost=69448 Card=1936427 Bytes=447314637)
> HASH JOIN (Cost=37085 Card=1936427 Bytes=447314637)
> TABLE ACCESS (FULL) OF DIM_DATE (Cost=34 Card=9135 Bytes=155295)
> HASH JOIN (Cost=37047 Card=1936427 Bytes=414395378)
> TABLE ACCESS (FULL) OF DIM_OFFICE (Cost=9 Card=8394
>Bytes=176274)
> HASH JOIN (Cost=37034 Card=1936427 Bytes=373730411)
> TABLE ACCESS (FULL) OF DIM_DATE (Cost=34 Card=73414
>Bytes=660726)
> HASH JOIN (Cost=36981 Card=1936427 Bytes=356302568)
> TABLE ACCESS (FULL) OF DIM_METER (Cost=1286 Card=2204807
>Bytes=50710561)
> HASH JOIN (Cost=34830 Card=1936427 Bytes=311764747)
> TABLE ACCESS (FULL) OF DIM_PREMISE (Cost=5076
>Card=2875649 Bytes=37383437)
> HASH JOIN (Cost=28989 Card=1936427 Bytes=286591196)
> TABLE ACCESS (FULL) OF DIM_ACCOUNT (Cost=17624
>Card=5730437 Bytes=114608740)
> HASH JOIN (Cost=9209 Card=2571184 Bytes=329111552)
> TABLE ACCESS (FULL) OF DIM_CUSTOMER (Cost=2437
>Card=2462171 Bytes=32008223)
> HASH JOIN (Cost=5931 Card=3503875 Bytes=402945625)
> TABLE ACCESS (FULL) OF DIM_MARKETER (Cost=1
>Card=14 Bytes=308)
> HASH JOIN (Cost=5929 Card=5005535 Bytes=465514755)
> TABLE ACCESS (FULL) OF DIM_METER_ACTIVITY
>(Cost=1 Card=12 Bytes=372)
> HASH JOIN (Cost=5927 Card=9593943
>Bytes=594824466)
> TABLE ACCESS (FULL) OF DIM_TARIFF_SCHEDULE
>(Cost=1 Card=12 Bytes=108)
> HASH JOIN (Cost=5925 Card=15989905
>Bytes=847464965)
> TABLE ACCESS (FULL) OF DIM_DELIVERY_GROUP
>(Cost=1 Card=8 Bytes=56)
> TABLE ACCESS (FULL) OF FACT_METER_ACTIVITY
>(Cost=5923 Card=21986120 Bytes=1011361520)
 |
 |