CODEWITHSUNDEEP
sqloracle-databaseoptimization
Sgtmoustache
Sgtmoustache

Reputation: 1

How to optimize Oracle request with clusters?

I need to optimise a request using clusters (on Oracle) but I'm having trouble understanding the documentation.

This is the request : 

SELECT COUNT(*) FROM TABLE1, TABLE2, TABLE3 
WHERE TABLE1.col1=TABLE3.col3
AND TABLE2.col1=TABLE3.col4
AND TABLE1.col2=1

I understand that I have to create a cluster using something like this

CREATE CLUSTER cluster_1(col2 INTEGER)

and then create a table using that cluster

CREATE TABLE TABLE1_CLUSTER

but the fact that i'm using 3 different tables is confusing me.

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

Your query should be written like this:

SELECT COUNT(*)
FROM TABLE1 JOIN
     TABLE3
     ON TABLE1.col1 = TABLE3.col3 JOIN
     TABLE2
     ON TABLE2.col1 = TABLE3.col4
WHERE TABLE1.col2 = 1;

The normal approach for optimization would be to use indexes:

  • TABLE1(col2, col1)
  • TABLE3(col3, col4)
  • TABLE2(col1)

Upvotes: 1

Related Questions