OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query Tunning

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

Query Tunning [message #477002]

Mon, 27 September 2010 15:00

bhatwalne
Messages: 2
Registered: September 2010
Location: Charlotte

Junior Member

SELECT DISTINCT a.colA, a.ColB, a.ColC
FROM TableA a, TableB b
WHERE a.ColA = b.ColA AND b.ColA IS NULL;

TableB has approx 2.6 million records while TableA is much smaller

However, this query takes days to complete

I am not a Oracle guy, and I need to get this query optimised to execute in minutes

Report message to a moderator

Re: Query Tunning [message #477003 is a reply to message #477002]

Mon, 27 September 2010 15:10

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Re: Query Tunning [message #477004 is a reply to message #477002]

Mon, 27 September 2010 15:11

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

By the way:

Quote:

WHERE a.ColA = b.ColA AND b.ColA IS NULL;

This is NEVER true, so the query will ALWAYS return no row.

Regards
Michel

Report message to a moderator

Re: Query Tunning [message #477005 is a reply to message #477002]

Mon, 27 September 2010 15:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>However, this query takes days to complete
I do not believe you.

posted SQL should return no rows

SELECT DISTINCT a.colA, a.ColB, a.ColC FROM TableA a WHERE a.ColA IS NULL;
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

enabled SQL_TRACE & post results back here

Report message to a moderator

Re: Query Tunning [message #477007 is a reply to message #477002]

Mon, 27 September 2010 15:16

bhatwalne
Messages: 2
Registered: September 2010
Location: Charlotte

Junior Member

My mistake...

Till I post some more information I'll correct the query here:

SELECT DISTINCT a.colA, a.ColB, a.ColC
FROM TableA a, TableB b
WHERE a.ColA = b.ColA AND b.ColB IS NULL;

Report message to a moderator

Re: Query Tunning [message #477009 is a reply to message #477007]

Mon, 27 September 2010 15:23

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 27 September 2010 22:10

Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888 and post the required information.

Regards
Michel

Report message to a moderator

Previous Topic:	Columns of v$ sessions_event.
Next Topic:	DBA_WAITERS , BLOCKERS $ V$LOCK

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 07:10:40 CST 2024