Oracle query optimization

optimizationoracleoracle-11gperformancequery-performance

We have a query that runs on a view and its execution time is about 6 min.

SELECT COUNT(*) 
FROM someschema.item_total_violations 
WHERE channel_id = 122507833
AND item_date >= TIMESTAMP'2015-02-01 00:00:00';

Its not a materialized view so there is no index on the item_date.

The base query of the view is below:

SELECT i.id
    ,i.title
    ,i.body
    ,i.description
    ,i.item_date
    ,i.created_date
    ,i.channel_id
    ,i.community_id
    ,i.person_id
    ,i.message_type
    ,ch.client_id
    ,upper(p.NAME) AS person_name_upper
    ,upper(ch.NAME) AS channel_name_upper
    ,p.NAME AS person_name
    ,ch.NAME AS channel_name
    ,ch.connector_type AS channel_type
    ,cm.NAME AS community_name
    ,cm.network_id
    ,nvl(ct2.cnt, 0) + nvl(ct4.cnt, 0) AS total_violations_count
    ,nvl(ct1.cnt, 0) + nvl(ct3.cnt, 0) AS quarantined_violations_count
    ,nvl(ct5.cnt, 0) AS quarantined_attributes_count
    ,nvl(lh_info.cnt_lh, 0) AS legal_holds_count
    ,nvl(lh_info.cnt_lh_w_no_date, 0) AS legal_holds_count_w_no_date
    ,nvl(lh_info.max_hold_until, to_date('1970-01-01', 'yyyy-mm-dd')) AS legal_hold_max_hold_until
FROM item i
    ,channel ch
    ,person p
    ,community cm
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(*) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY pv.item_id
        ) ct1
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(*) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
        GROUP BY pv.item_id
        ) ct2
    ,(
        SELECT /*+ NO_MERGE */ aa.item_id AS item_id
            ,COUNT(*) AS cnt
        FROM item_attachment aa
            ,policy_violation pv
        WHERE aa.id = pv.item_attachment_id
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY aa.item_id
        ) ct3
    ,(
        SELECT /*+ NO_MERGE */ aa.item_id AS item_id
            ,COUNT(*) AS cnt
        FROM item_attachment aa
            ,policy_violation pv
        WHERE aa.id = pv.item_attachment_id
            AND pv.item_id IS NULL
        GROUP BY aa.item_id
        ) ct4
    ,(
        SELECT /*+ NO_MERGE */ pv.item_id
            ,count(DISTINCT (pv.item_attribute_id)) AS cnt
        FROM policy_violation pv
        WHERE pv.item_id IS NOT NULL
            AND pv.item_attribute_id IS NOT NULL
            AND pv.quarantine_status = 'QUARANTINED'
        GROUP BY pv.item_id
        ) ct5
    ,(
        SELECT lh_rel.item_id AS item_id
            ,count(DISTINCT (lh.id)) AS cnt_lh
            ,max(lh.HOLD_UNTIL) AS max_hold_until
            ,sum(CASE 
                    WHEN lh.hold_until IS NULL
                        THEN 1
                    ELSE 0
                    END) AS cnt_lh_w_no_date
        FROM LEGAL_HOLD_RELATION lh_rel
            ,LEGAL_HOLD lh
        WHERE lh.id = lh_rel.legal_hold_id
            AND lh.STATUS = 'A'
        GROUP BY lh_rel.item_id
        ) lh_info
WHERE ch.STATUS = 'A'
    AND i.channel_id = ch.id
    AND i.person_id = p.id
    AND i.community_id = cm.id(+)
    AND i.id = ct1.item_id(+)
    AND i.id = ct2.item_id(+)
    AND i.id = ct3.item_id(+)
    AND i.id = ct4.item_id(+)
    AND i.id = ct5.item_id(+)
    AND i.id = lh_info.item_id(+)

The item table is the biggest and we have tried partitioning the table in chaannel_id but that didn't help our query. I understand that there is no index on item_date in original query and it has effect on performance. Apart from trying out materialized view, is there something that I can improve on the base query of the view?

Best Answer

Why do you use this view at all for this query?

The view collects violations and other stuff, but your query does not care about those at all, just the number of items. The view lists all items regardless of these because of the outer joins, so you basically perform a lot of unnecessary extra work to collect violations and other stuff (the NO_MERGE hint makes this even worse), then you throw it away and count the items based on a totally different criteria.

The query should be something like this:

select
  count(*) 
from
  item i
  join channel ch on (i.channel_id = ch.id)
  join person p on (i.person_id = p.id)
where
  ch.status = 'A'
  and i.channel_id = 122507833
  and i.item_date >= timestamp'2015-02-01 00:00:00';

Additionally, you don't need materialized views for indexing a column. Just create the index on the column(s) of the base table(s).

On top of the must-have indexes on primary and foreign keys, another possible candidate is item_date column depending on your data distribution.

Related Question