Full table scan instead of range scan

I am a beginner in SQL tuning. I have tried to tune the following query. The following is the table structure. 
attendancedata:-
CARD_ID       NOT NULL VARCHAR2(20) 
TRDATE        NOT NULL DATE
TRINTIME      DATE
TROUTTIME     DATE
STATE         CHAR(6)

employee_roster:-
ecode          not null varchar2(10)
roster_date    not null date 
shift_id       varchar2(10)

shift:-(SHIFT_ID IS THE PRIMARY KEY)
SHIFT_ID          NOT NULL VARCHAR2(10)
SHIFTSHORT_DESC   VARCHAR2(50)
START_FROM_TIME   DATE
START_TO_TIME     DATE
BREAK1_FROM_TIME  DATE 
BREAK1_TO_TIME    DATe
BREAK2_FROM_TIME  DATE
BREAK2_TO_TIME    DATE
SHIFT_DESC        VARCHAR2(100)
LATE_AFTER        DATE

EMPLOYEE:-
ECODE VARCHAR2(10) PRIMARY KEY,
CARD_NO VARCHAR2(10)/* THIS CARD_ID IS USED IN ATTENDANCEDATA) 
FNAME   VARCHAR2(50)
LANME   VARCHAR2(50)

explain plan for
select /*+ ordered */ a.fname || ' ' || a.lname
name,d.shift_id,c.trintime,c.trdate,
d.roster_date
from attendancedata c,employee_roster d,employee a,shift b 
where c.card_id=a.card_no and
d.ecode=a.ecode and
d.roster_date=c.trdate and
d.shift_id=b.shift_id and
c.trdate between '01-jun-05' and '12-jun-05'
AND d.roster_date between '01-jun-05' and '12-jun-05'

OPERATION            OPTIONS              OBJECT_NAME           ID
PARENT_ID       COST
-------------------- -------------------- -------------------- ---
--------- ----------
SELECT STATEMENT                                                 0 
67
NESTED LOOPS                                                     1
0         67
HASH JOIN                                                        2
1         67
TABLE ACCESS         FULL                 EMPLOYEE               3 
2          3
HASH JOIN                                                        4
2         63
TABLE ACCESS         FULL                 ATTENDANCEDATA         5
4         20
TABLE ACCESS         FULL                 EMPLOYEE_ROSTER        6 
4         43
INDEX                UNIQUE SCAN          PK_SHIFT_ID            7
1          0

8 rows selected.
I have used TRDATE COLUMN of ATTENDANCEDATA but why is a full table scan happening instead of range scan? The same thing happens for the EMPLOYEE_ROSTER table -- why? Can you explain or suggest any other table access path? Statistics are gathered for ATTENDANCEDATA and EMPLOYEE_ROSTER.

    Requires Free Membership to View

    Login

Your query uses a character string value for TRDATE, whose datatype is DATE; Oracle performs an implicit datatype conversion, which means that the index can't be used. Change the last clause in your query to 
c.trdate between to_date('01-jun-05','dd-mon-yy') 
and to_date('12-jun-05','dd-mon-yy')
AND d.roster_date between to_date('01-jun-05','dd-mon-yy') 
and to_date('12-jun-05','dd-mon-yy')

This was first published in July 2006

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top