1 2 Previous Next 21 Replies Latest reply on Apr 3, 2015 7:01 PM by Solomon Yakobson

    literal replace by bind variable

    2923785

      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.

       

      when is define a view : vw_patient, with all the same columns as patient + a column : name || ' ' || firstname as fullname

      and i run a query :

      select * from vw_patient where fullname like :<filter> then tracing shows use of the index on name

       

      this is a dynamic sort of query , defined by the user ,

      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) .

       

      is there another fix for this, or is working with a view the only solution ? ( my dba does not want me to create a virtual column either )


        • 1. Re: literal replace by bind variable
          2923785

          the trace output for the first query is :

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

          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        2      0.00       0.00          0          0          0           0
          Execute      2      0.00       0.00          0          0          0           0
          Fetch        4      0.48       0.49          0      18198          2           2
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total        8      0.48       0.49          0      18198          2           2

          Misses in library cache during parse: 1
          Misses in library cache during execute: 1
          Optimizer mode: FIRST_ROWS
          Parsing user id: 443 
          Number of plan statistics captured: 2

          Rows (1st) Rows (avg) Rows (max)  Row Source Operation
          ---------- ---------- ----------  ---------------------------------------------------
                   1          1          1  TABLE ACCESS FULL PATIENT (cr=9099 pr=0 pw=0 time=2799 us cost=1801 size=3153528 card=12514)

          ********************************************************************************

           

          for the second query :

           

          SELECT vw_patient_11.*,ROWID FROM vw_patient_11  
          WHERE (   (patient_nr = :"SYS_B_0")
                  OR ( fullename LIKE
                          :"SYS_B_1"))

          call     count       cpu    elapsed       disk      query    current        rows
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          Parse        1      0.00       0.00          0          0          0           0
          Execute      1      0.01       0.00          0          0          0           0
          Fetch        2      0.00       0.01          2          6          0           1
          ------- ------  -------- ---------- ---------- ---------- ----------  ----------
          total        4      0.01       0.02          2          6          0           1

          Misses in library cache during parse: 1
          Optimizer mode: FIRST_ROWS
          Parsing user id: 443 
          Number of plan statistics captured: 1

          Rows (1st) Rows (avg) Rows (max)  Row Source Operation
          ---------- ---------- ----------  ---------------------------------------------------
                   1          1          1  TABLE ACCESS BY INDEX ROWID AFDA_PATIENTEN (cr=6 pr=2 pw=0 time=18765 us cost=6 size=120 card=2)
                   0          0          0     BITMAP CONVERSION FROM ROWIDS (cr=3 pr=2 pw=0 time=18707 us)
                   0          0          0      SORT ORDER BY (cr=3 pr=2 pw=0 time=18706 us)
                   0          0          0       INDEX RANGE SCAN PAT_VOLLEDIGE_NAAM_I (cr=3 pr=2 pw=0 time=18697 us cost=3 size=0 card=0)(object id 1602919)

          ********************************************************************************

          • 2. Re: literal replace by bind variable
            John Stegeman

            Ouch.

             

            First of all - cursor sharing.

            Second of all - using a concatenated set of columns in your where clause.

            Thirdly - you are comparing two different queries...

             

            I suppose you *may* get some benefit by creating a function-based-index on name  || ' ' || first_name

            • 3. Re: literal replace by bind variable
              JimmyOTNC

              why don't you create an index on name || ' ' || firstname?

              • 4. Re: literal replace by bind variable
                2923785

                @user2300390

                my mistake, there is an index on name ||' ' || firstname :

                but because of the replace by bind variable it is not used in the tracing.

                 

                didnt notice it because the index is not visible as such in the index properties in (quest sql navigator),

                but when generating the ddl for the table, i see that the pat_volledige_naam_i is such an index :

                 

                CREATE INDEX pat_volledige_naam_i ON patient

                  (

                    "NAME"||' '||"FIRSTNAME" ASC

                  )

                 

                CREATE INDEX pat_naam_nr ON patient

                  (

                    name                            ASC,

                    firstname                        ASC,

                    patient_nr                      ASC

                  )

                 

                neither index gets used in the first example

                • 5. Re: literal replace by bind variable
                  2923785

                  @ johnstegeman

                  1 : yes, thats my problem, how to fix it ?, was hoping bind peeking or /*+ bind_aware*/  would fix that, but it didnt ( or i did not use it correctly)

                  2 : what do you mean ? the original query was using concat instead of || , and resulted in following trace : WHERE  (CONCAT(CONCAT(Patient.Name , :"SYS_B_1" ), Patient.firstname) like :"SYS_B_2"))
                  so the space was also replace by bind variable, concatting without a space in between would solve my problem, but i cannot expect my users to know that they have to search for john doe , by typeing 'doejo%' instead of 'doe jo%'

                  3 : i guess so, but is was mentionning that second query because it is the only solution i have found so far , so i was saving those wanting to help me the time of mentionning this solution, and also proving that name || ' ' || firstname is supposed to use an index .

                  • 6. Re: literal replace by bind variable
                    Saubhik

                    but i cannot expect my users to know that they have to search for john doe , by typeing 'doejo%' instead of 'doe jo%'

                    Can you please explain with some example that what is the content of those two columns and What are the possible search patterns ?

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

                      When you use like with a bind variable the optimizer can not estimate the outcome since it does not know if your bind variable will contain a wild card. (%).

                      It will estimate 25% of the tabkes rows (if I remember that right, might be version dependend). It will not use an index. That is expected behaviour.

                      In your second case the inclusion of patient number does help, but it shoud be the first column in the index.

                      Some applications will always use like for comaprison, but we will get good search criteria.

                      Then under careful consideration it might be advisable to set "_like_with_bind_as_equality". Remember you must get an ok from Oracle support before setting an hidden parameter.

                      Consider what happens if a bad search criteria is used when the hidden parameter is set.

                      Using an index hint will always just effect one statement. A parameter on database level will affect all statements. But sometimes it is that what you want.

                      You can test if that will help you by using :

                       

                      alter session set "_like_with_bind_as_equality" =true;

                       

                      and then running your query.

                      • 8. Re: literal replace by bind variable
                        2923785

                        @saubhik

                         

                        the columns contains first and lastname of patients.

                        the user is registering something, and needs te select the relevant patient.

                        This will happen by specifiing the name,

                        if the name is simple they will just type part of it, and the system appends%

                        if the name is ambigous ( john or jon or johan , mutiple possibilities, and the user is not sure, they will themselves use % to cover that )

                        if the name is widely spred, they will also type part of the first name  ( 'doe j%' instead of doe%')

                        • 9. Re: literal replace by bind variable
                          2923785

                          @lothar flatz

                          i  get the same result in my tracing when i write my query without bind variable ( thx to cursor sharing),

                           

                          its oracle that replaces my constants with a bind variable  .


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

                            ok, but also then the hidden parameter should help. It does not matter where the bind variable is coming from. Try if it helps.

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

                              Lothar Flatz wrote:

                               

                              ok, but also then the hidden parameter should help. It does not matter where the bind variable is coming from. Try if it helps.

                              No parameter will help since the existing FBI index cannot be used when the expression "Patient.Name || :"SYS_B_1"  || Patient.Firstname" comes out of forced cursor sharing - the bind value in theory could be something different than the space so Oracle can't use the index, it's just an awkward side effect of forced cursor sharing, hence even a (correctly specified) index hint doesn't help here.

                               

                              Randolf

                              1 person found this helpful
                              • 12. Re: literal replace by bind variable
                                Randolf Geist

                                55b21ede-9939-420e-b84f-df2267bcf50c wrote:

                                 

                                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) .

                                 

                                is there another fix for this, or is working with a view the only solution ? ( my dba does not want me to create a virtual column either )

                                 

                                Well, you have solutions that work, either a view or a virtual column would at least enable the FBI index usage. You might want to tell your DBA that there is already a virtual column that was created for the FBI expression (check DBA/ALL/USER_TAB_COLS for columns called SYS_NC$...).

                                 

                                In principle, if you don't want to use the view approach, you would need to drop the existing FBI index, add a virtual column, and index the virtual column, because you can't have two virtual columns with the same expression on a single table.

                                 

                                Of course using proper bind variables and switching to cursor sharing exact would be great. You could even switch to exact cursor sharing on statement level using the CURSOR_SHARING_EXACT hint, but you probably would add too much overhead since you then end up with every single query as separate parent cursor and hard parse.

                                 

                                By the way, I'm a bit puzzled by the execution plan shown in the TKPROF output of your second example query (using an OR on NR and FULLNAME) - could you generate a plan output using DBMS_XPLAN.DISPLAY_CURSOR for that particular query and include the predicate section?

                                 

                                Randolf

                                1 person found this helpful
                                • 13. Re: literal replace by bind variable
                                  Randolf Geist

                                  55b21ede-9939-420e-b84f-df2267bcf50c wrote:

                                   

                                  is there another fix for this, or is working with a view the only solution ? ( my dba does not want me to create a virtual column either )

                                   

                                  It just occurred to me that you could use a "hack" with the existing FBI index and use the name of the hidden virtual column in your query as "virtual column" name - so something like WHERE SYS_NC00xx$ = .... Since you would need to change your application anyway with either solution (view or explicit virtual column) it's at least an option to consider, and you wouldn't have to change anything about the existing database objects.

                                   

                                  However, since the column name is generated, it's really more like a hack, although the generated column name is (somewhat) deterministic, Depending on the DDL applied to the table the generated column names could differ for different installations.

                                   

                                  Randolf

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

                                    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:


                                    SQL> create table employees as select * from hr.employees;

                                    Table created.

                                    SQL> create index employees_idx1
                                      2    on  employees(first_name || ' ' || last_name)
                                      3  /

                                    Index created.

                                    SQL> variable b varchar2(1)
                                    SQL> explain plan for
                                      2  select * from employees
                                      3  where first_name || :b || last_name like 'A%'
                                      4  /

                                    Explained.

                                    SQL> select * from table(dbms_xplan.display);

                                    PLAN_TABLE_OUTPUT
                                    ---------------------------------------------------------------------------------------------------------------
                                    Plan hash value: 1445457117

                                    -------------------------------------------------------------------------------
                                    | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
                                    -------------------------------------------------------------------------------
                                    |  0  | SELECT STATEMENT  |           |    5  |  665  |      3  (0)| 00:00:01 |
                                    |*  1 |  TABLE ACCESS FULL| EMPLOYEES |    5  |  665  |      3  (0)| 00:00:01 |
                                    -------------------------------------------------------------------------------

                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------

                                    PLAN_TABLE_OUTPUT
                                    ---------------------------------------------------------------------------------------------------------------

                                      1 - filter("FIRST_NAME"||:B||"LAST_NAME" LIKE 'A%')

                                    Note
                                    -----
                                      - dynamic sampling used for this statement (level=2)

                                    17 rows selected.

                                    SQL> explain plan for
                                      2  select * from employees
                                      3  where :b = ' '
                                      4    and first_name || ' ' || last_name like 'A%'
                                      5  union all
                                      6  select * from employees
                                      7  where nvl(:b,'X') != ' '
                                      8    and first_name || :b || last_name like 'A%'
                                      9  /

                                    Explained.

                                    SQL> select * from table(dbms_xplan.display);

                                    PLAN_TABLE_OUTPUT
                                    ---------------------------------------------------------------------------------------------------------------
                                    Plan hash value: 3050124404

                                    ------------------------------------------------------------------------------------------------
                                    | Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
                                    ------------------------------------------------------------------------------------------------
                                    |  0  | SELECT STATEMENT              |                |    10 |  1455 |     5  (60)| 00:00:01 |
                                    |  1  |  UNION-ALL                    |                |       |       |            |          |
                                    |*  2 |  FILTER                       |                |       |       |            |          |
                                    |  3  |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     5 |   790 |      2  (0)| 00:00:01 |
                                    |*  4 |    INDEX RANGE SCAN           | EMPLOYEES_IDX1 |     1 |       |      1  (0)| 00:00:01 |
                                    |*  5 |  FILTER                       |                |       |       |            |          |

                                    PLAN_TABLE_OUTPUT
                                    ---------------------------------------------------------------------------------------------------------------
                                    |*  6 |    TABLE ACCESS FULL          | EMPLOYEES      |     5 |   665 |      3  (0)| 00:00:01 |
                                    ------------------------------------------------------------------------------------------------

                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------

                                      2 - filter(:B=' ')
                                      4 - access("FIRST_NAME"||' '||"LAST_NAME" LIKE 'A%')
                                          filter("FIRST_NAME"||' '||"LAST_NAME" LIKE 'A%')
                                      5 - filter(' '<>NVL(:B,'X'))
                                      6 - filter("FIRST_NAME"||:B||"LAST_NAME" LIKE 'A%')

                                    PLAN_TABLE_OUTPUT
                                    ---------------------------------------------------------------------------------------------------------------

                                    Note
                                    -----
                                      - dynamic sampling used for this statement (level=2)

                                    26 rows selected.

                                    SQL>

                                     

                                    SY.

                                    1 2 Previous Next