The following interesting issue does not have clear explanation till now.

I have query that is using the following predicates.
...
AND COL1 LIKE '%%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

May be I should explain from where is coming this strange query.
If you are developping some application and you need to generate some dynamic search query you can put a bind variables between the '%' symbols and to build dynamically this query. Usually it looks for Oracle like this:
...
AND COL1 LIKE '%:B1%'
AND COL2 LIKE '%:B2%'
AND COL3 LIKE '%:B3%'
AND COL4 LIKE '%:B4%'
...

During the investigation for the whole query I have removed the bind variables just to test it with real values. I put some real values for LIKE clauses. I have tested it without values for the bind variables as well. During testing I have changed incidentally '%%' to '%' within the LIKE clauses:
...
AND COL1 LIKE '%'
AND COL2 LIKE '%'
AND COL3 LIKE '%'
AND COL4 LIKE '%'
...

The whole execution plan has changed! Without clear reason!
Because these different execution plans, the cost for the first and second query is 69 but for the third one is 175, the whole statistics for buffer reads and recursive calls are changed too. This unexpected behaviour have provoked me to try to find the reason why the CBO is changing the whole plan.
First of all, there is no difference for the LIKE clause when you put '%%' or '%' symbols after it. The returned results are the same in both cases. Of course when your variable have some value then the generated LIKE will looks like this LIKE '%A%' (for static SQL). In this case the count of percent symbols will matter: LIKE '%A%' will return different results than LIKE '%'. But if you don't use a variable it will does not matter, the query will return the same result set in both cases.
This case is very specific, it will matter only when you write some static SQL with LIKE '%%' clauses but they do not restrict anything, it is expected you do not put unrestrictive clauses within your SQL queries. Anyway, I have decided to make some investigation about that.
I have discovered an additional fact. The execution plan have changed when changes have been applied just for one of the columns that is allowing NULL values - COL1:
...
AND COL1 LIKE '%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%%'
AND COL4 LIKE '%%'
...

You can see above that only one simple change is applied for COL1 and that's changing everything, again cost is 175. For example the following doesn't impact the execution plan in any way:
...
AND COL1 LIKE '%%'
AND COL2 LIKE '%'
AND COL3 LIKE '%'
AND COL4 LIKE '%'
...

The only difference between all four columns is that COL2, COL3, COL4 does not allows NULL values but COL1 allows. Another finding - from the execution plan I have seen that the index for COL1 is used only when LIKE '%%' is used, when you write LIKE '%' then the index is not used.
I have supposed that the decision of the CBO depends from the fact that an index for COL1 exists and that column allows NULL values which affects the decision of the CBO.
After that I have changed another of the NOT NULL columns (that have index over it) - COL3 to allow NULL values and rewrite the clauses like that:
...
AND COL1 LIKE '%%'
AND COL2 LIKE '%%'
AND COL3 LIKE '%'
AND COL4 LIKE '%%'
...

But the execution plan have not been affected - Cost: 69. Then I recompute statistics for that table and in result, different execution plan for the above query - Cost: 175.
I think this shows that the CBO is affected of the possibility to find expected rows inside the index, when column is NULLable then the possibility is small because the index will not contain the NULL values. But why it is affected from LIKE clauses in this way? When you put '%%' it is using the index, in case you put only '%' it is not using it.
I have tried to trace the CBO but I didn't succeed to find something additional that can help me on that.

I will appreciate any comments about this issue as well!

Labels: CBO