user2556379
Reputation: 21
Oracle sql query needs to optimize
I have the following requirements.
I want to fetch the job schedules of the staffs for particular day(current day) and particular store (e.g. 12). Also, there is scenario that other store (13) staffs will come and work for store (12) and store 12 staffs can work for other stores.
Status table is storing the present/Absent details if staffs mark their attendance.
I want to fetch 1) the schedules of the staffs for the store 12 from staffschedules regardless of staffstatus has entry or not on the particular day.
2) the schedules from staffschedules who are all from other stores and reject the schedules of the staffs who went to other stores by referring the staffstatus (if entry is in status table, i need to retrieve, else nothing)
2 Tables:
- Schedules
- Status ----> staffs present/absent details
I have written the below query. It takes average of 30 seconds to run. In worst cases, 93 seconds.
STAFFSCHEUDLES TABLE SCHEMA:
CREATE TABLE "STAFFSCHEDULES"
(
"STORE_ID" VARCHAR2(75 BYTE) NOT NULL ENABLE,
"START_DATE" DATE NOT NULL ENABLE,
"JOB_ID" VARCHAR2(22 BYTE) NOT NULL ENABLE,
"START_TIME" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"END_DATE" DATE NOT NULL ENABLE,
"END_TIME" VARCHAR2(5 BYTE) NOT NULL ENABLE,
"JOBNAME" VARCHAR2(1500 BYTE)
CONSTRAINT "PK_STAFFSCHEDULES" PRIMARY KEY ("STORE_ID", "START_DATE", "JOB_ID", "START_TIME")
CREATE UNIQUE INDEX "PK_STAFFSCHEDULES" ON "STAFFSCHEDULES"
(
"STORE_ID", "START_DATE", "JOB_ID", "START_TIME"
)
CREATE INDEX "IDX1_STAFFSCHEDULES_STORSTDT" ON "STAFFSCHEDULES"
(
"STORE_ID",
"START_DATE"
)
CREATE INDEX "STAFFSCHEDULES_IDX" ON "STAFFSCHEDULES"
(
"JOB_ID"
)
STAFFSTATUS TABLE SCHEMA:
CREATE TABLE "STAFFSTATUS"
(
"JOB_SEQ_ID" NUMBER(10,0) NOT NULL ENABLE,
"JOB_ID" VARCHAR2(15 BYTE) NOT NULL ENABLE,
"STORE_ID" VARCHAR2(4 BYTE) NOT NULL ENABLE,
"JOB_DATE" DATE NOT NULL ENABLE,
"STATUS" VARCHAR2(1 BYTE) DEFAULT 'N' ,
"SERVER_DATE" DATE
CONSTRAINT "PK_STAFFSTATUS" PRIMARY KEY ("JOB_SEQ_ID")
CONSTRAINT "UK_STAFFSTATUS" UNIQUE ("JOB_ID", "STORE_ID", "JOB_DATE")
)
CREATE INDEX "INDEX_STAFFSTATUS" ON "STAFFSTATUS"
(
"STORE_ID",
"STATUS"
)
CREATE INDEX "INDEX_STAFFSTATUS_JOB_DT" ON "STAFFSTATUS"
(
"STORE_ID",
"JOB_DATE",
"STATUS"
)
CREATE UNIQUE INDEX "PK_STAFFSTATUS" ON "STAFFSTATUS"
(
"JOB_SEQ_ID"
)
CREATE UNIQUE INDEX "UK_STAFFSTATUS" ON "STAFFSTATUS"
(
"JOB_ID", "STORE_ID", "JOB_DATE"
)
CREATE INDEX "INDEX_STAFFSTATUS_UPDT" ON "STAFFSTATUS"
(
"STORE_ID",
"SERVER_DATE"
)
QUERY TO RETRIEVE THE SCHEDULES:
SELECT *
From StaffSchedules
WHERE store_id='15'
AND ((start_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY')) AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+(1-1/24/60/60)
AND TO_CHAR(start_date,'HH24:MI') <= start_time)
OR((end_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1/24/60/60 AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1)
AND TO_CHAR(end_date,'HH24:MI') >= end_time))
AND job_id NOT IN
(SELECT distinct s2.job_id
FROM staffschedules s2
WHERE s2.store_id=store_id
AND ((s2.start_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY')) AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+(1-1/24/60/60)
AND TO_CHAR(s2.start_date,'HH24:MI') <= s2.start_time)
OR((s2.end_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1/24/60/60 AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1)
AND TO_CHAR(s2.end_date,'HH24:MI') >= s2.end_time))
AND SUBSTR(ses2.org_path,INSTR(ses2.org_path,'/',1,7)+1,8) = 'NOTALLOCATED')
AND job_id NOT IN (
Select job_Id From staffStatus Where Trunc(job_Date)=Trunc(to_date('07/08/2013','MM/DD/YYYY')) And Store_Id!='15')
UNION ALL
SELECT *
From StaffSchedules ss
Right Outer Join staffStatus status On Es.job_Id=status.job_Id And status.Store_Id<>ss.Store_Id And
(Trunc(status.job_Date)=Trunc(ss.Start_Date) Or Trunc(status.job_Date)=Trunc(ss.End_Date))
AND ((start_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY')) AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+(1-1/24/60/60)
AND TO_CHAR(start_date,'HH24:MI') <= start_time)
OR((end_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1/24/60/60 AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1)
AND TO_CHAR(end_date,'HH24:MI') >= end_time))
AND job_id NOT IN
(SELECT distinct s2.job_id
FROM staffschedules s2
WHERE s2.store_id=store_id
AND ((s2.start_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY')) AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+(1-1/24/60/60)
AND TO_CHAR(s2.start_date,'HH24:MI') <= s2.start_time)
OR((s2.end_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1/24/60/60 AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1)
AND TO_CHAR(s2.end_date,'HH24:MI') >= s2.end_time))
AND SUBSTR(ses2.org_path,INSTR(ses2.org_path,'/',1,7)+1,8) = 'NOTALLOCATED')
AND status.store_id='15' AND TRUNC(status.job_date)=TRUNC(to_date('07/08/2013','MM/DD/YYYY'))
ORDER BY job_id,start_date,start_time;
EXECUTION PLAN:
----------------------------------------------------------------------------------------------------- -------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------- -------------
| 0 | SELECT STATEMENT | | 41 | 10865 | 37990 (1)| 00:07:36 |
| 1 | SORT ORDER BY | | 41 | 10865 | 37989 (67)| 00:07:36 |
| 2 | UNION-ALL | | | | | |
| 3 | NESTED LOOPS ANTI | | 1 | 265 | 12649 (1)| 00:02:32 |
| 4 | NESTED LOOPS ANTI | | 1 | 146 | 12620 (1)| 00:02:32 |
|* 5 | TABLE ACCESS BY INDEX ROWID| STAFFSCHEDULES | 109 | 13734 | 12401 (1)| 00:02:29 |
|* 6 | INDEX RANGE SCAN | IDX1_STAFFSCHEDULES_STORSTDT | 65068 | | 410 (1)| 00:00:05 |
|* 7 | INDEX RANGE SCAN | UK_STAFFSTATUS | 137K| 2694K| 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | STAFFSCHEDULES | 1 | 119 | 29 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | STAFFSCHEDULES_IDX | 83 | | 2 (0)| 00:00:01 |
| 10 | NESTED LOOPS ANTI | | 40 | 10600 | 25340 (1)| 00:05:05 |
| 11 | NESTED LOOPS | | 40 | 5840 | 24820 (1)| 00:04:58 |
| 12 | TABLE ACCESS BY INDEX ROWID| STAFFSTATUS | 2208 | 44160 | 2931 (1)| 00:00:36 |
|* 13 | INDEX RANGE SCAN | INDEX_STAFFSTATUS_SCHD_DT | 2208 | | 1525 (1)| 00:00:19 |
|* 14 | TABLE ACCESS BY INDEX ROWID| STAFFSCHEDULES | 1 | 126 | 29 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | STAFFSCHEDULES_IDX | 83 | | 2 (0)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID | STAFFSCHEDULES | 1 | 119 | 13 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | PK_STAFFSCHEDULES | 1 | | 12 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------- -------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter(("START_DATE"<=TO_DATE(' 2013-07-08 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
"START_DATE">=TO_DATE(' 2013-07-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"START_TIME">=TO_CHAR(INTERNAL_FUNCTION(START_DATE"),'HH24:MI') OR
"END_DATE"<=TO_DATE(' 2013-07-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"END_DATE">=TO_DATE(' 2013-07-08 00:00:01', 'syyyy-mm-dd hh24:mi:ss') AND
"END_TIME"<=TO_CHAR(INTERNAL_FUNCTION("END_DATE"),'HH24:MI')))
6 - access("STORE_ID"='15')
7 - access("JOB_ID"="JOB_ID")
filter(TRUNC(INTERNAL_FUNCTION("JOB_DATE"))=TO_DATE(' 2013-07-08 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "STORE_ID"<>'15')
8 - filter("S2"."STORE_ID"='15' AND ("S2"."START_DATE"<=TO_DATE(' 2013-07-08 23:59:59',
'syyyy-mm-dd hh24:mi:ss') AND "S2"."START_DATE">=TO_DATE(' 2013-07-08 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "S2"."START_TIME">=TO_CHAR(INTERNAL_FUNCTION("S2"."START_DATE"),'HH24:MI'
) OR "S2"."END_DATE"<=TO_DATE(' 2013-07-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"S2"."END_DATE">=TO_DATE(' 2013-07-08 00:00:01', 'syyyy-mm-dd hh24:mi:ss') AND
"S2"."END_TIME"<=TO_CHAR(INTERNAL_FUNCTION("S2"."END_DATE"),'HH24:MI')) AND
SUBSTR("S2"."JOBNAME",INSTR("S2"."JOBNAME",'/',1,7)+1,8)='NOTALLOCATED')
9 - access("JOB_ID"="S2"."JOB_ID")
13 - access("STATUS"."STORE_ID"='15')
filter(TRUNC(INTERNAL_FUNCTION("STATUS"."JOB_DATE"))=TO_DATE(' 2013-07-08 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
14 - filter(("START_DATE"<=TO_DATE(' 2013-07-08 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
"START_DATE">=TO_DATE(' 2013-07-08 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"START_TIME">=TO_CHAR(INTERNAL_FUNCTION("START_DATE"),'HH24:MI') OR
"END_DATE"<=TO_DATE(' 2013-07-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"END_DATE">=TO_DATE(' 2013-07-08 00:00:01', 'syyyy-mm-dd hh24:mi:ss') AND
"END_TIME"<=TO_CHAR(INTERNAL_FUNCTION("END_DATE"),'HH24:MI')) AND
"STATUS"."STORE_ID"<>"STORE_ID" AND (TRUNC(INTERNAL_FUNCTION("STATUS"."JOB_DATE"))=TRUNC(INTERNAL_FUNCT
ION("START_DATE")) OR TRUNC(INTERNAL_FUNCTION("STATUS"."JOB_DATE"))=TRUNC(INTERNAL_FUNCTION("
END_DATE"))) AND "STORE_ID"<>'15')
15 - access("STATUS"."JOB_ID"="JOB_ID")
16 - filter(("S2"."STORE_ID"='15' AND ("S2"."START_DATE"<=TO_DATE(' 2013-07-08 23:59:59',
'syyyy-mm-dd hh24:mi:ss') AND "S2"."START_DATE">=TO_DATE(' 2013-07-08 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "S2"."START_TIME">=TO_CHAR(INTERNAL_FUNCTION("S2"."START_DATE"),'HH24:MI'
) OR "S2"."END_DATE"<=TO_DATE(' 2013-07-09 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"S2"."END_DATE">=TO_DATE(' 2013-07-08 00:00:01', 'syyyy-mm-dd hh24:mi:ss') AND
"S2"."END_TIME"<=TO_CHAR(INTERNAL_FUNCTION("S2"."END_DATE"),'HH24:MI')) AND
SUBSTR("S2"."JOBNAME",INSTR("S2"."JOBNAME",'/',1,7)+1,8)='NOTALLOCATED')
17 - access("S2"."STORE_ID"="STORE_ID" AND "JOB_ID"="S2"."JOB_ID")
filter("JOB_ID"="S2"."JOB_ID" AND "S2"."STORE_ID"<>'15')
Sample Staffschedule data:
storeid job_id startdate starttime enddate endtime jobname
12 1 2013-07-11 09:00:00 09:00 2013-07-11 18:00:00 10:00 class A
12 1 2013-07-11 09:00:00 10:00 2013-07-11 18:00:00 11:00 class B
12 1 2013-07-11 09:00:00 11:00 2013-07-11 18:00:00 18:00 class C
Please help me in this issue.
Thanks in advance
Upvotes: 2
Views: 374
Answers (2)
ThinkJet
Reputation: 6735
Ok, there are big changes in question data, so let's examine it from scratch.
First of all, in query text present one unspecified thing:
AND SUBSTR(ses2.org_path,INSTR(ses2.org_path,'/',1,7)+1,8) = 'NOTALLOCATED')
There are no table with alias ses2 in query, so I have no ideas about what to do with that thing. Only note that it never evaluates to true because length('NOTALLOCATED') more than 8. So that condition removed from answer.
The main factor affecting query performance is overuse of NOT IN conditions. Such conditions in most cases must be changed to simple tests against field in main query.
In query from question we have situation like that:
select
t.*
from
my_table t
where
<big-basket-of-conditions-here>
and
t.field_1 not in ( select t.field_1
from my_table t
where
<same-big-basket-of-conditions>
and
<one_more_test>
)
which must be changed to
select
t.*
from
my_table t
where
<big-basket-of-conditions-here>
and
not (<one-more-test>)
E.g. you have:
AND job_id NOT IN
(SELECT distinct s2.job_id
FROM staffschedules s2
WHERE s2.store_id=store_id
AND ((s2.start_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY')) AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+(1-1/24/60/60)
AND TO_CHAR(s2.start_date,'HH24:MI') <= s2.start_time)
OR((s2.end_date BETWEEN TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1/24/60/60 AND TRUNC(to_date('07/08/2013','MM/DD/YYYY'))+1)
AND TO_CHAR(s2.end_date,'HH24:MI') >= s2.end_time))
AND SUBSTR(ses2.org_path,INSTR(ses2.org_path,'/',1,7)+1,8) = 'NOTALLOCATED')
which can be changed to just
AND SUBSTR(ses2.org_path,INSTR(ses2.org_path,'/',1,7)+1,8) <> 'NOTALLOCATED')
without subqueries and NOT IN.
If you need to join another tables to perform a test, better to do it in main select: do outer join and then test field:
select * from (
select
ss.*,
nvl(SUBSTR(ses2.org_path,INSTR(ses2.org_path,'/',1,7)+1,12),'X') alloc_flag
from
staffschedules ss,
some_table ses2
where
ses2.some_field (+) = ss.some_field and ...
)
where alloc_flag <> 'NOTALLOCATED'
Another good example of such situation is this condition:
AND job_id NOT IN (
Select job_Id
From staffStatus
Where Trunc(job_Date)=Trunc(to_date('07/08/2013','MM/DD/YYYY')) And Store_Id!='15'
)
It contains two bad things. One of them discussed above, and next is usage of != in condition. Such conditions eliminates any possibility of effective index usage. And worst thing with this case is that all of your indexes have a store_id on first place, so database engine must loop through entire index to find all conditions and can't fallback to another index with job_date on first place.
So, that condition can be rewrited with same technique
select * from (
select
ss.*,
nvl(statuses.store_id,'X') status_store_id
from
staffschedules ss,
staffstatus statuses
where
statuses.job_id (+) = ss.some_field
Trunc(job_Date (+)) = Trunc(to_date('07/08/2013','MM/DD/YYYY'))
and ...
)
where status_store_id = '15'
And last thing is function usage on fields while joining. Trunc(job_date) looks intuitive may be, but bad for performance because database can't use index on job_date in that case. So in such situation better to use boundary conditions:
job_Date >= to_date('07/08/2013','MM/DD/YYYY')
and
job_Date < to_date('07/08/2013','MM/DD/YYYY') + 1
And last, I written example query wich gets full schedule list. You can test it on this SQLFiddle or look below.
It returns full set of schedules and statuses for specified store and date.
Because I not sure about understanding scheduling logic, you can filter a results of the query on your own.
with params as (
-- Just to write parameters only once.
-- Note that parameter date truncated.
select
trunc(to_date('07/24/2013 07:20','MM/DD/YYYY HH24:MI')) as date_val,
'15' as store_id
from dual
),
store_jobs_from_schedule as (
-- Select all valid schedules on desired date for specified store with corresponding statuses if any.
select
scheduled_jobs.job_id job_id,
scheduled_jobs.store_id schedule_store_id,
scheduled_jobs.start_date schedule_start_date,
scheduled_jobs.start_date_time schedule_start_time,
scheduled_jobs.end_date schedule_end_date,
scheduled_jobs.end_date_time schedule_end_time,
scheduled_jobs.jobname schedule_job_name,
status_list.job_seq_id status_id,
status_list.store_id status_store_id,
status_list.job_date status_job_date,
status_list.status status
from
(
select -- get all schedules for specified date and store.
p.date_val,
schedules.job_id,
schedules.store_id,
schedules.start_date,
( -- Calculate exact start time as date and time value
trunc(schedules.start_date) +
(to_date(schedules.start_time,'hh24:mi') - to_date('00:00','hh24:mi'))
) start_date_time,
schedules.end_date,
( -- Calculate exact end time as date and time value
trunc(schedules.end_date) +
(to_date(schedules.end_time,'hh24:mi') - to_date('00:00','hh24:mi'))
) end_date_time,
schedules.jobname
from
params p,
staffschedules schedules
where
-- scheduled to specified store
schedules.store_id = p.store_id
and
-- start before the end of desired date
schedules.start_date < p.date_val + 1
and
-- end after beginning of desired date
schedules.end_date >= p.date_val
)
scheduled_jobs,
staffstatus status_list
where
-- Check if schedule start time are valid
(scheduled_jobs.start_date <= scheduled_jobs.start_date_time)
and
-- Check if schedule end time are valid
(scheduled_jobs.end_date >= scheduled_jobs.end_date_time)
and
-- Link status by staff and date if any - only on desired date,
-- not for full schedule length
status_list.job_id (+) = scheduled_jobs.job_id
and
status_list.job_date (+) >= scheduled_jobs.date_val
and
status_list.job_date (+) < scheduled_jobs.date_val + 1
),
store_stuff_jobs as (
-- Select all statuses for specified date and store and link it to corresponding schedules if any
select -- clear data in invalid schedules
job_id job_id,
decode(is_valid_schedule,'N', null, schedule_store_id) schedule_store_id,
decode(is_valid_schedule,'N', cast(null as date), schedule_start_date) schedule_start_date,
decode(is_valid_schedule,'N', cast(null as date), schedule_start_time) schedule_start_time,
decode(is_valid_schedule,'N', cast(null as date), schedule_end_date) schedule_end_date,
decode(is_valid_schedule,'N', cast(null as date), schedule_end_time) schedule_end_time,
decode(is_valid_schedule,'N', null, schedule_job_name) schedule_job_name,
status_id status_id,
status_store_id status_store_id,
status_job_date status_job_date,
status status
from (
select -- Calculate if selected schedule are valid
job_id,
schedule_store_id,
schedule_start_date,
schedule_start_time,
schedule_end_date,
schedule_end_time,
schedule_job_name,
status_id,
status_store_id,
status_job_date,
status,
( -- Calculate flag to check if times of schedules are valid
case
when
(schedule_start_date > schedule_start_time)
or
(schedule_end_date < schedule_end_time)
then 'N'
else 'Y'
end
) is_valid_schedule
from (
select
status_list.job_id job_id,
schedules.store_id schedule_store_id,
schedules.start_date schedule_start_date,
( -- Calculate exact start time as date and time value
trunc(schedules.start_date) +
(to_date(schedules.start_time,'hh24:mi') - to_date('00:00','hh24:mi'))
) schedule_start_time,
schedules.end_date schedule_end_date,
( -- Calculate exact end time as date and time value
trunc(schedules.end_date) +
(to_date(schedules.end_time,'hh24:mi') - to_date('00:00','hh24:mi'))
) schedule_end_time,
schedules.jobname schedule_job_name,
status_list.job_seq_id status_id,
status_list.store_id status_store_id,
status_list.job_date status_job_date,
status_list.status status
from
params p,
staffstatus status_list,
staffschedules schedules
where
status_list.job_date >= p.date_val
and
status_list.job_date < p.date_val + 1
and
status_list.store_id = p.store_id
and
-- Link schedules for same staff on same date if any.
-- Even schedules to same store, because we need exactly same
-- record as in first query to eliminate duplicates on last step.
schedules.job_id (+) = status_list.job_id
and
schedules.start_date (+) < trunc(status_list.job_date) + 1 -- start before the end of desired date
and
schedules.end_date (+) >= trunc(status_list.job_date) -- end after beginning of desired date
)
)
)
select
-- records comes from schedules
job_id,
schedule_store_id store_id,
schedule_store_id,
schedule_start_date,
schedule_start_time,
schedule_end_date,
schedule_end_time,
schedule_job_name,
status_id,
status_store_id,
status_job_date,
status
from
store_jobs_from_schedule
union -- duplicates eliminated
select
-- records comes from status
job_id,
status_store_id store_id,
schedule_store_id,
schedule_start_date,
schedule_start_time,
schedule_end_date,
schedule_end_time,
schedule_job_name,
status_id,
status_store_id,
status_job_date,
status
from
store_stuff_jobs
order by
job_id, schedule_start_date, schedule_start_time
Upvotes: 1
Glenn
Reputation: 9150
Your main filter is on date. So you will want to index by date. But this will only make sense if the number of records for a date is significantly less than the number of records in the table. Say < 5%. So your first best bang for your buck would be an index on staffschedules.date (or some other multi-part index starting with date).
You should run the two queries individually with an example date in sqlplus and see where the time is being spent. If the time is being spent on the first query, then this will be the date index or even a date-store index. This part should not be slow.
If the second part of the UNION is slow, break that into two parts and investigate each:
SELECT *
FROM staffschedules
WHERE date = v_date
AND store <> v_store
and
SELECT staffid
FROM staffstatus
WHERE store=v_store
AND jobdate=v_date
If the first part is slow, again, the date index. If the second one is slow, then it may need an index on staffstatus.jobdate. If each runs fairly quickly, then you have to look at how the staffid is joined, but it is most likely a case of an index on jobdate.
Upvotes: 0