Oracle 10g – How to Improve Select Query Performance

oracle-10gperformanceselect

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?

Best Answer

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.

Related Solutions

MS Access Query Performance – How to Resolve Performance Issues

Performing those subqueries repeatedly for each distinct ID value is what's killing you. I ran a test of your method on a sample table of 10,000 rows and it took ~2.5 minutes to execute on my notebook. By comparison, the following code consistently returned the same results in less than 0.5 seconds:

Option Compare Database
Option Explicit

Sub UsingAggregateQuery()
    Dim cdb As DAO.Database, t0 As Single
    Set cdb = CurrentDb
    cdb.Execute "DELETE FROM FinalTable", dbFailOnError
    t0 = Timer
    cdb.Execute _
            "INSERT INTO FinalTable(ID, FID, KeyValue1, KeyValue2, Date1) " & _
            "SELECT InputTable.ID, " & _
            "       Last(InputTable.FID), " & _
            "       Last(InputTable.KeyValue1), " & _
            "       Last(InputTable.KeyValue2), " & _
            "       Last(InputTable.Date1) " & _
            "FROM " & _
            "    (" & _
            "        SELECT ID, FID, KeyValue1, KeyValue2, Date1 " & _
            "        FROM InputTable " & _
            "        WHERE Date1 <> '' " & _
            "        ORDER BY ID, Date2 " & _
            "    ) " & _
            "GROUP BY ID;", _
            dbFailOnError
    Debug.Print Format(Timer - t0, "0.0") & " seconds"
    Set cdb = Nothing
End Sub

Edit

I just ran another test on a sample table of 400,000 rows and the code above completed in under 7 seconds

Oracle 10g – Update Column Based on Sum of Data from Another Table

If you have a primary key, you don't have duplicate rows.

In Oracle, you do multi-table UPDATEs by using MERGE, and not UPDATE, because that is inefficient.

merge into table1 
using (select col1, col2, col3, col4, sum(val2) sum_val2 from table2 group by col1, col2, col3, col4) table2
on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and table1.col3 = table2.col3 and table1.col4 = table2.col4)
when matched then update set table1.val1 = table2.sum_val2, table1.status = 'V';

If col1, col2, col3, col4 are really a composite primary key, no need to GROUP BY and SUM:

merge into table1 
using table2
on (table1.col1 = table2.col1 and table1.col2 = table2.col2 and table1.col3 = table2.col3 and table1.col4 = table2.col4)
when matched then update set table1.val1 = table2.val2, table1.status = 'V';

Best Answer

Related Solutions

MS Access Query Performance – How to Resolve Performance Issues

Oracle 10g – Update Column Based on Sum of Data from Another Table

Related Question