Hey folks, I am porting an expense reporting application from MS SQL Server 2000 to Oracle 9.2.0.5. I have a large base table which contains 1.3M expense records for my company. Here is the schema:
Due to the size of this table, I summarized it into another table which grouped all results by RCC, RCO, EFFRCC, EFFRCO, EXPENSEDATE. The SELECT portion of the query contained a number of CASE statements based on business rules set up by the company. The actual query is very long, so I will just put a sample here:
The aim of this query is to have one record for each item I'm grouping by (RCC,RCO,EFFRCC,EFFRCO,EXPENSEDATE) and a corresponding amount for each business rule SUM. I've included only four of the business rules which make up the various sums. There are 30 in total, all of which use a combination of XC, EG, FIN or FINROLLUP to come up with an expense amount.
This query takes around 5 minutes to begin returning results in Oracle. Here is TKPROF output for this query:
I had 4 indexes built in SQL Server to assist in this query. Each one was built like this: (EFFRCC, EXPENSEDATE, FINROLLUP, FIN, EG, XC)
Each index was the same, except the first column was changed to RCC, RCO, and EFFRCO in the subsequent indexes. Building these indexes in Oracle made no difference in query execution time. In addition, I built an index solely for the group columns and that made no difference either. What index(es) would assist in bringing this query time down?
In addition, I'd like to build a materialized view for this purpose in Oracle. The issues are that the base table may be updated at various points in the day and the new data must be accurate immediately after those updates. I was previously doing deletes/inserts on the summary table to accomplish this task. I believe I need a primary key for real time update to happen. The lowest common denominator would be at least 4 columns (RCC,RCO,EXPENSEDATE,XC). Does anyone have experience with something similar? Feel free to weigh in if so.
Code:
EXPENSEAMT NUMBER(17,2) - money!
RCC CHAR(9) - Actual Organization charged TO
RCO CHAR(9) - Actual Organization charged FROM
EXPENSEDATE DATE - Date of charge (only contains MM/YY data)
EFFRCC CHAR(9) - Effective Org. charged TO
EFFRCO CHAR(9) - Effective Org. charged FROM
FIN VARCHAR2(7) - 3rd tier expense code
FINROLLUP VARCHAR2(7) - 4th tier expense code
XC VARCHAR2(7) - lowest common expense code
EG VARCHAR2(7) - 2nd tier expense codeDue to the size of this table, I summarized it into another table which grouped all results by RCC, RCO, EFFRCC, EFFRCO, EXPENSEDATE. The SELECT portion of the query contained a number of CASE statements based on business rules set up by the company. The actual query is very long, so I will just put a sample here:
Code:
SELECT RCC, RCO, EffRCC, EffRCO, ExpenseDate,
Sum(CASE WHEN FIN IN ('502_F','509_F') THEN ExpenseAmt ELSE 0 END) MgmtWages,
Sum(CASE WHEN FIN = '501_F' THEN (CASE WHEN XC NOT IN ('121V0','127V0','128V0') THEN ExpenseAmt ELSE 0 END) ELSE 0 END) NonMgmtWages,
Sum(CASE WHEN EG = '8FX' THEN ExpenseAmt ELSE 0 END) IncurredFleet,
Sum(CASE WHEN XC = 'B2D' THEN ExpenseAmt ELSE 0 END) FleetCapital
GROUP BY RCC, RCO, EffRCC, EffRCO, ExpenseDate
ORDER BY RCC, ExpenseDateThe aim of this query is to have one record for each item I'm grouping by (RCC,RCO,EFFRCC,EFFRCO,EXPENSEDATE) and a corresponding amount for each business rule SUM. I've included only four of the business rules which make up the various sums. There are 30 in total, all of which use a combination of XC, EG, FIN or FINROLLUP to come up with an expense amount.
This query takes around 5 minutes to begin returning results in Oracle. Here is TKPROF output for this query:
Code:
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=75045 Card=1138118 Bytes=81944496)
1 0 SORT (GROUP BY) (Cost=75045 Card=1138118 Bytes=81944496)
2 1 TABLE ACCESS (FULL) OF 'FIN_EXPENSES' (Cost=1886 Card=1138118 Bytes=81944496
)
Statistics
----------------------------------------------------------
847 recursive calls
1503 db block gets
12528 consistent gets
33797 physical reads
128 redo size
14013599 bytes sent via SQL*Net to client
153207 bytes received via SQL*Net from client
13753 SQL*Net roundtrips to/from client
48 sorts (memory)
1 sorts (disk)
206266 rows processedI had 4 indexes built in SQL Server to assist in this query. Each one was built like this: (EFFRCC, EXPENSEDATE, FINROLLUP, FIN, EG, XC)
Each index was the same, except the first column was changed to RCC, RCO, and EFFRCO in the subsequent indexes. Building these indexes in Oracle made no difference in query execution time. In addition, I built an index solely for the group columns and that made no difference either. What index(es) would assist in bringing this query time down?
In addition, I'd like to build a materialized view for this purpose in Oracle. The issues are that the base table may be updated at various points in the day and the new data must be accurate immediately after those updates. I was previously doing deletes/inserts on the summary table to accomplish this task. I believe I need a primary key for real time update to happen. The lowest common denominator would be at least 4 columns (RCC,RCO,EXPENSEDATE,XC). Does anyone have experience with something similar? Feel free to weigh in if so.
