How to improve the performance of this select query?

Question

I have two tables: TABLE1 and TABLE2.

And I am running this select query to fetch the data. It takes long time to run:

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4 FROM TABLE1, TABLE2     
WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')     
  AND TABLE1.COL2= 1800
  AND TABLE2.COL1 = 10519198     
  AND TABLE1.COL3>= 100
  AND TABLE1.COL4= TABLE2.COL2
  AND TABLE2.COL3=('TEXT')

How could I improve the performance of this select query? Does too many AND affect my performance?

Answer 1

Based on your comments, here is a maybe faster version:

SELECT TABLE1.* , TABLE2.COL1 , TABLE2.COL4
FROM TABLE1
INNER JOIN TABLE2 ON TABLE1.COL4 = TABLE2.COL2
WHERE TABLE1.COL1 IN ('1','TEXT','HO TED')     
  AND TABLE1.COL2=1800
  AND TABLE1.COL3>=100
  AND TABLE2.COL1=10519198     
  AND TABLE2.COL3=('TEXT')

And you need to create a tree-index on TABLE1(COL2, COL1, COL3). Beware: hash-based index weren't o.k, because of your condition of TABLE1.COL3>=100 is an interval-query, which is impossible to be fastened by hash-based indices! This is also the cause, why must be COL3 the last element of the TABLE1(COL2, COL1, COL3) index.

Another index which can make things better were a hash-based index on TABLE2(COL1,COL3).

Further indices which will probably help: TABLE1(COL4) and TABLE2(COL2) (both).

None of the indices should be unique.