Search code examples
mysqlsql-serverdatabaseoraclequery-optimization

Why the current DB query optimization techniques doesn't support the optimization for the calculated column?

For the following query we know currently relational database systems, regardless of it is MYSQL, SQL Server or Oracle, don't support the index seek query even there is a index built on the column1 .

select * from table1 where column1+123=1000

My question is why currently QO techniques doesn't do the optimization such as transforming the above SQL statement to the below ?

select * from table1 where column1=877
Solution

  • On the one hand, this question will evoke opinions which are discouraged here.

    On the other hand, there is one practical reason why the optimization should not take place but it requires a use case that is more complicated than the one you provided.

    Given:

    SQL statements in your application

    select * from table1 where column1 = :val1 and column2 = :val2
select * from table1 where column1 = :val1
select * from table1 where column2 = :val2

    Indexes in the table

    I1 on (column1)
I2 on (column2)

    The first statement's execution plan is suboptimal (for some versions of an RDBMS) such that an very expensive AND-EQUAL row source operation (RSO) is chosen where the child RSOs are index range scans of the two candidate indexes. But you won't see the problem until you have a customer with the data to manifest the problem.

    The second statement uses the column1 index and usually performs well.

    The third statement uses the column2 index and usually performs well.

    The first statement performs so poorly that the vendor is contacted. The vendor identifies multiple solutions and then decides to modify the query thus:

     select * from table1 where column1+0 = :val1 and column2 = :val2

    or like this when the column is varchar2:

     select * from table1 where column1||'' = :val1 and column2 = :val2

    to preclude the suboptimal execution plan. The technique works exceptionally well for the stated goal and I've seen it used by multiple software vendors.

    If the RDBMS company had long ago employed the automated optimization you describe, then the software vendors and developers would have had one less manual SQL optimization tool at their disposal.