Search code examples
sqldatabaseoraclequery-optimizationdatabase-performance

Non-structured Query performs better than on clustered, hash-clustered and indexed?

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?

Query:

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:

Unstructured: enter image description here

With index added on the join columns Sales.Cust_id and Customer.Cust_id: enter image description here

With both tables added to a cluster where the cluster index is the Cust_id: enter image description here

With Both tables added to a hash cluster with 10000 keys:

enter image description here

Solution

  • You select 72% of data. I don't think that any structure will help significantly for this scenario. The unstructured seems to be the best.