| 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 -> Question on "Non-Mergeable View"
I have a view that is non-mergeable. Could you please give you an idea
regarding this
View :
CREATE OR REPLACE VIEW AS PAY_CN_ASG_ELEMENTS_V
(
ROW_ID, ASSIGNMENT_ACTION_ID, CLASSIFICATION_NAME,
ELEMENT_REPORTING_NAME,
PROCESSING_PRIORITY, AMOUNT, FOREIGN_CURRENCY_CODE, EXCHANGE_RATE
)
SELECT
pet.rowid ROW_ID ,
paa.assignment_action_id ASSIGNMENT_ACTION_ID ,
decode(pec.classification_name ,
'Special Payments',
'Taxable Earnings' ,
'Retro Taxable Earnings',
'Taxable Earnings' ,
'Retro Special Payments',
'Taxable Earnings' ,
'Voluntary Deductions',
'Voluntary Dedn' ,
'Severance Payments',
'Taxable Earnings' ,
'Direct Payments',
'Non Taxable Earnings' ,
'Retro Statutory Deductions',
'Statutory Deductions' ,
'Retro Variable Yearly Earnings',
'Taxable Earnings' ,
'Variable Yearly Earnings',
'Taxable Earnings' ,
pec.classification_name) CLASSIFICATION_NAME ,
nvl(petl.reporting_name,
petl.element_name) ELEMENT_REPORTING_NAME ,
pet.processing_priority PROCESSING_PRIORITY ,
sum(decode(substr(piv.uom,
1,
1),
'M',
prrv.result_value,
null)) AMOUNT ,
decode(pet.input_currency_code,
'CNY',
null,
pet.input_currency_code) FOREIGN_CURRENCY_CODE ,
pay_cn_payslip.get_exchange_rate(pet.input_currency_code ,
pet.output_currency_code , ppa.effective_date , ppa.business_group_id) EXCHANGE_RATE
pay_payroll_actions ppa , pay_assignment_actions paa , pay_run_results prr , pay_run_result_values prrv , pay_input_values_f piv , pay_element_types_f pet , pay_element_types_f_tl petl , pay_element_classifications pec
ppa.action_type in ('R','Q') AND
ppa.action_status = 'C' AND
ppa.payroll_action_id = paa.payroll_action_id AND
paa.assignment_action_id = prr.assignment_action_id AND
pec.classification_name IN ('Taxable Earnings' ,'Voluntary
Deductions' ,'Non Taxable Earnings' ,'Statutory Deductions' ,'Special
Payments' , 'Severance Payments' ,'Direct Payments' ,'Retro Taxable
Earnings' ,'Retro Statutory Deductions' ,'Retro Special Payments' ,
'Variable Yearly Earnings' ,'Retro Variable Yearly Earnings' ) AND
pec.legislation_code = 'CN' AND pec.classification_id = pet.classification_id AND pet.element_name <> 'Special Payments Normal' AND pet.element_type_id = petl.element_type_id ANDpetl.language = USERENV('LANG') AND
pet.element_type_id = prr.element_type_id AND prr.run_result_id = prrv.run_result_id AND piv.input_value_id = prrv.input_value_id AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND
,pai.locking_action_id ASSIGNMENT_ACTION_ID ,decode(pec.classification_name ,'Special Payments','Taxable Earnings' , 'Retro Taxable Earnings','Taxable Earnings' ,'Retro SpecialPayments','Taxable Earnings' ,'Voluntary Deductions','Voluntary Dedn' ,'Severance Payments', 'Taxable Earnings' ,'Direct Payments','Non Taxable Earnings' ,'Retro Statutory Deductions','Statutory Deductions' ,'Retro Variable Yearly Earnings', 'Taxable Earnings' ,'Variable Yearly Earnings','Taxable Earnings' ,pec.classification_name) CLASSIFICATION_NAME ,nvl(petl.reporting_name, petl.element_name) ELEMENT_REPORTING_NAME ,pet.processing_priority PROCESSING_PRIORITY ,sum(decode(substr(piv.uom,1,1), 'M', prrv.result_value, null)) AMOUNT ,decode(pet.input_currency_code, 'CNY',null, pet.input_currency_code) FOREIGN_CURRENCY_CODE ,
,pet.output_currency_code ,ppa.effective_date ,ppa.business_group_id) EXCHANGE_RATE FROM pay_payroll_actions ppa ,pay_assignment_actions paa ,pay_run_results prr ,pay_run_result_values prrv ,pay_input_values_fpiv , pay_element_types_f pet ,pay_element_types_f_tl petl ,pay_element_classifications pec ,pay_action_interlocks pai WHERE
ppa.action_type in ('R','Q') AND ppa.action_status = 'C' AND
ppa.payroll_action_id = paa.payroll_action_id AND
paa.assignment_action_id = prr.assignment_action_id AND
pec.classification_name IN ('Taxable Earnings' ,'Voluntary Deductions'
,'Non Taxable Earnings' ,'Statutory Deductions' ,'Special Payments' ,
'Severance Payments' ,'Direct Payments' ,'Retro Taxable Earnings'
,'Retro Statutory Deductions' ,'Retro Special Payments' , 'Variable
Yearly Earnings' ,'Retro Variable Yearly Earnings' ) AND
pec.legislation_code = 'CN' AND pec.classification_id =
pet.classification_id AND pet.element_name <> 'Special Payments
Normal' AND pet.element_type_id = petl.element_type_id AND
petl.language = USERENV('LANG') AND pet.element_type_id =
piv.element_type_id AND piv.name =
pet.effective_start_date AND pet.effective_end_date AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date AND pai.locked_action_id = paa.assignment_action_id GROUP BY pet.rowid ,pai.locking_action_id ,decode(pec.classification_name ,'Special Payments','Taxable Earnings'
,'Retro Taxable Earnings','Taxable Earnings' ,'Retro Special Payments', 'Taxable Earnings' ,'Voluntary Deductions','Voluntary Dedn' ,'Severance Payments','Taxable Earnings' ,'Direct Payments','NonTaxable Earnings' , 'Retro Statutory Deductions','Statutory Deductions' ,'Retro Variable Yearly Earnings','Taxable Earnings'
,'Variable Yearly Earnings', 'Taxable Earnings' ,pec.classification_name) ,nvl(petl.reporting_name,petl.element_name) ,pet.processing_priority ,pet.input_currency_code ,pay_cn_payslip.get_exchange_rate(pet.input_currency_code,pet.output_currency_code,ppa.effective_date, ppa.business_group_id)
Query :
SELECT element_reporting_name
,classification_name
,amount
,foreign_currency_code
,exchange_rate
FROM pay_cn_asg_elements_v
WHERE assignment_action_id = p_assignment_action_id;
Execution Plan
| Id | Operation | Name
| Rows | Bytes | Cost |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 2 | 264 | 154 |
| 1 | VIEW | PAY_CN_ASG_ELEMENTS_V
| 2 | 264 | 154 |
| 2 | UNION-ALL |
| | | |
| 3 | SORT GROUP BY |
| 1 | 252 | 68 |
| 4 | NESTED LOOPS |
| 1 | 252 | 21 |
| 5 | NESTED LOOPS |
| 1 | 208 | 20 |
| 6 | NESTED LOOPS |
| 1 | 193 | 17 |
| 7 | NESTED LOOPS |
| 1 | 151 | 15 |
| 8 | NESTED LOOPS |
| 1 | 121 | 14 |
| 9 | NESTED LOOPS |
| 1 | 55 | 12 |
| 10 | NESTED LOOPS |
| 1 | 36 | 5 |
| 11 | TABLE ACCESS BY INDEX ROWID | PAY_ASSIGNMENT_ACTIONS
| 1 | 13 | 3 |
|* 12 | INDEX UNIQUE SCAN |
PAY_ASSIGNMENT_ACTIONS_PK | 1280K| | 2 |
|* 13 | INDEX RANGE SCAN |
PAY_ACTION_INTERLOCKS_FK2 | 3 | 21 | 3 |
|* 14 | TABLE ACCESS BY INDEX ROWID | PAY_PAYROLL_ACTIONS
| 1 | 23 | 2 |
|* 15 | INDEX UNIQUE SCAN | PAY_PAYROLL_ACTIONS_PK
| 431 | | 1 |
| 16 | TABLE ACCESS BY INDEX ROWID | PAY_RUN_RESULTS
| 1 | 19 | 7 |
|* 17 | INDEX RANGE SCAN | PAY_RUN_RESULTS_N50
| 1 | | 3 |
|* 18 | TABLE ACCESS BY INDEX ROWID | PAY_ELEMENT_TYPES_F
| 1 | 66 | 2 |
|* 19 | INDEX RANGE SCAN | PAY_ELEMENT_TYPES_F_PK
| 1 | | 1 |
|* 20 | TABLE ACCESS BY INDEX ROWID |
PAY_ELEMENT_CLASSIFICATIONS | 1 | 30 | 1 |
|* 21 | INDEX UNIQUE SCAN |
PAY_ELEMENT_CLASSIFICATION_PK | 1541 | | |
| 22 | TABLE ACCESS BY INDEX ROWID | PAY_INPUT_VALUES_F
| 1 | 42 | 2 |
|* 23 | INDEX RANGE SCAN | PAY_INPUT_VALUES_F_UK2
| 1 | | 1 |
| 24 | TABLE ACCESS BY INDEX ROWID | PAY_RUN_RESULT_VALUES
| 1 | 15 | 3 |
|* 25 | INDEX UNIQUE SCAN |
PAY_RUN_RESULT_VALUES_PK | 1 | | 2 |
| 26 | TABLE ACCESS BY INDEX ROWID | PAY_ELEMENT_TYPES_F_TL
| 1 | 44 | 1 |
|* 27 | INDEX UNIQUE SCAN |
PAY_ELEMENT_TYPES_F_TL_PK | 5 | | |
| 28 | SORT GROUP BY |
| 1 | 265 | 86 |
| 29 | NESTED LOOPS |
| 1 | 265 | 39 |
| 30 | NESTED LOOPS |
| 1 | 250 | 36 |
| 31 | NESTED LOOPS |
| 1 | 208 | 34 |
| 32 | NESTED LOOPS |
| 1 | 164 | 33 |
| 33 | NESTED LOOPS |
| 1 | 134 | 32 |
| 34 | NESTED LOOPS |
| 1 | 68 | 30 |
| 35 | NESTED LOOPS |
| 1 | 49 | 10 |
| 36 | NESTED LOOPS |
| 2 | 52 | 6 |
|* 37 | INDEX RANGE SCAN |
PAY_ACTION_INTERLOCKS_PK | 2 | 26 | 3 |
| 38 | TABLE ACCESS BY INDEX ROWID| PAY_ASSIGNMENT_ACTIONS
| 1 | 13 | 2 |
|* 39 | INDEX UNIQUE SCAN |
PAY_ASSIGNMENT_ACTIONS_PK | 1 | | 1 |
|* 40 | TABLE ACCESS BY INDEX ROWID | PAY_PAYROLL_ACTIONS
| 1 | 23 | 2 |
|* 41 | INDEX UNIQUE SCAN | PAY_PAYROLL_ACTIONS_PK
| 431 | | 1 |
| 42 | TABLE ACCESS BY INDEX ROWID | PAY_RUN_RESULTS
| 7 | 133 | 20 |
|* 43 | INDEX RANGE SCAN | PAY_RUN_RESULTS_N50
| 83 | | 3 |
|* 44 | TABLE ACCESS BY INDEX ROWID | PAY_ELEMENT_TYPES_F
| 1 | 66 | 2 |
|* 45 | INDEX RANGE SCAN | PAY_ELEMENT_TYPES_F_PK
| 1 | | 1 |
|* 46 | TABLE ACCESS BY INDEX ROWID |
PAY_ELEMENT_CLASSIFICATIONS | 1 | 30 | 1 |
|* 47 | INDEX UNIQUE SCAN |
PAY_ELEMENT_CLASSIFICATION_PK | 1541 | | |
| 48 | TABLE ACCESS BY INDEX ROWID | PAY_ELEMENT_TYPES_F_TL
| 1 | 44 | 1 |
|* 49 | INDEX UNIQUE SCAN |
PAY_ELEMENT_TYPES_F_TL_PK | 5 | | |
| 50 | TABLE ACCESS BY INDEX ROWID | PAY_INPUT_VALUES_F
| 1 | 42 | 2 |
|* 51 | INDEX RANGE SCAN | PAY_INPUT_VALUES_F_UK2
| 1 | | 1 |
| 52 | TABLE ACCESS BY INDEX ROWID | PAY_RUN_RESULT_VALUES
| 1 | 15 | 3 |
|* 53 | INDEX UNIQUE SCAN |
PAY_RUN_RESULT_VALUES_PK | 1 | | 2 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
12 - access("SYS_ALIAS_1"."ASSIGNMENT_ACTION_ID"=TO_NUMBER(:Z))
filter( NOT EXISTS (SELECT /*+ */ 0 FROM
"PER"."PAY_ACTION_INTERLOCKS" "PAI" WHERE
"PAI"."LOCKED_ACTION_ID"=:B1))
13 - access("PAI"."LOCKED_ACTION_ID"=:B1)
14 - filter(("PPA"."ACTION_TYPE"='Q' OR "PPA"."ACTION_TYPE"='R') AND
"PPA"."ACTION_STATUS"='C')
15 -
access("PPA"."PAYROLL_ACTION_ID"="SYS_ALIAS_1"."PAYROLL_ACTION_ID")
17 - access("PRR"."ASSIGNMENT_ACTION_ID"=TO_NUMBER(:Z))
filter("SYS_ALIAS_1"."ASSIGNMENT_ACTION_ID"="PRR"."ASSIGNMENT_ACTION_ID")
18 - filter("PET"."ELEMENT_NAME"<>'Special Payments Normal')
19 - access("PET"."ELEMENT_TYPE_ID"="PRR"."ELEMENT_TYPE_ID" AND
"PPA"."EFFECTIVE_DATE"<="PET"."EFFECTIVE_END_DATE" AND
"PPA"."EFFECTIVE_DATE">="PET"."EFFECTIVE_START_DATE")
filter("PPA"."EFFECTIVE_DATE">="PET"."EFFECTIVE_START_DATE" AND
"PPA"."EFFECTIVE_DATE"<="PET"."EFFECTIVE_END_DATE")
20 - filter(("PEC"."CLASSIFICATION_NAME"='Direct Payments' OR
"PEC"."CLASSIFICATION_NAME"='Non Taxable
Earnings' OR "PEC"."CLASSIFICATION_NAME"='Retro Special Payments' OR "PEC"."CLASSIFICATION_NAME"='Retro
Statutory Deductions' OR
"PEC"."CLASSIFICATION_NAME"='Retro Taxable Earnings' OR
"PEC"."CLASSIFICATION_NAME"='Severance Payments' OR "PEC"."CLASSIFICATION_NAME"='Special Payments' OR
"PEC"."CLASSIFICATION_NAME"='Statutory Deductions' OR "PEC"."CLASSIFICATION_NAME"='Taxable Earnings' OR
"PEC"."CLASSIFICATION_NAME"='Voluntary Deductions') AND "PEC"."LEGISLATION_CODE"='CN')
21 - access("PEC"."CLASSIFICATION_ID"="PET"."CLASSIFICATION_ID")
23 - access("PET"."ELEMENT_TYPE_ID"="PIV"."ELEMENT_TYPE_ID" AND
"PIV"."NAME"=DECODE("PEC"."CLASSIFICATION_NAME",'Special
Payments','Payment Amount','Pay Value') AND
"PPA"."EFFECTIVE_DATE"<="PIV"."EFFECTIVE_END_DATE" AND
"PPA"."EFFECTIVE_DATE">="PIV"."EFFECTIVE_START_DATE")
filter("PPA"."EFFECTIVE_DATE">="PIV"."EFFECTIVE_START_DATE" AND
"PPA"."EFFECTIVE_DATE"<="PIV"."EFFECTIVE_END_DATE")
25 - access("PIV"."INPUT_VALUE_ID"="PRRV"."INPUT_VALUE_ID" AND
"PRR"."RUN_RESULT_ID"="PRRV"."RUN_RESULT_ID")
27 - access("PET"."ELEMENT_TYPE_ID"="PETL"."ELEMENT_TYPE_ID" AND
"PETL"."LANGUAGE"=:B1)
37 - access("PAI"."LOCKING_ACTION_ID"=TO_NUMBER(:Z))
39 - access("PAI"."LOCKED_ACTION_ID"="PAA"."ASSIGNMENT_ACTION_ID")
40 - filter(("PPA"."ACTION_TYPE"='Q' OR "PPA"."ACTION_TYPE"='R') AND
"PPA"."ACTION_STATUS"='C')
44 - filter("PET"."ELEMENT_NAME"<>'Special Payments Normal')
45 - access("PET"."ELEMENT_TYPE_ID"="PRR"."ELEMENT_TYPE_ID" AND
"PPA"."EFFECTIVE_DATE"<="PET"."EFFECTIVE_END_DATE" AND
"PPA"."EFFECTIVE_DATE">="PET"."EFFECTIVE_START_DATE")
filter("PPA"."EFFECTIVE_DATE">="PET"."EFFECTIVE_START_DATE" AND
"PPA"."EFFECTIVE_DATE"<="PET"."EFFECTIVE_END_DATE")
46 - filter(("PEC"."CLASSIFICATION_NAME"='Direct Payments' OR
"PEC"."CLASSIFICATION_NAME"='Non Taxable
Earnings' OR "PEC"."CLASSIFICATION_NAME"='Retro Special Payments' OR "PEC"."CLASSIFICATION_NAME"='Retro
Statutory Deductions' OR
"PEC"."CLASSIFICATION_NAME"='Retro Taxable Earnings' OR
"PEC"."CLASSIFICATION_NAME"='Severance Payments' OR "PEC"."CLASSIFICATION_NAME"='Special Payments' OR
"PEC"."CLASSIFICATION_NAME"='Statutory Deductions' OR "PEC"."CLASSIFICATION_NAME"='Taxable Earnings' OR
"PEC"."CLASSIFICATION_NAME"='Voluntary Deductions') AND
"PEC"."LEGISLATION_CODE"='CN')
47 - access("PEC"."CLASSIFICATION_ID"="PET"."CLASSIFICATION_ID")
49 - access("PET"."ELEMENT_TYPE_ID"="PETL"."ELEMENT_TYPE_ID" AND
"PETL"."LANGUAGE"=:B1)
51 - access("PET"."ELEMENT_TYPE_ID"="PIV"."ELEMENT_TYPE_ID" AND
"PIV"."NAME"=DECODE("PEC"."CLASSIFICATION_NAME",'Special Payments','Payment Amount','Pay Value') AND
"PPA"."EFFECTIVE_DATE"<="PIV"."EFFECTIVE_END_DATE" AND
"PPA"."EFFECTIVE_DATE">="PIV"."EFFECTIVE_START_DATE")
filter("PPA"."EFFECTIVE_DATE">="PIV"."EFFECTIVE_START_DATE" AND
"PPA"."EFFECTIVE_DATE"<="PIV"."EFFECTIVE_END_DATE")
53 - access("PIV"."INPUT_VALUE_ID"="PRRV"."INPUT_VALUE_ID" AND
"PRR"."RUN_RESULT_ID"="PRRV"."RUN_RESULT_ID")
Note: cpu costing is off
Would appreciate if any one let me know what to do. Received on Fri Feb 25 2005 - 03:12:44 CST
 |
 |