Home » RDBMS Server » Performance Tuning » Whey sometimes using Exists Function is better
Whey sometimes using Exists Function is better [message #301441] Wed, 20 February 2008 11:41 Go to next message
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!
Re: Whey sometimes using Exists Function is better [message #301447 is a reply to message #301441] Wed, 20 February 2008 12:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: Whey sometimes using Exists Function is better [message #301478 is a reply to message #301447] Wed, 20 February 2008 16:57 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Whey sometimes using Exists Function is better [message #301502 is a reply to message #301478] Wed, 20 February 2008 21:13 Go to previous messageGo to next message
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.

Re: Whey sometimes using Exists Function is better [message #301521 is a reply to message #301502] Wed, 20 February 2008 23:30 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Whey sometimes using Exists Function is better [message #301579 is a reply to message #301441] Thu, 21 February 2008 01:19 Go to previous messageGo to next message
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
Re: Whey sometimes using Exists Function is better [message #301580 is a reply to message #301579] Thu, 21 February 2008 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
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
Re: Whey sometimes using Exists Function is better [message #301694 is a reply to message #301580] Thu, 21 February 2008 07:01 Go to previous message
rleishman
Messages: 3724
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.
Previous Topic: Optimization for Large-Volume Data Streams
Next Topic: Execution time differences between environments
Goto Forum:
  


Current Time: Thu Dec 08 16:14:22 CST 2016

Total time taken to generate the page: 0.07886 seconds