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?

This Content Component encountered an error
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 first published in July 2006

Dig deeper on Oracle database performance problems and tuning

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close