I'm trying to find a equi-join query that shows a decent performance bump when I use an index, cluster or hash-cluster structure on my data. I initially need to run the query on unstructured data first and the execution time should be significant so that I can see a performance boost with any of the 3 structures. The issues I am having is that if I use a query that utilizes an index column the search is too narrow and so I get too few rows returned and then my baseline query's execution time is too fast so I can't measure the time later.

It seems the only queries that have any major effect on my baseline queries execution time are those that cause a full table scan of the table with the most rows, but then that makes using an index structure useless as it won't use the index. Do clusters or hash-clusters benefit from full table scans - in general I don't know what queries benefit from clusters/hash-clusters.

My table has 500,000+ rows and some of the queries I have tried:

SELECT c.Cust_name, s.total_price 
FROM Sales s, Customer c 
WHERE s.Cust_id = c.Cust_id 
ORDER BY c.Cust_name;

SELECT count(*) 
from Sales s, Customer c 
WHERE s.Cust_id < 500 
    AND s.Cust_id = c.Cust_id;

SELECT c.Cust_name, s.total_price 
FROM Sales s, Customer c 
WHERE s.Cust_id = c.Cust_id 
    AND c.Cust_name LIKE '%A';

The last query was returning 34k rows and was still forcing the hash-join and full table scan over using the index. Is there any query I can run that is best suited for either indexes or clusters such that without these structures I would get a slow execution time on the baseline?

  • When using a LIKE clause the optimizer will usually choose a FTS over an index. – Stringer Sep 30 '15 at 3:25
  • %A can't use an index. A% might. – Mat Sep 30 '15 at 5:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.