1 2 Previous Next 21 Replies Latest reply on Apr 3, 2015 7:01 PM by Solomon Yakobson Go to original post
      • 15. Re: literal replace by bind variable
        2923785


        @randolf,

        nevermind that second query,

        i cleaned up the trace plan (removed the patient_nr)

        but forgot to cleanup the where statment .

         

        it was irrelevant for my problem .

         

        thx for ur answer tho

        • 16. Re: literal replace by bind variable
          Lothar Flatz

          Yes, damn it, that is right. No easy way round due cursor sharing.

          • 17. Re: literal replace by bind variable
            Lothar Flatz

            Well, just for the fun of it, you could create an index and avoid any constant.

            by using select * from patient where rpad(name,length(name) +(length(name)/length(name)))||firstname like :b1

             

            Here a simulation with cursor sharing force:

             

            create index i on emp(rpad(ename,length(ename) +(length(ename)/length(ename)))||sal);

             

            select * from emp where rpad(ename,length(ename) +(length(ename)/length(ename)))||sal > 'B'

             

            Header 1

            --------------------------------------------------------------------------------------------

            | Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |

            --------------------------------------------------------------------------------------------

            |   0 | SELECT STATEMENT                    |      |       |       |     2 (100)|          |

            |   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP  |    14 |   728 |     2   (0)| 00:00:01 |

            |*  2 |   INDEX RANGE SCAN                  | I    |    14 |       |     1   (0)| 00:00:01 |

            --------------------------------------------------------------------------------------------

             

            Predicate Information (identified by operation id):

            ---------------------------------------------------

             

               2 - access("EMP"."SYS_NC00011$">:SYS_B_0)

            • 18. Re: literal replace by bind variable
              Randolf Geist

              Lothar Flatz wrote:

               

              Well, just for the fun of it, you could create an index and avoid any constant.

              by using select * from patient where rpad(name,length(name) +(length(name)/length(name)))||firstname like :b1

               

              Very creative! Looks weird but should work :-)

               

              Randolf

              • 19. Re: literal replace by bind variable
                Randolf Geist

                Solomon Yakobson wrote:

                 

                As others already noted:


                Patient.Name || :"SYS_B_1"  || Patient.Firstname like :"SYS_B_2"

                 

                isn't same as:


                Patient.Name || ' '  || Patient.Firstname like :"SYS_B_2"


                What you could do is something like:

                 

                That's a nice idea but I don't think it will work with cursor_sharing = force because the space literal will again be replaced with a bind, hence the index can't be used in the branch you can use it in your example.

                 

                Randolf

                • 20. Re: literal replace by bind variable
                  Randolf Geist

                  2923785 wrote:

                   

                  Hello,

                  when I execute following query ( oracle 11.2.3)

                  select * from patient where name || ' ' || firstname like :<filter>

                  then my tracing shows a full table scan, even tho an index exists on name.

                   

                  so i prefer not to use the index hint , since i dont know beforehand that the user will search on name . ( i tried it , and it did not even make a difference) .

                  Well, I just played around a little bit in 11.2.0.1, and actually the FBI can get used when I force it via index hint.

                   

                  So possibly what I stated was wrong that index can't be used. In principle my assumption should be correct but Oracle seems to know "internally" that the replaced literal is a space. If I change the expression to use a different literal the index isn't used, so some built-in "intelligence" seems to enable the index usage.

                   

                  This means Lothar could be right and it's just a cardinality estimate issue (and your attempt to use an index hint was incorrectly specified).

                   

                  Then you could try it with the parameter Lothar mentioned to see if the index gets used automatically.

                   

                  Randolf

                  • 21. Re: literal replace by bind variable
                    Solomon Yakobson

                    I guess I missed bind variable comes as a result of  cursor_sharing = force is the first place, so I confused chicken for egg .

                     

                    SY.

                    1 2 Previous Next