I am using the following query as a base line on some unstructured data (no indexes, no clusters etc) and the query is performing better on the unstrutured data than when I add indexes to the join columns or add the tables to a cluster or hash-cluster. I feel my baseline query isn't ideal, my task is to take a baseline query and find a structure that performs the best with it but all the structures I have tried perform worse than the unstructured. Is there anything I can do to my baseline query that will at least find one structure that has a noticeable improvement over the unstructured?
SELECT Cust_name, price FROM Customer, Sales WHERE price > 1000 AND num_sold > 10 AND Sales.Cust_id = Customer.Cust_id;
This query is returning 108k+ rows out of 150k.
Here are the traces from the testing:
With Both tables added to a hash cluster with 10000 keys: