# Query for building a view

I am currently trying to improve on a query that is being used to build a view. The query is in PL/SQL, in an Oracle database. There are 3 different types of reports (100,200, and 300) that are generated at each building. We track the consecutive years that each report is generated, and based on the combination of (1) the type(s) of report(s) generated for a given year and (2) the consecutive years each report has been generated, we arrive at a Result type for that building.

Here is a description of the criteria for the Result types:

Result 600 - If all 3 report types have been generated in the current year, where: Level 1: all reports were generated in 1 consecutive year (this is the first year) Level 2: at least 1 report type has been generated for 2 consecutive years (none have 3 consecutive years) Level 3: at least one report type has been generated for 3 consecutive years

Result 100 - Only report type 100 has been generated in the current year, where: Level 1 - 1 consecutive year Level 2 - 2 consecutive years Level 3 - 3 consecutive years

Result 200 - Only report type 200 has been generated in the current year, where: Level 1 - 1 consecutive year Level 2 - 2 consecutive years Level 3 - 3 consecutive years

Result 300 - Only report type 300 has been generated in the current year, where: Level 1 - 1 consecutive year Level 2 - 2 consecutive years Level 3 - 3 consecutive years

Result 400 - Only reports 100 and 200 have been generated, where: Level 1: both reports were generated in 1 consecutive year (this is the first year) Level 2: at least 1 report type has been generated for 2 consecutive years (neither have 3 consecutive years) Level 3: at least one report type has been generated for 3 consecutive years

Result 500 - Only reports 100 and 300 have been generated, where: Level 1: both reports were generated in 1 consecutive year (this is the first year) Level 2: at least 1 report type has been generated for 2 consecutive years (neither have 3 consecutive years) Level 3: at least one report type has been generated for 3 consecutive years

Result 700 - Only reports 200 and 300 have been generated, where: Level 1: both reports were generated in 1 consecutive year (this is the first year) Level 2: at least 1 report type has been generated for 2 consecutive years (neither have 3 consecutive years) Level 3: at least one report type has been generated for 3 consecutive years

Here is the current code that is used to generate this view, which is simply a display of the result:

CREATE OR REPLACE FORCE VIEW REPORTS.REPORT_RESULT_VIEW
(
BUILDING,
BUILDING_NAME,
GROUP,
YEAR,
TYPE,
SUBTYPE,
CONSEC,
RESULT
)
AS
WITH cte1
AS (SELECT 1 ID_100,
1 ID_200,
1 ID_300,
'600 Level 1' RESULT
FROM DUAL
UNION ALL
SELECT 2 ID_100,
2 ID_200,
2 ID_300,
'600 Level 2' RESULT
FROM DUAL
UNION ALL
SELECT 3 ID_100,
3 ID_200,
3 ID_300,
'600 Level 3' RESULT
FROM DUAL
UNION ALL
SELECT 1 ID_100,
1 ID_200,
2 ID_300,
'600 Level 2' RESULT
FROM DUAL),


(note - there are 63 total combinations that are listed in the actual code... I only entered the first few to give you an idea of how it is set up)

 cte2
AS (  SELECT MAX (ID_100) ID_100_CONSEC,
MAX (ID_200) ID_200_CONSEC,
MAX (ID_300) ID_300_CONSEC,
YEAR,
BUILDING
FROM (SELECT CONSEC ID_100,
NULL ID_200,
NULL ID_300,
YEAR,
TYPE || SUBTYPE TYPE,
BUILDING
FROM REPORT_MASTER_VIEW
WHERE TYPE || SUBTYPE = '100'
UNION
SELECT NULL ID_100,
CONSEC ID_200,
NULL ID_300,
YEAR,
TYPE || SUBTYPE TYPE,
BUILDING
FROM REPORT_MASTER_VIEW
WHERE TYPE || SUBTYPE = '200'
UNION
SELECT NULL ID_100,
NULL ID_200,
CONSEC ID_300,
YEAR,
TYPE || SUBTYPE TYPE,
BUILDING
FROM REPORT_MASTER_VIEW
WHERE TYPE || SUBTYPE = '300')
GROUP BY YEAR, BUILDING),
cte3
AS (SELECT c2.*, c1.RESULT
FROM    cte2 c2
JOIN
cte1 c1
ON     NVL (c2.ID_100_CONSEC, 0) = c1.ID_100
AND NVL (c2.ID_200_CONSEC, 0) = c1.ID_200
AND NVL (c2.ID_300_CONSEC, 0) = c1.ID_300)
SELECT t1."BUILDING",
t1."BUILDING_NAME",
t1."GROUP",
t1."YEAR",
t1."TYPE",
t1."SUBTYPE",
t1."CONSEC",
t2.RESULT
FROM    REPORT_MASTER_VIEW t1
JOIN
cte3 t2
ON t1.BUILDING  = t2.BUILDING AND t1.YEAR = t2.YEAR
WHERE T1.TYPE IN ('100', '200' '300')
ORDER BY t1.BUILDING;


Now, because for every report combination, it has to run through all the possible combinations, this view takes about 24 seconds to build. In the app that it is referenced in, it takes nearly a minute to load the page. For this reason, I am trying to figure out ways to make the query more efficient. At first, I was thinking of using nested CASE statements, but I wasn't really sure how that would work.

cte1, besides needing a better name, needs to just be a fully-fledged table. With 63 combinations, it takes a while to process how to get all these in correctly. Putting this in a view doesn't speed up that CTE at all. So, just create a permanent table in the database preloaded with these values.

That should cut a massive chunk out of your query time.

• So, then a process would have to be put in place to keep the table in sync with the rest of the data. Is the performance gain worth the increased complexity? Aug 12 '14 at 3:10
• I don't understand what has to be kept in sync? Am I looking at cte1 wrong? It looks like it's just a set of constant values, right? But to your question "Is the performance gain worth the increased complexity?" In this specific case since I'm certain that this is a huge chunk of the really long query time, yes. In SQL in general, if the query runs slow enough that it is noticeable to the user (anything over 1 second), any performance game that will make a noticeable difference to the end user is usually worth it. Aug 12 '14 at 11:24
• You're right. I should have looked closer. Everything is being selected from DUAL. It should be a table. Aug 12 '14 at 11:27

I'll compliment in saying that formatting, capitalization, etc. are consistent.

Nitpicking

cte1 and cte2 are not very good names. Aliases should reflect what they actually represent.

# UNION

To me the gorilla in the room is that UNION is used 64 times to explicitly spell out every possible scenario instead of using logic. Let me illustrate.

PS: Note I'm using PL/pgSQL (a.k.a. PostgreSQL) so the syntax might need a few adjustments. I don't have access to an Oracle machine.

-- making cte1
drop table if exists numbers;
create temporary table numbers(
counter serial,
n int
);
insert all
into numbers (n) values (null), -- no report of any type
into numbers (n) values (1),
into numbers (n) values (2),
into numbers (n) values (3)
;
drop table if exists NumbersWithResults;
create table NumbersWithResults as
select
ID_100.n as ID_100,
ID_200.n as ID_200,
ID_300.n as ID_300
from numbers as ID_100
cross join numbers as ID_200
cross join numbers as ID_300
;
-- Query returned successfully: 64 rows affected, 17 ms execution time.
-- if you need to check results, uncomment this:
-- select * from NumbersWithResults
-- order by ID_100, ID_200, ID_300;


As you can see, execution is fast, and you just now need to work in a few logical rules to add column and update values based on a set of conditions... for example:

alter table NumbersWithResults
;
-- examples only, add to or change as needed
update NumbersWithResults
set RESULT = '600 Level 1'
where   ID_100 = ID_200 and ID_200 = ID_300,
set RESULT = '600 Level 2'
where   COUNT(ID_100, ID_200, ID_300) = 2,
set RESULT = '600 Level 3'
where   COUNT(ID_100, ID_200, ID_300) = 3
-- etc.


Hope this helps.

PS: Like @nhgriff said, this should be a permanent table, not a CTE, if this is getting called regularly. Also consider making a stored procedure/function.

PPS: Some of my conditions at the end may be wrong... make sure you test.

• Oracle doesn't support multiple rows in VALUES, the way PostgreSQL does. Aug 12 '14 at 2:02
• I changed the insert code to factor this in, thanks @200_success. Aug 12 '14 at 4:30