My experience, findings and thoughts in my daily work with Oracle products



Strange behaviour of the CBO, part 1


E-mail this post



Remember me (?)



All personal information that you provide here will be governed by the Privacy Policy of Blogger.com. More...



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:



2 Responses to “Strange behaviour of the CBO, part 1”

  1. Blogger Gary Myers 
    I think the clause "LIKE '%' " is one of those special cases and gets treated under the hood as if it were "IS NOT NULL"

    Apart from that, LIKE is expected to filter the results by 99% (ie give a 1% success)

    Can't find a reference for that though.
  2. Blogger Radoslav Rusinov 
    Yes, there is no much practical sense to play with some query that cannot be usefull anywhere. But the case is specific and the behavior of the CBO is strange. Because that I tried to play with it and to understand how exactly the CBO is taking its decisions, without big success :)

Leave a Reply

      Convert to boldConvert to italicConvert to link

 



About me

  • » I'm Radoslav Rusinov
  • » From Sofia, Bulgaria
  • » Employer TechnoLogica Ltd.
  • » I am working as a Database Consultant in Sofia, Bulgaria. My main professional interests are in the database area and especially in the Oracle RDBMS, including database design, development, security and administration.
  • » The views expressed on this blog are my own and do not necessarily reflect the views of my employing company and its affiliates
  • » My profile

RSS 2.0 Feed

Search This Blog with Google

Search This Blog with Free Find


powered by FreeFind

Search This Blog with Technorati

Previous Posts

Archives

Articles & Presentations

Discover Bulgaria

Oracle News & Blogs Aggregators

Oracle Resources

Remote DBA

Oracle User Groups

Oracle Blogs

Oracle Forums

Security Resources

Professional CV

Blog Statistics

              

              

               Page Rank Checker