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?