Oracle Database 10g Release 10.2.0.1.0
select m.makeupid, j.jobnumber
from Makeup m, Job j, Jobcrew jc
where m.workdate > sysdate
and m.jobcrewid = jc.jobcrewid
and jc.jobid = j.jobid
shows a plan of
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 159 | 6519 | 125 (2)| 00:00:02 |
|* 1 | HASH JOIN | | 159 | 6519 | 125 (2)| 00:00:02 |
|* 2 | HASH JOIN | | 321 | 9309 | 98 (2)| 00:00:02 |
| 3 | TABLE ACCESS BY INDEX ROWID| MAKEUP | 353 | 6707 | 75 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_MAKEUP_DT | 355 | | 4 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | JOBCREW | 11627 | 113K| 22 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | JOB | 5082 | 60984 | 26 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("JC"."JOBID"="J"."JOBID")
2 - access("M"."JOBCREWID"="JC"."JOBCREWID")
4 - access("M"."WORKDATE">SYSDATE@!)
Table Makeup has an index on WorkDate, which is being used, but a full table scan happens on both JobCrew and Job.
The primary key for table Job is field JobId, which being the primary key should be indexed on
Similarly, the primary key on table JobCrew is JobCrewId which is also not being used
ALTER TABLE JOBCREW ADD CONSTRAINT SYS_C005219 PRIMARY KEY (JOBCREWID)
ALTER TABLE JOB ADD CONSTRAINT SYS_C005144 PRIMARY KEY (JOBID)
Any ideas on why these table scans happen and how these can be prevented? Thanks.
Edited by: 997026 on Jun 7, 2013 3:54 PM