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  |
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
|
|
|
|
| Re: Fine Tune Big SQL Reporting Query [message #669066 is a reply to message #669063] |
Sat, 31 March 2018 01:14  |
John Watson
Messages: 8961
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 |
John Watson
Messages: 8961
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 |
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 #669074 is a reply to message #669072] |
Sat, 31 March 2018 10:15 |
John Watson
Messages: 8961
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> |
|
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 15:25:25 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
