sqloracle-databasecreate-view

Optimise RANK based query in Create View

I have a view and i used 2 select cases in order to us ethe RANK function. Is there a way to optimise this and avoid the 2 select clauses and make it one.

The query is as below :

CREATE OR REPLACE VIEW "TEMP" (
   "P_LOCAL_POLICY_ID",
   "P_GIRDA_POLICY_ID",
   "C_OE_CONS_UNIT_TITLE",
   "C_ROW_NUMBER",
   "C_LOAD_TIMESTAMP",
   "ERROR_TEXT",
   "RECORD_2_DELETE",
   "ERROR_TIMESTAMP",
   "RANK_NUMBER"
) AS
   SELECT
       p_local_policy_id,
       p_girda_policy_id,
       c_oe_cons_unit_title,
       c_row_number,
       c_load_timestamp,
       err_error_text,
       err_record_2_delete,
       err_load_timestamp,
       RANK() OVER(PARTITION BY
           err_error_text
           ORDER BY
               ROWNUM
       )
   FROM
       (
           SELECT
               po.local_policy_id AS p_local_policy_id,
               po.girda_policy_id AS p_girda_policy_id,
               MAX(cl.oe_cons_unit_title) AS c_oe_cons_unit_title,
               SUM(cl.row_number) AS c_row_number,
               MAX(cl.load_timestamp) AS c_load_timestamp,
               error.error_text AS err_error_text,
               error.record_2_delete AS err_record_2_delete,
               error.load_timestamp AS err_load_timestamp
           FROM
              <condition>
           WHERE
                  <condition>
           GROUP BY
               po.local_policy_id,
               po.girda_policy_id,
               error.error_text,
               error.record_2_delete,
               error.load_timestamp
       );

Thanks, Chithra

Solution

  • You can combine two blocks of SELECT list as

    CREATE OR REPLACE VIEW TEMP AS
SELECT po.local_policy_id AS "P_LOCAL_POLICY_ID",
       po.girda_policy_id AS "P_GIRDA_POLICY_ID",
       MAX(cl.oe_cons_unit_title) AS "C_OE_CONS_UNIT_TITLE",
       SUM(cl.row_number) AS "C_ROW_NUMBER",
       MAX(cl.load_timestamp) AS "C_LOAD_TIMESTAMP",
       error.error_text AS "ERROR_TEXT",
       error.record_2_delete AS "RECORD_2_DELETE",
       error.load_timestamp AS "ERROR_TIMESTAMP",
       RANK() OVER( PARTITION BY err_error_text ORDER BY 1 ) AS "RANK_NUMBER"
  FROM <condition>
 WHERE <condition>
 GROUP BY po.local_policy_id, po.local_region,
          error.error_text, error.record_2_delete, error.load_timestamp