OraFAQ Forum: Performance Tuning » Whey sometimes using Exists Function is better

Home » RDBMS Server » Performance Tuning » Whey sometimes using Exists Function is better

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

Whey sometimes using Exists Function is better [message #301441]

Wed, 20 February 2008 11:41

epall
Messages: 3
Registered: February 2008
Location: KL

Junior Member

Hi guys,

I am really wondering why the cost for query B is better than query A.

-----Query B---
select NAME
,WEEK_KEY as WEEK_KEY
,count(1) as TOTAL_RECORD
from SRC_CUST_INFO src
where RECORD_TYPE_ID ='PAS'
and AGE > 30
group by WEEK_KEY

-----Query B---
select NAME
,WEEK_KEY as WEEK_KEY
,count(1) as TOTAL_RECORD
from SRC_CUST_INFO src
where exists ( select null from LOOKUP_CUST_MOB where ID = 'PAS')
and AGE > 30
group by WEEK_KEY

--------------------------------------------
Welcome & thank you very much for donating your idea!

Report message to a moderator

Re: Whey sometimes using Exists Function is better [message #301447 is a reply to message #301441]

Wed, 20 February 2008 12:11

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

Senior Member
Account Moderator

They are 2 different queries, why should they run in the same time?

In addition, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code and align the columns in result. Use SQL Formatter.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

Also read How to Identify Performance Problem and Bottleneck .
And post requested information for Performances questions.

Regards
Michel

Report message to a moderator

Re: Whey sometimes using Exists Function is better [message #301478 is a reply to message #301447]

Wed, 20 February 2008 16:57

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

Senior Member

The cost of two different execution plans for a ONE query are comparable (meaningless, but comparable).

Oracle makes no assertions that the cost of TWO different queries is comparable.

Ross Leishman

Report message to a moderator

Re: Whey sometimes using Exists Function is better [message #301502 is a reply to message #301478]

Wed, 20 February 2008 21:13

epall
Messages: 3
Registered: February 2008
Location: KL

Junior Member

Hi guys,
Thanks for the prompt reply.
Basically, the outcome of above query are the same.
However, the execution time of query B is less than query A.

First, I thought the inner qeury is costly. Therefore, I have perform some data cleasing and create a column name as RECORD_TYPE_ID.

However, after this exercise, i found out the result is getting worst. That's y, I am really wondering y.

Report message to a moderator

Re: Whey sometimes using Exists Function is better [message #301521 is a reply to message #301502]

Wed, 20 February 2008 23:30

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

Senior Member

Ahh. If you're saying B is FASTER than A (rather than 'cheaper') then that's different. Post the Explain Plans.

Ross Leishman

Report message to a moderator

Re: Whey sometimes using Exists Function is better [message #301579 is a reply to message #301441]

Thu, 21 February 2008 01:19

epall
Messages: 3
Registered: February 2008
Location: KL

Junior Member

YES. The cost & execution time of QUERY B is cheaper and faster than query A.

Explain plan for QUERY A (COST = 42252 ,NUM OF ROW =1)
------------
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 1 42252
PX COORDINATOR
PX SEND QC (RANDOM) SYS.:TQ10000 1 11 42252 :Q1000 P->S QC (RANDOM)
PX BLOCK ITERATOR 1 11 42252 :Q1000 PCWC 1 36
TABLE ACCESS FULL CI_STAGING.SRC_LOG_INP 1 11 42252 :Q1000 PCWP 1 36

Explain plan for QUERY B - USING EXITS FUNCTION
(COST = 41669 ,NUM OF ROW =39M)
------------
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=ALL_ROWS 39 M 41669
PX COORDINATOR
PX SEND QC (RANDOM) SYS.:TQ10001 39 M 785 M 41669 :Q1001 P->S QC (RANDOM)
HASH JOIN RIGHT SEMI 39 M 785 M 41669 :Q1001 PCWP
PX RECEIVE 1 12 0 :Q1001 PCWP
PX SEND BROADCAST SYS.:TQ10000 1 12 0 :Q1000 P->P BROADCAST
PX BLOCK ITERATOR 1 12 0 :Q1000 PCWC
TABLE ACCESS FULL CI_STAGING.LOOKUP_LOG_RECORD_TYPE 1 12 0 :Q1000 PCWP
PX BLOCK ITERATOR 117 M 1010 M 41580 :Q1001 PCWC 1 36
TABLE ACCESS FULL CI_STAGING.SRC_LOG_INP 117 M 1010 M 41580 :Q1001 PCWP 1 36

Report message to a moderator

Re: Whey sometimes using Exists Function is better [message #301580 is a reply to message #301579]

Thu, 21 February 2008 01:22

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

Senior Member
Account Moderator

Plan without code tags are unreadable. Read the link I posted.

But once again they are 2 different queries that give 2 different result sets why do you want to compare them?

Regards
Michel

Report message to a moderator

Re: Whey sometimes using Exists Function is better [message #301694 is a reply to message #301580]

Thu, 21 February 2008 07:01

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

Senior Member

I can't read your last post. Can you please add [code]...[/code] tags so that the indentation is preserved.

Report message to a moderator

Previous Topic:	Optimization for Large-Volume Data Streams
Next Topic:	Execution time differences between environments

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 28 10:00:47 CDT 2024