If you are here, you have already:
Oracle will refuse to perform a hash cluster scan for a number of reasons, but they boil down to two possibilities:
To find out which of these is true for your SQL, add a HASH hint to your SQL.
SELECT /*+ HASH(a) */ a.*
FROM emp a
WHERE a.emp_no = 111
Now run the SQL through Explain Plan. Is the cluster being used now? If the cluster is now being scanned, it means that the Cost Based Optimiser doesn't think the cluster is very useful for your SQL. Have you checked the statistics? Try running the SQL again with the HASH hint; is the performance better now? If the performance is still poor, then Oracle was probably right to ignore the cluster - you almost certainly have a Bad Cluster problem.
If the HASH hint did not work (ie. Oracle is still using Full Table Scan or an Index Scan), then there is something about the SQL preventing the hash cluster scan from being used.
- WHERE col IN (SELECT ...)
- WHERE col = (SELECT ...)
- WHERE col >= ANY (SELECT ...)
- WHERE col <= ALL (SELECT ...)
- WHERE col NOT IN (SELECT ...)
One of the bullet points above should determine why the hash cluster scan is not being used. If not, then work on the process of elimination. First confirm that oracle recognises the hash cluster - run the following through Explain Plan
SELECT /*+ HASH(a)*/ *
FROM table_name a
WHERE clust_col = :val
If this simple SQL refuses to perform a hash cluster scan, then there is a fundamental problem. Get the DBA to rebuild the cluster.
If the sample SQL above does use a hash cluster scan, then start with your original SQL and remove lines one at a time, running each successively smaller SQL through Explain Plan as you go. Somewhere between the original SQL and the one above, the hash cluster scan will start to work. Once you find the problem, check the bullet points above to see whether any of them explain the fault. If not, discuss the problem with the DBA.