Cost of execution [message #406433] |
Thu, 04 June 2009 00:44  |
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?
Please enlighten me.
Thank you
Mahesh
|
|
|
|
Re: Cost of execution [message #406466 is a reply to message #406452] |
Thu, 04 June 2009 02:52   |
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   |
flyboy
Messages: 1903 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   |
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  |
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.
|
|
|