Database Reference
In-Depth Information
EODA@ORA12CR1> begin
2 dbms_stats.gather_table_stats
3 (user,'EMP',cascade=>true);
4 end;
5 /
PL/SQL procedure successfully completed.
We now have an index on the UPPER value of a column. Any application that already issues case-insensitive
queries like the following will make use of this index, gaining the performance boost an index can deliver:
EODA@ORA12CR1> set autotrace traceonly explain
EODA@ORA12CR1> select *
2 from emp
3 where upper(ename) = 'KING';
Execution Plan
----------------------------------------------------------
Plan hash value: 3831183638
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)...
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 110 | 2 (0)...
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMP | 2 | 110 | 2 (0)...
|* 2 | INDEX RANGE SCAN | EMP_UPPER_IDX | 2 | | 1 (0)...
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(UPPER("ENAME")='KING')
Before this feature was available, every row in the EMP table would have been scanned, uppercased, and
compared. In contrast, with the index on UPPER(ENAME) , the query takes the constant KING to the index, range scans a
little data, and accesses the table by rowid to get the data. This is very fast.
This performance boost is most visible when indexing user-written functions on columns. Oracle 7.1 added the
ability to use user-written functions in SQL, so we could do something like this:
SQL> select my_function(ename)
2 from emp
3 where some_other_function(empno) > 10
4 /
This was great because we could now effectively extend the SQL language to include application-specific
functions. Unfortunately, however, the performance of the preceding query was a bit disappointing at times. Say the
EMP table had 1,000 rows in it. The function SOME_OTHER_FUNCTION would be executed 1,000 times during the query,
once per row. In addition, assuming the function took one-hundredth of a second to execute, this relatively simple
query now takes at least ten seconds.
Search WWH ::




Custom Search