1

oracle-pro-c has recommended using indicator variables as "NULL flags" attached to host variables. As per documentation, we can associate every host variable with an optional indicator variable (short type). For example:

short indicator_var;
EXEC SQL SELECT xyz INTO :host_var:indicator_var
    FROM ...;

We can also alternatively use NVL as documented in https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm, for example, as:

EXEC SQL SELECT NVL(TO_CHAR(xyz), '') INTO :host_var
        FROM ...;

Which one is better in terms of performance?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Dr. Debasish Jana
  • 6,980
  • 4
  • 30
  • 69
  • 2
    Using the indicator variables is probably 'more efficient' but probably almost immeasurably so. It does mean less SQL to parse by the server. It does mean fewer function executions. I'd go with 'the SQL is simpler' as being a major benefit. – Jonathan Leffler Aug 12 '16 at 03:16
  • @JonathanLeffler agreed with simpler SQL with less parsing logic, I'm getting slowness with indicator variables, and looking for options to have any better solution, without indicator variables, it's faster, but if some col is null valued, then? – Dr. Debasish Jana Aug 12 '16 at 05:04
  • I don't know enough about Oracle Pro\*C to be able to help further. My prior comment (not an answer, be it noted) was based on generic experience with other DBMS than Oracle — and that's why it is not an answer. In my experience, you're best off coding the null handling in the client (Pro\*C) program than making the DBMS deal with it all. I believe there are some issues with Oracle and empty strings vs null strings, but again, I don't have the direct experience of Oracle work to confirm that. – Jonathan Leffler Aug 12 '16 at 05:07

2 Answers2

2

Ah, Pro*C. It's a while back, over 20 years, but I think my memory serves me well here.

Using the indicator variables will be better in terms of performance, for two reasons:

  • The SQL is simpler, so there is less parsing and fewer bytes will be transferred over the network to the database server.
  • In Oracle in general, a "Null" value is encoded in 0 bytes. An empty string contains a length (N bytes) and storage (0 bytes). So a NULL value is encoded more efficiently in the returned result set.

Now in practice, you won't notice the difference much. But you asked :-)

fork2execve
  • 1,561
  • 11
  • 16
0

In my experience NVL was much slower than indicator variables especially if nested (yes you can nest them) for INSERT or UPDATE of fields. it was a long time ago and I don't remember exactly the circumstances but I remember the performance gain was real. On select it was not that obvious but using indicator variables allows also to detect cases when truncation happen.

If you use VARCHAR or UVARCHAR columns there is a third options to detect NULL/Empty strings in Oracle. The len field will be set to 0 and it means the value is empty. As Oracle does not distinguish between NULL and length 0 strings it is more or less the same.

Patrick Schlüter
  • 11,394
  • 1
  • 43
  • 48