Home » SQL & PL/SQL » SQL & PL/SQL » Cost of execution (10)
Cost of execution [message #406433] Thu, 04 June 2009 00:44 Go to next message
maheshmhs
Messages: 93
Registered: October 2008
Location: Nepal
Member
Hello

There was a
insert into .. select ... from tbl1 a
left join
tbl2 b
on 
'123' = b.clientid 

code.
This execution took more than 20 hrs. So the execution was stopped. Cost of this query was nearly 13 million.
Again
insert into .. select ... from tbl1 a,
tbl2 b
where
b.clientid ='123'

This code is not executed. But its cost has decreased to 0.3 million.

I don't get why there is such difference in cost of execution.
One more thing, does hard coded values in join operation increase cost?
on '123' = b.clientid 


Please enlighten me.

Thank you
Mahesh


Re: Cost of execution [message #406452 is a reply to message #406433] Thu, 04 June 2009 01:52 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Read this link of Asktom http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:313416745628 and you will find that he clearly says that you should not compare cost of 2 queries for performance reason.
Re: Cost of execution [message #406466 is a reply to message #406452] Thu, 04 June 2009 02:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Bonker: I would have thought that these two should be comparable - they're the same query, expressed in ANSI and Oracle syntax.

@maheshmhs: Could you post the explain plans for the two queries, and the actual querys.

Re: Cost of execution [message #406477 is a reply to message #406433] Thu, 04 June 2009 03:23 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Additionally, the twou queries you provided are different, as demonstrated below:
SQL> create table tbl1
  2  (
  3    clientid varchar2(10),
  4    column12 integer
  5  );

Table created.

SQL> create table tbl2
  2  (
  3    clientid varchar2(10),
  4    column22 integer
  5  );

Table created.

SQL> insert into tbl1(clientid, column12) values('121', 0);

1 row created.

SQL> insert into tbl1(clientid, column12) values('122', 0);

1 row created.

SQL> insert into tbl1(clientid, column12) values('123', 0);

1 row created.

SQL> insert into tbl2(clientid, column22) values('121', 0);

1 row created.

SQL> insert into tbl2(clientid, column22) values('122', 0);

1 row created.

SQL> commit;

Commit complete.

SQL> select *
  2  from tbl1 a left join tbl2 b on '123' = b.clientid;

CLIENTID     COLUMN12 CLIENTID     COLUMN22
---------- ---------- ---------- ----------
121                 0
122                 0
123                 0

SQL> select *
  2  from tbl1 a, tbl2 b
  3  where '123' = b.clientid;

no rows selected

SQL> 

In my opinion, "hard coded values in join operation" makes a little sense; it is not real join, but a filtering condition.
It is possible to "join" without condition at all - it is called "cross join" or cartesian product (used in the second query).
By the way, what is the query supposed to return?
Re: Cost of execution [message #406482 is a reply to message #406477] Thu, 04 June 2009 03:38 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Quote:
I would have thought that these two should be comparable - they're the same query, expressed in ANSI and Oracle syntax.


Even I felt that way but Tom categorically replies in that link that you should not compare cost of 2 queries even if queries are identical. He also mentions that there are cases when some times higher cost queries runs in millisecond whereas lower cost queries may take ages to run.

I think rather than comparing cost one should compare the execution plan to spot the difference.


[Updated on: Thu, 04 June 2009 03:40]

Report message to a moderator

Re: Cost of execution [message #406485 is a reply to message #406482] Thu, 04 June 2009 03:44 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
He's talking about similar queries, ie queries with structural differences - ANSI and Oracle syntax are resolved internally to the same structure.
It's the same as re-ordering the where clause - it makes no difference to the query as the CBO sees it, even though it looks different.

But, as @Flyboy pointed out, I was wrong, and the two queries are significantly different.
Previous Topic: how to transform one row to multi-row with sql clause
Next Topic: string matching
Goto Forum:
  


Current Time: Fri Dec 02 18:30:35 CST 2016

Total time taken to generate the page: 0.30622 seconds