I have a View(web.individual_vw) using which another view(web.INDIVIDUAL_USAGE_VW) is created.
I am trying to optimize view (web.INDIVIDUAL_USAGE_VW)
CREATE OR REPLACE VIEW web.INDIVIDUAL_USAGE_VW
AS
WITH xx_usage AS (
SELECT * FROM app_log.hist_usage WHERE hit_date BETWEEN SYSDATE - INTERVAL '12' MONTH AND SYSDATE
UNION
SELECT * FROM web.usage WHERE hit_date BETWEEN SYSDATE - INTERVAL '12' MONTH AND SYSDATE
), xx_usage_sums AS (
SELECT DISTINCT
v.person_id,
SUM(CASE WHEN t.tax_id=858 THEN 1 ELSE 0 END) AS Business_economy,
SUM(CASE WHEN t.tax_id=859 THEN 1 ELSE 0 END) AS Executive_rewards,
SUM(CASE WHEN t.tax_id=860 THEN 1 ELSE 0 END) AS Health_benefits,
SUM(CASE WHEN t.tax_id=862 THEN 1 ELSE 0 END) AS Investment,
SUM(CASE WHEN t.tax_id=863 THEN 1 ELSE 0 END) AS Corporate_marketing,
SUM(CASE WHEN t.tax_id=866 THEN 1 ELSE 0 END) AS Retirement,
SUM(CASE WHEN t.tax_id=1747 THEN 1 ELSE 0 END) AS In_General,
SUM(CASE WHEN t.tax_id=1520 THEN 1 ELSE 0 END) AS Mergers_acquisitions,
SUM(CASE WHEN t.tax_id=1214 THEN 1 ELSE 0 END) AS Corporate_strategy_operations,
SUM(CASE WHEN t.tax_id=1215 THEN 1 ELSE 0 END) AS Broad_based_rewards,
SUM(CASE WHEN t.tax_id=1216 THEN 1 ELSE 0 END) AS Leadership,
SUM(CASE WHEN t.tax_id=1217 THEN 1 ELSE 0 END) AS Talent,
SUM(CASE WHEN t.tax_id=1218 THEN 1 ELSE 0 END) AS Other,
SUM(CASE WHEN t.tax_id=1842 THEN 1 ELSE 0 END) AS Health_Safety_Environment,
SUM(CASE WHEN t.tax_id=2120 THEN 1 ELSE 0 END) AS Employment_law
FROM web.individual_vw v
LEFT JOIN xx_usage u ON u.person_id = v.person_id
JOIN web.tax bt ON bt.blurb_id = u.blurb_id
JOIN web.taxonomy t ON t.tax_id = bt.tax_id
WHERE t.tax_type = 'Q'
GROUP BY v.person_id
)
SELECT DISTINCT
v."Contact Name",
v.contact_email AS "Contact Email",
v.Person_id AS "Person ID",
v."Company Name",
COALESCE(x.Business_economy,0) AS "Business & economy",
COALESCE(x.Executive_rewards,0) as "Executive rewards",
COALESCE(x.Health_benefits,0) as "Health & benefits",
COALESCE(x.Investment,0) as "Investment",
COALESCE(x.Corporate_marketing,0) as "Corporate marketing",
COALESCE(x.Retirement,0) as "Retirement",
COALESCE(x.In_General,0) as "In General",
COALESCE(x.Mergers_acquisitions,0) as "Mergers & acquisitions",
COALESCE(x.Corporate_strategy_operations,0) as "Corporate strategy operations",
COALESCE(x.Broad_based_rewards,0) as "Broad-based rewards",
COALESCE(x.Leadership,0) as "Leadership",
COALESCE(x.Talent,0) as "Talent",
COALESCE(x.Other,0) as "Other",
v."Global Basic",
v."Global Insights Customized",
v."Global Insights Complete",
v."GOT Complete",
v."GOT Customized",
v."CA Basic",
v."UK RE Legislative",
v."UK RE Full",
v."US Basic",
v."US Premium ER",
v."US Premium HB",
v."US Premium RE",
v."SSM",
v."HB KnowHow",
v."UK Basic"
FROM web.individual_vw v
LEFT JOIN xx_usage_sums x ON x.person_id = v.person_id
WHERE NOT (LOWER(v."Global Basic") = 'no' AND LOWER(v."Global Insights Customized") = 'no' AND
LOWER(v."Global Insights Complete") = 'no' AND LOWER(v."GOT Complete") = 'no' AND
LOWER(v."GOT Customized") = 'no' AND LOWER(v."CA Basic") = 'no' AND LOWER(v."UK RE Legislative") = 'no' AND
LOWER(v."UK RE Full") = 'no' AND LOWER(v."US Basic") = 'no' AND LOWER(v."US Premium ER") = 'no' AND
LOWER(v."US Premium HB") = 'no' AND LOWER(v."US Premium RE") = 'no' AND LOWER(v."SSM") = 'no' AND
LOWER(v."HB KnowHow") = 'no' AND LOWER(v."UK Basic") = 'no')
ORDER BY "Company Name";
And the reference View (web.individual_vw) is:
CREATE OR REPLACE VIEW web.individual_vw
AS
WITH xx_persons_of_interest AS (
SELECT
p.person_id,
s.product_id,
wip.product_name,
c.company_name,
p.last_name || ', ' || p.first_name AS contact_name,
p.email AS contact_email
FROM web.person p
JOIN web.company c ON c.company_id = p.company_id
JOIN web.subscription s ON p.person_id = s.person_id
JOIN web.product wip ON s.product_id = wip.product_id
WHERE COALESCE(s.stop_date, TRUNC(SYSDATE)) >= TRUNC(SYSDATE,'YYYY')
AND COALESCE(s.billing_end_date, TRUNC(SYSDATE)) >= TRUNC(SYSDATE)
AND p.person_id IN
(
SELECT p1.person_id FROM web.person p1 INNER JOIN web.company c1 ON (c1.company_id = p1.company_id)
WHERE c1.company_id NOT IN (503,2817,3007,4377,18224,22442,29515,28989,30198)
UNION
SELECT p2.person_id FROM web.person p2 WHERE p2.person_id IN (11962,57308,78370,78038)
)
), xx_subs AS (
SELECT
person_id,
MAX(CASE WHEN product_id=22 THEN 'Yes' ELSE 'No' END) AS "US Basic",
MAX(CASE WHEN product_id=24 THEN 'Yes' ELSE 'No' END) AS "UK RE Full",
MAX(CASE WHEN product_id=38 THEN 'Yes' ELSE 'No' END) AS "US Premium HB",
MAX(CASE WHEN product_id=82 THEN 'Yes' ELSE 'No' END) AS "UK RE Legislative",
MAX(CASE WHEN product_id=94 THEN 'Yes' ELSE 'No' END) AS "US Premium ER",
MAX(CASE WHEN product_id=98 THEN 'Yes' ELSE 'No' END) AS "US Premium RE",
MAX(CASE WHEN product_id=122 THEN 'Yes' ELSE 'No' END) AS "SSM",
MAX(CASE WHEN product_id=126 THEN 'Yes' ELSE 'No' END) AS "HB KnowHow",
MAX(CASE WHEN product_id=140 THEN 'Yes' ELSE 'No' END) AS "Global Insights Complete",
MAX(CASE WHEN product_id=182 THEN 'Yes' ELSE 'No' END) AS "Global Insights Customized",
MAX(CASE WHEN product_id=202 THEN 'Yes' ELSE 'No' END) AS "Global Basic",
MAX(CASE WHEN product_id=220 THEN 'Yes' ELSE 'No' END) AS "CA Basic",
MAX(CASE WHEN product_id=276 THEN 'Yes' ELSE 'No' END) AS "UK Basic",
MAX(CASE WHEN product_id=277 THEN 'Yes' ELSE 'No' END) AS "GOT Complete",
MAX(CASE WHEN product_id != 277 AND LOWER(product_name) LIKE '%zox%' THEN 'Yes' ELSE 'No' END) AS "GOT Customized"
FROM xx_persons_of_interest
GROUP BY person_id
)
SELECT DISTINCT
x1.contact_name AS "Contact Name",
x1.company_name AS "Company Name",
x1.person_id,
x1.contact_email,
x2."US Basic",
x2."UK RE Full",
x2."US Premium HB",
x2."UK RE Legislative",
x2."US Premium ER",
x2."US Premium RE",
x2."SSM",
x2."HB KnowHow",
x2."Global Insights Complete",
x2."Global Insights Customized",
x2."Global Basic",
x2."CA Basic",
x2."UK Basic",
x2."GOT Complete",
x2."GOT Customized"
FROM xx_persons_of_interest x1 JOIN xx_subs x2 ON x2.person_id = x1.person_id;
Now while reading the Oracle documentation for tuning,creating index is the best way to optimize any query. But on View, index can't be created.
Next option I got is to create a Materialized View, but my requirement is not to create any MV.
