4 Replies Latest reply: May 21, 2010 6:13 AM by Charles Hooper RSS

    MY insert statement is running for very long time how can i optimize

    687192
      Hi ,

      I have an Oracl 10gR2 databse I am running an insert statement in my database its runing since along time. How can i make it run faster.Suggestions required.

      This is teh insert statement:
      INSERT INTO ARREARS_CHECK_2
      SELECT a.student_id, c.uin_fin_no, c.student_name, d.type_code,
      d.school_code, d.school_name, c.level_xcode,(SELECT cd.code_full_desc
      FROM cp_code_profile cd
      WHERE cd.category_name = 'CITIZENSHIP'
      AND cd.code_value = b.student_citizenship) ctz,
           (SELECT cd.code_full_desc
      FROM CP_CODE_PROFILE cd
      WHERE cd.category_name = 'RACE'
      AND cd.code_value = b.STUDENT_RACE) STUDENT_RACE,
      (CASE
      WHEN ( c.academic_year = to_char(sysdate,'yyyy')
      AND c.student_status_icode = 'A'
      AND d.type_code IN ('A', 'G')
      )
      THEN 'Active'
      ELSE 'Not Active'
      END
      ) schooling_status,
      (SELECT code_desc
      FROM rm_com_cde
      WHERE code_type = 'ARR_NTF_STATUS'
      AND code_value =
      (NVL ((SELECT NVL ((SELECT distinct '7'
      FROM rm_arr_ntf_final_rem x3
      WHERE x3.student_id = a.student_id
      AND x3.ntf_date >
      x.notification_date),
      x.ntf_type
      ) arr
      FROM rm_arr_ntf_rpt x
      WHERE x.student_id = a.student_id
      AND x.ntf_rpt_rec_id =
      (SELECT MAX (y.ntf_rpt_rec_id)
      FROM rm_arr_ntf_rpt y
      WHERE y.student_id = a.student_id)),
      (SELECT distinct '7'
      FROM rm_arr_ntf_final_rem x3
      WHERE x3.student_id = a.student_id)
      )
      )) arrears_status,
      SUM
      ((CASE
      WHEN (a.bill_month <> (SELECT MAX (bill_month)FROM rm_bil_month))
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arrears_amount,
      SUM
      ((CASE
      WHEN ( a.bill_month between (SELECT add_months(MAX (bill_month),-3) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-1) FROM rm_bil_month)
      )
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arr_3_mth_below,
      SUM
      ((CASE
      WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-6) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-4) FROM rm_bil_month))
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arr_3_6_mths,
      SUM
      ((CASE
      WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-12) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-7) FROM rm_bil_month))
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arr_6_mth_1_yr,
      SUM
      ((CASE
      WHEN ( a.bill_month between (SELECT add_months(MAX (bill_month),-24) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-13) FROM rm_bil_month))
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arr_1_2_yr,
      SUM
      ((CASE
      WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-36) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-25) FROM rm_bil_month))
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arr_2_3_yr,
      SUM
      ((CASE
      WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-48) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-37) FROM rm_bil_month))
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arr_3_4_yr,
      SUM
      ((CASE
      WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-60) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-49) FROM rm_bil_month))
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arr_4_5_yr,
      SUM
      ((CASE
      WHEN (a.bill_month <= (SELECT add_months(MAX (bill_month),-61) FROM rm_bil_month) )
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) arr_above_5_yr,
      SUM
      ((CASE
      WHEN (a.bill_month = (SELECT MAX (bill_month) FROM rm_bil_month) )
      THEN (net_charges - amount_collected)
      ELSE 0
      END
      )
      ) curr_mth_net,
      SUM (net_charges - amount_collected) total_os,
      NVL ((SELECT TO_CHAR (MAX (w.dropout_date), 'yyyymmdd')
      FROM cp_stud_dropout w, cp_stud_profile w2
      WHERE w.student_id = c.student_id
      AND w2.student_id = w.student_id
      AND NOT ( w2.student_status_icode = 'A'
      AND w2.academic_year = (to_char(sysdate,'yyyy'))
      )),
      'NA'
      ) dropout_year,
      NVL ((SELECT TO_CHAR (MAX (effective_start_date), 'yyyymmdd')
      FROM rm_com_stud_profl_hist b1
      WHERE b1.student_id = a.student_id
      AND b1.item_type = 'SCH_ST'
      AND b1.item_value = 'I'),
      'NA'
      ) inactive_date,
      CASE
      WHEN (b.student_citizenship = '10' AND b.student_race = '1'
      )
      THEN 'SC(M)'
      WHEN ( b.student_citizenship = '10'
      AND b.student_race = '5'
      AND b.student_religion = '2'
      AND ( b.father_citizenship = '10'
      OR b.mother_citizenship = '10'
      )
      )
      THEN 'SC(IE)'
      WHEN (b.student_citizenship = '10')
      THEN 'SC'
      WHEN (b.student_citizenship BETWEEN '20' AND '29')
      THEN 'SPR'
      ELSE 'FS'
      END pupil_fee_profile,
      (CASE
      WHEN ( (select MAX (bill_month)+4 from rm_bil_month) BETWEEN b.pass_start_date AND b.pass_end_date)
      THEN b.student_pass_type
      ELSE 'NA'
      END
      ) pass_type,
      NVL (TO_CHAR (b.midtier_granted_month, 'yyyymmdd'),
      'NA'
      ) mid_tier_grant_start_date,
      NVL
      ((SELECT u.scheme_name
      FROM rm_sub_grant t, rm_sub_scheme u
      WHERE t.scheme_id IN (1, 2)
      AND t.scheme_id = u.scheme_id
      AND (SELECT MAX (bill_month)FROM rm_bil_month)
      BETWEEN trunc(t.grant_start_month)
      AND NVL (trunc(t.rvcn_start_month), trunc(t.grant_end_month))
      AND t.student_id = a.student_id),
      'NA'
      ) moe_fas,
      NVL
      ((SELECT u.scheme_name
      FROM rm_sub_grant t, rm_sub_scheme u
      WHERE t.scheme_id NOT IN (1, 2)
      AND t.scheme_id = u.scheme_id
      AND (SELECT MAX (bill_month)FROM rm_bil_month)
      BETWEEN trunc(t.grant_start_month)
      AND NVL (trunc(t.rvcn_start_month), trunc(t.grant_end_month))
      AND t.student_id = a.student_id),
      'NA'
      ) sac_smc_fas,
      NVL
      ((SELECT DISTINCT 'Y'
      FROM rm_sub_scholarship_award u
      WHERE a.student_id = u.student_id
      AND (select MAX (bill_month)+4 from rm_bil_month)
      BETWEEN u.award_start_date
      AND u.award_end_date
      AND (select MAX (bill_month)+4 from rm_bil_month) >
      (SELECT MAX (u6.effective_date)
      FROM rm_sub_scholarship_status u6
      WHERE u6.award_id = u.award_id
      AND u6.status_code = 'AC')
      AND (select MAX (bill_month)+4 from rm_bil_month) <
      NVL ((SELECT MAX (u6.effective_date)
      FROM rm_sub_scholarship_status u6
      WHERE u6.award_id = u.award_id
      AND u6.status_code <> 'AC'),
      SYSDATE + 365
      )),
      'N'
      ) scholarship,
      NVL ((SELECT 'Y'
      FROM rm_arr_instl w, RM_ARR_INSTL_DTL w1
      WHERE w.student_id = a.student_id and w.status = '2' and w.INSTALMENT_ID=w1.INSTALMENT_ID
                          and w1.DUE_DATE > (SELECT MAX (bill_month)FROM rm_bil_month) group by w.student_id),
      'N'
      ) instalment_plan,
      NVL ((SELECT 'Y'
      FROM rm_edu_so w
      WHERE w.student_id = a.student_id AND w.status_code = '1'),
      'N'
      ) edusave_so,
      NVL ((SELECT w.balance_amount
      FROM rm_edu_acct w
      WHERE w.student_id = a.student_id),
      0) edusave_account_balance
      FROM rm_rev_knock_off_coll a,
      rm_com_stud b,
      cp_stud_profile c,
      cp_arch_school d,
      arr_check e
      WHERE a.student_id = b.student_id
      AND a.student_id = e.student_id
      AND c.school_code = d.school_code
      AND b.cp_student_id = c.student_id
      AND a.full_payment_ind = 'N'
      GROUP BY a.student_id,
      c.student_id,
      c.uin_fin_no,
      c.student_name,
      d.type_code,
      d.school_code,
      d.school_name,
      c.level_xcode,
      b.father_citizenship,
      b.mother_citizenship,
      b.student_citizenship,
      b.STUDENT_RACE,
      b.student_race,
      b.student_religion,
      b.pass_start_date,
      b.pass_end_date,
      b.student_pass_type,
      b.midtier_granted_month,
      (CASE
      WHEN ( c.academic_year = to_char(sysdate,'yyyy')
      AND c.student_status_icode = 'A'
      AND d.type_code IN ('A', 'G')
      )
      THEN 'Active'
      ELSE 'Not Active'
      END
      );




      Regards,
      Ashlee
        • 1. Re: MY insert statement is running for very long time how can i optimize
          sb92075
          How can i make it run faster.Suggestions required.
          post DDL for all tables & indexes involved.

          post formatted EXPLAIN PLAN
          • 2. Re: MY insert statement is running for very long time how can i optimize
            687192
            here is the explain plan output:

            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------
            Plan hash value: 2955142331

            --------------------------------------------------------------------------------
            ------------------------

            | Id | Operation | Name | Rows | Bytes |TempSpc|
            Cost (%CPU)| Time |

            --------------------------------------------------------------------------------
            ------------------------


            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------
            | 0 | INSERT STATEMENT | | 195K| 56M| |
            81541 (5)| 00:16:19 |

            | 1 | HASH GROUP BY | | 195K| 56M| |
            81541 (5)| 00:16:19 |

            |* 2 | HASH JOIN | | 195K| 56M| |
            81502 (5)| 00:16:19 |

            | 3 | TABLE ACCESS FULL | CP_ARCH_SCHOOL | 713 | 47058 | |
            38 (0)| 00:00:01 |

            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------

            |* 4 | HASH JOIN | | 195K| 44M| 19M|
            81459 (5)| 00:16:18 |

            |* 5 | HASH JOIN | | 119K| 17M| 8848K|
            65358 (6)| 00:13:05 |

            |* 6 | HASH JOIN | | 119K| 7444K| |
            58730 (6)| 00:11:45 |

            | 7 | TABLE ACCESS FULL| ARR_CHECK | 56503 | 717K| |

            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------
            33 (7)| 00:00:01 |

            |* 8 | TABLE ACCESS FULL| RM_REV_KNOCK_OFF_COLL | 1084K| 52M| |
            58668 (6)| 00:11:45 |

            | 9 | TABLE ACCESS FULL | RM_COM_STUD | 740K| 66M| |
            2439 (5)| 00:00:30 |

            | 10 | TABLE ACCESS FULL | CP_STUD_PROFILE | 1216K| 90M| |
            9891 (2)| 00:01:59 |


            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------
            --------------------------------------------------------------------------------
            ------------------------


            Predicate Information (identified by operation id):
            ---------------------------------------------------

            2 - access("C"."SCHOOL_CODE"="D"."SCHOOL_CODE")
            4 - access("B"."CP_STUDENT_ID"="C"."STUDENT_ID")
            5 - access("A"."STUDENT_ID"="B"."STUDENT_ID")
            6 - access("A"."STUDENT_ID"="E"."STUDENT_ID")

            PLAN_TABLE_OUTPUT
            --------------------------------------------------------------------------------
            8 - filter("A"."FULL_PAYMENT_IND"='N')

            Note
            -----
            - dynamic sampling used for this statement

            30 rows selected.
            • 3. Re: MY insert statement is running for very long time how can i optimize
              Niall Litchfield
              Hi

              You'll need to concentrate on the SELECT statement. I see you've given some requested information - You'll find the steps detailed in the thread at HOW TO: Post a SQL statement tuning request - template posting very useful.

              Niall Litchfield
              http://www.orawin.info/
              • 4. Re: MY insert statement is running for very long time how can i optimize
                Charles Hooper
                user8095482 wrote:
                here is the explain plan output:
                Although the link provided by Niall will be very helpful, I strongly suggest a complete rewrite of the SQL statement. There is a problem with the explain plan that you posted (note that I am posting this with a { code } tag (without spaces) before and after the text to preserve spaces).
                Plan hash value: 2955142331
                
                --------------------------------------------------------------------------------------------------------
                | Id  | Operation              | Name                  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                --------------------------------------------------------------------------------------------------------
                |   0 | INSERT STATEMENT       |                       |   195K|    56M|       | 81541   (5)| 00:16:19 |
                |   1 |  HASH GROUP BY         |                       |   195K|    56M|       | 81541   (5)| 00:16:19 |
                |*  2 |   HASH JOIN            |                       |   195K|    56M|       | 81502   (5)| 00:16:19 |
                |   3 |    TABLE ACCESS FULL   | CP_ARCH_SCHOOL        |   713 | 47058 |       |    38   (0)| 00:00:01 |
                |*  4 |    HASH JOIN           |                       |   195K|    44M|    19M| 81459   (5)| 00:16:18 |
                |*  5 |     HASH JOIN          |                       |   119K|    17M|  8848K| 65358   (6)| 00:13:05 |
                |*  6 |      HASH JOIN         |                       |   119K|  7444K|       | 58730   (6)| 00:11:45 |
                |   7 |       TABLE ACCESS FULL| ARR_CHECK             | 56503 |   717K|       |    33   (7)| 00:00:01 |
                |*  8 |       TABLE ACCESS FULL| RM_REV_KNOCK_OFF_COLL |  1084K|    52M|       | 58668   (6)| 00:11:45 |
                |   9 |      TABLE ACCESS FULL | RM_COM_STUD           |   740K|    66M|       |  2439   (5)| 00:00:30 |
                |  10 |     TABLE ACCESS FULL  | CP_STUD_PROFILE       |  1216K|    90M|       |  9891   (2)| 00:01:59 |
                --------------------------------------------------------------------------------------------------------
                
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                   2 - access("C"."SCHOOL_CODE"="D"."SCHOOL_CODE")
                   4 - access("B"."CP_STUDENT_ID"="C"."STUDENT_ID")
                   5 - access("A"."STUDENT_ID"="B"."STUDENT_ID")
                   6 - access("A"."STUDENT_ID"="E"."STUDENT_ID")
                   8 - filter("A"."FULL_PAYMENT_IND"='N')
                
                Note
                -----
                   - dynamic sampling used for this statement
                Where is the problem? If you work your way through the SQL statement from top to bottom, you will find the following tables listed after a FROM clause:
                CP_CODE_PROFILE cd
                rm_com_cde
                rm_arr_ntf_final_rem
                rm_arr_ntf_rpt
                rm_arr_ntf_rpt
                rm_arr_ntf_final_rem x3
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                rm_bil_month
                cp_stud_dropout w
                cp_stud_profile w2
                rm_com_stud_profl_hist
                rm_bil_month
                rm_sub_grant t
                rm_sub_scheme u
                rm_bil_month
                rm_sub_grant t
                rm_sub_scheme u
                rm_bil_month
                rm_sub_scholarship_award u
                rm_bil_month
                rm_bil_month
                rm_sub_scholarship_status
                rm_bil_month
                rm_sub_scholarship_status u6
                rm_arr_instl w
                RM_ARR_INSTL_DTL w1
                rm_bil_month
                rm_edu_so w
                rm_edu_acct w
                rm_rev_knock_off_coll a
                rm_com_stud b
                cp_stud_profile c
                cp_arch_school d 
                arr_check e
                The execution plan is missing about 24 references of the table rm_bil_month, as well as a number of other tables. I tried to "unwind" the SQL statement. However, I simply could not finish due to the number of nested SELECT statements. Take a look:
                INSERT INTO ARREARS_CHECK_2
                SELECT
                  a.student_id,
                  c.uin_fin_no,
                  c.student_name,
                  d.type_code,
                  d.school_code, d.school_name, c.level_xcode,
                  (SELECT
                    cd.code_full_desc 
                  FROM
                    cp_code_profile cd 
                  WHERE
                    cd.category_name = 'CITIZENSHIP' 
                    AND cd.code_value = b.student_citizenship) ctz, 
                  (SELECT
                    cd.code_full_desc
                  FROM
                    CP_CODE_PROFILE cd
                  WHERE
                    cd.category_name = 'RACE'
                    AND cd.code_value = b.STUDENT_RACE) STUDENT_RACE,
                  (CASE 
                    WHEN ( c.academic_year = to_char(sysdate,'yyyy') 
                      AND c.student_status_icode = 'A' 
                      AND d.type_code IN ('A', 'G') ) 
                    THEN 'Active' 
                    ELSE 'Not Active' 
                   END ) schooling_status, 
                  (SELECT
                    code_desc 
                  FROM
                    rm_com_cde 
                  WHERE
                    code_type = 'ARR_NTF_STATUS' 
                    AND code_value = (NVL(
                      (SELECT
                        NVL(
                          (SELECT distinct
                            '7' 
                          FROM
                            rm_arr_ntf_final_rem x3 
                          WHERE
                            x3.student_id = a.student_id 
                            AND x3.ntf_date > x.notification_date), 
                          x.ntf_type 
                          ) arr 
                      FROM
                        rm_arr_ntf_rpt x 
                      WHERE
                        x.student_id = a.student_id 
                        AND x.ntf_rpt_rec_id = 
                          (SELECT
                            MAX (y.ntf_rpt_rec_id) 
                          FROM
                            rm_arr_ntf_rpt y 
                          WHERE
                            y.student_id = a.student_id)), 
                  (SELECT distinct
                    '7' 
                  FROM
                    rm_arr_ntf_final_rem x3 
                  WHERE
                    x3.student_id = a.student_id) 
                ) 
                )) arrears_status, 
                SUM 
                ((CASE 
                WHEN (a.bill_month (SELECT MAX (bill_month)FROM rm_bil_month)) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arrears_amount, 
                SUM 
                ((CASE 
                WHEN ( a.bill_month between (SELECT add_months(MAX (bill_month),-3) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-1) FROM rm_bil_month) 
                ) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arr_3_mth_below, 
                SUM 
                ((CASE 
                WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-6) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-4) FROM rm_bil_month)) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arr_3_6_mths, 
                SUM 
                ((CASE 
                WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-12) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-7) FROM rm_bil_month)) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arr_6_mth_1_yr, 
                SUM 
                ((CASE 
                WHEN ( a.bill_month between (SELECT add_months(MAX (bill_month),-24) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-13) FROM rm_bil_month)) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arr_1_2_yr, 
                SUM 
                ((CASE 
                WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-36) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-25) FROM rm_bil_month)) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arr_2_3_yr, 
                SUM 
                ((CASE 
                WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-48) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-37) FROM rm_bil_month)) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arr_3_4_yr, 
                SUM 
                ((CASE 
                WHEN (a.bill_month between (SELECT add_months(MAX (bill_month),-60) FROM rm_bil_month) and (SELECT add_months(MAX (bill_month),-49) FROM rm_bil_month)) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arr_4_5_yr, 
                SUM 
                ((CASE 
                WHEN (a.bill_month <= (SELECT add_months(MAX (bill_month),-61) FROM rm_bil_month) ) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) arr_above_5_yr, 
                SUM 
                ((CASE 
                WHEN (a.bill_month = (SELECT MAX (bill_month) FROM rm_bil_month) ) 
                THEN (net_charges - amount_collected) 
                ELSE 0 
                END 
                ) 
                ) curr_mth_net, 
                SUM (net_charges - amount_collected) total_os, 
                NVL ((SELECT TO_CHAR (MAX (w.dropout_date), 'yyyymmdd') 
                FROM cp_stud_dropout w, cp_stud_profile w2 
                WHERE w.student_id = c.student_id 
                AND w2.student_id = w.student_id 
                AND NOT ( w2.student_status_icode = 'A' 
                AND w2.academic_year = (to_char(sysdate,'yyyy')) 
                )), 
                'NA' 
                ) dropout_year, 
                NVL ((SELECT TO_CHAR (MAX (effective_start_date), 'yyyymmdd') 
                FROM rm_com_stud_profl_hist b1 
                WHERE b1.student_id = a.student_id 
                AND b1.item_type = 'SCH_ST' 
                AND b1.item_value = 'I'), 
                'NA' 
                ) inactive_date, 
                CASE 
                WHEN (b.student_citizenship = '10' AND b.student_race = '1' 
                ) 
                THEN 'SC(M)' 
                WHEN ( b.student_citizenship = '10' 
                AND b.student_race = '5' 
                AND b.student_religion = '2' 
                AND ( b.father_citizenship = '10' 
                OR b.mother_citizenship = '10' 
                ) 
                ) 
                THEN 'SC(IE)' 
                WHEN (b.student_citizenship = '10') 
                THEN 'SC' 
                WHEN (b.student_citizenship BETWEEN '20' AND '29') 
                THEN 'SPR' 
                ELSE 'FS' 
                END pupil_fee_profile, 
                (CASE 
                WHEN ( (select MAX (bill_month)+4 from rm_bil_month) BETWEEN b.pass_start_date AND b.pass_end_date) 
                THEN b.student_pass_type 
                ELSE 'NA' 
                END 
                ) pass_type, 
                NVL (TO_CHAR (b.midtier_granted_month, 'yyyymmdd'), 
                'NA' 
                ) mid_tier_grant_start_date, 
                NVL 
                ((SELECT u.scheme_name 
                FROM rm_sub_grant t, rm_sub_scheme u 
                WHERE t.scheme_id IN (1, 2) 
                AND t.scheme_id = u.scheme_id 
                AND (SELECT MAX (bill_month)FROM rm_bil_month) 
                BETWEEN trunc(t.grant_start_month) 
                AND NVL (trunc(t.rvcn_start_month), trunc(t.grant_end_month)) 
                AND t.student_id = a.student_id), 
                'NA' 
                ) moe_fas, 
                NVL 
                ((SELECT u.scheme_name 
                FROM rm_sub_grant t, rm_sub_scheme u 
                WHERE t.scheme_id NOT IN (1, 2) 
                AND t.scheme_id = u.scheme_id 
                AND (SELECT MAX (bill_month)FROM rm_bil_month) 
                BETWEEN trunc(t.grant_start_month) 
                AND NVL (trunc(t.rvcn_start_month), trunc(t.grant_end_month)) 
                AND t.student_id = a.student_id), 
                'NA' 
                ) sac_smc_fas, 
                NVL 
                ((SELECT DISTINCT 'Y' 
                FROM rm_sub_scholarship_award u 
                WHERE a.student_id = u.student_id 
                AND (select MAX (bill_month)+4 from rm_bil_month) 
                BETWEEN u.award_start_date 
                AND u.award_end_date 
                AND (select MAX (bill_month)+4 from rm_bil_month) > 
                (SELECT MAX (u6.effective_date) 
                FROM rm_sub_scholarship_status u6 
                WHERE u6.award_id = u.award_id 
                AND u6.status_code = 'AC') 
                AND (select MAX (bill_month)+4 from rm_bil_month) < 
                NVL ((SELECT MAX (u6.effective_date) 
                FROM rm_sub_scholarship_status u6 
                WHERE u6.award_id = u.award_id 
                AND u6.status_code <> 'AC'), 
                SYSDATE + 365 
                )), 
                'N' 
                ) scholarship, 
                NVL ((SELECT 'Y' 
                FROM rm_arr_instl w, RM_ARR_INSTL_DTL w1 
                WHERE w.student_id = a.student_id and w.status = '2' and w.INSTALMENT_ID=w1.INSTALMENT_ID 
                and w1.DUE_DATE > (SELECT MAX (bill_month)FROM rm_bil_month) group by w.student_id), 
                'N' 
                ) instalment_plan, 
                NVL ((SELECT 'Y' 
                FROM rm_edu_so w 
                WHERE w.student_id = a.student_id AND w.status_code = '1'), 
                'N' 
                ) edusave_so, 
                NVL ((SELECT w.balance_amount 
                FROM rm_edu_acct w 
                WHERE w.student_id = a.student_id), 
                0) edusave_account_balance 
                FROM rm_rev_knock_off_coll a, 
                rm_com_stud b, 
                cp_stud_profile c, 
                cp_arch_school d, 
                arr_check e 
                WHERE a.student_id = b.student_id 
                AND a.student_id = e.student_id 
                AND c.school_code = d.school_code 
                AND b.cp_student_id = c.student_id 
                AND a.full_payment_ind = 'N' 
                GROUP BY a.student_id, 
                c.student_id, 
                c.uin_fin_no, 
                c.student_name, 
                d.type_code, 
                d.school_code, 
                d.school_name, 
                c.level_xcode, 
                b.father_citizenship, 
                b.mother_citizenship, 
                b.student_citizenship, 
                b.STUDENT_RACE,
                b.student_race, 
                b.student_religion, 
                b.pass_start_date, 
                b.pass_end_date, 
                b.student_pass_type, 
                b.midtier_granted_month, 
                (CASE 
                WHEN ( c.academic_year = to_char(sysdate,'yyyy') 
                AND c.student_status_icode = 'A' 
                AND d.type_code IN ('A', 'G') 
                ) 
                THEN 'Active' 
                ELSE 'Not Active' 
                END 
                );
                In general, it is a bad idea from a performance point of view to have a SELECT statement in a column position. Syntax such as *"SELECT distinct '7' "* seems to be a bit odd. Rather than referencing the rm_bil_month table at least 24 times, would not it make more sense to reference it once or pass in the value as a bind variable?

                Good luck.

                Charles Hooper
                Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
                http://hoopercharles.wordpress.com/
                IT Manager/Oracle DBA
                K&M Machine-Fabricating, Inc.