Forum Stats

  • 3,741,519 Users
  • 2,248,442 Discussions
  • 7,861,847 Comments

Discussions

N before string literals in query causing performance issue

Vikram Shelke
Vikram Shelke Member Posts: 21
edited Jul 17, 2020 3:41AM in SQL & PL/SQL

Hi guys,

I have a query which is giving performance issue, taking long time to complete.

Oracle db version - 12c

Using Toad to execute queries.

My query is like

Select *

  From some_table

Where column_name in (:name);

But when I provide value for bind variable during run time the query becomes,

Select *

  From some_table

Where column_name in (N'abcd');

Above is the not actual query I am running, it is just the demo of what is happening with my big query.

Column data type is VARCHAR2 and not NVARCHAR.

Column has simple index on it and my guess is because of this N, the index is not getting used and hence the performance issue.

I search on net and can see few have got this type of problem but didn't find solution.

Also Google tells, N is national language character.

Any suggestions on this would be appreciated.

Tagged:
Vikram Shelke

Best Answer

  • mathguy
    mathguy Member Posts: 9,841 Gold Crown
    edited Jul 11, 2020 10:06AM Accepted Answer

    This is a Toad question, right? It's OK to ask here (on this website), since many Oracle developers do use Toad and they may have some ideas; but you should definitely ask on a Toad forum too.

    First, all your assumptions are correct. N' ... '  is the syntax for text literals in the national character set. And when values in the database character set (as in your table column) are compared to values in the national character set, the values in the database character set are implicitly converted to the national character set first; this is found in the Oracle documentation. And, indeed, that causes the index not to be used.

    Before you jump to a band-aid solution like wrapping your bind variable within TO_CHAR or CAST(... as VARCHAR2(n)), it would be best to find out why Toad is doing what it is doing, and fix it. I haven't used Toad much, and I haven't used it in over three years, so I won't be much help with that; but Toad must have some reason it thinks it must present your literals in the national character set. Find out why Toad thinks that, and fix it.

    Converting or casting your bind variable to the database character set may cause some issues. Suppose, for example, that Toad was correct in that the string you supplied as the bind variable includes characters that are not supported by your database character set. So Toad had no choice but to give the bind variable in the national character set. If then you convert it to the database character set, some characters may be mapped in a way that you did not intend. (That is - possibly - why Toad would choose not to do that itself in the first place.) You might, then, get false positives - rows that should be filtered out, but aren't.

    The general assumption (which may very well be false in specific cases) is that the characters supported by the database character set are a subset of the characters in the national character set - that is, all db characters are in the national character set too, but possibly not the other way around. (That is very likely why Oracle converts from db character set to national character set and not the other way around.)

    If in your query you are 100% certain that the bind values you give are always supported in your db character set, then go ahead and wrap the bind variable within TO_CHAR(...) or CAST(... as varchar2(4000)) or whatever. That may also be OK in other cases. For example, suppose that the national character set supports some accented letters, which the db character set doesn't. If the column stores values like Tiriac or Nastase, should they match the bind variable Țiriac or Năstase? If you are lucky the accented characters are mapped to the non-accented characters you expect, in which case - even if Toad was right to present the bind variable in the national character set - what you do want, in fact, is the variable mapped to the db character set. (Although, in this case, the correct solution would be for the column itself to be in the national character set, and if you want to ignore accents when you compare strings, you can do that explicitly, using accent-insensitive sorting. Mapping to a smaller character set with no accented characters and hoping that the mappings are what you need them to be is also a band-aid.)

    However, if your investigations show that Toad is right to present the bind variable in the national character set (as opposed to this being a mistake caused by a wrong setting in Toad, for example), then you should leave the bind variable alone, or be prepared for the query to give wrong results. If performance is a problem, you have one possible solution: create an index on TO_NCHAR(column_name). Then Oracle will be able to use THIS index when it must compare column values to a variable in the national character set.

    Vikram ShelkeVikram Shelke

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,778 Bronze Crown
    edited Jul 11, 2020 1:51AM

    What is the database NLS characterset?

    What characterset is toad using?

    Perhaps the difference between client and database characterset setting is forcing the datatype conversion.

    To stop the datatype conversion try wrapping the bind variable in TO_CHAR(:name).

    Is this conversion a toad thing? How is the bind variable actually declared?

    Also, do you get the same results if you run the same query in sqlplus?

  • Paulzip
    Paulzip Member Posts: 8,370 Blue Diamond
    edited Jul 11, 2020 6:34AM

    It doesn't surprise me it's slow. I'd imagine every source value is having to be converted to your NLS_NCHAR_CHARACTERSET character set for the predicate comparison. So probably UTF-8 to UTF-16.

    Cast the parameter to varchar2 and performance should be fine.

    Vikram ShelkeVikram Shelke
  • mathguy
    mathguy Member Posts: 9,841 Gold Crown
    edited Jul 11, 2020 10:06AM Accepted Answer

    This is a Toad question, right? It's OK to ask here (on this website), since many Oracle developers do use Toad and they may have some ideas; but you should definitely ask on a Toad forum too.

    First, all your assumptions are correct. N' ... '  is the syntax for text literals in the national character set. And when values in the database character set (as in your table column) are compared to values in the national character set, the values in the database character set are implicitly converted to the national character set first; this is found in the Oracle documentation. And, indeed, that causes the index not to be used.

    Before you jump to a band-aid solution like wrapping your bind variable within TO_CHAR or CAST(... as VARCHAR2(n)), it would be best to find out why Toad is doing what it is doing, and fix it. I haven't used Toad much, and I haven't used it in over three years, so I won't be much help with that; but Toad must have some reason it thinks it must present your literals in the national character set. Find out why Toad thinks that, and fix it.

    Converting or casting your bind variable to the database character set may cause some issues. Suppose, for example, that Toad was correct in that the string you supplied as the bind variable includes characters that are not supported by your database character set. So Toad had no choice but to give the bind variable in the national character set. If then you convert it to the database character set, some characters may be mapped in a way that you did not intend. (That is - possibly - why Toad would choose not to do that itself in the first place.) You might, then, get false positives - rows that should be filtered out, but aren't.

    The general assumption (which may very well be false in specific cases) is that the characters supported by the database character set are a subset of the characters in the national character set - that is, all db characters are in the national character set too, but possibly not the other way around. (That is very likely why Oracle converts from db character set to national character set and not the other way around.)

    If in your query you are 100% certain that the bind values you give are always supported in your db character set, then go ahead and wrap the bind variable within TO_CHAR(...) or CAST(... as varchar2(4000)) or whatever. That may also be OK in other cases. For example, suppose that the national character set supports some accented letters, which the db character set doesn't. If the column stores values like Tiriac or Nastase, should they match the bind variable Țiriac or Năstase? If you are lucky the accented characters are mapped to the non-accented characters you expect, in which case - even if Toad was right to present the bind variable in the national character set - what you do want, in fact, is the variable mapped to the db character set. (Although, in this case, the correct solution would be for the column itself to be in the national character set, and if you want to ignore accents when you compare strings, you can do that explicitly, using accent-insensitive sorting. Mapping to a smaller character set with no accented characters and hoping that the mappings are what you need them to be is also a band-aid.)

    However, if your investigations show that Toad is right to present the bind variable in the national character set (as opposed to this being a mistake caused by a wrong setting in Toad, for example), then you should leave the bind variable alone, or be prepared for the query to give wrong results. If performance is a problem, you have one possible solution: create an index on TO_NCHAR(column_name). Then Oracle will be able to use THIS index when it must compare column values to a variable in the national character set.

    Vikram ShelkeVikram Shelke
  • Vikram Shelke
    Vikram Shelke Member Posts: 21
    edited Jul 14, 2020 2:18AM

    Database character set is as below -

    Capture.PNG

    It does not matter I am using toad or sqlPlus.

    Actually in bind variable I can have multiple comma separated string values hence it is like WHERE column_name IN (:bind_var) where bind_var can be 'abcd','xyz','pqr' so can't use to_char on bind_var.

    Thanks for your suggestions.

  • cormaco
    cormaco Member Posts: 1,588 Bronze Crown
    edited Jul 14, 2020 3:41AM
    WHERE column_name IN (:bind_var) where bind_var can be 'abcd','xyz','pqr'

    This doesn't work like that:

    variable bind_var varchar2(20)execute :bind_var := q'['abcd','xyz','pqr']';print :bind_varselect * from dualwhere 'xyz' in (:bind_var)

    Output:

    PL/SQL procedure successfully completed.BIND_VAR--------------------------------------------------------------------------------'abcd','xyz','pqr'no rows selected

    You can read here how to use dynamic IN lists:

    https://oracle-base.com/articles/misc/dynamic-in-lists

    Vikram ShelkeVikram Shelke
  • Vikram Shelke
    Vikram Shelke Member Posts: 21
    edited Jul 17, 2020 3:41AM

    Thanks for sharing this.

    You are correct, this way does not work in Toad or Sql Developer but that is how I pass values in input field of one of the reporting tool that I am using to run this query.

Sign In or Register to comment.