DISQUS

Pete_s · 3 years ago

Oracle uses this function in the 'default' index on materialized views for fast refresh - which in the case of DW Mviews is a little odd as I would not expect NULLS in my fact summaries!

Eddie Awad · 3 years ago

Thanks for the extra tip Pete.

Michael Friedman · 3 years ago

This would be a lot more interesting if it did not just act like nvl(x,'FF'). What's the point? You could have FF in your data.

Eddie Awad · 3 years ago

Michael, I guess you have just found out why it's not documented. There are many other ways to compare two nulls, so there is really no need to use sys_op_map_nonnull. However, I will always be interested in the "undocumented", just to satisfy my curiosity.

Jonathan Lewis · 3 years ago

sys_op_map_nonnull() is a little more subtle than nvl(x,'0xff').

The thing it maps to is an internally stored FF, and I don't think you can get this actual value stored for any type except RAW. Then, to deal with the problem of comparing raws when using sys_op_mapnonnull(), sys_op_mapnonnull(ff) returns a two-byte value which is 00ff, so sys_op_map_nonnull(ff) != ff.

Eddie Awad · 3 years ago

Jonathan, thanks for clarifying the subtlety of sys_op_map_nonnull.

Here is a quick test that I think demonstrates Michael's point that sys_op_map_nonnull (col1) is the same as nvl(col1,'FF'):

<pre> EDDIE@XE> CREATE TABLE t (
2 col1 VARCHAR2(10),
3 col2 VARCHAR2(10));

Table created.

EDDIE@XE> INSERT INTO t
2 VALUES (NULL,
3 NULL
4 )
5 /

1 row created.

EDDIE@XE> INSERT INTO t
2 VALUES ('FF',
3 'FF'
4 )
5 /

1 row created.

EDDIE@XE> INSERT INTO t
2 VALUES ('Eddie',
3 'Awad'
4 )
5 /

1 row created.

EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
2 NVL (col2, 'I am null') col2
3 FROM t
4 WHERE nvl(col1,'FF') = nvl(col2,'FF')
5 /

COL1 COL2
---------- ----------
I am null I am null
FF FF

EDDIE@XE> SELECT NVL (col1, 'I am null') col1,
2 NVL (col2, 'I am null') col2
3 FROM t
4 WHERE sys_op_map_nonnull (col1) = sys_op_map_nonnull (col2)
5 /

COL1 COL2
---------- ----------
I am null I am null
FF FF
</pre>

So, if sys_op_map_nonnull (col1) did not act like nvl(col2,'FF'), Oracle would not return the row with the varchar2 value FF in both col1 and col2.

I think the explanation here is that sys_op_map_nonnull ('FF') equals sys_op_map_nonnull ('FF') but sys_op_map_nonnull ('FF') does not equal 'FF':

<pre> EDDIE@XE> SELECT *
2 FROM dual
3 where sys_op_map_nonnull ('FF') = sys_op_map_nonnull ('FF')
4 /

D
-
X

EDDIE@XE> SELECT *
2 FROM dual
3 where sys_op_map_nonnull ('FF') = 'FF'
4 /

no rows selected
</pre>

Michael Friedman · 3 years ago

Whoa.

Am I confused?

I thought sys_op_map_nonnull('any non-null value') was supposed be 'any non-null value'.

I can also guarantee to you that

<pre>select *
from dual
where sys_op_map_nonnull(null) = 'FF'</pre>

returns a row.

Now I think it's doing something more subtle - it's doing a mapping of anything you put in it so

<pre>select *
from dual
where sys_op_map_nonnull(null) = sys_op_map_nonnull('FF')</pre>

will not return a row... need to test.

Laurent Schneider · 2 years ago

I tried
<pre>
select * from dual where sys_op_map_nonnull(null) = 'FF' ;
D
-
X
</pre>
It does return a row on my 10.2.0.3