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