Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Upper Case Indexes

Re: Upper Case Indexes

From: Mike Kennedy <hkennedy_at_bea.com>
Date: Mon, 25 Sep 2000 15:19:58 -0600
Message-Id: <10630.117812@fatcity.com>


A note from Oracle:

Doc ID:
 Note:69492.1
 Subject:
 Oracle8i -
 Function-based
 index
 Type:
 BULLETIN
 Status:
 PUBLISHED  Content Type:
 TEXT/PLAIN
 Creation Date:
 12-APR-1999
 Last Revision
 Date:
 22-JUN-2000
 Language:
 USAENG  Introduction


 While previous releases of Oracle8 only allowed for indexes to be  created on one or more columns. With Oracle8i, one can have  function-based index. A table may contain columns for COST and  REVENUE, but users may often query on REVENUE - COST (i.e. profit).  A function-based index could be built on the function (REVENUE - COST),  which would greatly improve the performance of queries on the profit  function.

 Example(s)


 The following index:
 CREATE INDEX idx ON table_1 (a + b * (c - 1), a, b);  then Oracle can use it when processing queries such as this:  SELECT a FROM table_1 WHERE a + b * (c - 1) < 100;

 The following index:
 CREATE INDEX uppercase_idx ON emp (UPPER(empname));  can facilitate processing queries such as this:  SELECT * FROM emp WHERE UPPER(empname) = 'RICHARD';

 The function-based index option is only available

 with the Enterprise
 Edition (EE). It is not available in the standard edition.  You can check this in v$option view. See [NOTE:100911.1].

 Prerequisites


  1. The following initialization parameters must be defined:
    • QUERY_REWRITE_INTEGRITY must be set to TRUSTED
    • QUERY_REWRITE_ENABLED must be set to TRUE
    • COMPATIBLE must set to 8.1.0.0.0 or a greater value
  2. The user must be granted CREATE INDEX and QUERY REWRITE, or CREATE ANY INDEX and GLOBAL QUERY REWRITE.
  3. To use a function-based index:
    • Cost Based Optimizer (CBO) must be used to optimize the query (Eg: The table is analyzed after the index is created or a hint supplied)
    • The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.
  4. Rule-based optimization never uses function-based indexes. Thus the optimizer_mode should NOT be set to RULE. If set to CHOOSE then the table must be analyzed.
  5. If any user-written function is used in a function-based index, it must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future. Also the index owner needs the EXECUTE privilege on the function used to define a function-based index.

 Typical use of function-based index


 A common use of function-based indexes is in the creation of

 case-insensitive indexes, which can be implemented by creating an index  on the upper-case function applied to a character column. However,  function-based indexes can be used for many purposes specific to the  requirements of a data warehouse.

 Another use, specific to data-warehousing, suppose the users of a data  warehouse might want to analyze how their customer's age affects their  buying patterns. Rather than querying on specific ages, the users of a  data-warehousing are more likely to query on age categories, such as  '10-19','20-29', and so on. A function-based index could be created  for this age-category function, which would allow users to query directly  on age categories.

 Other use of function based index


 Another particularly useful application for function-based indexes is  to provide a simple form of data-compression. In some data warehouse  applications, a large table may contain many small attributes. For  example, each call-detail record in a telecommunications warehouse may  have hundreds of attributes. Many of these attributes are small - one  or two characters long, stored as a coded field. In some cases, a  significant space savings can be realized by taking a set of these  small attributes, and compressing, and/or concatenating them into a  single database column. Previously, the problem associated with this  technique was that there was no efficient way to query this  multi-attribute column. Function-based indexes solve this difficulty.  A function-based index can be built on each attribute

 of a compressed
 field. Queries with predicates on these compressed fields are able to  utilize the function-based index. Thus, query-performance is not  detrimentally affected by the compression.  .


 Please note that the cost optimizer may decide that sort operation is cheaper  than index. If you want to enforce the index usage, add the /*+INDEX(tab  ind)*/ hint. But note also that the hint will never force the optimizer to use an
 execution path which creates illegal results.

--

"Joseph S. Testa" wrote:

> dont forget to:
>
> Note:
>
>             You must set the QUERY_REWRITE_ENABLED session parameter to true to
> enable function-based indexes for queries. If
>             QUERY_REWRITE_ENABLED is false, then function-based indexes are not
> used for obtaining the values of an expression in
>             the function-based index. However, function-based indexes can still
> be used for obtaining values in real columns.
>             QUERY_REWRITE_ENABLED is a session-level and also an instance-level
> parameter.
>
> joe
>
> Oliver Artelt wrote:
>
> > How about:
> >
> > Function-based indexes defined on UPPER(column_name) or LOWER(column_name)
> > can facilitate case-insensitive searches. For
> > example, the following index:
> >
> > CREATE INDEX uppercase_idx ON emp (UPPER(empname));
> >
> > can facilitate processing queries such as this:
> >
> > SELECT * FROM emp WHERE UPPER(empname) = 'RICHARD';
> >
> > check it out,
> > oli
> >
> > On Mon, 25 Sep 2000, Stuart Houlston wrote:
> > > All,
> > >
> > > I have a problem with searching a name column within the database where the
> > > case of the data must be preserved. This name is then used in the search
> > > criteria of many queries in the application.
> > >
> > > The data is
> > >
> > > Table DEALERS
> > > Column NAME
> > > Data
> > > BMW Freecars Ltd
> > > BMW FREECARS ltd
> > > BMW FreeCars LTD
> > >
> > > Table DEALERS will contain several thousand entries and an index is placed
> > > on the NAME column to assist with lookups.
> > > These are all valid entries but the developers want to be able to search on
> > >
> > > SELECT *
> > > FROM DEALERS
> > > WHERE  NAME = 'BMW FREECARS LTD'
> > >
> > > This should bring back  the 3 rows above.
> > >
> > > I could of course use the UPPER function around the column name but this
> > > will invalidate the use of my carefully designed index strategy and cause
> > > the query to take a thousand years to complete (well a few seconds anyway).
> > >
> > > The only suggestion that I have come up with is to have a second column
> > > called UPPER_NAME on the table that hold all the values from NAME in
> > > UPPERCASE. This could then be indexed and used in the search criteria. This
> > > column will be maintained by the use of database triggers. A bit messy but
> > > it should work.
> > >
> > > Any other helpful hints and tips would be gratefully accepted.
> > >
> > >
> > > Many Thanks
> > >
> > > Stuart
> >
> > --
> > ---
> >
> > Oliver Artelt, System- und Datenbankadministration
> > ---------------------------------------------------------------
> >   cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
> > telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
> >    email: oli@cubeoffice.de # web: http://www.cubeoffice.de
> > ---------------------------------------------------------------
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Oliver Artelt
> >   INET: oli_at_md.transnet.de
> >
> > Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> > San Diego, California        -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from).  You may
> > also send the HELP command for other information (like subscribing).
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Joseph S. Testa
>   INET: teci_at_oracle-dba.com
>
> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
> San Diego, California        -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing).
Received on Mon Sep 25 2000 - 16:19:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US