7 Replies Latest reply: Mar 19, 2014 7:01 AM by Karthick_Arp RSS

    Creating Index on the view

    Saro

      Hi friends,

       

      Is it possible to create index on the view. Suppose if i try to execute a simple select statement on my view like below means


      select distinct centre_name from d_qualification_syl_comp_dim

       

      It is taking more than one hour to execute the stmt. It has more than 3 millions of records in it and because of that it is very slow to execute, so i thought of creating index on that view for the better performance. Is it possible to create an index on that view. This is my below view codings.

       

      CREATE OR REPLACE FORCE VIEW BEC_DW_NEW.D_QUALIFICATION_SYL_COMP_DIM

      (

         QUALIFICATION_NAME,

         QUALIFICATION_SHORTNAME,

         QUALIFICATION_CODE,

         PK_QUALIFICATION_ID,

         PK_SYLLABUS_ID,

         SYLLABUS_CODE,

         SYLLABUS_NAME,

         SYLLABUS_SHORTNAME,

         PK_SERIES_ID,

         SERIES_DESCRIPTION,

         SERIES_YEAR,

         SERIES_MONTH,

         FK_CANDIDATE_MST_ID,

         PK_CENTRE_ID,

         CENTRE_NAME,

         QUALIFICATION_GRADE,

         CANDIDATE_NO,

         SYLLABUS_GRADE,

         SYLLABUS_SCORE,

         CENTRE_NUMBER,

         REGION_NAME,

         DISTRICT_NAME,

         PLACE_NAME,

         DIMENSION_TYPE_NAME,

         PK_DIMENSION_TYPE_ID

      )

      AS

         SELECT   DISTINCT a.qualification_name,

                           a.qualification_shortname,

                           a.qualification_code,

                           a.pk_qualification_id,

                           b.pk_syllabus_id,

                           b.syllabus_code,

                           b.syllabus_name,

                           b.syllabus_shortname,

                           d.pk_series_id,

                           d.series_description,

                           d.series_year,

                           d.series_month,

                           e.fk_candidate_mst_id,

                           f.pk_centre_id,

                           f.centre_name,

                           g.grade_name Qualification_Grade,

                           e.candidate_no,

                           i.grade_name syllabus_grade,

                           e.candidate_overall_grade_score Syllabus_Score,

                           j.m_cent_qfl_no Centre_Number,

                           k.region_name,

                           k.district_name,

                           k.place_name,

                           l.dimension_type_name,

                           l.pk_dimension_type_id

           FROM   d_qualification a,

                  d_syllabs b,

                  d_series d,

                  d_registry_candidate_series e,

                  d_centre f,

                  d_grade g,

                  d_reg_cand_syllabus h,

                  d_grade i,

                  malepa_staging.m_mst_cent_qfl j,

                  d_geography k,

                  d_dimension_type l

          WHERE       b.fk_qualification_id = a.pk_qualification_id

                  AND d.fk_qualification_id = a.pk_qualification_id

                  AND a.pk_qualification_id = e.fk_qualification_id

                  AND a.pk_qualification_id = g.fk_qualification_id

                  AND a.pk_qualification_id = i.fk_qualification_id

                  AND d.pk_series_id = e.fk_series_id

                  AND e.fk_centre_id = f.pk_centre_id

                  AND e.fk_grade_id = g.pk_grade_id

                  AND f.fk_region_id = k.pk_region_id

                  AND f.fk_district_id = k.pk_district_id

                  AND f.fk_place_id = k.pk_place_id

                  AND a.qualification_shortname IN ('JCE', 'PSLE')

                  AND h.fk_candidate_mst_id = e.fk_candidate_mst_id

                  AND h.fk_series_id = e.fk_series_id

                  AND h.fk_qualification_id = e.fk_qualification_id

                  AND h.fk_syllabus_id = b.pk_syllabus_id

                  AND h.fk_grade_id = i.pk_grade_id

                  AND f.pk_centre_id = j.m_fk_cent_id

                  AND l.fk_qualification_id = a.pk_qualification_id

         UNION ALL

         SELECT   a.qualification_name,

                  a.qualification_shortname,

                  a.qualification_code,

                  a.pk_qualification_id,

                  b.pk_syllabus_id,

                  b.syllabus_code,

                  b.syllabus_name,

                  b.syllabus_shortname,

                  d.pk_series_id,

                  d.series_description,

                  d.series_year,

                  d.series_month,

                  e.fk_candidate_mst_id,

                  f.pk_centre_id,

                  f.centre_name,

                  NULL Qualification_Grade,

                  e.candidate_no,

                  i.grade_name Syllabus_Grade,

                  e.candidate_overall_grade_score Syllabus_Score,

                  j.m_cent_qfl_no centre_Number,

                  k.region_name,

                  k.district_name,

                  k.place_name,

                  NULL dimension_type_name,

                  NULL pk_dimension_type_id

           FROM   d_qualification a,

                  d_syllabs b,

                  d_series d,

                  d_registry_candidate_series e,

                  d_centre f,

                  d_reg_cand_syllabus h,

                  d_grade i,

                  malepa_staging.m_mst_cent_qfl j,

                  d_geography k

          WHERE       b.fk_qualification_id = a.pk_qualification_id

                  AND d.fk_qualification_id = a.pk_qualification_id

                  AND a.pk_qualification_id = e.fk_qualification_id

                  AND d.pk_series_id = e.fk_series_id

                  AND e.fk_centre_id = f.pk_centre_id

                  AND f.fk_region_id = k.pk_region_id

                  AND f.fk_district_id = k.pk_district_id

                  AND f.fk_place_id = k.pk_place_id

                  AND a.qualification_shortname IN ('BGCSE')

                  AND h.fk_candidate_mst_id = e.fk_candidate_mst_id

                  AND h.fk_series_id = e.fk_series_id

                  AND h.fk_syllabus_id = b.pk_syllabus_id

                  AND h.fk_qualification_id = e.fk_qualification_id

                  AND h.fk_grade_id = i.pk_grade_id

                  AND f.pk_centre_id = j.m_fk_cent_id;

       

      The above view has poor performance during execution, is it possible to create index on the above view for the better performance.

       

      Thanks in advance.

       

      Regards,

      Saro

        • 1. Re: Creating Index on the view
          Nag Aswadhati

          A view is a stored SQL statement, so you can not create an index on a view.

          create the index on the base table and check the performace of the view.

          • 2. Re: Creating Index on the view
            Moazzam

            You cannot create index on view.

            Index on View

             

            Try to create indexes on the underlying tables in the view to improve the performance of view.

            • 3. Re: Creating Index on the view
              Paul  Horth

              You can't create indexes on a normal view.

               

              You create indexes on the underlying tables of the view.

               

              I would question whether you should be using that complex query for answering such a simple thing

              as the distinct centre_names - can you write a more specific (and efficient) query to do that?

              • 4. Re: Creating Index on the view
                Karthick_Arp

                View is just a stored SELECT statement. It don't have any data of its own. If you have a view like

                create or replace view emp_dept_view

                as

                select e.ename, d.dname

                  from emp e join dept d

                    on e.deptno = d.deptno;

                 

                and you execute

                select * from emp_dept_view

                 

                The SELECT statement of emp_dept_view is executed. So the query is rewitten like this.

                select *

                  from (

                          select e.ename, d.dname

                            from emp e join dept d

                              on e.deptno = d.deptno;

                       )

                 

                So you need to have INDEX created on the tables used by the SELECT statement in the view.

                 

                Now whenever you have a performance issue the first step would be to spend time on identifying the root cause. Don't jump into conclusions. How do you know creating index will improve performance?

                 

                Following link contains links to two posts. First talks about tools available to diagnose a performance issue and the second talks about the details that are essential for any one to tune your SQL. So please read it.

                 

                Re: 3. How to  improve the performance of my query? / My query is running slow.

                • 5. Re: Creating Index on the view
                  onkar.nath

                  Saro: as others suggested, it would make more sense to create index on base tables rather than creating indexes on view which you can not. If your concern is performance of the view then why not consider working on base query itself. Every reference of the view will run underlying SQL statement so try to concentrate on tuning the base query. Use explain plan, generate trace, go through them and check if you can tweak something or change something to make this query run faster (if you feel or reported to about the query performance).

                   

                  Onkar

                  • 6. Re: Creating Index on the view
                    Top Gun

                    You can create an index on a materialised view.

                    You need to decide if you want to replace the view with a materialised view, then index the materialised view.

                    • 7. Re: Creating Index on the view
                      Karthick_Arp

                      Top Gun wrote:

                       

                      You can create an index on a materialised view.

                      You need to decide if you want to replace the view with a materialised view, then index the materialised view.

                       

                      Materialized view exist to address very different problem. I don't thing that's a good idea.