3

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...?

require_once
  • 1,995
  • 3
  • 21
  • 29
  • 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
  • @Ben I have put index on the cluster too – require_once Apr 12 '12 at 20:25
  • 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

1 Answers1

4

Possible causes:

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

Please post the two plans.

usr
  • 168,620
  • 35
  • 240
  • 369