Optimize Query

Bolwahnn · March 8, 2021, 11:40pm

Hey Guys,
Need some help on a query - I'm using the below script to get detailed data on a Daily, Weekly, Monthly basis. I'm using unions in the script to categorize the different time periods, so that when I use the dataset in PowerBI, I can add a slicer to select between Daily, Weekly, Monthly.

My question: Is there a better way to write this script without Unions? I believe this is inefficient as it needs to scan the table each time. A single query would be better, but I'm not sure how to write it so that is continues to provide what I'm looking for.......

--Here's the script - It's the same script 3 times but broken down to Daily, Weekly, Monthly.......
select
c.id AS Client_ID
, c.description AS Client_Name
, cl3.code AS Site_ID
, trunc(to_date(v.LAST_ACTION_DTIME),'ddd') AS LastActionDates
, trunc(to_date(v.vio_DTIME),'ddd') AS VioDates
, 'Daily' AS Time_Period
, cl.description AS Reject_reason
, q.queue_name AS Queue_Name
, vt.code AS Vio_type
, decode(cl.value,'Y', 'Controllable', 'N', 'Not Controllable', 'E', 'Non-Event', 'Other') AS Reject_Type
, v.last_action_user
, count()
from
violations v
, clients c
, category_lists cl
, categories cat
, category_lists cl2
, categories cat2
, category_lists cl3
, queues q
, violation_types vt
where
c.id = cl.client_id
and cl.client_id = cat.client_id
and cat.id = cl.category_id
and cl.client_id = v.client_id
and cl.id = v.last_action_id
and cl.value IN ('Y','N','E')
AND v.location_id = cl3.id
AND v.client_id = cl3.client_id
and v.in_use_flag = 'R'
AND v.in_use_flag != 'I'
and cl2.client_id = v.client_id
and cl2.id = v.location_id
and cl2.category_id = cat2.id
and cat2.code = 'LOCATION'
and cat2.client_id = cl2.client_id
and v.vio_type_id = vt.id
and v.LAST_ACTION_DTIME >= to_date('20200101000000', 'yyyymmddhh24miss')
and v.queue_id = q.id
and v.client_id = q.client_id
group by
c.id
, c.description
, cl3.code
, trunc(to_date(v.LAST_ACTION_DTIME),'ddd')
, trunc(to_date(v.vio_DTIME),'ddd')
, cl.description
, q.queue_name
, vt.code
, decode(cl.value,'Y', 'Controllable', 'N', 'Not Controllable', 'E', 'Non-Event', 'Other')
, v.last_action_user
UNION
select
c.id AS Client_ID
, c.description AS Client_Name
, cl3.code AS Site_ID
, trunc(to_date(v.LAST_ACTION_DTIME), 'IW') AS LastActionDates
, trunc(to_date(v.vio_DTIME),'IW') AS VioDates
, 'Weekly' AS Time_Period
, cl.description AS Reject_reason
, q.queue_name AS Queue_Name
, vt.code AS Vio_type
, decode(cl.value,'Y', 'Controllable', 'N', 'Not Controllable', 'E', 'Non-Event', 'Other') AS Reject_Type
, v.last_action_user
, count()
from
violations v
, clients c
, category_lists cl
, categories cat
, category_lists cl2
, categories cat2
, category_lists cl3
, queues q
, violation_types vt
where
c.id = cl.client_id
and cl.client_id = cat.client_id
and cat.id = cl.category_id
and cl.client_id = v.client_id
and cl.id = v.last_action_id
and cl.value IN ('Y','N','E')
AND v.location_id = cl3.id
AND v.client_id = cl3.client_id
and v.in_use_flag = 'R'
AND v.in_use_flag != 'I'
and cl2.client_id = v.client_id
and cl2.id = v.location_id
and cl2.category_id = cat2.id
and cat2.code = 'LOCATION'
and cat2.client_id = cl2.client_id
and v.vio_type_id = vt.id
and v.LAST_ACTION_DTIME >= to_date('20200101000000', 'yyyymmddhh24miss')
and v.queue_id = q.id
and v.client_id = q.client_id
group by
c.id
, c.description
, cl3.code
, trunc(to_date(v.LAST_ACTION_DTIME), 'IW')
, trunc(to_date(v.vio_DTIME),'IW')
, cl.description
, q.queue_name
, vt.code
, decode(cl.value,'Y', 'Controllable', 'N', 'Not Controllable', 'E', 'Non-Event', 'Other')
, v.last_action_user
UNION
select
c.id AS Client_ID
, c.description As Client_Name
, cl3.code AS Site_ID
, trunc(to_date(v.LAST_ACTION_DTIME), 'Month') AS LastActionDates
, trunc(to_date(v.vio_DTIME),'Month') AS VioDates
, 'Month' AS Time_Period
, cl.description AS Reject_reason
, q.queue_name AS Queue_Name
, vt.code AS Vio_type
, decode(cl.value,'Y', 'Controllable', 'N', 'Not Controllable', 'E', 'Non-Event', 'Other') AS Reject_Type
, v.last_action_user
, count(*)
from
violations v
, clients c
, category_lists cl
, categories cat
, category_lists cl2
, categories cat2
, category_lists cl3
, queues q
, violation_types vt
where
c.id = cl.client_id
and cl.client_id = cat.client_id
and cat.id = cl.category_id
and cl.client_id = v.client_id
and cl.id = v.last_action_id
and cl.value IN ('Y','N','E')
AND v.location_id = cl3.id
AND v.client_id = cl3.client_id
and v.in_use_flag = 'R'
AND v.in_use_flag != 'I'
and cl2.client_id = v.client_id
and cl2.id = v.location_id
and cl2.category_id = cat2.id
and cat2.code = 'LOCATION'
and cat2.client_id = cl2.client_id
and v.vio_type_id = vt.id
and v.LAST_ACTION_DTIME >= to_date('20200101000000', 'yyyymmddhh24miss')
and v.queue_id = q.id
and v.client_id = q.client_id
group by
c.id
, c.description
, cl3.code
, trunc(to_date(v.LAST_ACTION_DTIME), 'Month')
, trunc(to_date(v.vio_DTIME),'Month')
, cl.description
, q.queue_name
, vt.code
, decode(cl.value,'Y', 'Controllable', 'N', 'Not Controllable', 'E', 'Non-Event', 'Other')
, v.last_action_user

-- Thanks for your time!

robert_volk · March 8, 2021, 11:59pm

Sounds like GROUPING SETS is what you're looking for:

FYI, SQLTeam is primarily a Microsoft SQL Server site, we don't have as much Oracle experience.

Bolwahnn · March 10, 2021, 6:28pm

Thanks for the response @robert_volk , but I'm not sure this works for what I'm looking to do. This appears to provide the time periods in separate columns, but I'm looking to just have one column that states the time period (Daily, Weekly, Monthly) so that I can create a slicer in Power BI so that the user can choose what time period they want to see the data in.

Example (hope this screenshot works)

robert_volk · March 10, 2021, 8:31pm

Since your JOIN and WHERE conditions are identical, I'd expect Oracle would be able to reuse the results from an initial read from disk. Once data is in memory, at worst it would process it from RAM. SQL Server has spool operators that perform a similar task. The only reason it would spool to disk is if there's not enough RAM for the spooled results. Do you see multiple disk access in the EXPLAIN PLAN?
Is there any reason why you're not using PowerBI to do the summarization? It is designed for that kind of thing. Adding a date dimension, or just adding the Day/Week/Month values as 3 separate columns would eliminate the UNION and GROUP BY and you can do the aggregation entirely in PowerBI.

Bolwahnn · March 11, 2021, 2:59pm

Genius! I don't know why I didn't think of Power Query (w/in Power BI) to solve this problem. I'm just always trying to clean the data prior to importing. In this situation, its far more optimal to clean up the script (remove unions) and just make the changes in Power BI.

Thanks for the suggestion Robert!

yosiasz · March 11, 2021, 4:19pm

look at this video, might find this channel informative