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

[mike9]

The following statement is wrong because you compare
strings and not dates:

(to_char(l.date_added,'DD/MON/YYYY') >= '01/JAN/2002' and
to_char(l.date_added,'DD/MON/YYYY') <= '31/DEC/2002')

A string like '01/DEC/2003' will be smaler than '31/JAN/2003', I guess it's not soemthing you realy want.


Try the follwing statement and check if it's faster

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

[anandkl]

Hi Mike,
Its still doing FULL TABLESCAN


select count(distinct l.com_id),l.date_added, a.source
from tabs a,lis l
where
l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and
l.ad_id = a.ad_id
group by l.date_added,a.source 2 3 4 5 6 7
8 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=602938 Card=5108360
Bytes=148142440)

1 0 SORT* (GROUP BY) (Cost=602938 Card=5108360 Bytes=148142440 :Q142066
) 003

2 1 MERGE JOIN* (Cost=559467 Card=5108360 Bytes=148142440) :Q142066
002

3 2 SORT* (JOIN) (Cost=234139 Card=5267012 Bytes=105340240 :Q142066
) 002

4 3 TABLE ACCESS* (FULL) OF 'LIS' (Cost=211905 Card= :Q142066
5267012 Bytes=105340240) 001

5 2 SORT* (JOIN) (Cost=325328 Card=7492600 Bytes=67433400) :Q142066
002

6 5 TABLE ACCESS* (FULL) OF 'TABS' (Cost=305557 Card=74926 :Q142066
00 Bytes=67433400) 000



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

2 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_MERGE(A2) *
/ A1.C2 C0,A2.C1 C1,A1.C1 C2 FROM :Q

3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_TO_PARALLEL SELECT /*+ Q142066001 NO_EXPAND ROWID(A1) */
A1."AD_ID" C0,A1."COMP_ID" C1,A1

5 PARALLEL_COMBINED_WITH_PARENT
6 PARALLEL_FROM_SERIAL

[abhaysk]

What is the % of recs being selected?.

[anandkl]

Its close to 5 - 6%

regards
anandkl

[mike9]

Which % of rows of the table lis satisfay the follwoing condition:

l.date_added >= to_date('01/JAN/2002','DD/MON/YYYY') and
l.date_added <= to_date('31/DEC/2002','DD/MON/YYYY') and

Is it only 5-6% ?

[anandkl]

Yes..it 5 - 6%

regards
anandkl

[abhaysk]

wht type of inx is it?

wat is the card of the cols indexed?

was the no of recs in the table 'lis'

last thing that you may like to do is reorg table/index & analyze

[slimdave]

It's not the % of rows that matters, it's the % of blocks that they represent.

What is the clustering factor of the index on date_added?

How many blocks are there in the LIS table below the high water mark?

[anandkl]

Hi,
Its a normal bit map index, the total no of recoreds in lis is
13368818 records

I have analyzed the tables, as well as the index

regards
anandkl