Fine tuning Big Select Reporting Query

vijz

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 Thorton

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

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

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

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

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 query

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

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

AndrewSayer

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

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.

vijz

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

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.