1、- 1 - 外文原文 Oracle is a very good database, but it cannot read your mind. Therefore, if you want Oracle to use a function-based index for a query, you must use the exact same text for the function in the query as you do in your function-based index. For instance, the following query, although similar
2、 to the one shown in Figure 10-11, will not use the function-based index: SELECT * FROM SH.CUSTOMERS S WHERE CUST_LAST_NAME = UPPER(Ruddy) Although the selection criteria logically dictates that CUST_LAST_NAME should be the result of an UPPER() function, you cannot expect Oracle to deduce this on it
3、s own. This simple example makes sense, but for more complex function-based indexes, you will have to remember to use the same implementation in your SQL statements as you did when you defined the function-based index. You can avoid this potential caveat by creating a view based on a function-based
4、index. The view will create a column that uses the same function as the function-based index, in effect adding the invisible function-based index column to the table. To implement this for the function-based index we have been working with, you could run the following code, included as script CH10_CREATE_FBI_VIEW.SQL: CREATE OR REPLACE VIEW SH.FBI_VIEW AS SELECT cust_id, UPPER(cust_last_name) upper_last_name, cust_first_name, cust_last_name, cust_street_address FROM SH.CUSTOMERS; With this vie
