10 Replies Latest reply: Sep 6, 2013 7:29 AM by Howard (... in Training) RSS

    Optimizing PL\SQL query for APEX

    Ivan2405

      Hi!

       

      I'm having problems with one query in my APEX application.

      It lasts too long compared to all the others, because of the way the SQL query is written.

      Page consists of 45 text fields that are used as input for filtering the table. After user has written filters for the where clause that he wants(he can set 1 or all of them) he pushes the "Filter" button that resubmits the page and that is the part that lasts too long.


      Main problem is that the text field can be left empty and also that the value of all the rows in table can be null; that's why the code is written in the way that it is.

       

      Does anyone have any idea how I could improve it?
      Code is written below.

       

      select

      "ROWID",

      "BNK_ID",

      "MSR_PRD_ID",

      "SRC_STM_ID",

      "ID",

      "ID_RETKA",

      "RSP",

      "OZNAKA_RETKA",

      "DATUM_STANJA",

      "OZNAKA_IZVJESCA",

      "OZNAKA_KOMITENTA",

      "MBR_KOMITENTA",

      "KOMITENT_NEREZ",

      "ZUPANIJA",

      "DRZAVA",

      "SEKTOR_NEREZIDENTA",

      "VRSTA_POVEZANOSTI",

      "INSTRUMENT",

      "ISIN",

      "VALUTA",

      "OTKAZNI_ROK",

      "IZVORNO_DOSPIJECE",

      "VRSTA_INDEKSACIJE",

      "VALUTA_INDEKSACIJE",

      "PORTFELJ",

      "UTRZIVOST_KREDITA",

      "ZNACAJKE_KAPITALA",

      "RIZICNA_SKUPINA",

      "UGRADJENI_DERIVAT",

      "ODNOSNA_VARIJABLA",

      "PREDZNAK",

      "IZNOS",

      "IZNOS_ACTUAL",

      "VRSTA_IZNOSA",

      "KOMITENT_PBR",

      "UDJELI_POVEZ_C",

      "AR_ID",

      "AU_ID",

      "ACT_AR_BAL_KN",

      "ACT_AR_BAL",

      "AR_BUSS_ID",

      "MTI_CCY_TP_ID",

      "REG_NO",

      "REG_SFX",

      "JMBG_ID_NO",

      "IP_ID",

      "NO_DYS_OO",

      "TAX_ID_NO",

      "INSTRUMENT_OLD",

      "PREDZNAK_OLD",

      "IZNOS_ACTUAL_OLD",

      "ACT_AR_BAL_KN_OLD",

      "ACT_AR_BAL_OLD",

      "NAPOMENA",

      "NOVI_POSAO_F",

      "LISTA_SUMARNA",

      "LISTA_REKAP",

      "POSTOTAK1",

      "POSTOTAK2",

      "POSTOTAK3",

      "DZS_IDY_CL_ID",

      "HNB_IP_CL_ID",

      "EXG_RT_CRD_RSK_F"

      from "#OWNER#"."IZV_SLOG_DET"

       

       

      WHERE

       

       

        nvl(MSR_PRD_ID,1) = nvl(nvl(:P7_X_MSR_PRD_ID,MSR_PRD_ID),1) and

        nvl(SRC_STM_ID,1) =  nvl(nvl(:P7_X_SRC_STM_ID,SRC_STM_ID),1) and

        nvl(OZNAKA_KOMITENTA,1) = nvl(nvl(:P7_X_OZNAKA_KOMITENTA,OZNAKA_KOMITENTA),1) and

         nvl(RSP,1) =  nvl(nvl(:P7_X_RSP,RSP),1) and

         nvl(OZNAKA_RETKA,1) =  nvl(nvl(:P7_X_OZNAKA_RETKA,OZNAKA_RETKA),1)  and

         nvl(OZNAKA_IZVJESCA,1) =  nvl(nvl(:P7_X_OZNAKA_IZVJESCA,OZNAKA_IZVJESCA),1) and

         nvl(MBR_KOMITENTA,1) =  nvl(nvl(:P7_X_MBR_KOMITENTA,MBR_KOMITENTA),1) and

         nvl(KOMITENT_NEREZ,1) =  nvl(nvl(:P7_X_KOMITENT_NEREZ,KOMITENT_NEREZ),1) and

         nvl(ZUPANIJA,1) =  nvl(nvl(:P7_X_ZUPANIJA,ZUPANIJA),1) and

         nvl(DRZAVA,1) =  nvl(nvl(:P7_X_DRZAVA,DRZAVA),1) and

         nvl(SEKTOR_NEREZIDENTA,1) =  nvl(nvl(:P7_X_SEKTOR_NEREZIDENTA,SEKTOR_NEREZIDENTA),1) and

         nvl(VRSTA_POVEZANOSTI,1) =  nvl(nvl(:P7_X_VRSTA_POVEZANOSTI,VRSTA_POVEZANOSTI),1) and

         nvl(INSTRUMENT,1) =  nvl(nvl(:P7_X_INSTRUMENT,INSTRUMENT),1) and

         nvl(VALUTA,1) =  nvl(nvl(:P7_X_VALUTA,VALUTA),1) and

        nvl(ISIN,1) =  nvl(nvl(:P7_X_ISIN,ISIN),1) and

      nvl(OTKAZNI_ROK,1) =  nvl(nvl(:P7_X_OTKAZNI_ROK,OTKAZNI_ROK),1) and

      nvl(IZVORNO_DOSPIJECE,1) =  nvl(nvl(:P7_X_IZVORNO_DOSPIJECE,IZVORNO_DOSPIJECE),1)  and

      nvl(VRSTA_INDEKSACIJE,1) =  nvl(nvl(:P7_X_VRSTA_INDEKSACIJE,VRSTA_INDEKSACIJE),1) and

      nvl(VALUTA_INDEKSACIJE,1) =  nvl(nvl(:P7_X_VALUTA_INDEKSACIJE,VALUTA_INDEKSACIJE),1)  and

      nvl(PORTFELJ,1) =  nvl(nvl(:P7_X_PORTFELJ,PORTFELJ),1) and

      nvl(UTRZIVOST_KREDITA,1) =  nvl(nvl(:P7_X_UTRZIVOST_KREDITA,UTRZIVOST_KREDITA),1) and

      nvl(ZNACAJKE_KAPITALA,1) =  nvl(nvl(:P7_X_ZNACAJKE_KAPITALA,ZNACAJKE_KAPITALA),1) and

      nvl(RIZICNA_SKUPINA,1) =  nvl(nvl(:P7_X_RIZICNA_SKUPINA,RIZICNA_SKUPINA),1)  and

      nvl(UGRADJENI_DERIVAT,1) =  nvl(nvl(:P7_X_UGRADJENI_DERIVAT,UGRADJENI_DERIVAT),1) and

      nvl(ODNOSNA_VARIJABLA,1) =  nvl(nvl(:P7_X_ODNOSNA_VARIJABLA,ODNOSNA_VARIJABLA),1) and

      nvl(PREDZNAK,1) =  nvl(nvl(:P7_X_PREDZNAK,PREDZNAK),1) and

      nvl(VRSTA_IZNOSA,1) =  nvl(nvl(:P7_X_VRSTA_IZNOSA,VRSTA_IZNOSA),1) and

      nvl(KOMITENT_PBR,1) =  nvl(nvl(:P7_X_KOMITENT_PBR,KOMITENT_PBR),1) and

      nvl(UDJELI_POVEZ_C,1) =  nvl(nvl(:P7_X_UDJELI_POVEZ_C,UDJELI_POVEZ_C),1) and

      nvl(AR_ID,1) =  nvl(nvl(:P7_X_AR_ID,AR_ID),1) and

      nvl(AU_ID,1) =  nvl(nvl(:P7_X_AU_ID,AU_ID),1) and

      nvl(AR_BUSS_ID,1) =  nvl(nvl(:P7_X_AR_BUSS_ID,AR_BUSS_ID),1) and

      nvl(MTI_CCY_TP_ID,1) =  nvl(nvl(:P7_X_MTI_CCY_TP_ID,MTI_CCY_TP_ID),1) and

      nvl(REG_NO,1) =  nvl(nvl(:P7_X_REG_NO,REG_NO),1) and

      nvl(REG_SFX,1) =  nvl(nvl(:P7_X_REG_SFX,REG_SFX),1) and

      nvl(JMBG_ID_NO,1) =  nvl(nvl(:P7_X_JMBG_ID_NO,JMBG_ID_NO),1) and

      nvl(IP_ID,1) =  nvl(nvl(:P7_X_IP_ID,IP_ID),1) and

      nvl(TAX_ID_NO,1) =  nvl(nvl(:P7_X_TAX_ID_NO,TAX_ID_NO),1) and

      nvl(INSTRUMENT_OLD,1) =  nvl(nvl(:P7_X_INSTRUMENT_OLD,INSTRUMENT_OLD),1) and

      nvl(PREDZNAK_OLD,1) =  nvl(nvl(:P7_X_PREDZNAK_OLD,PREDZNAK_OLD),1) and

      nvl(NAPOMENA,1) =  nvl(nvl(:P7_X_NAPOMENA,NAPOMENA),1) and

      nvl(NOVI_POSAO_F,1) =  nvl(nvl(:P7_X_NOVI_POSAO_F,NOVI_POSAO_F),1) and

      nvl(LISTA_SUMARNA,1) =  nvl(nvl(:P7_X_LISTA_SUMARNA,LISTA_SUMARNA),1) and

      nvl(LISTA_REKAP,1) =  nvl(nvl(:P7_X_LISTA_REKAP,LISTA_REKAP),1) and

      nvl(DZS_IDY_CL_ID,1) =  nvl(nvl(:P7_X_DZS_IDY_CL_ID,DZS_IDY_CL_ID),1) and

      nvl(HNB_IP_CL_ID,1) =  nvl(nvl(:P7_X_HNB_IP_CL_ID,HNB_IP_CL_ID),1) and

      nvl(NO_DYS_OO,1) = nvl(nvl(:P7_X_NO_DYS_OO,NO_DYS_OO),1) and

      nvl(POSTOTAK1,1) = nvl(nvl(:P7_X_POSTOTAK1,POSTOTAK1),1) and

      nvl(POSTOTAK2,1) = nvl(nvl(:P7_X_POSTOTAK2,POSTOTAK2),1) and

      nvl(POSTOTAK3,1) = nvl(nvl(:P7_X_POSTOTAK3,POSTOTAK3),1) and

      nvl(BNK_ID,1) = nvl(nvl(:P7_X_BNK_ID,BNK_ID),1) and

      nvl(ID,1) = nvl(nvl(:P7_X_ID,ID),1) and

      nvl(ID_RETKA,1) = nvl(nvl(:P7_X_ID_RETKA,ID_RETKA),1)  and

      nvl(DATUM_STANJA,1) = nvl(nvl(:P7_X_DATUM_STANJA,DATUM_STANJA),1) and

      nvl(IZNOS,1) = nvl(nvl(:P7_X_IZNOS,IZNOS),1) and

      nvl(IZNOS_ACTUAL,1) = nvl(nvl(:P7_X_IZNOS_ACTUAL,IZNOS_ACTUAL),1) and

      nvl(ACT_AR_BAL_KN,1) = nvl(nvl(:P7_X_ACT_AR_BAL_KN,ACT_AR_BAL_KN),1) and

      nvl(ACT_AR_BAL,1) = nvl(nvl(:P7_X_ACT_AR_BAL,ACT_AR_BAL),1) and

      nvl(IZNOS_ACTUAL_OLD,1) = nvl(nvl(:P7_X_IZNOS_ACTUAL_OLD,IZNOS_ACTUAL_OLD),1) and

      nvl(ACT_AR_BAL_KN_OLD,1) = nvl(nvl(:P7_X_ACT_AR_BAL_KN_OLD,ACT_AR_BAL_KN_OLD),1) and

      nvl(ACT_AR_BAL_OLD,1) = nvl(nvl(:P7_X_ACT_AR_BAL_OLD,ACT_AR_BAL_OLD),1) and

      nvl(EXG_RT_CRD_RSK_F,1) = nvl(nvl(:P7_X_EXG_RT_CRD_RSK_F,EXG_RT_CRD_RSK_F),1)