How to use NVL2 function in informix index

Question

I want to create index using NVL2 function:

create index i_name on PERSON (
   NVL2(date1,date2,date3)
);

this print error:

Routine (nvl2) can not be resolved.

What I miss ? Why this doesnt work

ceinmart · Accepted Answer

The use of NVL2 isn't available because builtin functions aren't acceptable for functional indexes. Here is the basic rules to use a functional index at Informix (v12.10) : (quoted from the manual)

Important: The database server imposes the following restrictions on the user-defined routines (UDRs) on which a functional index is defined:

The arguments cannot be column values of a collection data type.

The function cannot return a large object (including built-in types BLOB, BYTE, CLOB, and TEXT).

The function cannot be a VARIANT function.

The function cannot include any DML statement of SQL.

The function must be a UDR, rather than a built-in function. However, you can create an SPL wrapper that calls and returns the value from a built-in function of SQL.

Here I'm copying an effective example which you able to run at your environment for test.
Maybe need some little adjustment, because I run this at version 11.70 and set my own dbspaces ....

$ cat test.sql
database sysutils;

-- showing my version of informix...
select dbinfo('version','full') from sysmaster:sysdual;

-- creating a database for this test
drop database if exists teste  ;
create database teste in dbadat1dbs with buffered log;

-- registering excompat datablate just to enable the use of the dbms_random function for this example.
execute function sysbldprepare('excompat.1.0', 'create');

-- create my test table
drop table if exists mytables;
create table if not exists mytables ( id int, name char(20), created date, modified date, invalidated date) ;

-- including some records...
insert into mytables(id,name,created) select tabid, tabname , created from sysmaster:systables;

-- creating some random data for this example
update mytables set modified = dbinfo('utc_to_datetime',dbms_random_random()) where name matches '*[xmfz]*';
update mytables set invalidated = dbinfo('utc_to_datetime',dbms_random_random()) where modified is not null;


-- trying use the nvl2... which is not possible because it is a builtin function
create index i1_mytables on mytables ( nvl2(modified, created, invalidated)) ;

-- creating my own function, non variant mode.
drop function if exists my_nvl2_date;
create function if not exists  my_nvl2_date (  dt date , is_null date, not_null date )
  returning date
with (not variant)
return nvl2(dt, is_null, not_null) ;
end function
;

-- create the index
create index i1_mytables on mytables ( my_nvl2_date(modified, created, invalidated)) ;

set explain file to 'test.out' ;
select first 20 * from mytables;
-- here, the index will be used
select first 20 * from mytables where my_nvl2_date(modified, created, invalidated) >= '01/01/2000';
-- this will not use the index because don't match exactly the parameters
select first 20 * from mytables where my_nvl2_date(modified, invalidated, created) >= '01/01/2000';

Here is the execution. Pay attention to explain file , where show when the index is used and isn't.

$ dbaccess -e - test.sql
database sysutils;
Database selected.



-- showing my version of informix...
select dbinfo('version','full') from sysmaster:sysdual;

(constant)

IBM Informix Dynamic Server Version 11.70.FC6

1 row(s) retrieved.



-- creating a database for this test
drop database if exists teste  ;
Database dropped.


create database teste in dbadat1dbs with buffered log;
Database closed.


Database created.



-- registering excompat datablate just to enable the use of the dbms_random function for this example.
execute function sysbldprepare('excompat.1.0', 'create');

(expression)

           0

1 row(s) retrieved.



-- create my test table
drop table if exists mytables;
Table dropped.


create table if not exists mytables ( id int, name char(20), created date, modified date, invalidated date) ;
Table created.



-- including some records...
insert into mytables(id,name,created) select tabid, tabname , created from sysmaster:systables;
276 row(s) inserted.



-- creating some random data for this example
update mytables set modified = dbinfo('utc_to_datetime',dbms_random_random()) where name matches '*[xmfz]*';
113 row(s) updated.


update mytables set invalidated = dbinfo('utc_to_datetime',dbms_random_random()) where modified is not null;
113 row(s) updated.




-- trying use the nvl2... which is not possible because it is a builtin function
create index i1_mytables on mytables ( nvl2(modified, created, invalidated)) ;
  674: Routine (nvl2) can not be resolved.
Error in line 26
Near character position 75


-- creating my own function, non variant mode.
drop function if exists my_nvl2_date;
Routine dropped.


create function if not exists  my_nvl2_date (  dt date , is_null date, not_null date )
  returning date
with (not variant)
return nvl2(dt, is_null, not_null) ;;
end function
;
Routine created.

;

-- create the index
create index i1_mytables on mytables ( my_nvl2_date(modified, created, invalidated)) ;
Index created.



set explain file to 'test.out' ;
Explain set.


select first 20 * from mytables;

         id name                 created    modified   invalidated

          1 systables            05/06/2013
          2 syscolumns           05/06/2013 22/10/1942 13/04/2034
          3 sysindices           05/06/2013
          4 systabauth           05/06/2013
          5 syscolauth           05/06/2013
          6 sysviews             05/06/2013
          7 sysusers             05/06/2013
          8 sysdepend            05/06/2013
          9 syssynonyms          05/06/2013 15/03/1981 19/05/1963
         10 syssyntable          05/06/2013
         11 sysconstraints       05/06/2013
         12 sysreferences        05/06/2013 15/08/1912 09/06/1956
         13 syschecks            05/06/2013
         14 sysdefaults          05/06/2013 09/01/1959 01/03/1943
         15 syscoldepend         05/06/2013
         16 sysprocedures        05/06/2013
         17 sysprocbody          05/06/2013
         18 sysprocplan          05/06/2013
         19 sysprocauth          05/06/2013
         20 sysblobs             05/06/2013

20 row(s) retrieved.


-- here, the index will be used
select first 20 * from mytables where my_nvl2_date(modified, created, invalidated) >= '01/01/2000';

         id name                 created    modified   invalidated

          2 syscolumns           05/06/2013 22/10/1942 13/04/2034
          9 syssynonyms          05/06/2013 15/03/1981 19/05/1963
         12 sysreferences        05/06/2013 15/08/1912 09/06/1956
         14 sysdefaults          05/06/2013 09/01/1959 01/03/1943
         25 sysfragments         05/06/2013 29/10/1951 11/02/1953
         28 sysfragauth          05/06/2013 06/12/2024 03/11/1948
         30 sysxtdtypes          05/06/2013 20/03/1909 03/05/1975
         32 sysxtddesc           05/06/2013 23/04/1933 23/07/1961
         35 syslogmap            05/06/2013 01/03/2015 30/05/2020
         37 sysxtdtypeauth       05/06/2013 16/03/1911 26/12/2037
         40 sysams               05/06/2013 02/06/1940 19/08/2000
         41 systabamdata         05/06/2013 21/04/2032 17/02/1907
         45 systracemsgs         05/06/2013 14/04/2006 09/11/1936
         49 sysxasourcetypes     05/06/2013 03/04/1994 28/05/1972
         50 sysxadatasources     05/06/2013 09/09/1967 29/11/1978
         51 sysseclabelcomponent 05/06/2013 26/04/1979 21/09/1984
         52 sysseclabelcomponent 05/06/2013 10/10/2035 16/06/2021
         54 syssecpolicycomponen 05/06/2013 23/08/1962 20/05/1983
         55 syssecpolicyexemptio 05/06/2013 09/01/1966 04/12/2027
         57 sysseclabelnames     05/06/2013 09/03/1948 03/09/1937

20 row(s) retrieved.


-- this will not use the index because don't match exactly the parameters
select first 20 * from mytables where my_nvl2_date(modified, invalidated, created) >= '01/01/2000';

         id name                 created    modified   invalidated

          1 systables            05/06/2013
          2 syscolumns           05/06/2013 22/10/1942 13/04/2034
          3 sysindices           05/06/2013
          4 systabauth           05/06/2013
          5 syscolauth           05/06/2013
          6 sysviews             05/06/2013
          7 sysusers             05/06/2013
          8 sysdepend            05/06/2013
         10 syssyntable          05/06/2013
         11 sysconstraints       05/06/2013
         13 syschecks            05/06/2013
         15 syscoldepend         05/06/2013
         16 sysprocedures        05/06/2013
         17 sysprocbody          05/06/2013
         18 sysprocplan          05/06/2013
         19 sysprocauth          05/06/2013
         20 sysblobs             05/06/2013
         21 sysopclstr           05/06/2013
         22 systriggers          05/06/2013
         23 systrigbody          05/06/2013

20 row(s) retrieved.
Database closed.

Here is the explain file with the 3 selects executed. Observe only the second select used the index.

$ cat test.out

QUERY: (OPTIMIZATION TIMESTAMP: 03-21-2014 21:43:22)
------
select first 20 * from mytables

Estimated Cost: 0
Estimated # of Rows Returned: 276

  1) informix.mytables: SEQUENTIAL SCAN


Query statistics:
-----------------

The final cost of the plan is reduced because of the FIRST n specification in
 the query.

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                mytables

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     20         276       38         00:00.00   1


QUERY: (OPTIMIZATION TIMESTAMP: 03-21-2014 21:43:22)
------
select first 20 * from mytables where my_nvl2_date(modified, created, invalidated) >= '01/01/2000'

Estimated Cost: 1
Estimated # of Rows Returned: 92

  1) informix.mytables: INDEX PATH

    (1) Index Name: informix.i1_mytables
        Index Keys: informix.my_nvl2_date(modified,created,invalidated)   (Serial, fragments: ALL)
        Lower Index Filter: informix.my_nvl2_date(informix.mytables.modified ,informix.mytables.created ,informix.mytables.invalidated )>= 01/01/2000

UDRs in query:
--------------
    UDR id  :   540
    UDR name:   my_nvl2_date
    UDR id  :   540
    UDR name:   my_nvl2_date

Query statistics:
-----------------

The final cost of the plan is reduced because of the FIRST n specification in
 the query.

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                mytables

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     20         92        20         00:00.00   1


QUERY: (OPTIMIZATION TIMESTAMP: 03-21-2014 21:43:22)
------
select first 20 * from mytables where my_nvl2_date(modified, invalidated, created) >= '01/01/2000'

Estimated Cost: 2
Estimated # of Rows Returned: 92

  1) informix.mytables: SEQUENTIAL SCAN

        Filters: informix.my_nvl2_date(informix.mytables.modified ,informix.mytables.invalidated ,informix.mytables.created )>= 01/01/2000

UDRs in query:
--------------
    UDR id  :   540
    UDR name:   my_nvl2_date

Query statistics:
-----------------

The final cost of the plan is reduced because of the FIRST n specification in
 the query.

  Table map :
  ----------------------------
  Internal name     Table name
  ----------------------------
  t1                mytables

  type     table  rows_prod  est_rows  rows_scan  time       est_cost
  -------------------------------------------------------------------
  scan     t1     20         92        38         00:00.00   3

How to use NVL2 function in informix index

Answers (1)

Related Questions