Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I have created a cluster and have two tables emp and sep on the cluster.

When I do the simple query...

explain plan select * from emp_cluster join dep_cluster using (dno)

the cost is 26.

I created two other tables on heap (emp_heap and dep_heap). When I do:

explain plan select * from emp_heap join dep_heap using (dno)

the cost is only 15, Which is less than that of the cluster.

I have inserted 33,000 records in the emp tables and 99 records in the department tables. I know that in join cluster behaves well but in my case it the opposite is true?

share|improve this question
2  
Please show us the full execution plan (and ideally the CREATE TABLE statements as well) – a_horse_with_no_name Apr 12 '12 at 20:50

2 Answers

up vote 2 down vote accepted
  • Are the statistics on all the objects up to date?
  • Which query is actually more efficient in terms of logical I/O or runtime?
  • What are the full query plans for the two queries?

In general, you shouldn't rely on the COST to compare two different queries. COST is only intended to be used internally by the optimizer in ranking different plans for the same query. It is entirely possible that one query with a cost of 26 is more efficient than a different query with a cost of 15.

share|improve this answer
what are the other factors to judge which query is best in Oracle?other than cost – zainshah120 Apr 13 '12 at 16:07
1  
@zainshah120 - The actual amount logical I/O that the query does (consistent gets and block gets), the amount of CPU that it consumes, the amount of memory that it consumes, etc. If you're looking at a query plan rather than running a query, you should be much more concerned that the cardinality estimates for each step are accurate rather than focusing on the COST. – Justin Cave Apr 13 '12 at 16:12
is there any way to make this query better to perform on Cluster? – zainshah120 Apr 13 '12 at 16:17
up vote 3 down vote

Most likely using a cluster for this query won't be beneficial.

A cluster in Oracle allows data from multiple tables to be stored physically close when they share a common key (here dno I suppose). This allows some query to perform better but a cluster will intrinsically consume more space than standard heap tables because for each key there will be some unused space. Insert-only heap tables on the other hand are one of the most efficient way to store data space-wise, since the rows fill all blocks nicely up to the HWM.

In your case since you don't have a filter so all rows will be read, producing a FULL SCAN of the data. Because the rows are stored in a more compact manner in heap tables, the cost will be less than the cost for the cluster.

The cluster, however, should have an edge when you look for a specific key, but this will also depend on the distribution of the data (number of rows per key), and on the length of the rows. You could build an example where the heap tables with regular B-Tree indexes will outperform a cluster for single-key queries.

In conclusion, clustering tables in Oracle will help for some queries, but will also be hurtful to others, it has restrictions and drawbacks, it is not a silver bullet for optimal performance. Heap tables are the default for a good reason: they have good performance for most queries.

share|improve this answer
I have put 300 records for each department in my employee table – zainshah120 Apr 13 '12 at 16:12

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.