Q

Full table scan instead of range scan

I am trying to tune this SQL query. Why is a full table scan happening instead of a 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.
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 last published in July 2006

Dig Deeper on Oracle database performance problems and tuning

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close