Skip to Main Content
  • Questions

  • Functional Index ignore/skips call to function while actual function used in SQL Query

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mohammed Imran.

Asked: September 30, 2016 - 11:46 am UTC

Last updated: October 04, 2016 - 11:19 am UTC

Version: 11.2.0.3.0 - 64bit

Viewed 1000+ times

You Asked

Hi,

I have problem using functional index, below is what I did so far

1. Created a function(in package PKG) with 2 input parameters
Func1(param1 NUMBER,param2 NUMBER) return varchar2;
2. Using the function above created a functional index on table with two of its columns.
CREATE INDEX INDX1 ON TAB1
(PKG.Func1(col1, col2));
3. Updated table stats.

Now when I use the function in a sql query though explain plan shows index INDX1 used but no rows are returned(always). Further investigation and debugging showed that for single row(that is table tab1 has 1 row) the query runs fine and gives 1 row as output but for more that 1 row the function itself is not getting called.

Can you please explain this!

NOTE: Both deterministic and non deterministic function gave the same results.

Thanks,
Imran.

and Connor said...

Beside the normal key length issue, as long as you get the datatypes right you should be fine


SQL> create or replace
  2  package pkg is
  3    function Func1(param1 NUMBER,param2 NUMBER) return varchar2 deterministic;
  4  end;
  5  /

Package created.

SQL>
SQL>
SQL> create or replace
  2  package body pkg is
  3    function Func1(param1 NUMBER,param2 NUMBER) return varchar2 deterministic is
  4    begin
  5      return to_char(param1+param2);
  6    end;
  7  end;
  8  /

Package body created.

SQL>
SQL> drop table t purge;

Table dropped.

SQL> create table t as
  2  select * from dba_objects;

Table created.

SQL>
SQL> col x new_value y
SQL> select object_id + data_object_id x from user_objects
  2  where object_name = 'T';

         X
----------
    343008

1 row selected.

SQL>
SQL> create index ix1 on t ( pkg.func1(object_id, data_object_id) );
create index ix1 on t ( pkg.func1(object_id, data_object_id) )
                                             *
ERROR at line 1:
ORA-01450: maximum key length (6398) exceeded


SQL>
SQL> create index ix1 on t ( substr(pkg.func1(object_id, data_object_id),1,30) );

Index created.

SQL>
SQL> set autotrace on explain
SQL> select owner, object_name
  2  from t
  3  where pkg.func1(object_id, data_object_id) = ltrim('&&y');
old   3: where pkg.func1(object_id, data_object_id) = ltrim('&&y')
new   3: where pkg.func1(object_id, data_object_id) = ltrim('    343008')

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
MCDONAC                        T

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2932790928

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |   992 | 55552 |    75   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |   992 | 55552 |    75   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX1  |   397 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("PKG"."FUNC1"("OBJECT_ID","DATA_OBJECT_ID")='343008')
   2 - access(SUBSTR("PKG"."FUNC1"("OBJECT_ID","DATA_OBJECT_ID"),1,30)='343008')

SQL>
SQL> select owner, object_name
  2  from t
  3  where substr(pkg.func1(object_id, data_object_id),1,30) = ltrim('&&y');
old   3: where substr(pkg.func1(object_id, data_object_id),1,30) = ltrim('&&y')
new   3: where substr(pkg.func1(object_id, data_object_id),1,30) = ltrim('    343008')

OWNER                          OBJECT_NAME
------------------------------ ----------------------------------------
MCDONAC                        T

1 row selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2932790928

--------------------------------------------------------------------------------------------
| Id  | Operation                           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |      |   992 | 48608 |    75   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T    |   992 | 48608 |    75   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IX1  |   397 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access(SUBSTR("PKG"."FUNC1"("OBJECT_ID","DATA_OBJECT_ID"),1,30)='343008')

SQL>
SQL>


Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Package Function using global variables

Mohammed Imran Pasha, October 03, 2016 - 12:21 pm UTC

Thanks for the response.

I agree with your comments about datatypes. But my scenario is different, I should have mentioned this in my original question.

If package from your example is changed to
create or replace
 package pkg is
 g_var char(1);
   function Func1(param1 NUMBER,param2 NUMBER) return varchar2 deterministic ;
   Procedure Init;
 end;
 /
 create or replace
  package body pkg is
    
    Procedure Init Is
    Begin
        g_var := 'Y';
    End Init;
  
    function Func1(param1 NUMBER,param2 NUMBER) return varchar2  deterministic is
    begin
       IF g_var = 'Y' THEN
        return 'TRUE';
       else
        return 'FALSE'; 
       end if;
    end;
  end;

drop index ix1;

create index ix1 on t (pkg.func1(object_id, data_object_id));


Now, when I execute the query after calling Init procedure in session no rows are returned. I think this behavior is due to pre calculated values while indexed is created.

 select owner, object_name,object_id, data_object_id
  from t
  where  pkg.func1(object_id, data_object_id) = 'TRUE';

Could you please suggest me how can we use functional index in this case. Also my original function logic is a bit more complex then example above which requires package global variable to be used.

Thanks,
Imran.
Connor McDonald
October 04, 2016 - 11:19 am UTC

Having a global variable is similar to (say) having an index on something like "sysdate". Obviously it *cant* work, because the values in the index might be correct one day, and invalid the next.

You could possibly have one index which stores all the values for when global_var = 'Y' and one for when global_var = 'N'. But even that sounds not a particular sensible move.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library