not able to create materialized view of union based complex query

Question

I am trying to create materialized view (fast refresh method) but its throwing error

ORA-12015: cannot create a fast refresh materialized view from a complex query error.

I can't change the query because it's been using in our Oracle Apex application but it's a view originally I have to convert it in MV. Please suggest which kind of materialized view will be suitable for my query which contains 32 tables along with unions and analytic functions.

create materialized view SAMPLE
    refresh fast
    as
    select ms.employee_id,
           ms.sal_code code,
           'SALARY' Account_type,
           UPPER(sb.salary_breakup) Account_title,
           sb.account_code ACCOUNT_CODE,
           ('ADDITION') AMOUNT_TYPE,
           sb.pay_type PAY_TYPE,
           p.display_title PAY_TITLE,
           ms.salary AMOUNT,
           p.order_in_payroll,
           to_number((select order_by from payroll_account_title_org_wise
    where org_id=p.org_id and account_id=1) || substr(sb.sal_code,3,3)) Code_order,
           ms.month,
           sb.tax_exemption,
           sb.taxtation_type,
           'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
           p.org_id
           from PAY_MONTHLY_SALARY ms,pay_salary_breakups sb,PAYROL_TYPE p
           where ms.sal_code=sb.sal_code
          and p.pay_type=sb.pay_type
           --order by sb.sal_code
    union
    -----ALLOWANCE PART
    select mad.employee_id,
           mad.ad_code code,
           ad.type Account_type,
           UPPER(ad.ad_name) Account_title,
           ad.account_code ACCOUNT_CODE,
           'ADDITION' AMOUNT_TYPE,
          ad.pay_type PAY_TYPE,
          p.display_title PAY_TITLE,
           mad.amount AMOUNT,
           p.order_in_payroll,
           to_number((select order_by from payroll_account_title_org_wise
    where org_id=p.org_id and account_id=2) || substr(mad.ad_code,3,3)) Code_order,
           mad.month,
           ad.tax_exemption,
           ad.taxtation_type,
           'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
           p.org_id
           from Pay_Month_Allow_Ded mad,pay_allowance_deduction_setup ad,PAYROL_TYPE p
           where mad.ad_code=ad.ad_code
           and p.pay_type=ad.pay_type
           and ad.type ='ALLOWANCE'
           --order by ad.ad_code
    
    union
    -----DEDUCTION PART
    select mad.employee_id,
           mad.ad_code code,
           ad.type Account_type,
           UPPER(ad.ad_name) Account_title,
           ad.account_code ACCOUNT_CODE,
          'DEDUCTION' AMOUNT_TYPE,
          ad.pay_type PAY_TYPE,
          p.display_title PAY_TITLE,
           mad.amount AMOUNT,
           p.order_in_payroll,
           to_number((select order_by from payroll_account_title_org_wise
    where org_id=p.org_id and account_id=3) || substr(mad.ad_code,3,3)) Code_order,
           mad.month,
           ad.tax_exemption,
           ad.taxtation_type,
           'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
           p.org_id
           from Pay_Month_Allow_Ded mad,pay_allowance_deduction_setup ad,PAYROL_TYPE p
           where mad.ad_code=ad.ad_code
           and p.pay_type=ad.pay_type
           and ad.type='DEDUCTION'
           --order by ad.ad_code
    
    union
    ---- FUNDS AND CONTRIBUTION PART
    select e.employee_id
    ,fcs.fc_code
    ,'FUNDS' Account_type
    ,UPPER(fct.fc_type_name) ACCOUNT_TITLE
    ,(case when fct.type='EMPLOYEE' then fcs.account_code_credit
           when fct.type='EMPLOYER' then fcs.account_code
             end) ACCOUNT_CODE
    ,(case when fct.type='EMPLOYEE' then 'DEDUCTION'
           when fct.type='EMPLOYER' then 'OTHERTAX'
             end) AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,r.amount AMOUNT
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=4) || substr(fcs.fc_code,3,3)) Code_order
    ,r.month
    ,(case when fcs.TAX_EXEMPTION='Y' then fcs.tax_exemption
           when fcs.TAX_EXEMPTION='N' then fct.tax_exemption
             end) TAX_EXEMPTION,
            fcs.taxtation_type,
           'Y' INCLUDE_IN_MONTHLYTAX_INCOME,
           pt.org_id
    from
    hr_employee_info e
    ,fund_contribution_records r
    ,fund_contributions_type fct
    ,fund_contributions_setup fcs
    ,payrol_type pt
    where e.employee_id=r.employee_id
    and r.contribution_type_id=fct.contribution_type_id
    and r.fc_code=fcs.fc_code
    and fct.fc_code=fcs.fc_code
    and fct.payroll_type=pt.pay_type
    
    union
    ----- ATTENDACE DATA PART
    select
    pmt.employee_id
    ,att.att_code
    ,'ATTENDANCE' Account_type
    ,UPPER(att.attendace_type) ACCOUNT_TITLE
    ,NULL ACCOUNT_CODE
    ,'ZERO' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,pmt.attendance_days AMOUNT
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=5) || substr(att.att_code,3,3)) Code_order
    ,pmt.month
    ,'Y' TAX_EXEMPTION
    ,'NONE' taxtation_type
    ,'N' INCLUDE_IN_MONTHLYTAX_INCOME
    ,       pt.org_id
    from att_attendance_seup att
    ,pay_monthly_attendance pmt
    ,payrol_type pt
    where att.att_code=pmt.att_code
    and pt.pay_type=att.pay_type
    and pt.org_id=att.org_id
    
    /*union
    
    ---- TARGET DATA PART
    
    select
    pmt.employee_id
    ,pts.ta_code CODE
    ,UPPER(pts.target_name) ACCOUNT_TITLE
    ,'ZERO' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,pmt.value AMOUNT
    ,pt.order_in_payroll
    ,to_number(5 || substr(pts.ta_code,4,1)) Code_order
    ,pmt.month
    ,'Y' TAX_EXEMPTION,
           pt.org_id
    from
    pay_monthly_targets pmt
    ,pay_target_setup pts
    ,payrol_type pt
    where pts.ta_code=pmt.ta_code
    and pt.pay_type=pts.pay_type*/
    
    union
    
    ----LOAN DISBURSEMENT PART
    
    
    select
    pmld.employee_id
    ,plns.ln_code code
    ,'LOAN DISBURSEMENT' Account_type
    ,UPPER(plns.loan_name) ACCOUNT_TITLE
    ,plns.account_code ACCOUNT_CODE
    ,'ADDITION' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,pmld.amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=6) || substr(plns.ln_code,3,3)) Code_order
    ,pmld.month
    ,'Y' tax_exemption
    ,plns.taxtation_type
    ,'N' INCLUDE_IN_MONTHLYTAX_INCOME
    ,       pt.org_id
    from
    PAY_LOAN_SETUP plns
    ,pay_loan_monthly_disbursement pmld
    ,payrol_type pt
    where plns.ln_code=pmld.ln_code
    and plns.pay_type_disburse=pt.pay_type
    
    union
    
    ------- LOAN INSTALLMENTS PART
    select
    peld.employee_id
    ,plns.ln_code code
    ,'LOAN INSTALLMENT' Account_type
    ,UPPER(plns.loan_name || '(I)') ACCOUNT_TITLE
    ,plns.account_code_installment ACCOUNT_CODE
    ,'DEDUCTION' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,plei.amount amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=7) || substr(plns.ln_code,3,3)) Code_order
    ,plei.month
    ,'Y' tax_exemption
    ,plns.taxtation_type
    ,'N' INCLUDE_IN_MONTHLYTAX_INCOME
    ,       pt.org_id
    from
    PAY_LOAN_SETUP plns
    ,PAY_EMP_LOAN_DISBURSEMENT peld
    ,PAY_EMP_LOAN_INSTALMENTS plei
    ,payrol_type pt
    where plns.ln_code=peld.ln_code
    and peld.loan_id=plei.loan_id
    and plns.pay_type=pt.pay_type
    and pt.org_id=plns.org_id
    
    
    -----LOAN MARKUP
    union
    
    select
    peld.employee_id
    ,plns.ln_code code
    ,'LOAN MARKUP' Account_type
    ,UPPER(plns.loan_name || '(M)') ACCOUNT_TITLE
    ,plns.account_code_markup ACCOUNT_CODE
    ,'DEDUCTION' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,plei.benchmark_taken amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=8) || substr(plns.ln_code,3,3)) Code_order
    ,plei.month
    ,'Y' tax_exemption
    ,plns.taxtation_type
    ,'N' INCLUDE_IN_MONTHLYTAX_INCOME
    ,       pt.org_id
    from
    PAY_LOAN_SETUP plns
    ,PAY_EMP_LOAN_DISBURSEMENT peld
    ,PAY_EMP_LOAN_INSTALMENTS plei
    ,payrol_type pt
    where plns.ln_code=peld.ln_code
    and peld.loan_id=plei.loan_id
    and plns.pay_type=pt.pay_type
    
    --------REIMBURSEMENT PART
    
    union
    
    select
    hrc.employee_id
    ,hrs.re_code CODE
    ,'REIMBURSEMENT' Account_type
    ,UPPER(hrs.reimbursement_name) ACCOUNT_TITLE
    ,hrs.account_code ACCOUNT_CODE
    ,'ADDITION' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,hrc.amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=9) || substr(hrs.re_code,3,3)) Code_order
    ,hrc.month
    ,hrs.tax_exemption
    ,hrs.taxtation_type
    ,'Y' INCLUDE_IN_MONTHLYTAX_INCOME
    ,       pt.org_id
    from
    hr_reimbursement_setup hrs
    ,pay_monthly_reimbursement hrc
    ,payrol_type pt
    ,hr_employee_info e
    where hrs.re_code=hrc.re_code
    and hrc.employee_id=e.employee_id
    and hrs.pay_type=pt.pay_type
    
    --------OTHER INCOME TAX PART
    
    
    union
    
    select d.employee_id
    ,to_char('OT'||lpad(d.itax_other_id,3,000)) CODE
    ,'OTHER TAXABLE' Account_type
    ,UPPER(s.name || '(OT)') ACCOUNT_TITLE
    ,NULL ACCOUNT_CODE
    ,'OTHERTAX' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,(case when s.type='ADDITION' then i.amount
           when s.type='EXEMPTION' then i.amount*-1
             end) Amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=10) || lpad(d.itax_other_id,3,000)) CODE_ORDER
    ,i.month
    ,'N' TAX_EXEMPTION
    ,s.taxtation_type
    ,s.include_in_mothly_income  INCLUDE_IN_MONTHLYTAX_INCOME
    ,i.org_id
    from
    PAY_MONTHLY_OTHER_TAX_INCOME i
    ,ITAX_DECLARE_OTHER_EMP_INCOME d
    ,ITAX_OTHER_INCOME_SETUP s
    ,payrol_type pt
    where i.org_id=s.org_id
    and d.org_id=s.org_id
    and i.itax_declare_id=d.itax_declare_id
    and s.itax_other_id=d.itax_other_id
    and s.pay_type=pt.pay_type
    and s.org_id=pt.org_id
    
    -----TAXABLE INCOME(just to show)
    
    union
    
    select
    i.employee_id
    ,t.tx_code CODE
    ,'TAXABLE INCOME' Account_type
    ,'TAXABLE INCOME' ACCOUNT_TITLE
    ,NULL ACCOUNT_CODE
    ,'ZERO' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    , sum(i.amount) amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=i.org_id and account_id=11) || substr(t.tx_code,3,3)) Code_order
    ,i.month
    ,'Y' TAX_EXEMPTION
    ,'NONE' taxtation_type
    ,'N' INCLUDE_IN_MONTHLYTAX_INCOME
    ,i.org_id
    from
    TAX_CODE_REGIONS t
    ,PAY_EMP_MONTHLY_TAXABLE_INCOME i
    ,payrol_type pt
    where t.tx_code=i.tx_code
    and i.INCLUDE_IN_MOTHLY_INCOME='Y'
    --and t.pay_type=pt.pay_type
    and pt.pay_type=Default_Value.GET_SYSTEM_DEFAULT('TXPAYTYPE',i.org_id)
    group by i.employee_id,t.tx_code,pt.pay_type,pt.display_title,pt.order_in_payroll,i.month,i.org_id
    
    
    
    --------Income Tax Part
    
    union
    
    
    select
    i.employee_id
    ,t.tx_code CODE
    ,'INCOME TAX' Account_type
    ,'INCOME TAX' ACCOUNT_TITLE
    ,DEFAULT_VALUE.GET_SYSTEM_DEFAULT('ITAC',i.org_id) ACCOUNT_CODE
    ,'DEDUCTION' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,i.amount amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=12) || substr(t.tx_code,3,3)) Code_order
    ,i.month
    ,'Y' TAX_EXEMPTION
    ,'NONE' taxtation_type
    ,'N' INCLUDE_IN_MONTHLYTAX_INCOME
    ,i.org_id
    from
    TAX_CODE_REGIONS t
    ,pay_monthly_itax i
    ,payrol_type pt
    where t.tx_code=i.tx_code
    --and t.pay_type=pt.pay_type
    and pt.pay_type=Default_Value.GET_SYSTEM_DEFAULT('TXPAYTYPE',i.org_id)
    
    
    
    -----GRATUITY PART(only for report purpose)
    
    union
    
    select
    p.employee_id
    ,to_char('GP'||lpad(p.gratuity_id,3,000)) CODE
    ,'GRATUITY' Account_type
    ,UPPER(gs.name) ACCOUNT_TITLE
    ,gs.account_code ACCOUNT_CODE
    ,'ZERO' AMOUNT_TYPE
    ,gs.pay_type
    ,pt.display_title PAY_TITLE
    ,p.amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=13) || lpad(p.gratuity_id,3,000)) CODE_ORDER
    ,p.month
    ,'Y' TAX_EXEMPTION
    ,'NONE' taxtation_type
    ,'N' INCLUDE_IN_MONTHLYTAX_INCOME
    ,pt.org_id
    from
    HR_GRATUITY_MONTHLY_ACCRUALS p,
    HR_GRATUITY_SETUP gs,
    payrol_type pt
    where p.gratuity_id=gs.gratuity_id
    and p.org_id=gs.org_id
    and gs.pay_type=pt.pay_type
    and p.org_id=pt.org_id
    
    ---------LEAVE ENCASHMENT PART
    
    union
    
    select
    LED.Employee_Id
    ,'LE'||lpad(lt.leave_type_id,3,0) CODE
    ,'ALLOWANCE' ACCOUNT_TYPE
    ,UPPER(lt.leave_name) ACCOUNT_TITLE
    ,null ACCOUNT_CODE
    ,'ADDITION' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,LED.Amount
    ,pt.order_in_payroll
    ,to_number((select order_by from payroll_account_title_org_wise
    where org_id=pt.org_id and account_id=2)||lpad(lt.leave_type_id,3,0)) CODE_ORDER
    ,LED.Month
    ,'N' TAX_EXEMPTION
    ,'VARIABLE' TAXTATION_TYPE
    ,'Y' INCLUDE_IN_MONTHLYTAX_INCOME
    ,LED.Org_Id
    from
    HR_LEAVE_ENCASHMENT_DISBRSE LED
    ,Hr_Leave_Type lt
    ,(select max(pt.order_in_payroll) order_in_payroll,pt.org_id from Payrol_Type pt group by pt.org_id) pt_a
    ,Payrol_Type pt
    where LED.Leave_Type_Id=lt.leave_type_id
    and LED.Org_Id=lt.org_id
    and LED.Org_Id=pt_a.org_id
    and pt_a.org_id=pt.org_id
    and pt_a.order_in_payroll=pt.order_in_payroll
    
    ------Generate Fake Lines For every Employee against pay_type
    
    union
    
    select
    e.employee_id
    ,'FAKE' CODE
    ,pt.display_title Account_type
    ,'FAKE' ACCOUNT_TITLE
    ,pt.account_code ACCOUNT_CODE
    ,'ZERO' AMOUNT_TYPE
    ,pt.pay_type
    ,pt.display_title PAY_TITLE
    ,0 AMOUNT
    ,pt.order_in_payroll
    ,99991 CODE_ORDER
    ,pi.month
    ,'Y' TAX_EXEMPTION
    ,'NONE' taxtation_type
    ,'N' INCLUDE_IN_MONTHLYTAX_INCOME
    ,pi.org_id
    from
    PAY_MONTHLY_PROCESS_INFO pi
    ,hr_employee_info e
    ,payrol_type pt
    where pi.org_id=e.org_id
    and pi.org_id=pt.org_id
    and pi.month>=e.join_date;

docs.oracle.com/database/121/SQLRF/… you have a restriction so you cant do it, either you have to rewrite the query to qualify or use different approach. — JagaSrik
"Materialized views are not eligible for fast refresh if the defining query contains an analytic function or the XMLTable function." — JagaSrik
I can't rewrite this query. I heard there is some MV regarding union too. Do u have any idea? — Sumaiya Qureshi
yes because union avoids duplicates and sorts the data, can you use union all instead ? — JagaSrik
I used QUERY REWRITE it has solved previous error now getting error on where clause where plsql function is use that it's not supporting plsql function. any workaround for that? — Sumaiya Qureshi

JagaSrik JagaSrik · Accepted Answer · 2020-07-14T06:28:00

Suggestion for different approach -

You have a restriction so you cant do it, either you have to rewrite the query to qualify or use different approach.

Avoid union if you can see if you can use union all use it instead.
Try to create multiple MV on each queries that you posted, not all of your queries has analytical functions.
The ones which you have analytical functions, those create it as views
If you want to union then you just do it with sql or create a view

like

select * from mv1 union select * from view_1 union slect * from mv2 union ....

May be your database server might be on huge load if you have huge data with 30+ fast refresh MV, so please consult your DBA before implementing it.

not able to create materialized view of union based complex query

1 Answers