CODEWITHSUNDEEP
sqldatabaseoracle-database
Sushant Kumar Singh
Sushant Kumar Singh

Reputation: 323

Query Cost best practice

Suppose my select query has cost 100 and I have another way in which i am writing two queries which has cost (40 + 60). Both have same cost ultimately.

I want to know what is the most effective way.

Upvotes: 2

Views: 491

Answers (3)

DribeiroExp
DribeiroExp

Reputation: 19

In this case, you should test both scenarios (one query , cost 100 and two queries, cost 40 + 60) ... try each scenario at least the number of executions your queries would execute in a hour or even a day, remember to apply a alter system flush (buffer cache and shared pool) between the test on each scenarios...

Upvotes: 0

Luca Davanzo
Luca Davanzo

Reputation: 21528

In my opinion depends if your db allows parallelism in queries.

If you can, you should run the query in the second way: cost is the same, but you get the result faster.

For example, sqlserver allow you to run parallel query, take a look here.

In oracle, instance, it's explained here.

Upvotes: 0

DazzaL
DazzaL

Reputation: 21993

ignore cost. its not the starting place for tuning, its just an internal ranking used by Oracle to pick the best plan for a given SQL statement. Cost can never be used to compare two different SQL statements to determine which one is better; i.e you can have a query with very high cost that is faster and uses less resources than a query that has a low cost.

if the query is as fast as splitting it in two, then leave it as one SQL (1 less round trip to do).

Upvotes: 5

Related Questions