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>
