| 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 -> Wild query
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)
Received on Mon Nov 19 2001 - 10:00:33 CST
 |
 |