SQL> set autotrace traceonly
SQL> SET serveroutput ON
–let’s create a table that contains country names and codes from apex environment
SQL> CREATE TABLE t_func AS select * from flows_020100.wwv_flow_countries t;
Table created.
–then write a query that searches brazil 🙂
SQL> SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
Execution Plan
———————————————————-
Plan hash value: 175459743
—————————————————————————-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————-
| 0 | SELECT STATEMENT | | 1 | 146 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T_FUNC | 1 | 146 | 2 (0)| 00:00:01 |
—————————————————————————-
Predicate Information (identified by operation id):
—————————————————
1 – filter(UPPER(“COUNTRY_NAME”) LIKE ‘BRAZIL’)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
48 recursive calls
0 db block gets
12 consistent gets
1 physical reads
0 redo size
482 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
–then let’s create an function based index on column country_name
SQL> CREATE INDEX t_func_idx ON t_func(upper(country_name));
Index created.
–write the query again
SQL> SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
Execution Plan
———————————————————-
Plan hash value: 3533916538
——————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
——————————————————————————————
| 0 | SELECT STATEMENT | | 1 | 146 | 1 (0)| 00:00:01
| 1 | TABLE ACCESS BY INDEX ROWID| T_FUNC | 1 | 146 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | T_FUNC_IDX | 1 | | 1 (0)| 00:00:01 |
——————————————————————————————
Predicate Information (identified by operation id):
—————————————————
2 – access(UPPER(“COUNTRY_NAME”) LIKE ‘BRAZIL’)
Note
—–
– dynamic sampling used for this statement
Statistics
———————————————————-
28 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
482 bytes sent via SQL*Net to client
384 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> spool off
In the first example, query made a FULL TABLE SCAN and cost was 2.
In the second example, our index worked and it made an INDEX RANGE SCAN by using T_FUNC_IDX and cost was 1. These tables that we query on are small tables. The difference will be increase in larger tables…
tested in Oracle XE Edition 10g Release 2 (10.2)
And Code :
SET serveroutput ON
set autotrace traceonly
alter session set QUERY_REWRITE_ENABLED=TRUE;
alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
drop table t_func;
drop index t_func_idx;
CREATE TABLE t_func AS select * from flows_020100.wwv_flow_countries t;
SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
CREATE INDEX t_func_idx ON t_func(upper(country_name));
SELECT * FROM t_func WHERE upper(country_name) LIKE ‘BRAZIL’;
A advised and different perspective to function based index is 🙂
http://oracle-unix.blogspot.com/2007/07/performance-tuning-how-to-make-like.html
Akdora's Blog 