OraFAQ Forum: Performance Tuning » Query running slow

Home » RDBMS Server » Performance Tuning » Query running slow (Oracle 9i R2, Sun Soalris)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query running slow [message #331282]

Wed, 02 July 2008 20:08

poratips
Messages: 345
Registered: April 2005
Location: IL

Senior Member

Hi,
I have following query which is running slow.
Could you please guide me that how can tune the query?

SELECT 
DISTINCT a.EADDR, 
a.MID 
FROM MEM  a
WHERE ( a.Lst='gbby' 
AND a.MType='normal' 
AND a.SbType_='mail' 
AND a.EADDR IS NOT NULL ) 
AND ( ((a.DTJOINED < '23-JUN-08') AND 
(a.C_BHDAY1 IS NULL)) 
AND 
(NOT EXISTS (SELECT /*+ INDEX(clltrk_IX_clltrk_TmeClk) */ NULL FROM clktrck  b
WHERE a.MID = b.MID ))

I have currently following index on this table:

PK_MEM			MID	UNIQUE	
MEM_USR_IX1		Lst, MTYPE, SbTYPE, EADDR, DtJOINED, C_BDAY1	NONUNIQUE
IX_MEM_MTYPE		MTYPE, LST, DtJOINED	NONUNIQUE

My Explain plan is following

TABLE ACCESS(BY INDEX ROWID) MEM	ANALYZED	369	120	7920					
INDEX(RANGE SCAN) IX_MEM_MTYPE	ANALYZED	30	2781						
INDEX(RANGE SCAN) IX_ClkTrk2	ANALYZED	3	4	24
Total cost is 754

Other join table has following index:

IX_Clktrk2	MID, URLID	NONUNIQUE
IX_Clktrk_TmeClk	TMECLCK, MID, CLKID	NONUNIQUE

Thanks in advance!

Report message to a moderator

Re: Query running slow [message #331299 is a reply to message #331282]

Wed, 02 July 2008 23:30

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

First remove the hint and use correct datatype: '23-JUN-08' is a string not a date.

Regards
Michel

Report message to a moderator

Re: Query running slow [message #331319 is a reply to message #331299]

Thu, 03 July 2008 00:13

poratips
Messages: 345
Registered: April 2005
Location: IL

Senior Member

Thanks Michel!
I tried to remove hint but not much effect.
It's stored in database as string - '23-JUN-08' and my column is a DATE datatype.
I tried to use a.DtJOINED < to_date('23-06-08', 'DD-MM-YY')) but its not working the query.
I also tried with TO_CHAR but its also giving me an error.
Is it you have to query the date column the way its stored in DB?

Thanks,

Report message to a moderator

Re: Query running slow [message #331332 is a reply to message #331319]

Thu, 03 July 2008 00:52

Michel Cadot
Messages: 68665
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

It's stored in database as string - '23-JUN-08'

WRONG! Dates are stored in date format NOT in string one.

Quote:

I tried to use a.DtJOINED < to_date('23-06-08', 'DD-MM-YY')) but its not working the query.

So something is wrong in your query and/or data and/or model.

Regards
Michel

Report message to a moderator

Re: Query running slow [message #331371 is a reply to message #331332]

Thu, 03 July 2008 03:24

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Try:

a.DtJOINED < to_date('23-06-2008', 'DD-MM-YYYY'))

- How many rows in MEM?

- What does the following SQL return:

SELECT COUNT(*)
FROM MEM  a
WHERE a.Lst='gbby' 
AND   a.MType='normal' 
AND   a.SbType_='mail' 
AND   a.EADDR IS NOT NULL
AND   a.DTJOINED < to_date('23-JUN-2008', 'DD-MON-YYYY')
AND   a.C_BHDAY1 IS NULL

Ross Leishman

Report message to a moderator

Re: Query running slow [message #331462 is a reply to message #331371]

Thu, 03 July 2008 07:58

poratips
Messages: 345
Registered: April 2005
Location: IL

Senior Member

Thanks rleishman, really appreciate.

In table MEM - 2507803 Rows
In Table clktrck - 1881306 Rows

SELECT COUNT(*)
FROM MEM  a
WHERE a.Lst='gbby' 
AND   a.MType='normal' 
AND   a.SbType_='mail' 
AND   a.EADDR IS NOT NULL
AND   a.DTJOINED < to_date('23-JUN-2008', 'DD-MON-YYYY')
AND   a.C_BHDAY1 IS NULL

Returns 538493 Rows

Report message to a moderator

Re: Query running slow [message #331740 is a reply to message #331462]

Sat, 05 July 2008 02:56

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Post the Explain Plan for this. I am looking to make a HASH ANTI JOIN.

SELECT 
DISTINCT a.EADDR, 
a.MID 
FROM MEM  a
WHERE ( a.Lst='gbby' 
AND a.MType='normal' 
AND a.SbType_='mail' 
AND a.EADDR IS NOT NULL ) 
AND ( ((a.DTJOINED < '23-JUN-08') AND 
(a.C_BHDAY1 IS NULL)) 
AND 
(NOT EXISTS (SELECT NULL FROM clktrck  b
WHERE NVL(a.MID,-1) = NVL(b.MID,-1) ))

Also try:

SELECT 
DISTINCT a.EADDR, 
a.MID 
FROM MEM  a
WHERE ( a.Lst='gbby' 
AND a.MType='normal' 
AND a.SbType_='mail' 
AND a.EADDR IS NOT NULL ) 
AND ( ((a.DTJOINED < '23-JUN-08') AND 
(a.C_BHDAY1 IS NULL)) 
AND NVL(a.MID,-1) NOT IN (
SELECT NVL(b.MID,-1)FROM clktrck  b)

If one of them shows a HASH JOIN (ANTI) in the plan. Try it out.

Ross Leishman

Report message to a moderator

Re: Query running slow [message #331820 is a reply to message #331282]

Sat, 05 July 2008 14:32

Kriptas
Messages: 118
Registered: June 2008

Senior Member

please try this query

SELECT DISTINCT 
       a.EADDR, 
       a.MID 
FROM MEM  a
LEFT JOIN clktrck b on a.MID = b.MID
WHERE a.Lst='gbby' 
      AND a.MType='normal' 
      AND a.SbType_='mail' 
      AND a.EADDR IS NOT NULL  
      AND a.DTJOINED < '23-JUN-08' 
      AND a.C_BHDAY1 IS NULL 
      AND b.MID IS NULL

tables a and b must be joined by HASH JOIN (ANTI) (it can be without ANTI)

Report message to a moderator

Re: Query running slow [message #333182 is a reply to message #331820]

Thu, 10 July 2008 13:35

poratips
Messages: 345
Registered: April 2005
Location: IL

Senior Member

Thank you very much!
I will try it and let you know.
Currently i am using row_numer (over partition) clause and checking the data.

Thanks,

Report message to a moderator

Previous Topic:	ORACLE TRACE : maximum number of recursive SQL levels (50) exceeded
Next Topic:	Bind variable in a data warehouse

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jul 01 07:52:22 CDT 2024