Home » RDBMS Server » Performance Tuning » Query running slow (Oracle 9i R2, Sun Soalris)
Query running slow [message #331282] Wed, 02 July 2008 20:08 Go to next message
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!
Re: Query running slow [message #331299 is a reply to message #331282] Wed, 02 July 2008 23:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: Query running slow [message #331319 is a reply to message #331299] Thu, 03 July 2008 00:13 Go to previous messageGo to next message
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,
Re: Query running slow [message #331332 is a reply to message #331319] Thu, 03 July 2008 00:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: Query running slow [message #331371 is a reply to message #331332] Thu, 03 July 2008 03:24 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Query running slow [message #331462 is a reply to message #331371] Thu, 03 July 2008 07:58 Go to previous messageGo to next message
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


Re: Query running slow [message #331740 is a reply to message #331462] Sat, 05 July 2008 02:56 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Query running slow [message #331820 is a reply to message #331282] Sat, 05 July 2008 14:32 Go to previous messageGo to next message
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)
Re: Query running slow [message #333182 is a reply to message #331820] Thu, 10 July 2008 13:35 Go to previous message
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,
Previous Topic: ORACLE TRACE : maximum number of recursive SQL levels (50) exceeded
Next Topic: Bind variable in a data warehouse
Goto Forum:
  


Current Time: Thu Dec 08 19:53:33 CST 2016

Total time taken to generate the page: 0.18054 seconds