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