| Explain plan Cost vs time elapsed [message #236971] |
Fri, 11 May 2007 08:13  |
beetel
Messages: 96
Registered: April 2007
|
Member |
|
|
Initially, I had the following query in my program:
select a.acct_id, b.cust_id, c.org_id
from tableA a,
tableB b,
tableC c
where a.acct_id = b.acct_id
and a.invoice_date between :v_date_range
and a.uom_id = c.uom_id(+);
where :v_date_range can be something like
to_date('01-NOV-2006','dd-MON-yyyy') and to_date('30-NOV-2006','dd-MON-yyyy')
TableA is partitioned by invoice_date. Then I had to put an extra condition: and a.pk_id <= :max_pk_limit
where :max_pk_limit can be any integer. and PK_ID is the primary key of TableA which has 300M records. So the query is now:
select a.acct_id, b.cust_id, c.org_id
from tableA a,
tableB b,
tableC c
where a.acct_id = b.acct_id
and a.invoice_date between :v_date_range
and a.uom_id = c.uom_id(+)
and a.pk_id <= :max_pk_limit;
The old query costs more than the new query as per explain plan. BUT in the actual execution, the new query executes 5x more than the old query. Why is that so??? I put hints so that the second query has the same explain plan as the first. But still the costs are different. And the new query still runs slower than the first.
Does it mean that the extra condition/filter makes Oracle do an extra step -- even though the explain plan, or rather execution path are the same (but the costs are still different)? I think so, that's why the costs are different, though paths are the same.. Am I right???
|
|
|
|
|
|
| Re: Explain plan Cost vs time elapsed [message #237108 is a reply to message #236971] |
Sat, 12 May 2007 02:41  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
Given that:
- you don't post your Oracle version
- you don't post your real queries (the ones you posted are syntaxically wrong)
- you don't post your table definitions
- you don't post indexes
- you don't post statistics
- you don't post your explain plan
- you don't post...
How can you hope we help you? YOYO!
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
