-
Need help in tuning the SQL
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
Last edited by anandkl; 09-15-2003 at 06:33 AM.
anandkl
-
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
Last edited by mike9; 09-15-2003 at 07:56 AM.
-
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
anandkl
-
What is the % of recs being selected?.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Its close to 5 - 6%
regards
anandkl
anandkl
-
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% ?
-
Yes..it 5 - 6%
regards
anandkl
anandkl
-
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
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
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?
-
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
anandkl
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|