patternsqlMinor
Query for building a view
Viewed 0 times
queryforviewbuilding
Problem
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 yea
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 yea
Solution
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.
Context
StackExchange Code Review Q#27640, answer score: 6
Revisions (0)
No revisions yet.