1 Reply Latest reply: Sep 29, 2011 3:24 PM by user521233 RSS

    Expression Filters Performance

    80511
      Hi,

      I'm trying to test the expression filters functionality using two tables:

      Env: Oracle 11g R2 (11.2.0.2) EE on AIX

      T1 is the data table and contains about 90 million rows with around 60 columns. Only 4 columns are evaluated in the filter with three of them always having a value (me_cat, me_tp, me_code) and the other one (base_plan) having nulls in some rows.

      T2 contains 666 expressions using the four columns I mentioned before in every expression.

      I have tried different parameters for the index on the T2(expression column) but it doesn't help at all.

      This is the query I'm using:

      insert into ma_pps_mpt_eligibles
      select
      m.id_monthly_record,
      m.id_client,
      m.name_client,
      m.cd_sex,
      m.cd_race,
      m.dt_birth,
      m.sig,
      m.dt_elig,
      m.dt_smib_from,
      m.dt_smib_thru,
      m.flag_smib,
      m.cd_buy_in,
      m.base_plan,
      m.mc_county,
      m.mc_statuscode,
      m.mc_cd_occur,
      m.me_cat,
      m.me_code,
      m.dt_me_open,
      m.dt_me_close,
      m.me_tp,
      m.me_sd,
      m.me_cd_occur,
      m.age,
      m.contractor,
      m.mc_plan,
      m.dt_mc_from,
      m.dt_mc_to,
      m.cd_smib_buyin,
      m.id_hmo,
      m.id_state_rg,
      m.excl_prem,
      m.excl_count,
      m.rate_pure,
      m.rate_admin,
      m.page_count,
      m.rg_page,
      m.voucher_page,
      m.id_provider_sp,
      m.srv_grp,
      m.srv_code,
      m.med_choice_code,
      m.dt_admit,
      m.dt_discharge,
      m.dt_int_admit,
      m.flag_alias,
      m.id_client_real,
      m.zip_mail,
      m.size_family,
      m.income_family,
      m.education,
      m.id_case,
      m.flag_tx_hold,
      m.id_type_plan,
      m.cd_partition,
      m.validity,
      m.hic,
      m.ssn,
      m.flag_lockin,
      m.counter,
      m.rg_comp_id,
      t.id_mtp_elig_rule
      from
      ma_pps_monthly m,
      ma_pps_mtp_elig_rules_exp t
      where
      m.cd_partition = '201104' and
      m.validity = 0 and
      evaluate
      (
      t.mtp_rule_condition,
      ty_ma_pps_mtp_elig_attr (m.me_cat, m.me_tp, m.me_code, m.base_plan).getvarchar()
      ) = 1

      What I'm doing/assuming wrong here?

      Has anyone tried to evaluate such amount of data?

      Regards,
      Galo