Dear All,
In my department we having a fact table called Call_Facts
The average rows that are inserted on a daily basis in this table is 35Milion and this table is
partitioned by month and this makes the whole partition contains around 887,209,765, lots of time we hit this table to for reporting purposes, and the queries taking so much time to return, quires that have 7 calculated columns return in 8 hours minimum, and we already have a monthly aggregates over this table by contract_key, the problem is this aggregate fail many time during execution due to temp space (200 GIGA tempspace and when it succeeded it takes around 14 hours to be completed. So what I need here is there anyway to make the process of creating this aggregate be calculated on a fast way by using any techniques (not more than 6 hours)?
The call_facts structure :
This table has index on the contract_key level (NONUNIQUE and Bitmap type) and another on on the call_date_key level (NONUNIQUE and Bitmap type)
CREATE TABLE CALL_USAGE_FACTS
(
TARIFF_MODEL_KEY NUMBER(10) NOT NULL,
PRICE_GROUP_KEY NUMBER(10) NOT NULL,
CALL_DATE_KEY NUMBER(10) NOT NULL,
ENTRY_DATE_KEY NUMBER(10) NOT NULL,
CALL_KEY NUMBER(10) NOT NULL,
SERVICE_KEY NUMBER(10) NOT NULL,
CALLED_LOCATION_KEY NUMBER(10) NOT NULL,
CONTRACT_KEY NUMBER(10) NOT NULL,
NETWORK_KEY NUMBER(10) NOT NULL,
CELL_KEY NUMBER(10) NOT NULL,
DEMOGRAPHICS_KEY NUMBER(10) NOT NULL,
TARIFF_TIME_KEY NUMBER(10) NOT NULL,
O_P_NUMBER VARCHAR2(70 BYTE) NOT NULL,
CALL_TIME NUMBER(6) NOT NULL,
RATED_VOLUME NUMBER(10) NOT NULL,
ACTUAL_VOLUME NUMBER(10) NOT NULL,
ROUNDED_VOLUME NUMBER(10) NOT NULL,
RATED_AMOUNT NUMBER(10,2) NOT NULL,
SURCHARGE_AMOUNT NUMBER(10,2) NOT NULL,
CALL_MONTH_KEY NUMBER(5),
CALL_QTR_KEY NUMBER(5),
SERVICE_CLASS_KEY NUMBER(5),
IMEI VARCHAR2(8 BYTE),
FREE_CHARGE_AMOUNT NUMBER(10,2),
FREE_RATED_VOLUME NUMBER(10),
FREE_ROUNDED_VOLUME NUMBER(10),
CHECK ("CALL_MONTH_KEY" IS NOT NULL) DISABLE,
CHECK ("CALL_QTR_KEY" IS NOT NULL) DISABLE,
CHECK ("SERVICE_CLASS_KEY" IS NOT NULL) DISABLE
)
PARTITION BY RANGE (CALL_DATE_KEY)
(
PARTITION CALL_USAGE_FACTS_2006_05 VALUES LESS THAN (2805)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2006_06 VALUES LESS THAN (2835)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2006_07 VALUES LESS THAN (2866)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2006_08 VALUES LESS THAN (2897)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2006_09 VALUES LESS THAN (2927)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2006_10 VALUES LESS THAN (2958)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2006_11 VALUES LESS THAN (2988)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2006_12 VALUES LESS THAN (3019)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_01 VALUES LESS THAN (3050)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_02 VALUES LESS THAN (3078)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_03 VALUES LESS THAN (3109)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_04 VALUES LESS THAN (3139)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_05 VALUES LESS THAN (3170)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_06 VALUES LESS THAN (3200)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_07 VALUES LESS THAN (3231)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_08 VALUES LESS THAN (3262)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_09 VALUES LESS THAN (3292)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_10 VALUES LESS THAN (3323)
LOGGING
NOCOMPRESS,
PARTITION CALL_USAGE_FACTS_2007_11 VALUES LESS THAN (3353)
LOGGING
NOCOMPRESS
)
NOCACHE
NOPARALLEL
