Introduction

Performance is a critical consideration when choosing a database management system (DBMS). Many factors affect performance during SELECT, INSERT, UPDATE, and DELETE operations. These include:

• The speed and size of the persistent data store
• The structure(s) used to store the data
• The data access methods

As datasets grow larger, query performance becomes increasingly important.

Indexes are commonly used to improve query performance. An index associates the location of a row in the database with an ordered subset of the data and/or a derivative of the data. Performance benefits are realized when an index reduces the number of rows (or tuples) examined by the DBMS during query execution. Sometimes a query can be satisfied by examining the index only, without fetching any tuples from the table. For example, if you have an index on column c1 and you issue the query select c1 from t1 where c1 < 10 , then the index contains all of the information needed to satisfy the query.

Interestingly, the ANSI SQL standard does not specify how indexes are created, implemented, or maintained. This gives database vendors the freedom to implement indexes in their own way.

This article discusses Informix Dynamic Server's functional index feature. In order to grasp the concepts in this article, you should be familiar with basic database terminology and concepts, such as schema, tables, rows, columns, indexes, and extensibility. You should have some knowledge about basic Informix Dynamic Server (IDS) configuration and know how to start and stop the server and how to configure it using the ONCONFIG file. In addition, you should be familiar with basic SQL commands and know how to use dbaccess to execute SQL commands against the server.

The purpose of this article is to help you understand what functional indexes are and how they are used. In addition, you will learn how to create and use functional indexes and be given a list of things to consider before creating functional indexes.

Benefits of a functional index

An index stores column values in a sorted order. A functional index transforms the data in a column and stores the derived values in sorted order.

Suppose a table stores the names of employees in an organization, and the case of the names needs to be preserved. Then a query that requires a case-insensitive search, like the one below, must convert the data:

SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

If there is no index on name, the DBMS does a full table scan and applies the toUpper function on the name column for each tuple. Invoking the toUpper function is required in order to determine whether or not the tuple satisfies the query. Performance is negatively impacted when the table is large or when many sessions issue this type of query.

One way to eliminate the toUpper function is to store both the mixed case and the upper case names in the table. The application queries the case-insensitive column:

SELECT * FROM t1 WHERE ucname like 'ANTHONY % HOPKINS';

If there is no index on ucname, the DBMS still does a full table scan, but it need not do any further processing on the data to determine whether or not it satisfies the query. Though this improves performance, it is not an optimal solution because the table is larger and all applications that manipulate or access the data must include logic that handles ucname.

A better way to improve the performance of this query is to create a functional index on name:

    CREATE FUNCTION toUpper( name VARCHER(100) ) RETURNS VARCHAR(100)
        WITH (NOT VARIANT);

        RETURN upper( name );
    END FUNCTION;

    CREATE INDEX ucnameIndex ON t1 ( toUpper(name) );

Now when a query like this is executed, the DBMS can use the functional index ucnameIndex to determine which tuples satisfy the query. The DBMS fetches and returns only those tuples, as in the listing below:

SELECT * FROM t1 WHERE toUpper(name) LIKE 'ANTHONY % HOPKINS';

The DBMS automatically manages the functional index and applications need not include logic to manage the upper case data. The DBMS ensures that the index is always consistent with the data in the table by updating it during INSERT, UPDATE, and DELETE operations.

The in-depth discussion and examples that follow explore creating and using functional indexes and how to generate and examine query plans.

Various types of functional indexes

A functional index is created on values returned by a User Defined Routine (UDR). Here the term UDR is used generically to refer to functions that return a value. The UDR must be non-variant. This means that the UDR always returns the same value for given argument(s), and that the UDR does not modify the database or variable state. UDRs, like random number generators and current date/time functions, are variant and therefore cannot be used. You must explicitly specify NOT VARIANT when you define a UDR that is used for a functional index.

The UDR can be written as a Stored Procedure Language (SPL) function or as an external function using an external language (SQL, C/C++, or Java).

A functional index can be created on user defined types.

A functional index can use one of the following access methods:

• B-tree (default)
• R-tree
• User-defined secondary

An index can be created on a single column, on derived values of a single column, or on a combination of columns and derived values of columns. An index on more than one column is called a composite index. For example, below is a query that creates a composite index on the values in one column and on derived values in a second column:

			CREATE INDEX idx1 ON myTable( c1, f(c3) );

Limitations on functional indexes

A functional index cannot be a built-in algebraic, exponential, log, or hex function. If you need to define a functional index using a built-in function, then you must call that function from SPL or from an external language function.

A functional index cannot be created on a UDR that returns a large object. Using large objects as index keys is disallowed because they cannot, in general, be compared and sequenced. Note, however, that large objects can be passed to the UDR as arguments.

A UDR used by a functional index cannot have arguments that are collection data types. Collection data types include SETs, MULTISETs, and LISTs.

There are limitations on the number of arguments that can be passed to a UDR used by a functional index. These limitations vary depending on the Informix Data Server (IDS) release you are using and on the language in which your UDR is implemented. For example, as of IDS 9.4, you can pass up to 102 columns as arguments to a C UDR and up to 341 columns as arguments to a Java or SPL UDR. Check your documentation for more information.

Comparing functional indexes with non-functional indexes

There are not many differences between functional and non-functional indexes with respect to how you create and use them.

Index options like UNIQUE and CLUSTER, and access methods like B-Tree, R-Tree and user-defined access methods can apply to both functional and non-functional indexes. Similarly, both types of indexes can specify a FILLFACTOR and both can specify storage options that control where the index is created and whether or not it is fragmented. Both can specify an index operator class and both can be sorted in ascending or descending order.

The oncheck utility can be used to validate and repair both functional and non-functional indexes. For example, the following oncheck command validates an index called i1 on table tbl in database db: oncheck -ci db:tbl#i1. If oncheck reports a problem with the index, it can be repaired by specifying the oncheck -y option. Both functional and non-functional indexes can be repaired.

One user-visible difference between functional and non-functional indexes is how they are handled during the CREATE and DROP operations. You cannot specify the ONLINE keyword when you create or drop a functional index, as shown below:

CREATE INDEX ... ONLINE;  <== Not valid
DROP INDEX ... ONLINE;    <== Not valid
            

This means that an exclusive lock is always placed on the indexed table while a functional index is being created or dropped. All other users are prevented from accessing the table during this time.

Considerations before creating a functional index

There are costs associated with any index. These include resource use and execution time. All indexes require storage and all indexes require execution time to order and store their key values. A functional index incurs the additional cost of function execution. When a functional index is created, its associated function(s) must be executed for each row in the table. The function(s) must also be executed during INSERT and UPDATE operations.

It is always prudent to do a cost-benefit analysis on your database prior to creating a functional index. The amount of data in your tables and the type and frequency of queries executed against your data should figure into your analysis. If the tables are small or if the frequency of queries that would make use of the functional index is small, then you may not benefit from creating a functional index.

SQL EXPLAIN Files

Query plans are written to a SQL EXPLAIN file. The following table shows the location and name of this file.

Now that you have learned the definition of a functional index and have seen how and when to use them, below you will find some examples where a functional index is used. We provide step-by-step instructions on how you can implement the functional index yourself.

Examples

You have now learned the definition of a functional index and have seen how and when to use them. The following examples show specific uses. Each example provides step-by-step instructions so that you can implement the functional index yourself.

Example: Functional index on the area of a circle

This example shows how to create a functional index on the area of a circle. This is beneficial when the dataset is large and the frequency of queries that request the area of a circle is high.

1. First, create a table.

   CREATE TABLE circles ( radius FLOAT );

   
   

2. Next, create an SPL function that returns the area of a circle given its radius.

   CREATE FUNCTION circleArea( radius FLOAT ) RETURNS float WITH (NOT VARIANT); RETURN 3.14159 * radius * radius; END FUNCTION;

   
   

3. Create a functional index on the area of the circle.

   CREATE INDEX areaOfCircleIndex on circles( circleArea( radius ) );

   
   

4. Finally, execute a query that uses the functional index. The DBMS uses the index to determine which tuples satisfy the query and only fetches tuples that contribute to the query result.

   SELECT radius FROM circle WHERE circleArea( radius ) < 500;

   
   

Example: Functional index on the average RGB values of an image

In this example, we create functional indexes on the average color values of the pixels in a digital image. The UDR is written in C and can process simple color TIFF images. The following functions are provided:

• avgRGB: The average value over all pixels in the image
• avgRed: The average value of all red pixels in the image
• avgGreen: The average value of all green pixels in the image
• avgBlue: The average value of all blue pixels in the image

This example demonstrates how functional indexes can be used to select images with certain characteristics. When each functional index is created, its associated UDR is executed to pre-process the images and store the results as the index key. SELECT queries that specify image processing functions in their query filters now run faster because they use the index to determine whether or not each tuple satisfies the query.

Setup

This example was tested against IDS version 11.10 running on 64-bit Linux x86. Modify the instructions appropriately for different platforms. Refer to the makeinc.* files in $INFORMIXDIR/incl/dbdk for examples of appropriate compiler and linker options for your platform.

The source code and associated files for this example are included as downloads. See the Downloads section to download the code.

1. Set up the following environment variables:

   INFORMIXDIR the location of your IDS installation LD_LIBRARY_PATH add $INFORMIXDIR/lib add $INFORMIXDIR/lib/esql

   
   

2. Create the sbspace for the smart blobs. NOTE: The sbspace name must match the SBSPACENAME value in your ONCONFIG file. In this example, the name is sbsp2.

   Create an empty file called sbsp2 in the same directory as your root dbspace (the value for ROOTPATH in your ONCONFIG file specifies the name and location of your root dbspace).

   Change the file ownership to informix:informix.
   Change the file permissions to mode 660.

   As user informix, create the sbspace:

   onspaces -c -S sbsp2 -p <fullpath>/sbsp2 -o 0 -s 50000

   
   

3. Create a database and store images in it as smart blobs. The loadImages.sql script creates a database called imagedb and loads several images into it.

   cd <exampleDir> dbaccess - loadImages.sql

   
   

4. Compile the C UDR and create a shared library.

   cc -c -fPIC -I$INFORMIXDIR/incl/public imageUDR.c ld -shared -melf_x86_64 -Bsymbolic -o imageUDR.so imageUDR.o

   
   

5. Register the C UDR.

   Depending on how your IDS server is configured, you may need to have the EXTEND role in order to create the C UDRs. If your server is configured with IFX_EXTEND_ROLE set to OFF, then you can create the C UDRs regardless of whether or not you have the EXTEND role. If your server is configured with IFX_EXTEND_ROLE set to ON, then you will need the EXTEND role in order to create the UDRs.

   Your database administrator can grant the EXTEND role to you with this SQL command: GRANT EXTEND TO '<yourUser>'.

   You can check your server configuration settings with this command: onstat -c.

   Copy the shared library that contains the C UDRs to INFORMIXDIR:

   Copy imageUDR.so to $INFORMIXDIR/extend Change the file permissions to mode 755

   
   

   Register the C UDRs with Informix. Note that we specify HANDLESNULLS so that IDS allows our function to return a value when the column value is NULL.

   dbaccess imagedb - CREATE FUNCTION avgRGB( blob ) RETURNS INTEGER WITH ( NOT VARIANT, HANDLESNULLS ) EXTERNAL NAME '$INFORMIXDIR/extend/imageUDR.so( avgRGB )' LANGUAGE C; CREATE FUNCTION avgRed( blob ) RETURNS INTEGER WITH ( NOT VARIANT, HANDLESNULLS ) EXTERNAL NAME '$INFORMIXDIR /extend/imageUDR.so( avgRed )' LANGUAGE C; CREATE FUNCTION avgGreen( blob ) RETURNS INTEGER WITH ( NOT VARIANT, HANDLESNULLS ) EXTERNAL NAME '$INFORMIXDIR /extend/imageUDR.so( avgGreen )' LANGUAGE C; CREATE FUNCTION avgBlue( blob ) RETURNS INTEGER WITH ( NOT VARIANT, HANDLESNULLS ) EXTERNAL NAME '$INFORMIXDIR /extend/imageUDR.so( avgBlue )' LANGUAGE C;

   
   

6. Verify that the data was loaded and that the C UDRs can be accessed:

   dbaccess imagedb - Database selected. > select name, > avgRed(image) as avgRed, > avgGreen(image) as avgGreen, > avgBlue(image) as avgBlue, > avgRGB(image) as avgRGB > from images; ... name yellowbluestainedglass avgred 190 avggreen 190 avgblue 66 avgrgb 148 18 row(s) retrieved. >

   
   

Query

1. Issue a query that uses our UDRs. No indexes exist at this time. set explain on directs IDS to generate a file that shows the query plan.

   dbaccess imagedb - > set explain on; > select name, > avgRed(image) as avgRed, > avgGreen(image) as avgGreen, > avgBlue(image) as avgBlue, > avgRGB(image) as avgRGB > from images > where avgRGB(image) > 150 and avgBlue(image) > 160; name white avgred 255 avggreen 255 avgblue 255 avgrgb 255 name redblue avgred 255 avggreen 0 avgblue 255 avgrgb 170 name graygreentexture avgred 173 avggreen 173 avgblue 171 avgrgb 172 3 row(s) retrieved. >

   
   

2. Examine the SQL EXPLAIN output. This query plan shows a full table scan.

   QUERY: ------ select name, avgRed(image) as avgRed, avgGreen(image) as avgGreen, avgBlue(image) as avgBlue, avgRGB(image) as avgRGB from images where avgRGB(image) > 150 and avgBlue(image) > 160 Estimated Cost: 2 Estimated # of Rows Returned: 1 1) <owner>.images: SEQUENTIAL SCAN <== Full Table Scan Filters: (<owner>.avgblue(<owner>.images.image )> 160 AND <owner>.avgrgb(<owner>.images.image )> 150 ) UDRs in query: -------------- UDR id : 350 UDR name: avgblue UDR id : 347 UDR name: avgrgb UDR id : 347 UDR name: avgrgb UDR id : 350 UDR name: avgblue UDR id : 349 UDR name: avggreen UDR id : 348 UDR name: avgred

   
   

3. Create functional indexes on the functions provided by our C UDR and issue the query again:

   dbaccess imagedb - > create index avgRGBIndex on images( avgRGB( image ) ); > create index avgRedIndex on images( avgRed( image ) ); > create index avgGreenIndex on images( avgGreen( image ) ); > create index avgBlueIndex on images( avgBlue( image ) ); > set explain on; > select name, > avgRed(image) as avgRed, > avgGreen(image) as avgGreen, > avgBlue(image) as avgBlue, > avgRGB(image) as avgRGB > from images > where avgRGB(image) > 150 and avgBlue(image) > 160;

   
   

4. Examine the SQL EXPLAIN output. This query plan shows the use of a functional index.

   QUERY: ------ select name, avgRed(image) as avgRed, avgGreen(image) as avgGreen, avgBlue(image) as avgBlue, avgRGB(image) as avgRGB from images where avgRGB(image) > 150 and avgBlue(image) > 160 Estimated Cost: 1 Estimated # of Rows Returned: 2 1) <owner>.images: INDEX PATH <== Index Scan Filters: <owner>.avgblue(<owner>.images.image )> 160 (1) Index Keys: <owner>.avgrgb(image) (Serial, fragments: ALL) Lower Index Filter: <owner>.avgrgb(<owner>.images.image )> 150 UDRs in query: -------------- UDR id : 350 UDR name: avgblue UDR id : 347 UDR name: avgrgb UDR id : 347 UDR name: avgrgb UDR id : 350 UDR name: avgblue UDR id : 349 UDR name: avggreen UDR id : 348 UDR name: avgred UDR id : 347 UDR name: avgrgb

   
   

5. Use SQL directives to effect the query plan. Refer to your IDS documentation for complete information regarding optimizer-related SQL directives.

   Here, use the AVOID_INDEX directive to tell the query optimizer not to use your functional index.

   dbaccess imagedb - > set explain on; > select {+avoid_index(images avgrgbindex)} > * from images where avgrgb(image) > 100;

   
   

6. Examine the SQL EXPLAIN output. This query plan shows that the AVOID_INDEX directive is honored and shows a full table scan.

   QUERY: ------ select {+avoid_index(images avgrgbindex)} * from images where avgrgb(image) > 100 DIRECTIVES FOLLOWED: AVOID_INDEX ( images avgrgbindex ) DIRECTIVES NOT FOLLOWED: Estimated Cost: 2 Estimated # of Rows Returned: 6 1) <owner>.images: SEQUENTIAL SCAN <== Full Table Scan Filters: <owner>.avgrgb(<owner>.images.image )> 100 UDRs in query: -------------- UDR id : 347 UDR name: avgrgb

   
   

Example: Functional index using R-Tree access method

In this example we show you how to create a functional index that uses the R-Tree access method. An R-Tree data structure stores information about multi-dimensional data and is used for indexes on spatial or geographical data. Such indexes help to improve the performance of queries on spatial data.

This example assumes some familiarity with IDS DataBlade technology. In particular, we assume that you can install and configure the Spatial DataBlade for use. Refer to the IBM Informix Spatial DataBlade User's Guide for detailed information about this DataBlade.

The data for this example is geographical data about the cities and the tsunami inundation line in the state of Oregon. This geographical data is not included in the download for this article, but it is freely available at the Oregon Geospatial Enterprise Office website. Download the City Limits 2007 and Tsunami Inundation Line datasets from this site.

Suppose you want to find out what cities are in jeopardy should there be a tsunami. To find out, issue a spatial data query that reports all cities such that the bounding box of the tsunami line intersects the geographical city center. To satisfy this query, you must compute the geographical city center, so create a functional index that computes this value. Since the data is spatial, you need to specify the R-Tree access method for this index.

Some interesting facts about functional R-Tree indexes are:

• They are not bounding-box-only indexes; the leaf pages store the data objects themselves.
• The R-Tree is height-balanced:
  • All paths from the root page to any leaf page traverse the same number of levels.
  • All leave nodes are at the same level.

Setup

The spatial functions used in this example are provided by the Spatial DataBlade. Therefore, no additional code is needed in order to create and use the index.

This example was tested against IDS version 11.10 running on 64-bit Linux x86. Modify the instructions appropriately for different platforms.

1. Set up the following environment variables:

   INFORMIXDIR the location of your IDS installation PATH add $INFORMIXDIR/bin add $INFORMIXDIR/extend/<spatialDataBladeDir>/bin (see below)

   
   

2. Install the Spatial DataBlade. Installation instructions are avaible on the IDS 11.10 Information Center (see the link at the end of this article).

   The remaining instructions assume that you have installed Spatial DataBlade version 8.21 into the default location: $INFORMIXDIR/extend/spatial.8.21.xC1, where 'x' is a platform-specific letter. For 64-bit Linux x86, the default location is $INFORMIXDIR/extend/spatial.8.21.FC1. Modify the instructions appropriately for your environment.

3. Create the sbspace for the spatial data. NOTE: The sbspace name must match the SYSSBSPACENAME value in your ONCONFIG file. This example assumes that the name is syssbspace.

   Create an empty file called syssbspace in the same directory as your root dbspace (the value for ROOTPATH in your ONCONFIG file specifies the name and location of your root dbspace).

   Change the file ownership to informix:informix.
   Change the file permissions to mode 660.

   As user informix, create the sbspace:

   onspaces -c -S syssbspace -p <fullpath>/syssbspace -o 0 -s 100000

   
   

4. Create your database.

   dbaccess - - create database spatialdb;

   
   

5. Register the Spatial DataBlade.

   Use the blademgr utility to register the Spatial DataBlade. The Spatial DataBlade depends on the R-Tree DataBlade that ships automatically with IDS. You must register both DataBlades with your database.

   blademgr register ifxrltree.2.00 spatialdb register spatial.8.21.FC1 spatialdb list spatialdb DataBlade modules registered in database spatialdb: ifxrltree.2.00 spatial.8.21.FC1

   
   

6. Load the Spatial Data.

   Extract the files from the City Limits 2007 and the Tsunami Inundation Line downloads. You should have two directories named citylim_2007 and tsunami, respectively:

   ls citylim_2007 citylim_2007.dbf citylim_2007.prj citylim_2007.sbn citylim_2007.sbx citylim_2007.shp citylim_2007.shp.xml citylim_2007.shx ls tsunami PRJdevelopment.pdf tsunami.dbf tsunami.html tsunami.pdf tsunami.prj tsunami.shp tsunami.shp.xml tsunami.shx

   
   

   Note: on UNIX, the data file names are case sensitive and MUST be lower case. This is a requirement for Shapefile data. If the names are not correct, then the loadshp utility reports an error stating that it cannot find the data files.

   Correct configuration for the projection data is beyond the scope of this document. Since the loadshp utility gives an error if it tries to process the projection information without the proper configuration, hide or remove files with extension 'prj':

   rm citylim_2007/citylim_2007.prj rm tsunami/tsunami.prj

   
   

   Load the data into IDS using the loadshp utility included with the Spatial DataBlade. This utility resides in $INFORMIXDIR/extend/spatial.8.21.FC1/bin. loadshp creates two tables called cities and tsunami and loads the spatial data into the citylim and inundation columns, respectively.

   $INFORMIXDIDR/extend/spatial.8.21.FC1/bin/loadshp -o create -D spatialdb2 \ -l cities,citylim -f <fullpath>/citylim_2007 $INFORMIXDIDR/extend/spatial.8.21.FC1/bin/loadshp -o create -D spatialdb2 \ -l tsunami,inundation -f <fullpath>/tsunami

   
   

Query

1. Issue a query to ask our question. In this query,

   • st_intersects, st_centroid, and st_envelope are provided by the Spatial DataBlade
   • st_envelope computes the bounding box of each tsunami line, returning type st_multilinestring
   • st_centroid computes the center of each city, returning type st_multipolygon
   • st_intersects determines whether two geometries intersect each other

   There are no functional indexes defined at this time.

   dbaccess spatialdb - set explain on; select city_name from cities, tsunami where st_intersects( st_centroid( citylim ), st_envelope( inundation ) ); city_name Astoria Bandon Bay City Brookings Cannon Beach Cave Junction Coos Bay Coquille Depoe Bay Dunes City Elkton Falls City Florence Garibaldi Gearhart Glendale Gold Beach Grants Pass Lakeside Lincoln City Manzanita Myrtle Point Nehalem Newport North Bend Port Orford Powers Reedsport Riddle Rockaway Beach Seaside Siletz Tillamook Toledo Waldport Warrenton Wheeler Willamina Winston Yachats 40 row(s) retrieved.

   
   

2. Examine the SQL EXPLAIN output. This query plan shows full table scans. st_centroid() is computed for every tuple.

   QUERY: ------ select city_name from cities, tsunami where st_intersects( st_centroid( citylim ), st_envelope( inundation ) ) Estimated Cost: 25671 Estimated # of Rows Returned: 7018 1) <owner>.tsunami: SEQUENTIAL SCAN <== Full Table Scan 2) <owner>.cities: SEQUENTIAL SCAN <== Full Table Scan Filters: informix.st_intersects(informix.st_centroid(<owner>.cities.citylim ), informix.st_envelope(<owner>.tsunami.inundation )) NESTED LOOP JOIN UDRs in query: -------------- UDR id : 662 UDR name: st_intersects UDR id : 626 UDR name: st_envelope UDR id : 567 UDR name: st_centroid

   
   

3. Create a functional index that computes the city center.

   dbaccess spatialdb - create index citycenterindex on cities( st_centroid( citylim ) st_geometry_ops ) using rtree;

   
   

   Notes about this index:

   • Specify the R-Tree access method.
   • st_centroid() computes the city center.
   • st_geometry_ops specifies the operator class.

   An operator class is used in conjunction with the access method to specify the operators used for building the index and during query optimization. The operator class is typically a set of functions. For the Spatial DataBlade, the st_geometry_ops class consists of the following functions:

   ST_Contains() ST_Crosses() ST_Equals() SE_EnvelopesIntersect() ST_Intersects() SE_Nearest() SE_NearestBbox() ST_Overlaps() ST_Touches() ST_Within()

   
   

4. Execute the same query to ask our question again.

   dbaccess spatialdb - select city_name from cities, tsunami where st_intersects( st_centroid( citylim ), st_envelope( inundation ) );

   
   

   Note: The query optimizer considers using a functional index when the index expression is used in a relational expression or as an argument to one of the operator class functions. Since st_centroid() is a member of the operator class specified in our index (st_geometry_ops), the optimzer considers the index when generating the query plan.

   Note: Here is a query for which the optimizer cannot consider using the functional index. This is because st_distance() is not a member of the st_geometry_ops() operator class.

   select city_name from cities, tsunami where st_distance( st_centroid( citylim ), inundation ) < 5280;

   
   

5. Examine the SQL EXPLAIN output. This query plan shows an index scan using our functional index.

   Note: the query optimizer will not use the R-Tree index unless the statistics on the table are up to date. This means that you should either load your data prior to creating your index or update the statistics on your table after your data is loaded.

   QUERY: ------ select city_name from cities, tsunami where st_intersects( st_centroid( citylim ), st_envelope( inundation ) ) Estimated Cost: 12920 Estimated # of Rows Returned: 7018 1) <owner>.tsunami: SEQUENTIAL SCAN 2) <owner>.cities: INDEX PATH <== Index Scan (1) VII Index Keys: informix.st_centroid(citylim) (Serial, fragments: ALL) VII Index Filter: informix.st_intersects(informix.st_centroid(<owner>.cities.citylim), informix.st_envelope(<owner>.tsunami.inundation )) NESTED LOOP JOIN UDRs in query: -------------- UDR id : 662 UDR name: st_intersects UDR id : 626 UDR name: st_envelope UDR id : 567 UDR name: st_centroid UDR id : 567 UDR name: st_centroid

   
   

Conclusion

Functional indexes can be a powerful performance tuning tool. They improve performance by reducing the number of tuples fetched during query execution. They also improve performance by reducing the computation required during query execution.

A functional index can return any standard SQL type except for large objects. As well, a functional index can return a user-defined type. A functional index can specify different access methods that include B-Tree, R-Tree, and user-defined access methods.

The cost for storage and maintenance of a functional index can be high. You should complete a cost-benefit analysis of your system and create functional indexes only when warranted. Indexes won't help much when the size of your data is small or when the majority of queries issued against your database would generally not benefit from an index.

This article provides several samples of functional indexes. Hopefully by seeing the various ways that functional indexes are created and used, you will be able to use them to improve the performance of your query searches.

Back to top

Download

Information about download methods

Resources

Learn

• You can view the complete IDS version 11.1 documentation set at the IBM Informix Dynamic Server v11.1 Information Center
  
  
• In the Informix area on developerWorks , get the resources you need to advance your skills on IDS.
  
  
• Browse the technology bookstore for books on these and other technical topics.
  
  
• developerWorks Information Management zone: Learn more about Information Management. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  

Get products and technologies

• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  
• Download a free trial version of Informix Dynamic Server.
  
  
• Download IBM product evaluation versions and get your hands on application development tools and middleware products from Informix®, DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.
  
  

Discuss

• Participate in the discussion forum.
  
  
• Check out developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Benefits of a functional index
• Various types of functional indexes
• Limitations on functional indexes
• Comparing functional indexes with non-functional indexes
• Considerations before creating a functional index
• SQL EXPLAIN Files
• Examples
• Example: Functional index on the area of a circle
• Example: Functional index on the average RGB values of an image
• Example: Functional index using R-Tree access method
• Conclusion
• Download
• Resources
• About the authors
• Comments

Next steps from IBM

• Try: Informix Dynamic Server Enterprise Edition
• Demo: Use DB2 First Steps and the DB2 Control Center
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: Informix Dynamic Server Enterprise Edition

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers