Oracle Database SQL funciones no documentadas: LNNVL

La función LNNVL funciona como un semáforo FALSO/VERDADERO, evaluando la condición utilizada en el filtro de una sentencia, para devolver el predicado de la instrucción ejecutada.

Por ejemplo, si queremos devolver un mensaje, pero el mismo debe de darse sólo y sólo sí, la condición  de evaluación es incorrecta o falsa, podemos utilizar la siguiente sintaxis.

Esta función no puede evaluar condiciones compuestas que contengan los operadores AND, OR o BETWEEN.

También, puede ser utilizada internamente por la base de datos, para reescribir consultas.

[oracle@lab1 ~]$ sqlplus hr/hr

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 14 12:54:55 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select 'Aqui estoy' as Afirmacion

  2  from dual
  3  where lnnvl ( 1 = 2 );

AFIRMACION
----------
Aqui estoy

Elapsed: 00:00:00.01

Si cambiamos la condición de evaluación y la hacemos afirmativa, no obtendremos ningún mensaje de regreso con la ejecución de la sentencia.

SQL> select 'Aqui no estoy' as Afirmacion
  2  from dual
  3  where lnnvl ( 1 = 1 );

no rows selected

Elapsed: 00:00:00.01

Podemos utilizar no sólo condiciones simples, sino también, complejas, como operaciones aritméticas o evaluaciones de hileras de caracteres.

SQL> select 'Aqui estoy' as Afirmacion
  2  from dual
  3  where lnnvl ( (10/2) = 5 );

no rows selected


Elapsed: 00:00:00.01
SQL> select 'Aqui no estoy' as Afirmacion
  2  from dual
  3  where lnnvl ( (11/2) = 5);

AFIRMACION
-------------
Aqui no estoy

Elapsed: 00:00:00.00
SQL>

SQL> select 'Si funciona' as Afirmacion

  2  from dual

  3  where lnnvl ('ABC' = 'ABCD');

AFIRMACION

-----------

Si funciona

Elapsed: 00:00:00.01

Otro ejemplo utilizando funciones en el proceso de evaluación. Recordemos, que la condición evaluada, debe ser falsa, para devolver las columnas, valores o hileras establecidas en el "comando" ejecutado.

SQL> select 'Si funciona' as Afirmacion

  2  from dual

  3  where lnnvl (UPPER('oracle') = 'ORACLE');

no rows selected

Elapsed: 00:00:00.01

SQL> select 'Si funciona' as Afirmacion

  2  from dual

  3  where lnnvl ( LOWER('ORACLE') = 'ORACLE');

AFIRMACION

-----------

Si funciona

Elapsed: 00:00:00.01

SQL>

Otro ejemplo basado en la evaluación de valores de un campo de una tabla y devolviendo las columnas que no cumplen con la evaluación.

SQL> select * from countries;

CO COUNTRY_NAME                              REGION_ID

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

AR Argentina                                         2

AU Australia                                         3

BE Belgium                                           1

BR Brazil                                            2

CA Canada                                            2

CH Switzerland                                       1

CN China                                             3

DE Germany                                           1

DK Denmark                                           1

EG Egypt                                             4

FR France                                            1

IL Israel                                            4

IN India                                             3

IT Italy                                             1

JP Japan                                             3

KW Kuwait                                            4

ML Malaysia                                          3

MX Mexico                                            2

NG Nigeria                                           4

NL Netherlands                                       1

SG Singapore                                         3

UK United Kingdom                                    1

US United States of America                          2

ZM Zambia                                            4

ZW Zimbabwe                                          4

25 rows selected.

Elapsed: 00:00:00.06

SQL> SELECT region_id, country_name FROM countries

  2  where lnnvl ( substr(country_id,1,1) like 'Z');

 REGION_ID COUNTRY_NAME

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

         2 Argentina

         3 Australia

         1 Belgium

         2 Brazil

         2 Canada

         1 Switzerland

         3 China

         1 Germany

         1 Denmark

         4 Egypt

         1 France

         4 Israel

         3 India

         1 Italy

         3 Japan

         4 Kuwait

         3 Malaysia

         2 Mexico

         4 Nigeria

         1 Netherlands

         3 Singapore

         1 United Kingdom

         2 United States of America

23 rows selected.

Elapsed: 00:00:00.02

SQL>

Sin embargo al revisar el plan de ejecución de la sentencia utilizando la función "LNNVL"  o reemplazando la función por un "NOT LIKE"; a simple vista, se logra observar que la función, no es muy optimizada para devolver los resultados.

SQL> explain plan for

  2  select region_id, country_name from countries

  3  where lnnvl ( substr(country_id,1,1) like 'Z');

Explained.

Elapsed: 00:00:00.18

SQL> set linesize 200

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1253225340

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

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

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

|   0 | SELECT STATEMENT |                 |    25 |   375 |     1   (0)| 00:00:01 |

|*  1 |  INDEX FULL SCAN | COUNTRY_C_ID_PK |    25 |   375 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

   1 - filter(LNNVL(SUBSTR("COUNTRY_ID",1,1)='Z'))

13 rows selected.

Elapsed: 00:00:01.51

SQL> explain plan for

  2  select region_id, country_name from countries

  3  where substr(country_id,1,1) not like 'Z';

Explained.

Elapsed: 00:00:00.00

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

PLAN_TABLE_OUTPUT

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

Plan hash value: 1253225340

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

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

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

|   0 | SELECT STATEMENT |                 |     1 |    15 |     1   (0)| 00:00:01 |

|*  1 |  INDEX FULL SCAN | COUNTRY_C_ID_PK |     1 |    15 |     1   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

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

   1 - filter(SUBSTR("COUNTRY_ID",1,1)<>'Z')

13 rows selected.

Elapsed: 00:00:00.04

SQL>