Join on clustered tables shows greater cost than non-clustered tables?

Question

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

Now 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) and the 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 cluster. I have inserted 33000 records in the emp tables and 99 records in the department tables. I know that in join cluster behaves good but in my case it is opposite...?

MySQL _and_ Oracle? What are the indexes also? And [select * is considered harmful](http://stackoverflow.com/questions/3639861/why-is-select-considered-harmful). — Ben, Apr 12 '12 at 20:21
There might be better answers for this found on the DBA stack exchange site. Unfortunately we can't migrate it from here. — , Apr 12 '12 at 20:29
There are several options for CLUSTERs, you may want to post all the DDL and some sample data. — Jon Heller, Apr 13 '12 at 03:33

score 4 · Answer 1 · answered Apr 12 '12 at 21:22

4

Possible causes:

Statistics out of date
The clustered tables are fragmented or have low fill factor

Please post the two plans.

answered Apr 12 '12 at 21:22

usr

165,323
34
234
359

Join on clustered tables shows greater cost than non-clustered tables?

1 Answers1