Akdora’s Blog

Programming, Oracle, Life, Fun

A Function Based Example August 10, 2007

Filed under: Oracle — Akdora @ 4:41 pm
Tags: , , , ,

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s