Forum Stats

  • 3,759,484 Users
  • 2,251,550 Discussions
  • 7,870,674 Comments

Discussions

Fine tuning Big Select Reporting Query

vijz
vijz Member Posts: 682
edited Apr 1, 2018 5:05PM in SQL & PL/SQL

Hi All,

I have a Big Select Reporting query which retrieves columnar values from two similar table Structures of Eg: Table A and Table B . ID column

select count(1) from Table A = ~ 145000000

select count(1) from Table B= ~ 4090000

It was originally Designed in such a way that there are no relational constraints between Table A and Table B.

We have this Big reporting query which runs Union All between these Two Tables Columns where from_date and To_date along with other parameters are given as inputs. And this produces a Excel report for Analysis .  This query is running longer time. 

To Start with I have used, /*+ parallel(auto) NO_INDEX */ hint since query needs to scan the entire tables.

Report query will be run daily, monthly, quarterly.

Are the hints fine enough or any other fine tuning suggestions would be of great help like materialised views etc..

John Thortonvijz
«1

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited Mar 30, 2018 9:47PM
    vijz wrote:Hi All,I have a Big Select Reporting query which retrieves columnar values from two similar table Structures of Eg: Table A and Table B . ID columnselect count(1) from Table A = ~ 145000000select count(1) from Table B= ~ 4090000It was originally Designed in such a way that there are no relational constraints between Table A and Table B. We have this Big reporting query which runs Union All between these Two Tables Columns where from_date and To_date along with other parameters are given as inputs. And this produces a Excel report for Analysis . This query is running longer time. To Start with I have used, /*+ parallel(auto) NO_INDEX */ hint since query needs to scan the entire tables.Report query will be run daily, monthly, quarterly.Are the hints fine enough or any other fine tuning suggestions would be of great help like materialised views etc..

    How do I ask a question on the forums?

    If the report requires Full Table Scan of two tables "joined" by UNION ALL, then more data in tables means more time to complete the report.

    Here is a hint read & follow URL below to learn how to make a decent post.

    How to  improve the performance of my query? / My query is running slow.

  • vijz
    vijz Member Posts: 682
    edited Mar 31, 2018 9:22AM

    Apologies, since table data is huge.. posting the report query. And do not have access to run explain plan. Autotrace output is larger as well.

    Key Note: Original Database design for some reason is highly de-normalised. and cannot able to change for my scenario atleast

    select /*+ parallel(auto) NO_INDEX */FlightCharges_id,FlightCharges_date,upper(trip_od_pair) as trip_od_pair,trip_od_pair_id,tag_type,exit_terminal,entry_terminal,exit_date,avi_only_count,mixed_count,speed,Flight_Idn_only_count,tag_read,FlightOrigin_state,FlightOrigin_number,FlightOrigin_state || ' ' || FlightOrigin_number as Flight_Idn,case  when systrans_sub_type is null then 'NONE'  else upper(systrans_sub_type)end as systrans_sub_type,systran_type,FlightCharges_rated_id,case  when Fli_class is null then ''  else Fli_classend as Fli_class,Fli,case  when occupancy is null then 'FlightCharges'  when occupancy in (200,300) then 'HOV'  else 'FlightCharges'end as occupancy,case  when is_posted = -1 then 'N/A'  else to_char(is_posted)end as is_posted,venue_FlightCharges,account_id,expected_FlightCharges,discounted_FlightCharges,occupancy,route,exc_attr,adjacentfrom((select exc_attr,FlightCharges_id as FlightCharges_id, FlightCharges_date as FlightCharges_date, tag_read, FlightOrigin_state as FlightOrigin_state, FlightOrigin_number as FlightOrigin_number,exit_terminal as exit_terminal, entry_terminal as entry_terminal, exit_date,avi_only_count as avi_only_count,Flight_Idn_only_count as Flight_Idn_only_count,mixed_count as mixed_count,speed,(select tag_type from FlightCharges_segment where FlightCharges.FlightCharges_id=FlightCharges_segment.FlightCharges_id and FlightCharges_segment.tag_read <> ' ' and FlightCharges.tag_read=FlightCharges_segment.tag_read and rownum=1) as tag_type,trip_od_pair_id as trip_od_pair_id,(select trip_od_pair_name from trip_od_pair top where FlightCharges.trip_od_pair_id = top.trip_od_pair_id) as trip_od_pair,dated_type_id as FlightCharges_rated_id, Fli_class as Fli,(select description from code_lookup cl where cl.code_group='FLI_CLASS' and cl.code_id=FlightCharges.Fli_class) as Fli_class,(select discount_plan_name from discount_plan where discount_plan_id=FlightCharges.systrans_sub_type) as systrans_sub_type,(select description from code_lookup where FlightCharges.vio_type=code_lookup.code_id and code_group='VIOL_RESL') as systran_type,venue_FlightCharges as venue_FlightCharges, expected_FlightCharges as expected_FlightCharges, discounted_FlightCharges asdiscounted_FlightCharges, account_id as account_id, occupancy as occupancy, is_posted as is_posted,case  when FlightCharges.route_id is null then 'UNKNOWN'  when FlightCharges.route_id =1000 then 'Domestic Route'  when FlightCharges.route_id=10000 then 'International'end as route,case  when FlightCharges.adj_id <> 0 then to_char(FlightCharges.adj_id)  when FlightCharges.adj_id = 0 or FlightCharges.adj_id is null then ''  else ''endas adjacentfrom FlightChargeswhere1>0and((is_closed=0)or((is_closed<>0) and(((select count(1) from violation where FlightCharges.FlightCharges_id=violation_id) = 0)or((select count(1) from violation where FlightCharges.FlightCharges_id=violation_idand FlightCharges.updated_on>=updated_on and is_closed<>0) > 0))))and FlightCharges_revenue_date between to_date('2016-03-30','yyyy-mm-dd')  and  to_date('2016-03-30','yyyy-mm-dd')and to_number(SUBSTR('07:00:00',1,2)||SUBSTR('07:00:00',4,2)||SUBSTR('07:00:00',7,2))<=to_number(to_char(FlightCharges_date, 'hh24miss'))and to_number(SUBSTR('08:00:00',1,2)||SUBSTR('08:00:00',4,2)||SUBSTR('08:00:00',7,2)||'.999999')>to_number(to_char(FlightCharges_date, 'hh24miss'))and (select count(*) from violation where FlightCharges.FlightCharges_id=violation_id) = 0)union all(select 0 as exc_attr,PayViolation_id as FlightCharges_id, FlightCharges_date as FlightCharges_date, tag_read, FlightOrigin_state as FlightOrigin_state, FlightOrigin_number as FlightOrigin_number,exit_terminal as exit_terminal, entry_terminal as entry_terminal,exit_date,avi_only_count as avi_only_count,Flight_Idn_only_count as Flight_Idn_only_count,mixed_count as mixed_count,speed,(select tag_type from FlightCharges_segment where PayViolation.PayViolation_id=FlightCharges_segment.FlightCharges_id and FlightCharges_segment.tag_read <> ' ' and PayViolation.tag_read=FlightCharges_segment.tag_read and rownum=1) as tag_type,trip_od_pair_id as trip_od_pair_id,(select trip_od_pair_name from trip_od_pair top where PayViolation.trip_od_pair_id = top.trip_od_pair_id) as trip_od_pair,dated_type_id as FlightCharges_rated_id,  Fli_class as Fli,(select description from code_lookup cl where cl.code_group='FLI_CLASS' and cl.code_id=PayViolation.Fli_class) as Fli_class,(select discount_plan_name from discount_plan where discount_plan_id=PayViolation.systrans_sub_type) as systrans_sub_type,(select description from code_lookup where PayViolation.vio_type=code_lookup.code_id and code_group='VIOL_RESL') as systran_type,venue_FlightCharges as venue_FlightCharges, expected_FlightCharges as expected_FlightCharges, discounted_FlightCharges as discounted_FlightCharges, account_id as account_id, occupancy as occupancy, -1 asis_posted,case  when PayViolation.route_id is null then 'UNKNOWN'  when PayViolation.route_id =1000 then 'Domestic Route'  when PayViolation.route_id=10000 then 'International Route'  end as route,case  when PayViolation.adj_id <> 0 then to_char(PayViolation.adj_id)  when PayViolation.adj_id = 0 or PayViolation.adj_id is null then ''  else ''endas adjacentfrom PayViolationwhere1>0and((is_closed=0)or((is_closed<>0) and((select count(*) from FlightCharges where FlightCharges.FlightCharges_id=PayViolation_id and FlightCharges.updated_on<updated_onand FlightCharges.is_closed<>0) > 0)))and FlightCharges_revenue_date between to_date('2016-03-30','yyyy-mm-dd')  and  to_date('2016-03-30','yyyy-mm-dd')and to_number(SUBSTR(  '07:00:00' ,1,2)||SUBSTR(  '07:00:00' ,4,2)||SUBSTR(  '07:00:00' ,7,2))<=to_number(to_char(FlightCharges_date, 'hh24miss'))and to_number(SUBSTR( '08:00:00',1,2)||SUBSTR( '08:00:00',4,2)||SUBSTR( '08:00:00',7,2)||'.999999')>to_number(to_char(FlightCharges_date, 'hh24miss'))))order byroute asc,adjacent asc,FlightCharges_date asc,FlightCharges_id asc

    Any suggestions to fine tune above query like Hints, Materialised views etc.., would be appreciated

  • Stew Ashton
    Stew Ashton Member Posts: 2,861 Gold Trophy
    edited Mar 31, 2018 5:35AM

    You have several scalar subqueries, so for every row you look at, Oracle has to do several little queries to decide whether to select it or not.

    One method for avoiding scalar subqueries is to write one subquery and to join that to the main query. Here is an example using the DEPT and EMP tables.

    First, a scalar subquery:

    select d.deptno, d.dname, d.loc,(select max(e.sal) from emp e where e.deptno = d.deptno) max_salfrom dept d;

    Now, a rewrite using the ANSI join syntax:

    with max_sals as (  select deptno, max(sal) max_sal  from emp  group by deptno)select d.deptno, d.dname, d.loc, e.max_salfrom dept d left join max_sals e on d.deptno = e.deptno;

    And the same solution using the Oracle join syntax:

    with max_sals as (  select deptno, max(sal) max_sal  from emp  group by deptno)select d.deptno, d.dname, d.loc, e.max_salfrom dept d, max_sals e where d.deptno = e.deptno(+);

    Best regards, Stew Ashton

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Mar 31, 2018 6:43AM
    And do not have access to run explain plan. Autotrace output is larger as well.

    Then find someone that does. Without the execution plan and knowing what's really going on then anything you do is just a stab in the dark. Once you know where the time is going then you can make informed decisions are very easily get to the bottom of how to make it faster.

    Some freebies based on only the SQL:

    and FlightCharges_revenue_date between to_date('2016-03-30','yyyy-mm-dd')  and  to_date('2016-03-30','yyyy-mm-dd')

    Looks like a decent driving filter. If only a few rows are returned for this filter then an index on this column might be a good idea.

    and to_number(SUBSTR('07:00:00',1,2)||SUBSTR('07:00:00',4,2)||SUBSTR('07:00:00',7,2))<=to_number(to_char(FlightCharges_date, 'hh24miss'))and to_number(SUBSTR('08:00:00',1,2)||SUBSTR('08:00:00',4,2)||SUBSTR('08:00:00',7,2)||'.999999')>to_number(to_char(FlightCharges_date, 'hh24miss'))

    Is this supposed to be saying the time component of FlightCharges_date should be between 7am and 8am? You could achieve this much more simply with:

    FlightCharges_date-trunc(flightCharges_date) between 7/24 and 8/24

    This won't make a large difference to the performance but making things easier to read and understand makes your maintenance so much easier.

    and (select count(*) from violation where FlightCharges.FlightCharges_id=violation_id) = 0

    Is asking to count every row in violation that matches this condition for each different value of FlightCharges_Id in the rows you've filtered down to in FlightCharges. You don't really want to count all of the matching rows, you just want to know if any match. A not exists filter would do this for you.

    Earlier on you have:

    (((select count(1) from violation where FlightCharges.FlightCharges_id=violation_id) = 0)or((select count(1) from violation where FlightCharges.FlightCharges_id=violation_idand FlightCharges.updated_on>=updated_on and is_closed<>0) > 0))

    But you already are always filtering on

    (select count(1) from violation where FlightCharges.FlightCharges_id=violation_id) = 0)

    So that is always true, simplifying this to:

    (

    1=1

    or

    ((select count(1) from violation where FlightCharges.FlightCharges_id=violation_id

    and FlightCharges.updated_on>=updated_on and is_closed<>0) > 0)

    )

    ->

    1=1

    i.e. it's completely unnecessary work.

    Your use of scalar subqueries constantly makes it seem like you're unaware of JOIN, I trust this isn't really the case. Scalar subqueries do not scale.

    The second part of your union all can have similar comments applied to it.

    But obviously, without actually seeing where the time is going, this is just guesswork and could be a complete waste of everyone's time. I suggest you talk with whomever you need to talk to and make sure you have access to the execution plans of the SQL you need to make perform well enough. Have a read of my blog post here https://ctandrewsayer.wordpress.com/2017/03/21/4-easy-lessons-to-enhance-your-performance-diagnostics/ where I describe how to add row source statistics data into the execution plans so you can really see what's going on. There's hardly ever a need a guess.

  • vijz
    vijz Member Posts: 682
    edited Mar 31, 2018 9:47AM

    Below are the parameters as inputs:

    $P{StartDate_1} =  to_date('2016-03-01','yyyy-mm-dd')

    $P{EndDate_1}=  to_date('2016-03-30','yyyy-mm-dd')

    $P{StartTime}=  '07:00:00'

    $P{EndTime}= '08:00:00'

    Note: Changed the previously posted query for testing purpose now

    There were other parameters inputs as well, but removed for simplification. Below is my code snippet.

    Removed 1=0 condition. As start time and end time submitted as parameters, haven't modified the time calculation logic.

    May I know how can we remove scalar sub queries as where condition values of them depends on input parameter of main query. Below is the Auto Trace of the SQL queryAutotrace1.JPGAutotrace2.JPGAutotrace3.JPG

    select /*+ parallel(auto) NO_INDEX */toll_id,toll_date,upper(trip_od_pair) as trip_od_pair,trip_od_pair_id,tag_type,exit_plaza,entry_plaza,exit_date,avi_only_count,mixed_count,speed,lpn_only_count,tag_read,plate_state,plate_number,plate_state || ' ' || plate_number as lpn,case  when systrans_sub_type is null then 'NONE'  else upper(systrans_sub_type)end as systrans_sub_type,systran_type,toll_rated_id,case  when veh_class is null then ''  else veh_classend as veh_class,veh,case  when occupancy is null then 'TOLL'  when occupancy in (2,3) then 'HOV'  else 'TOLL'end as occupancy,case  when is_posted = -1 then 'N/A'  else to_char(is_posted)end as is_posted,venue_toll,account_id,expected_toll,discounted_toll,occupancy,road,exc_attr,adjacentfrom((select exc_attr,toll_id as toll_id, toll_date as toll_date, tag_read, plate_state as plate_state, plate_number as plate_number,exit_plaza as exit_plaza, entry_plaza as entry_plaza, exit_date,avi_only_count as avi_only_count,lpn_only_count as lpn_only_count,mixed_count as mixed_count,speed,(select tag_type from toll_segment where toll.toll_id=toll_segment.toll_id and toll_segment.tag_read <> ' ' and toll.tag_read=toll_segment.tag_read and rownum=1) as tag_type,trip_od_pair_id as trip_od_pair_id,(select trip_od_pair_name from trip_od_pair top where toll.trip_od_pair_id = top.trip_od_pair_id) as trip_od_pair,dated_type_id as toll_rated_id, veh_class as veh,(select description from code_lookup cl where cl.code_group='VEH_CLASS' and cl.code_id=toll.veh_class) as veh_class,(select discount_plan_name from discount_plan where discount_plan_id=toll.systrans_sub_type) as systrans_sub_type,(select description from code_lookup where toll.vio_type=code_lookup.code_id and code_group='VIOL_RESL') as systran_type,venue_toll as venue_toll, expected_toll as expected_toll, discounted_toll asdiscounted_toll, account_id as account_id, occupancy as occupancy, is_posted as is_posted,case  when toll.road_id is null then 'UNKNOWN'  when toll.road_id =1000 then 'Road to Moon'  when toll.road_id=9999 then 'Road to Sun'end as road,case  when toll.adj_id <> 0 then to_char(toll.adj_id)  when toll.adj_id = 0 or toll.adj_id is null then ''  else ''endas adjacentfrom tollwhere((is_closed=0)or((is_closed<>0) and(((select count(1) from violation where toll.toll_id=violation_id) = 0)or((select count(1) from violation where toll.toll_id=violation_idand toll.updated_on>=updated_on and is_closed<>0) > 0))))and toll_revenue_date between to_date('2016-03-01','yyyy-mm-dd')  and  to_date('2016-03-30','yyyy-mm-dd')and to_number(SUBSTR('07:00:00',1,2)||SUBSTR('07:00:00',4,2)||SUBSTR('07:00:00',7,2))<=to_number(to_char(toll_date, 'hh24miss'))and to_number(SUBSTR('08:00:00',1,2)||SUBSTR('08:00:00',4,2)||SUBSTR('08:00:00',7,2)||'.999999')>to_number(to_char(toll_date, 'hh24miss'))and (select count(*) from violation where toll.toll_id=violation_id) = 0)union all(select 0 as exc_attr,violation_id as toll_id, toll_date as toll_date, tag_read, plate_state as plate_state, plate_number as plate_number,exit_plaza as exit_plaza, entry_plaza as entry_plaza,exit_date,avi_only_count as avi_only_count,lpn_only_count as lpn_only_count,mixed_count as mixed_count,speed,(select tag_type from toll_segment where violation.violation_id=toll_segment.toll_id and toll_segment.tag_read <> ' ' and violation.tag_read=toll_segment.tag_read and rownum=1) as tag_type,trip_od_pair_id as trip_od_pair_id,(select trip_od_pair_name from trip_od_pair top where violation.trip_od_pair_id = top.trip_od_pair_id) as trip_od_pair,dated_type_id as toll_rated_id,  veh_class as veh,(select description from code_lookup cl where cl.code_group='VEH_CLASS' and cl.code_id=violation.veh_class) as veh_class,(select discount_plan_name from discount_plan where discount_plan_id=violation.systrans_sub_type) as systrans_sub_type,(select description from code_lookup where violation.vio_type=code_lookup.code_id and code_group='VIOL_RESL') as systran_type,venue_toll as venue_toll, expected_toll as expected_toll, discounted_toll as discounted_toll, account_id as account_id, occupancy as occupancy, -1 asis_posted,case  when violation.road_id is null then 'UNKNOWN'  when violation.road_id =1000 then 'Road to Moon'  when violation.road_id=9999 then 'Road to Sun'end as road,case  when violation.adj_id <> 0 then to_char(violation.adj_id)  when violation.adj_id = 0 or violation.adj_id is null then ''  else ''endas adjacentfrom violationwhere((is_closed=0)or((is_closed<>0) and((select count(*) from toll where toll.toll_id=violation_id and toll.updated_on<updated_onand toll.is_closed<>0) > 0)))and toll_revenue_date between to_date('2016-03-01','yyyy-mm-dd')  and  to_date('2016-03-30','yyyy-mm-dd')and to_number(SUBSTR(  '07:00:00' ,1,2)||SUBSTR(  '07:00:00' ,4,2)||SUBSTR(  '07:00:00' ,7,2))<=to_number(to_char(toll_date, 'hh24miss'))and to_number(SUBSTR( '08:00:00',1,2)||SUBSTR( '08:00:00',4,2)||SUBSTR( '08:00:00',7,2)||'.999999')>to_number(to_char(toll_date, 'hh24miss'))))order byroad asc,adjacent asc,toll_date asc,toll_id asc
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited Mar 31, 2018 9:52AM

    Might be worth trying along the lines of

    select FlightCharges_id,FlightCharges_date,upper(tp.trip_od_pair) as trip_od_pair,trip_od_pair_id,tt.tag_type,exit_terminal,entry_terminal,exit_date,avi_only_count,

           mixed_count,speed,Flight_Idn_only_count,tag_read,FlightOrigin_state,FlightOrigin_number,FlightOrigin_state||' '||FlightOrigin_number as Flight_Idn,

           case when st.systrans_sub_type is not null

                then upper(st.systrans_sub_type)

                else 'NONE'

           end systrans_sub_type,

           vr.vio_type systran_type,

           FlightCharges_rated_id,

           fc.Fli_class

           Fli,

           case when occupancy in (200,300)

                then 'HOV'

                else 'FlightCharges'

           end occupancy,

           case when is_posted != -1

                then to_char(is_posted)

                else 'N/A'

           end is_posted,

           venue_FlightCharges,account_id,expected_FlightCharges,discounted_FlightCharges,occupancy,route,exc_attr,adjacent   

      from (select exc_attr,FlightCharges_id,FlightCharges_date,tag_read,FlightOrigin_state,FlightOrigin_number,exit_terminal,entry_terminal,exit_date,avi_only_count,

                   Flight_Idn_only_count,mixed_count,speed,

                   tag_read,

                   trip_od_pair_id,

                   dated_type_id FlightCharges_rated_id,

                   Fli_class Fli,

                   Fli_class,

                   systrans_sub_type,

                   vio_type,

                   venue_FlightCharges,expected_FlightCharges,discounted_FlightCharges,account_id,occupancy,is_posted,

                   case when FlightCharges.route_id = 1000

                        then 'Domestic Route'

                        when FlightCharges.route_id = 10000

                        then 'International'

                        when FlightCharges.route_id is null

                        then 'UNKNOWN'

                   end route,

                   case when FlightCharges.adj_id <> 0

                        then to_char(FlightCharges.adj_id)

                   end adjacent

              from FlightCharges

             where (is_closed = 0)

                or (

                    (is_closed <> 0)

               and not(exists(select null

                                 from violation

                                where violation_id = FlightCharges.FlightCharges_id

                             )

               and     exists(select null

                                from violation

                               where violation_id = FlightCharges.FlightCharges_id

                                 and updated_on <= FlightCharges.updated_on

                                 and is_closed <> 0

                             )

                      )

                   )

               and FlightCharges_revenue_date between to_date('2016-03-30','yyyy-mm-dd') and to_date('2016-03-30','yyyy-mm-dd')

               and to_char(FlightCharges_date, 'hh24miss') between '070000' and '080000'

               and not exists(select null

                                from violation

                               where violation_id = FlightCharges.FlightCharges_id

                             )

           union all

           select 0 exc_attr,PayViolation_id FlightCharges_id,FlightCharges_date,tag_read,FlightOrigin_state,FlightOrigin_number,exit_terminal,entry_terminal,exit_date,

                  avi_only_count,Flight_Idn_only_count,mixed_count,speed,

                  tag_read,

                  trip_od_pair_id,

                  dated_type_id FlightCharges_rated_id,Fli_class Fli,

                  Fli_class,

                  systrans_sub_type,

                  vio_type,

                  venue_FlightCharges,expected_FlightCharges,discounted_FlightCharges,account_id,occupancy,-1 is_posted,

                  case when route_id = 1000

                       then 'Domestic Route'

                       when route_id = 10000

                       then 'International Route'

                       else 'UNKNOWN'

                  end as route,

                  case when adj_id <> 0

                       then to_char(adj_id)

                  end adjacent

             from PayViolation

            where (is_closed = 0)

               or

                  (

                   is_closed <> 0

              and

                   exists(select count(*)

                            from FlightCharges

                           where FlightCharges_id = PayViolation.PayViolation_id

                             and updated_on < PayViolation.updated_on

                             and is_closed <> 0

                         )

                  )

             and FlightCharges_revenue_date between to_date('2016-03-30','yyyy-mm-dd') and to_date('2016-03-30','yyyy-mm-dd')

             and to_char(FlightCharges_date,'hh24miss') between '070000' and '080000'

           ) u

           left outer join

           (select FlightCharges_id,tag_read,max(tag_type) tag_type

              from FlightCharges_segment

             where tag_read <> ' '

             group by FlightCharges_id,tag_read

           ) tt

        on u.FlightCharges_id = tt.FlightCharges_id

       and u.tag_read = tt.tag_read

           left outer join

           (select trip_od_pair_id,trip_od_pair_name trip_od_pair

              from trip_od_pair

           ) tp

        on u.trip_od_pair_id = tp.trip_od_pair_id

           left outer join

           (select discount_plan_id,discount_plan_name systrans_sub_type

              from discount_plan

           ) st

        on u.systrans_sub_type = st.discount_plan_id

           left outer join

           (select code_id,description Fli_class

              from code_lookup

             where code_group = 'FLI_CLASS'

           ) fc

        on u.Fli_class = fc.code_id

           left outer join

           (select code_id,description vio_type

              from code_lookup

             where code_group = 'VIOL_RESL'

           ) vr

        on u.vio_type = fc.code_id          

    order by u.route,u.adjacent,u.FlightCharges_date,u.FlightCharges_id

    Regards

    Etbin

    vijz
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Mar 31, 2018 10:20AM
    Below are the parameters as inputs:$P{StartDate_1} =  to_date('2016-03-01','yyyy-mm-dd')$P{EndDate_1}=  to_date('2016-03-30','yyyy-mm-dd')$P{StartTime}=  '07:00:00'$P{EndTime}= '08:00:00'

    You mean the query you posted wasn't the query you were actually running?

    Based on the predicates inside your autotrace, it's now clear that this is being run from inside a PL/SQL block and some of those literals are actually PL/SQL variables. Don't you think that would have been nice to know?

    May I know how can we remove scalar sub queries as where condition values of them depends on input parameter of main query

    rather than

    select (select b.column_b from table_b b where b.id = a.id)

    from table_a a

    Do

    select b.column_b

    from table_a a

    left join table_b b

    on b.id = a.id

    You could convert

    where (select count(*) from table_b b where b.id = a.id)=0

    To

    where not exists (select null form table_b b where b.id=a.id)

    Below is the Auto Trace of the SQL query

    Reading an autotrace in SQL Developer is difficult, it is even more difficult when you are reading screenshots, could you please extract it using SQL*Plus so you can use copy and paste? If you've already ran the statement with row source execution statistics enabled then you just need to search v$sql for the sql_id and then use

    select * from table(dbms_xplan.display_cursor(sql_id=>'YOUR SQL ID',format=>'typical allstats'));

    Include the entire results (included predicate sections and notes).

    Did you add the parallelism as a result of a guess? What was the plan before you decided to do this? What were the row source execution statistics?

  • Unknown
    edited Mar 31, 2018 4:41PM
    Report query will be run daily, monthly, quarterly.

    Then you should use a batch process to produce the data and report.

    1. create a stored procedure

    2. create appropriate work and report-ready tables to hold intermediate and final results

    3. break your complex query into pieces/steps

    4. execute each query step serially and save the results into the work tables

    5. query the work tables to roll up the data as appropriate

    6. insert the data into the final report-ready table

    IMHO there appears to be NO REASON or need to try to do everything in one complex query given that you want reports for three different ranges of values.

    John Thorton
  • vijz
    vijz Member Posts: 682
    edited Mar 31, 2018 8:05PM

    Hi, Thanks for your reply. There are further logical modification like cleansing of this report data, and then  aggregations, percentage calculations are applied on data by downstream Database. Our final goal is to perform all these downstream logic on our Database itself as core data is supplied from our own DB.  Yes, agreed to your suggestion of performing this final goal by breaking all this (along with future logic) into pieces/tables and produce final report-ready table.

    But before that, we would like to fine tune present Big Report Query, which is running long due to whichever reason. Can this existing query be fine tuned using Materialized views/any other method by any chance. If Yes, Please let us know the steps

  • Unknown
    edited Mar 31, 2018 9:01PM
    But before that, we would like to fine tune present Big Report Query, which is running long due to whichever reason. Can this existing query be fine tuned using Materialized views/any other method by any chance. If Yes, Please let us know the steps

    I don't see ANY reason to do that when you can use REALLY SIMPLE separate queries and save the intermediate data.

    You can EASILY execute some, if not all, of those nested subqueries separately and store their results in work tables. By nesting them you are forcing much more complicated row-by-row execution of those sub queries. That is totally unnecessary for your use case.

    Your use case just SCREAMS for a batch process:

    1. it will perform better

    2. it will enable you to collect metrics on the data

    3. it will be easier to understand

    4. it will be easier to debug and maintain

    5. it will be easier to fix data manually if that becomes necessary

    6. it will be easier to create the necessary indexes and gather statistics for the final report

    Contrast that with what you presented in this thread:

    1. a huge poorly performing query

    2. hard to understand what the query is supposed to be doing

    3. hard to debug and validate if the results are correct

    4. no info about the metrics of the data

    5. multiple nested sub-queries that are linked row-by-row to the main query

    6. nothing at all preserved after execution to analyze

    Can the query be 'tuned'? Yes - to a degree

    Should it be tuned? Absolutely not - it should be broken up into pieces and those pieces organized at I outlined above.

    Even if such I needed such a monolithic query as my final result I would NEVER develop it that way.

    The SIMPLE development process for complex queries (when possible as in your case) is to develop them step by step as I outlined earlier.

    Once each step works properly, performs satisfactorily and produces the correct results I then combine the pieces into a larger query.

    So either way you slice it the method is to use a step by step approach.

This discussion has been closed.