The myth of cost when comparing Oracle Explain Plans

The cost that shows up in Oracle Explain Plans has long been a source of confusion for developers. I can’t count how many times I have heard experience Oracle developers comparing the cost of Explain Plans between different queries.
Now let me define different queries because I think that is often part of the confusion. Say you write a query to go get Lot History from Intel’s factory databases. As you go to write the query you realize that there are two ways that you could write it – perhaps the different ways involve different tables or different where clauses. If you then go get the Explain Plan for those queries they will show you the access paths that the database is taking to retrieve the data you want. Even though you are wanting to get the same data (so in that case the queries could be considered the same because they yield the same result set) the queries are different and as such you cannot use the cost information in the two Explain Plans to compare which query is more efficient or faster. Tom Kyte describes in detail why this is in a response on AskTom found here.

 

Technorati tags: ,
Advertisements
This entry was posted in Technology. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s