Need help in tuning the SQL

[anandkl]

Hi Friends,
We are using oracle 8.1.7, solaris 2.8.

can u please help me in tuning this SQL.

INDEX is present on both date_added and ad_id column

select count(distinct l.com_id),l.date_added, a.source
from tabs a,lis l where
(to_char(l.date_added,'DD/MON/YYYY') >= '01/JAN/2002' and
to_char(l.date_added,'DD/MON/YYYY') <= '31/DEC/2002') and
l.ad_id = a.ad_id
group by l.date_added,a.source

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=279116 Card=32505 By
tes=942645)

1 0 SORT* (GROUP BY) (Cost=279116 Card=32505 Bytes=942645) :Q142035
001

2 1 NESTED LOOPS* (Cost=278935 Card=32505 Bytes=942645) :Q142035
000

3 2 TABLE ACCESS* (FULL) OF 'LIS' (Cost=211905 Card=33 :Q142035
515 Bytes=670300) 000

4 2 TABLE ACCESS* (BY INDEX ROWID) OF 'TABS' (Cost=2 Card=74 :Q142035
92600 Bytes=67433400) 000

5 4 INDEX* (UNIQUE SCAN) OF 'PKS_KEYS' (UNIQUE) (Cost= :Q142035
1 Card=7492600) 000



1 PARALLEL_TO_SERIAL SELECT A1.C0,A1.C1,COUNT(DISTINCT A1.C2) FRO
M :Q142035000 A1 GROUP BY A1.C0,A1.C

2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_NL(A2) INDE
X(A2 "PKS_KEYS") */ A1.C1 C0,A2."S

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_COMBINED_WITH_PARENT
5 PARALLEL_COMBINED_WITH_PARENT

U can see there is a full table scan happening on LIS table


regards
anandkl