Home » RDBMS Server » Performance Tuning » Fine Tune Big SQL Reporting Query
Fine Tune Big SQL Reporting Query [message #669063] Fri, 30 March 2018 23:56 Go to next message
dinavahi.saradhi@gmail.co
Messages: 8
Registered: December 2008
Junior Member
I have a Big Select Reporting query which retrieves columnar values from two similar table Structures of Eg: Table A and Table B .

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 at least.

select

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_class

end 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,

adjacent

from

(

(

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 as

discounted_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 ''

end

as adjacent

from FlightCharges

where

1>0

and

(

(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_id

and 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 as

is_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 ''

end

as adjacent

from PayViolation

where

1>0

and

((is_closed=0)

or

((is_closed<>0) and

((select count(*) from FlightCharges where FlightCharges.FlightCharges_id=PayViolation_id and FlightCharges.updated_on

and 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 by

route asc,

adjacent asc,

FlightCharges_date asc,

FlightCharges_id asc
Any suggestions to fine tune above query like Hints, Materialised views etc.., would be appreciated
Re: Fine Tune Big SQL Reporting Query [message #669066 is a reply to message #669063] Sat, 31 March 2018 01:14 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
You do need to put your code through a formatter. Ive done it for you here:
SELECT 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_class
       end                    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,
       adjacent
FROM   ((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                                     AS
                        discounted_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 ''
                end                                                          AS
                       adjacent
         FROM   flightcharges
         WHERE  1 > 0
                AND ( ( 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_id
AND 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                                                          AS
is_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 ''
end                                                         AS adjacent
FROM   payviolation
WHERE  1 > 0
AND ( ( is_closed = 0 )
OR ( ( is_closed <> 0 )
AND ( (SELECT Count(*)
FROM   flightcharges
WHERE
flightcharges.flightcharges_id = payviolation_id
AND flightcharges.updated_on
AND 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  BY route ASC,
          adjacent ASC,
          flightcharges_date ASC,
          flightcharges_id ASC 
Re: Fine Tune Big SQL Reporting Query [message #669067 is a reply to message #669066] Sat, 31 March 2018 01:31 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
Quote:
And do not have access to run explain plan. Autotrace output is larger as well.
Autotrace uses EXPLAIN PLAN, so I do not see how you can have one but not the other. Without an execution plan or any information about the tables, there is not much one can say. HOwever, there are some rather odd structures. For example:

Quote:
CASE
WHEN fli_class IS NULL THEN ''
ELSE fli_class
end AS Fli_class,
What is the CASE doing for you?


Quote:
AND flightcharges_revenue_date BETWEEN
To_date('2016-03-30', 'yyyy-mm-dd') AND
To_date('2016-03-30', 'yyyy-mm-dd')
why BETWEEN when equality would do?

Quote:
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'))
can you use EXTRACT to compare the time rather than this somewhat complicated method?



That is just a few. If you can remove all the unnecessary complications in the query it might be easier to understand what it is doing. THen perhaps on can tune it.


[Updated on: Sat, 31 March 2018 01:32]

Report message to a moderator

Re: Fine Tune Big SQL Reporting Query [message #669072 is a reply to message #669066] Sat, 31 March 2018 08:50 Go to previous messageGo to next message
dinavahi.saradhi@gmail.co
Messages: 8
Registered: December 2008
Junior Member
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_class

end 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,

adjacent

from

(

(

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 as

discounted_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 ''

end

as adjacent

from toll

where

 

 

(

(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_id

and 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 as

is_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 ''

end

as adjacent

from violation

 

 

where

 

 

((is_closed=0)

or

((is_closed<>0) and

((select count(*) from toll where toll.toll_id=violation_id and toll.updated_on<updated_on

and 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 by

road asc,

adjacent asc,

toll_date asc,

toll_id asc[img]http://[/img]
Re: Fine Tune Big SQL Reporting Query [message #669073 is a reply to message #669072] Sat, 31 March 2018 09:18 Go to previous messageGo to next message
BlackSwan
Messages: 26701
Registered: January 2009
Location: SoCal
Senior Member
https://community.oracle.com/thread/4133897
Re: Fine Tune Big SQL Reporting Query [message #669074 is a reply to message #669072] Sat, 31 March 2018 10:15 Go to previous messageGo to next message
John Watson
Messages: 8268
Registered: January 2010
Location: Global Village
Senior Member
You replied to me, but ignored everything I said. Hmmm.

Please put your code through a code formatter. I used this one,
http://www.dpriver.com/pp/sqlformat.htm
I won't do it for you again.

Your date logic is horrible and will be suppressing index usage. What is wrong with concatenating your date and time parameters, using to_date on the result, and then comparing that directly with your date column?

That screen shot of what you call "autotrace" is useless. You need to launch SQL*Plus, and run autotrace properly. Like this:
orclx>
orclx> set autotrace on
orclx> select * from emp join dept using (deptno);

    DEPTNO      EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM DNAME          LOC
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- -------------- -------------
        10       7782 CLARK      MANAGER         7839 1981-06-09:00:00:00       2450            ACCOUNTING     NEW YORK
        10       7839 KING       PRESIDENT            1981-11-17:00:00:00       5000            ACCOUNTING     NEW YORK
        10       7934 MILLER     CLERK           7782 1982-01-23:00:00:00       1300            ACCOUNTING     NEW YORK
        20       7566 JONES      MANAGER         7839 1981-04-02:00:00:00       2975            RESEARCH       DALLAS
        20       7902 FORD       ANALYST         7566 1981-12-03:00:00:00       3000            RESEARCH       DALLAS
        20       7876 ADAMS      CLERK           7788 1987-05-23:00:00:00       1100            RESEARCH       DALLAS
        20       7369 SMITH      CLERK           7902 1980-12-17:00:00:00        800            RESEARCH       DALLAS
        20       7788 SCOTT      ANALYST         7566 1987-04-19:00:00:00       3000            RESEARCH       DALLAS
        30       7521 WARD       SALESMAN        7698 1981-02-22:00:00:00       1250        500 SALES          CHICAGO
        30       7844 TURNER     SALESMAN        7698 1981-09-08:00:00:00       1500          0 SALES          CHICAGO
        30       7499 ALLEN      SALESMAN        7698 1981-02-20:00:00:00       1600        300 SALES          CHICAGO
        30       7900 JAMES      CLERK           7698 1981-12-03:00:00:00        950            SALES          CHICAGO
        30       7698 BLAKE      MANAGER         7839 1981-05-01:00:00:00       2850            SALES          CHICAGO
        30       7654 MARTIN     SALESMAN        7698 1981-09-28:00:00:00       1250       1400 SALES          CHICAGO

14 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |    14 |   812 |     6  (17)| 00:00:01 |
|   1 |  MERGE JOIN                  |         |    14 |   812 |     6  (17)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     4 |    80 |     2   (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN           | PK_DEPT |     4 |       |     1   (0)| 00:00:01 |
|*  4 |   SORT JOIN                  |         |    14 |   532 |     4  (25)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMP     |    14 |   532 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
       filter("EMP"."DEPTNO"="DEPT"."DEPTNO")


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1779  bytes sent via SQL*Net to client
        608  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         14  rows processed

orclx>
Re: Fine Tune Big SQL Reporting Query [message #669082 is a reply to message #669074] Sun, 01 April 2018 16:18 Go to previous message
dinavahi.saradhi@gmail.co
Messages: 8
Registered: December 2008
Junior Member
Hi,

Thanks for your email. We have performed some optimisations to this query to an extent. This SQL need to be run for window data of daily, weekly, monthly and quarterly data. Can we create materialised views to perform this. If yes, Please let us know the steps

[Updated on: Sun, 01 April 2018 16:40]

Report message to a moderator

Previous Topic: Direct path load insert take much longer than query inside it
Next Topic: Better query for max(date) per account in big table
Goto Forum:
  


Current Time: Wed May 27 18:36:38 CDT 2020