Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Question on "Non-Mergeable View"

Question on "Non-Mergeable View"

From: <nekkalapudi.siva_at_gmail.com>
Date: 25 Feb 2005 01:12:44 -0800
Message-ID: <1109322764.713540.277810@f14g2000cwb.googlegroups.com>


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

FROM
  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

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 = decode(pec.classification_name,'Special Payments','Payment Amount','Pay Value') 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

  NOT EXISTS (SELECT null FROM pay_action_interlocks pai WHERE pai.locked_action_id = paa.assignment_action_id) GROUP BY pet.rowid ,paa.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) ,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) UNION ALL SELECT pet.rowid ROW_ID
,pai.locking_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  FROM 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 ,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 =
decode(pec.classification_name,'Special Payments','Payment Amount','Pay Value') 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.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','Non
Taxable 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')
  41 - access("PPA"."PAYROLL_ACTION_ID"="PAA"."PAYROLL_ACTION_ID")   43 -
access("PAA"."ASSIGNMENT_ACTION_ID"="PRR"."ASSIGNMENT_ACTION_ID")
  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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US