Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question.

Asked: May 18, 2001 - 11:44 am UTC

Last updated: November 20, 2020 - 9:50 am UTC

Version: 8.1.X

Viewed 50K+ times! This question is

You Asked

I'd like to use function based indexes on index organized tables.
Can you explain why a function based index isn't allowed on a
primary key? Is there a workaround?

Thanks

and Tom said...

Well, a PRIMARY KEY isn't allowed to be a function, but you can most certainly create a function based index on an IOT's primary key.

If you need the primary key to be some function of the COLUMNS of the IOT then what you need to do is add a column to the IOT and use a trigger to maintain the value of that COLUMN. It'll be the same exact thing as having a function based primary key (it will be a function based primary key!)

to show you can have an FBI on an IOT's primary key:



tkyte@TKYTE816> create table emp
2 (empno , ename primary key, job, mgr, hiredate, sal, comm, deptno )
3 organization index
4 as
5 select * from scott.emp;
Table created.

tkyte@TKYTE816> update emp set ename = initcap(ename);
14 rows updated.

tkyte@TKYTE816> commit;
Commit complete.

tkyte@TKYTE816> create index emp_upper_idx on emp(upper(ename));
Index created.

tkyte@TKYTE816> analyze table emp compute statistics
2 for table
3 for all indexed columns
4 for all indexes;
Table analyzed.

tkyte@TKYTE816> alter session set QUERY_REWRITE_ENABLED=TRUE;
Session altered.

tkyte@TKYTE816> alter session set QUERY_REWRITE_INTEGRITY=TRUSTED;
Session altered.

tkyte@TKYTE816> set autotrace on explain
tkyte@TKYTE816> select ename, empno, sal from emp where upper(ename) = 'KING';

ENAME EMPNO SAL
---------- ---------- ----------
King 7839 5000


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=31)
1 0 INDEX (RANGE SCAN) OF 'SYS_IOT_TOP_31966' (UNIQUE) (Cost=1 Card=1
2 1 INDEX (RANGE SCAN) OF 'EMP_UPPER_IDX' (NON-UNIQUE) (Cost=1 Card=1)



Rating

  (145 ratings)

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

Comments

-

A reader, October 14, 2002 - 11:30 am UTC

Tom,
Suppose i have a function based index on a column. How do i know which column and what is the function used, from the database. I do not get these information user_ind_columns. It gives me a SYS_ name for the column. Also, it is not available in all_source.

From where do i get that information assuming the fuction/column for the index was undocumented.

regards




Tom Kyte
October 14, 2002 - 3:55 pm UTC



USER_IND_EXPRESSIONS




function based index

mo, January 28, 2003 - 6:21 pm UTC

Tom:

I read you section on function based indexes and got the impression that by creating an index on a user-defined function I may be able to improve the speed of a query except I do not see how it happens. I have a table with 1000 stock items on it. Then I have a query that uses a function several times (different warehouse) to compute different total of each item in each warehouse (3 warehouses) and how much each consumed per month. How would the index help me in this case since I have to read every single stock item (is it because the index has pre-calculated the value) , run the fucntion and return the result. The report is taking 25 seconds for the web page but I may get it faster with this. Do you think I need to create indexes on each function and make the functions determinstic.


FOR x in ( select class1,stock_number1,description1,
INS1,MSE1,MSW1,total,used_this_fy1,used_prev_fy1,
used_per_month1,round(total/used_this_fy1,0) months_left1,
resp1 from

( select class class1,stock_number stock_number1,
description description1,
INS INS1, MSE MSE1, MSW MSW1, INS+MSE+MSW total,
used_this_fy used_this_fy1,
used_prev_fy used_prev_fy1,
used_per_month used_per_month1,
resp resp1
from
(select class,a.stock_number,
substr(description,1,50) description,
compute_qty_org('INS',a.stock_number) INS,
compute_qty_org('CA11',a.stock_number) MCE,
compute_qty_org('VA11',a.stock_number) MCW,
compute_used_this_fy(a.stock_number) used_this_fy,
compute_used_prev_fy(a.stock_number) used_prev_fy,
compute_used_per_month(a.stock_number) used_per_month,
b.person_responsible resp
from stock_item a,stock_warehouse b
where a.stock_number = b.stock_number(+) and
a.stock_number <> '99999')


Tom Kyte
January 28, 2003 - 7:59 pm UTC

let me see the code for compute_qty_org and compute_used_this_fy and compute_used_per_month.

odd

A reader, May 12, 2003 - 5:58 am UTC

Hi

in </code> http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/sql_1016.htm#28529 <code>

it says this

When you need to use SQL functions on filters or join predicates, do not use them on the columns on which you want to have an index; rather, use them on the opposite side of the predicate, as in the following statement:

TO_CHAR(numcol) = varcol
rather than
varcol = TO_CHAR(numcol)

Now.... I dont see the difference at all. May you explain a bit?

Tom Kyte
May 12, 2003 - 7:55 am UTC

that does look like a typo. they meant


use:

indexed_number_column = to_number( varcol )

rather then

to_char( indexed_number_column ) = varcol



two function indexes on a table

Fubar, July 30, 2003 - 4:27 pm UTC

I have a table with two function indexes on first and last name with an upper. If I combine the indexes or use the index with another index in the table my session crashes with end of file communication ora-3113. Any thoughts.

Tom Kyte
July 30, 2003 - 7:17 pm UTC

thoughts = 3113 always means "tar"

my FBI doesn't in effect

lancelot, August 21, 2003 - 10:51 pm UTC

I don't understand why the "second" not use the FBI, but the "thrid" use the FBI?

SQL> show parameter query

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
query_rewrite_enabled                boolean TRUE
query_rewrite_integrity              string  trusted
SQL> show parameter compatible

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
compatible                           string  8.1.0

SQL> show user 
USER &#20026;"SYSTEM" 
SQL> create table dept (deptno number, dname varchar2(10),loc varchar2(10)); 

Table created. 

SQL> insert into dept values(30, 'SALES', 'CHICAGO'); 

1 row created. 


SQL> create index upper_dept 
2 on dept(UPPER(dname)); 

Index created.


first&#65306; 
SQL> set autotrace on 
SQL> select * 
2 from dept 
3 where UPPER(dname) = 'SALES'; 

DEPTNO DNAME LOC 
---------- ---------- ---------- 
30 SALES CHICAGO 


Execution Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE 
1 0 TABLE ACCESS (FULL) OF 'DEPT' 




Statistics 
---------------------------------------------------------- 
0 recursive calls 
4 db block gets 
1 consistent gets 
0 physical reads 
0 redo size 
480 bytes sent via SQL*Net to client 
425 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
0 sorts (memory) 
0 sorts (disk) 
1 rows processed 


second&#65306; 
SQL> analyze table dept compute statistics; 

Table analyzed. 

SQL> analyze index upper_dept compute statistics; 

Index analyzed. 

SQL> select * 
2 from dept 
3 where UPPER(dname) = 'SALES'; 

DEPTNO DNAME LOC 
---------- ---------- ---------- 
30 SALES CHICAGO 


Execution Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=14) 
1 0 TABLE ACCESS (FULL) OF 'DEPT' (Cost=1 Card=1 Bytes=14) 




Statistics 
---------------------------------------------------------- 
5 recursive calls 
4 db block gets 
5 consistent gets 
0 physical reads 
0 redo size 
480 bytes sent via SQL*Net to client 
425 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
3 sorts (memory) 
0 sorts (disk) 
1 rows processed 


third&#65306; 
SQL> select count(*) 
2 from dept 
3 where UPPER(dname) = 'SALES'; 

COUNT(*) 
---------- 
1 


Execution Plan 
---------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=5) 
1 0 SORT (AGGREGATE) 
2 1 INDEX (RANGE SCAN) OF 'UPPER_DEPT' (NON-UNIQUE) (Cost=1 
Card=1 Bytes=5) 





Statistics 
---------------------------------------------------------- 
0 recursive calls 
0 db block gets 
1 consistent gets 
0 physical reads 
0 redo size 
364 bytes sent via SQL*Net to client 
425 bytes received via SQL*Net from client 
2 SQL*Net roundtrips to/from client 
1 sorts (memory) 
0 sorts (disk) 
1 rows processed



SQL>  

Tom Kyte
August 22, 2003 - 8:37 am UTC

because DEPT is tiny,

because DEPT is a single block.


because the second plan would have involved:

1) index range scan
2) table access by rowid

which would take more io then

1) full scan table

in this case.

the last one used the index cause the index could be used to answer the entire question without hitting the table itself.

oracle version lead to different result

lancelot, August 21, 2003 - 11:36 pm UTC

my friend get the same result with me .
In oracle817, the "second" and the "third" return the different result.
But in Oracle920, the "second" and the "third" return the same result , that they all use FBI.

Tom Kyte
August 22, 2003 - 8:39 am UTC

it is a function of the amount of data.

on a set of 4 rows -- using an index is not always the wisest thing to do.

remember and keep repeating:

indexes are not goodness incarnate
table scans are not evil
(repeat above two lines until you believe it)

Case Construct in Function based index

A reader, September 09, 2003 - 5:16 am UTC

Hi Tom,

Can a case structure be used in function based index?
I am using following case construct in most of my queries.
(CASE WHEN CP_ACC_PROPERTY_HDR.ACCPRPTY_EFFECTIVE_DATE < 500000
THEN (20000000 + CP_ACC_PROPERTY_HDR.ACCPRPTY_EFFECTIVE_DATE)
ELSE (19000000 + CP_ACC_PROPERTY_HDR.ACCPRPTY_EFFECTIVE_DATE)END)

I need this check as I want to use max of this date. ACCPRPTY_EFFECTIVE_DATE is number(6) in our source system.

Tom Kyte
September 09, 2003 - 11:40 am UTC

you can use case, yes.




what is difference between Lead function and prior by connect clause

suresh, September 11, 2003 - 1:17 am UTC


what is the difference between Lead function and Prior by connect clause in Oracle ?

what are the data dictionary tables(systems tables) ?

Tom Kyte
September 11, 2003 - 8:32 am UTC



they are apples and oranges.

one (connect by prior) lets you do a hierarchy

the other lets you reference the values of a prior (or future) row in the result set in the current row.

you can create a hierarchy with connect by prior. you cannot compare YOUR salary to the salary of the last person hired right before you with connect by prior.


you cannot create a hiearchy with lag/lead. you CAN compare your salary......


all_objects for example is a data dictionary table.
sys.obj$ is another.

Behaviour of Function based Index and Normal Index

Vivek Sharma, December 27, 2003 - 12:16 pm UTC

Dear Tom,

I have a query as regards to the behaviour of Function based indexes.
The parameters query_rewrite_enabled is set to true and query_rewrite_integrity
is set to integrity. Now I have 2 tables with same number of rows. These are

scott@ORCL>show parameter query

NAME TYPE VALUE
------------------------------------ ------- ---------------
query_rewrite_enabled boolean TRUE
query_rewrite_integrity string TRUSTED

create table test as
select * from all_objects;

create table test1 as
select * from all_objects;

create index test1_idx on test1(created);

create index test_idx on test(to_char(created,'dd-mon-yyyy'));

analyze table test compute statistics for table for all indexes for all indexed columns;

analyze table test1 compute statistics for table for all indexes for all indexed columns;

So my Test table has a function based index and test1 has normal btree index.

When I fire a query

scott@ORCL>explain plan for
2 select count(created) from test;

Explained.

scott@ORCL>@utlxpls

Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | | 52 | | |
| SORT AGGREGATE | | 1 | | | | |
| TABLE ACCESS FULL |TEST | 24K| | 52 | | |
--------------------------------------------------------------------------------

6 rows selected.

scott@ORCL>explain plan for
2 select count(created) from test1;

Explained.

scott@ORCL>@utlxpls

Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 1 | | 11 | | |
| SORT AGGREGATE | | 1 | | | | |
| INDEX FAST FULL SCAN |TEST1_IDX | 24K| | 11 | | |
--------------------------------------------------------------------------------

6 rows selected.

My Question :

Why does it goes for a full table scan for a table with Function based index
whereas it does a Index Fast Full Scan ?

For my normal queries with where clause it uses indexes on both the tables. But
for count it behaves differently. Why ? What could be the reason ?

Your explanation will be highly appreciated.

Thanks and Regards
Vivek Sharma



Tom Kyte
December 27, 2003 - 1:27 pm UTC

it is not 100% known to the optimizer that your function never returns NULLS.

Since entirely NULL entries are not indexed, there is the chance (not in your case, but in general) that the index would have a different number of elements in it then the table.

Hence, it won't use it.


With the "normal" b*tree, the optimizer says "ah hah, index on a NOT NULL COLUMN, lets count rows using that"

You should know that in later releases, it is much smarter with BUILTIN functions.  Not only is query rewrite not needed, but it knows which will return NULLS and which will NOT.  Hence:

ops$tkyte@ORA9IR2> create table t as select * from all_objects;
 
Table created.
 
ops$tkyte@ORA9IR2> create index t_idx on t(to_char(created,'dd-mon-yyyy'));
 
Index created.
 
ops$tkyte@ORA9IR2> analyze table t compute statistics for table;
 
Table analyzed.
 
ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select count(*) from t;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=4 Card=29526)
 
 
 
ops$tkyte@ORA9IR2> set autotrace off
ops$tkyte@ORA9IR2> show parameter query
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      FALSE
query_rewrite_integrity              string      enforced
 

Why the INDEX SCAN?

Gabe, May 03, 2004 - 3:43 pm UTC

oltp@ABCD> show parameter query

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled string FALSE
query_rewrite_integrity string enforced

oltp@ABCD> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production

oltp@ABCD> create table t
2 ( id number(9) not null
3 ,text1 varchar2(10) not null
4 ,text2 varchar2(10) null
5 ,text3 varchar2(10) not null
6 );

Table created.

oltp@ABCD> create unique index f on t (text1||'~'||text2||'~'||text3);

Index created.

oltp@ABCD> begin
2 for i in 1..10 loop
3 insert into t values (i,to_char(i), decode(mod(i,2),0, null, to_char(i)), to_char(i));
4 end loop;
5 end;
6 /

PL/SQL procedure successfully completed.

oltp@ABCD> analyze table t compute statistics for table for all indexes for all columns;

Table analyzed.

oltp@ABCD> set autotrace on
oltp@ABCD> select * from t
2 where text1||'~'||text2||'~'||text3 = '5'||'~'||'5'||'~'||'5'
3 ;

ID TEXT1 TEXT2 TEXT3
---------- ---------- ---------- ----------
5 5 5 5

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=7)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=1 Card=1 Bytes=7)
2 1 INDEX (UNIQUE SCAN) OF 'F' (UNIQUE)




Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
1:
Why does the optimizer choose to go through the index when the full table scan will probably incur just one IO? BTW, I get the same plan even if I load the table with just one record.

2:
Is the query_rewrite_enabled parameter still relevant (9iR2) wrt FBIs? ... the documentation (or my interpretation of it) seems to suggest it is still relevant.

3:
Similar types of FBIs are built in a star-modeled application for every single dimension. Their use is only to enforce uniqueness on the dimensions ... that is, the refresh process would do a lookup with " ... where text1||'~'||text2||'~'||text3 = :p1||'~'||:p2||'~'||:p3" ... there are no other queries requiring this concatenation (BTW, it is a Frankenstein application ... dubious architect and no recourse to his decisions). Anyway, I am of the opinion that a normal unique composite index of (text1, text2, text3) would be a better choice in this case since the index could also be used for queries using some of the leading columns … where the FBI won’t be useful for things like ‘… where text1 = :p1” … if anything, the FBI would _simplify_ the text of the queries (not having to worry about those “is null” constructs). What is your take on it? Am I missing some big benefit of the FBI in this case? Or is it just a case of “there is a _new_ cool feature … let us (ab)use it!”?

Thank you.


Tom Kyte
May 03, 2004 - 7:56 pm UTC

1) test it - make it use a full scan, let it use the index and see which does less work for say 1,000 iterations...

ops$tkyte@ORA9IR2> create or replace procedure p1
  2  as
  3  begin
  4  for x in (
  5  select * from t
  6  where text1||'~'||text2||'~'||text3 = '5'||'~'||'5'||'~'||'5'
  7  )
  8  loop null;
  9  end loop;
 10  end;
 11  /
 
Procedure created.
 
ops$tkyte@ORA9IR2> create or replace procedure p2
  2  as
  3  begin
  4  for x in (
  5  select /*+ FULL(t) */ * from t
  6  where text1||'~'||text2||'~'||text3 = '5'||'~'||'5'||'~'||'5'
  7  )
  8  loop null;
  9  end loop;
 10  end;
 11  /
 
Procedure created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec p1
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p2
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_start;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec for i in 1 .. 1000 loop p1; end loop;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_middle;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec for i in 1 .. 1000 loop p2; end loop;
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec runStats_pkg.rs_stop;
Run1 ran in 7 hsecs
Run2 ran in 13 hsecs
run 1 ran in 53.85% of the time
 
Name                                  Run1        Run2        Diff
LATCH.active checkpoint queue            0           1           1
LATCH.redo writing                       0           1           1
STAT...active txn count during           1           0          -1
STAT...cleanout - number of kt           1           0          -1
STAT...db block changes              1,039       1,038          -1
STAT...change write time                 1           0          -1
LATCH.library cache pin              2,165       2,167           2
STAT...enqueue releases                  4           2          -2
STAT...free buffer requested             8          10           2
LATCH.library cache pin alloca         122         124           2
STAT...bytes received via SQL*         904         902          -2
LATCH.simulator lru latch                0           2           2
LATCH.messages                           0           2           2
LATCH.cache buffers lru chain            7          10           3
STAT...CPU used by this sessio           8          11           3
STAT...CPU used when call star           8          11           3
STAT...enqueue requests                  5           2          -3
LATCH.enqueue hash chains                6           2          -4
LATCH.enqueues                           7           3          -4
LATCH.library cache                  2,430       2,435           5
STAT...Elapsed Time                      9          15           6
STAT...db block gets                   538         532          -6
LATCH.checkpoint queue latch             0          32          32
STAT...redo size                    65,140      64,984        -156
STAT...index fetch by key            1,000           0      -1,000
STAT...table scans (short tabl           0       1,000       1,000
STAT...table fetch by rowid          1,010          10      -1,000
STAT...rows fetched via callba       1,000           0      -1,000
LATCH.simulator hash latch               0       1,158       1,158
STAT...calls to get snapshot s       1,007       3,006       1,999
STAT...consistent gets - exami       2,001           0      -2,001
STAT...buffer is not pinned co       2,020       6,020       4,000
STAT...session logical reads         2,550       8,543       5,993
STAT...consistent gets               2,012       8,011       5,999
STAT...no work - consistent re          10       6,010       6,000
STAT...table scan blocks gotte           0       6,000       6,000
STAT...table scan rows gotten            0      10,000      10,000
LATCH.cache buffers chains           4,641      18,648      14,007
 
Run1 latches total versus runs -- difference and pct
Run1        Run2        Diff       Pct
11,312      26,519      15,207     42.66%
 
PL/SQL procedure successfully completed.
 

full scans are more costly for truly small tables than an index would be.  it is (has been, was) a myth that indexing small tables is not "good"


2) when you use BUILTIN functions that Oracle "knows are safe", they are not needed.

when you code your OWN functions, it is still relevant


3) Well, it does have interesting "null properties" to it (eg: it is never null and compares a null to another null as if they were the same).  

if all three columns are nullable -- it *might* make sense.  If one of the three was not, i'd put it on the leading edge and use a straight where clause.
 

more FBI stuff ...

Gabe, May 07, 2004 - 11:16 am UTC

1:
Fair comment about the /*+ test it */ hint. I jumped ahead too quickly believing it was the FBI … indeed, the optimizer will go for index access for a normal composite index as well. Guess an FT has a bigger footprint than the combined index scan and table by rowid.

3:
In our case there will always be at least one mandatory column … even if all columns were defined as optional, at least one of the supplied values would be not null (the composite key represents the natural key of the dimension). Anyway I can see more issues with the FBI vs. composite index:
i. The 32 column limit would apply to both … so not a differentiator.
ii. For 8K blocks, the maximum key length for the composite index is bigger than the 4000 bytes of the FBI. In fairness, this is about declared length vs. actual length ... but ...
iii. More importantly, the normal index will assess the key length and raise “ORA-01450: maximum key length (6398) exceeded” upon definition … the FBI will raise length-related exceptions at runtime.
iv. I can define an unique constraint and have my composite index too; the FBI, even if unique, won’t be useful for the unique constraint. So, less metadata.
v. The FBI is for a single purpose … the composite can satisfy multiple types of queries.

I do have a question though … given our particular need (hope I provided enough info), do you think the idea of computing a record hash (stored as raw and indexed) and comparing to the new hash has any merits? … at least, it will do away with the 32 columns limit on indexes … any caveats you can think of?

Thank you.


Tom Kyte
May 07, 2004 - 1:07 pm UTC

the hash is a good idea to find candidate rows that could be the same very efficiently.

index on fuction?

Florin, May 14, 2004 - 4:20 am UTC

Hi Tom,

I have the following scenario:

create table my_rf1 (SOC char(9));
insert into my_rf1 values ('L60 ');
insert into my_rf1 values ('L60A');
insert into my_rf1 values ('L60B');

Also I have an index on SOC.

I need a query to return only the value: L60

If I'm doing the following , the query uses the index but the result is not what I expected (I need only L60):

select * from my_rf1 where soc like 'L60%';

SOC
---------
L60
L60A
L60B

The following also uses the index but still I don't get L60:
select * from my_rf1 where soc =rtrim('L60');

no rows selected

The only way to do is the following , but then it does full tablescan because of the rtrim(soc):

select * from my_rf1 where rtrim(soc)=rtrim('L60');

SOC
---------
L60


How should I solve this problem , in order to make use of the index? What about:

create index SOC_IX on MY_RF1 (rtrim(SOC)); ???


Would you please help me with this.

Many thanks in advance!

Florin



Tom Kyte
May 14, 2004 - 10:18 am UTC

select * from t where soc = rpad( :x, 9 )


you might want to stop using char, char stinks. Hate them.

I don't get it

Florin, May 14, 2004 - 11:15 am UTC

Hi,

I don't understand your solution. RPAD allows you to pad the right side with any characters.

What I need is to get in my select only L60 even if the values were inserted with or without spaces.
Ex:
'L60'
'L60 ' ---> one space
'L60 ' ---> 2 spaces

How can I get in my select three times L60?
The only solution I know is:

select * from my_rf1 where rtrim(soc)=rtrim('L60');

SOC
---------
L60
L60
L60

But in this case , the index is not used. I do need the index because the table is huge.

Thanks

Tom Kyte
May 15, 2004 - 10:38 am UTC

they are all in char(9)'s

all of them are 'L60 '

all of them. none of them are 'L60', 'L60 ', they are all 9 characters long. that is the very essence of "char"

Thanks

Florin, May 17, 2004 - 5:50 am UTC

Hi,

So , in my case, what you actually say that even the RPAD is not needed, if is no difference between 'L60' and 'L60 ' , so I allways get L60 in my select by simply doing:

select * from my_rf1 where soc='L60'.
Why do I need rpad?



Tom Kyte
May 17, 2004 - 7:53 am UTC

because you need to use bind variables and 

select * from my_rf1 where soc = :x

will ONLY work if you use a bind variable that is 9 characters long.  consider:

ops$tkyte@ORA9IR2> create table t ( bad_datatype char(9) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t values ( 'L60' );
 
1 row created.
 
ops$tkyte@ORA9IR2> insert into t values ( 'L60    ' );
 
1 row created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> variable x varchar2(9)
ops$tkyte@ORA9IR2> exec :x := 'L60';
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t where bad_datatype = :x;
 
no rows selected
 
ops$tkyte@ORA9IR2> select * from t where bad_datatype = rpad(:x,9);
 
BAD_DATAT
---------
L60
L60


<b>Most every client in the world is going to use a varchar bind variable, they will be thwarted by your use of CHAR -- they will need to be rpadded (and I hope you dont ever alter that column to be a char(10), code rewrite = your future if you do).  I'd seriously rethink your datatype</b>
 

Florin, May 17, 2004 - 9:38 am UTC

The only problem is that our application is designed (don't ask my why) to allow the user to insert values with spaces. Thats the reason not using varchar2.

Example:

create table my_rated_feature (SOC VARCHAR2(9));

Table created.

insert into my_rated_feature values ('L120 '); --> one space

1 row created.

select * from my_rated_feature where soc='L120';

no rows selected


Tom Kyte
May 17, 2004 - 3:09 pm UTC

trigger to rtrim would fix that right up :)

Rebuild Function Based Indexes through Procedure

Wayne, June 06, 2004 - 4:32 pm UTC

Tom,
I am on 91R2 (OS windows XP). I try to create a procedure like:
REBUILD_INDEX(owner_in, tabname_in) to be called by some operation account.
Inside the procedure I will do a loop to do the following:
execute immediate 'alter index '||c.owner||'.'||c.index_name||' rebuild';

For some reason, I cannot make it work for FBIs (normal or Bitmaps are ok), even executed as the schema owner.
Error message:
ORA-20001: Rebuild index for t: ORA-20002: Rebuild ABC.T_FBI1 ERROR: ORA-01031:
insufficient privileges
ORA-06512: at "ABC.REBUILD_INDEX", line 24
ORA-06512: at line 1

If I do it directly, it works fine:
ABC>alter index t_fbi1 rebuild;

Index altered.

Do you see anything I missed or there is something on FBI that is very special?

Tom Kyte
June 06, 2004 - 5:03 pm UTC

well, my first question would be "why". Hopefully you are not rebuilding "just because you heard from someone that heard from someone that heard it is supposed to be a good idea" (because it isn't)

there is no reason to rebuild all indexes in a schema.

In order to rebuild the FBI, you would need (directly granted, since no roles in a definers rights procedure) execute on the function itself -- or the seriously all powerful "execute any procedure" priv.


I would suggest really studying "why" you are creating such a procedure in the first place myself.



Rebuilding FBI Through Procedure

Wayne, June 06, 2004 - 9:10 pm UTC

Tom,
Thanks for your quick response. I am very grateful of your dedication on helping us.
I didn't attempt to rebuild all indexes for a schema, but just the 'UNUSABLE' indexes for an affected table during an ETL. My problem started from bitmap indexes in DW: they lock the whole bitmap index and cause space explosioin from 1MB to several GBs and slow the process significantly if not marked unusable. Since there might be multiple bitmap indexes, putting them in a procedure sounds a good idea (instead of rebuild ind1, rebuild ind2,...), that's where my idea is coming from. It can be called once the ETL is completed (we use a third party ETL tool). Now, if the table is partitioned, we can use DBMS_PCTXUTIL, otherwise, I have to call my own version (are there any other built-ins?).
The goal of the this procedure is to pick up all 'Unusable' indexes in a table and rebuild them, so if there's an unusable FBI (we currently don't have any), it will be part of rebuilding process, and that's where my problem is during the test.

What I don't understand is why the schema owner (with DBA priv)of the table and the procedure still cannot execute the procedure successfully if an FBI is part of the process So what specific direct grants I need to do or are there better ways to handle these situation (DW ETL)? Note that direct call such as "alter index my_FBI rebuild" works perfectly on SQL*Plus.

Thanks,

Tom Kyte
June 07, 2004 - 8:10 am UTC

Rebuild FBI through Procedure:

Wayne, June 07, 2004 - 9:32 pm UTC

Tom,
Thanks for the link. I used invoker's right to make it work (authid current_user), but only successful when executed as the schema owner, before that not even for schema owner! Now if I log in as another user (with all the grants, the FBIs still won't get rebuilt(insufficient priv), all other indexes will.
Could you kindly tell what exactly I am missing? What exactly the invokers right helped the schema owner in this case?


Thanks,

Here's my procedure:
CREATE OR REPLACE PROCEDURE REBUILD_INDEX(owner_in varchar2, tab_name_in varchar2)
AUTHID CURRENT_USER
IS
l_statement varchar2(1000);
BEGIN
FOR d IN ( SELECT owner, index_name
FROM ALL_INDEXES
WHERE table_name = UPPER(tab_name_in)
AND status = 'UNUSABLE'
AND owner = UPPER(owner_in)
)
LOOP
l_statement := 'ALTER INDEX '||d.owner||'.'||d.index_name||' REBUILD';
BEGIN
-- rebuild unusable indexes
EXECUTE IMMEDIATE l_statement;
EXCEPTION
WHEN OTHERS THEN
-- let's continue but show the error message.
DBMS_OUTPUT.PUT_LINE(l_statement||'--'||substr(sqlerrm, 1, 100));
END;
END LOOP;
END REBUILD_INDEX;
/


Tom Kyte
June 08, 2004 - 8:03 am UTC

invokers rights allowed for the procedure to be run as if it were an anonymous block. it ran/runs with the RIGHTS of the invoker, not the definer (person who compiled it).


It is as if the invoker typed in the commands as far as SQL goes in the block. (it is still as if the defined typed in the commands as far as the PLSQL goes)


It is a bit like running a pro*c program that connects as you. The code was compiled and linked by some programmer (you have no access to that) but the SQL runs "as you", not as them.


So, when the owner of the indexes runs this - they of course

a) have the ability to execute the function
b) the ability to rebuild their own indexes

and it works. When someone else runs this -- they would need:

a) alter any index ( to rebuild someone elses index)
b) execute any procedure OR execute on that procedure in the FBI to rebuild someone elses FBI

Rebuild FBI through Procedure

Wayne, June 08, 2004 - 2:03 pm UTC

Thanks Tom,
I got it work with proper grants and using invoker's right,
executed under both the owner of the table and another account.

I am still not very clear why as the owner of the table, as the owner of its FBI's and as the owner of the rebuild procedure, it still cannot rebuild the FBI (regular indexes are okay) through the procedure if not specifying "AUTHID CURRENT_USER". In this case, the owner is both the invoker and the definer (compiler) of the procedure, isn't it?



Tom Kyte
June 08, 2004 - 3:39 pm UTC

because without AUTHID CURRENT_USER -- no roles are in place -- you need a grant to do the FBI and they must have that grant via a ROLE.

Concise, right to the point!

Wayne, June 08, 2004 - 9:14 pm UTC

Got it!
Thank you very much!

Rebuilding of Function Based Index

Vivek Sharma, August 30, 2004 - 7:41 am UTC

Dear Tom,

I have a partitioned table on which we have created a Function Based Local Index. Due to some space problem, one of the index became unusable. We added the space to the tablespace and tried to rebuild the index in order to make it usable. The command I issued is as under :

ALTER INDEX <INDEX NAME> rebuild partition <partition name>.

The rebuilding went on and did not complete till 2 hrs. Whereas the Index was very small. When I dropped and recreated the Index (complete Index), it got created in 45 minutes time. I could reproduce this twice. Both the times, the rebuilding of a partition did not get complete in 2 hours whereas the complete index creation got finished in 45 minutes time.

In both the cases, I had increased the sort_area_size parameter at session level to 100m.

What could be the problem ?

Regards
Vivek

Tom Kyte
August 30, 2004 - 8:55 am UTC

no idea as I don't have a test case to work with, don't know what the function does, have no directions to reproduce the issue.

all i do know is that "insufficient space" should not do that, it would prevent modifications, not let the index go south. what happened?

Rebuilding Issue of a Function Based Index

Vivek Sharma, August 30, 2004 - 10:26 am UTC

Dear Tom,

The above mentioned Function Based Index is a simple SUBSTR(column_name,1,6) function applied to a column. There was sufficient space in the tablespace. Even I don;t understand why the rebuilding should take so much time.

Is there any relation between the Index Statistics and Rebuilding ? The said Index was not analyzed and hence there were no statistics of the Indexes in DBA_TAB_PARTITIONS and DBA_INDEXES. If I generate the Statistics and then try to rebuild will it help ? I feel that the statistics has nothing to do with Rebuilding of Indexes.

Thanks and Regards
Vivek


Tom Kyte
August 30, 2004 - 10:35 am UTC

if there was "sufficient space", you wouldn't have had to rebuild according to you in the first place.

No example, no way to reproduce, no comment

Issue in Rebuilding FBI

Vivek Sharma, August 30, 2004 - 10:54 am UTC

Dear Tom,

Initially there was no space due to which the index went unusable. Then I added sufficient space to the tablespace. Infact more than double the size of the Index. Even then it was going slow.

How do I prove this? I don't know.. But it just happended twice.

Also please let me know whether is there any relation between Statistics in DBA_IND_PARTITIONS & DBA_INDEXES and Rebuilding. My fellow DBA says that, If I would have generated the statistics, it would have helped rebuilding. I don't agree with this. What are your commenbts ?

Regards
Vivek

Tom Kyte
August 30, 2004 - 11:02 am UTC

stats don't have anything to do with a rebuild.

during a rebuild, you can gather stats, but the "plan" for a rebuild is pretty simple -- only two choices, use existing usuable index and rebuild it or read source data and reconstruct it. In your case, "reconstruct" was the only choice.



query_rewrite_integrity = TRUSTED

Salim Adenwala, August 31, 2004 - 1:20 pm UTC

Tom,
We are going to build function based indexes on expressions such as substr(FACCT,1,1). Does query_rewrite_integrity have to be set to TRUSTED? If yes, why?

Thanks in advance for your reply.

Salim Adenwala

Tom Kyte
August 31, 2004 - 1:46 pm UTC

in 9204 and up, query rewrite is not needed for FBI's anymore.

query_rewrite_integrity = TRUSTED

Salim Adenwala, September 01, 2004 - 10:38 am UTC

Tom,

Sorry, I should have mentioned my version. We are on 9.2.0.3. The CREATE INDEX syntax in SQL REFERENCE guide mentions that query_rewrite_integrity should be set to TRUSTED to use FBI's, but the Performance and Tuning guide mentions that query_rewrite_integrity should be set to TRUSTED only if we are building FBI on user-defined functions.

We are not planning to build FBI on user-defined functions.

Thanks again for your help.

Salim Adenwala

Tom Kyte
September 01, 2004 - 10:53 am UTC

it is not necessary (try it, you'll see!)



Dynamic Allocation of Value for Function based Index

MSU, September 08, 2004 - 8:26 am UTC

Hi Tom,

Have a look at this.

Create table test_tab(NUM NUMBER(8),VAL VARCHAR2(256));

SQL> desc test_tab
 Name                           Null?    Type
 ---------------------------- -------- --------------
 NUM                                     NUMBER(8)
 VAL                                     VARCHAR2(256)


Insert into test_tab values(1,'b1');
Insert into test_tab values(2,'b1,b2');
Insert into test_tab values(3,'b1,b2,b3');
Insert into test_tab values(4,'b1,b2,b3,b4');
Insert into test_tab values(5,'b1,b2,b3,b4,b5');
Insert into test_tab values(6,'b3');
Insert into test_tab values(7,'b3,b7');
Insert into test_tab values(8,'b1,b3,b7');

SQL> select * from test_tab;

       NUM VAL
---------- --------------------
         1 b1
         2 b1,b2
         3 b1,b2,b3
         4 b1,b2,b3,b4
         5 b1,b2,b3,b4,b5
         6 b3
         7 b3,b7
         8 b1,b3,b7

When I have to create a function based Index, it will be 
 
Create Index Idx_Fun on test_tab(Instr(val,?,1,1))
 
In the place of question mark - It is the value which will be allocated dynamically. How to achieve that dynamically. Plz do let me know.
 
Thanking you in anticipation. 

Tom Kyte
September 08, 2004 - 10:17 am UTC

think about what you are asking for here.  just think about it for a while.

Perhaps when the quantum computer -- the one that can simultaneously look at all possible solutions -- is released, we'll have that.


if you are trying to search a comma delimited list of values (can anyone spell "master detail") you might try an TEXT index.


ops$tkyte@ORA9IR2> create index t_idx on test_tab(val) indextype is ctxsys.context;
 
Index created.
 
ops$tkyte@ORA9IR2> select * from test_tab where
  2  contains( val, 'b7' ) > 0;
 
       NUM
----------
VAL
-------------------------------------------------------------------------------
         8
b1,b3,b7
 
         7
b3,b7
 
 
 

VARCHAR2 Bug In FBI?

Dan, September 08, 2004 - 5:34 pm UTC

Tom,

First, most excellent site. Thank you so much for the information and the humor.

Second, feel free to deflect this if it's not really appropriate...

Is there a bug in 9.2.0.[345] with FBI's and VARCHAR2 fields? (See TAR 3986496.999 or BUG 3777256 for my open case complete with exact steps.) Or is it my improper use of the feature? Besides not using VARCHAR2 fields can you think of workarounds? Is it the interaction of CASE + VARCHAR2? Might other functions work?

Many Thanks,
Dan

Tom Kyte
September 08, 2004 - 7:35 pm UTC

has nothing to do with varchar2 -- you can update your tar with this - the analyst will be able to find the base bug easily and close yours.

It is all about the CASE statement -- the rewrote it for some reason (that is the bug).  consider:

ops$tkyte@ORA9IR2> create table t ( processed_flag varchar2(25) );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create index t1_idx on t ( case when processed_flag = 'N' then 'N' else NULL end );
 
Index created.
 
ops$tkyte@ORA9IR2> select * from user_ind_expressions;
 
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_EXPRESSION
-------------------------------------------------------------------------------
COLUMN_POSITION
---------------
T1_IDX                         T
CASE "PROCESSED_FLAG" WHEN 'N' THEN 'N' ELSE NULL END
              1
<b>see, they turned it into a searched case expression....

so, your workarounds are:

a) use decode
b) use that syntax on the case
c) add something to the case:</b> 
 
ops$tkyte@ORA9IR2> drop index t1_idx;
 
Index dropped.
 
ops$tkyte@ORA9IR2> create index t1_idx on t ( case when processed_flag = 'N' and 1=1 then 'N' else NULL end );
 
Index created.
 
ops$tkyte@ORA9IR2> select * from user_ind_expressions;
 
INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_EXPRESSION
-------------------------------------------------------------------------------
COLUMN_POSITION
---------------
T1_IDX                         T
CASE  WHEN ("PROCESSED_FLAG"='N' AND 1=1) THEN 'N' ELSE NULL END
              1
 

Dan, September 09, 2004 - 1:05 pm UTC

Thanks again. Simply amazing (on so many levels :).

function based index and ORA-01450: maximum key

Winston, October 16, 2004 - 2:25 am UTC

Hi Tom, 

I have a problem with function based index.
I am trying an example on Mastering Oracle pl/sql on page 281
on Version 9.2.0.4.

create table big_tab
as select owner, name, type, line
from dba_source;


create or replace
 function do_something(p_owner varchar2)
 return varchar2 deterministic is
 begin
 counter.inc;
 return p_owner;
 end;
/

Function created.

SQL> create index big_ix on
  2  big_tab ( do_something(owner));
big_tab ( do_something(owner))
                       *
ERROR at line 2:
ORA-01450: maximum key length (3118) exceeded


SQL> select max(length(owner)) from big_tab;  

MAX(LENGTH(OWNER))
------------------
                12

removing counter pkg call gave me the same error:

create or replace
 function do_something(p_owner varchar2)
 return varchar2 deterministic is
 begin
 --counter.inc;
 return p_owner;
 end;
/

SQL> create index big_ix on
  2  big_tab ( do_something(owner));
big_tab ( do_something(owner))
                       *
ERROR at line 2:
ORA-01450: maximum key length (3118) exceeded

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

Don't know why Connor worked around this.
 

Tom Kyte
October 16, 2004 - 10:48 am UTC

the maximum key length is a function of your block size.

a function returning a varchar2 is always returning varchar2(4000).

</code> http://asktom.oracle.com/~tkyte/article1/index.html <code>

and search for maximum, you'll see an explanation and howto do this.

Gabe

A reader, November 21, 2004 - 9:44 pm UTC

"iii. More importantly, the normal index will assess the key length and raise “ORA-01450: maximum key length (6398) exceeded” upon definition … the FBI will raise length-related exceptions at runtime"

Regarding Gabe's comment above, I dont quite understand. Why would a regular index raise the max key length error upon its creation and the FBI at runtime? The FBI also scans the table, evaluates the expression and stores the data, right? So why wouldnt the FBI also get the error when it is defined?

Thanks

Deadlock error

robert, December 02, 2004 - 6:46 pm UTC

8.1.7.4
Trying to create a FBI and got deadlock error:

create index my_fbi on
ma1_transactions(substr(mypkg.locations_transposed(transaction_id),1,300))

ORA-04020: deadlock detected while trying to lock object MANDA.MA1_TRANSACTIONS

I ran this and no rows returned :

select
b.username,c.sid,c.owner, c.object,b.lockwait,a.sql_text
from v$sqltext a,
v$session b,
v$access c,
v$locked_object d,
dba_objects e
where a.address=b.sql_address
and a.hash_value=b.sql_hash_value
and b.sid=c.sid
and d.object_id=e.object_id
and e.object_name=c.object
and c.owner!='SYS'
order by b.username ;

Tom Kyte
December 02, 2004 - 7:46 pm UTC

that package -- does it itself reference the table?

does this reproduce with a small example you can share with us.

deadlock issue

robert, December 03, 2004 - 9:44 am UTC


>> that package -- does it itself reference the table?
oh, yes
and I verified that this is indeed causing the deadlock.

Thanks !

Tom Kyte
December 03, 2004 - 10:04 am UTC

yeah, that gets tricky with invalidations and things like that. you'll want to keep that "separate"

Case is not working while decode does

Sasa, December 08, 2004 - 10:43 am UTC

Hi Tom,
I tested on our test db (version 9.2.0.3.0 EE) some function based index and got very strange results.
I used table with over 4mil records and field which has couple of hundreds 'W3' value.
Then created index:

CREATE INDEX I_STATE_test ON FOSELECTION
(CASE WHEN IDFOSELECTIONSUSPENSIONTYPE = 'W3' THEN IDFOSELECTIONSUSPENSIONTYPE ELSE NULL END);

ANALYZE INDEX I_STATE_test COMPUTE STATISTICS;

CREATE OR REPLACE VIEW TEST_FBI AS
SELECT
NAME,
idfoselection,
(CASE WHEN IDFOSELECTIONSUSPENSIONTYPE = 'W3' THEN IDFOSELECTIONSUSPENSIONTYPE ELSE NULL END) AS fbi
FROM FOSELECTION;

ALTER SESSION SET QUERY_REWRITE_ENABLED=TRUE;

ALTER SESSION SET QUERY_REWRITE_INTEGRITY=TRUSTED;

Explain plan showed me that full table occured, did tkprof to convince myself but same results, ie. full table scan.

I changed index and put decode instead of case:

DROP INDEX I_STATE_test;

CREATE INDEX I_STATE_test ON FOSELECTION
(DECODE(IDFOSELECTIONSUSPENSIONTYPE, 'W3', IDFOSELECTIONSUSPENSIONTYPE, NULL));

CREATE OR REPLACE VIEW TEST_FBI AS
SELECT
NAME,
idfoselection,
(DECODE(IDFOSELECTIONSUSPENSIONTYPE, 'W3', IDFOSELECTIONSUSPENSIONTYPE, NULL)) AS fbi
FROM FOSELECTION;

SELECT * FROM mmfixedodds.test_fbi WHERE fbi = 'W3'

and voila it works.

SELECT STATEMENT Optimizer MODE=CHOOSE 48 K 250
TABLE ACCESS BY INDEX ROWID FOSELECTION 48 K 6 M 250
INDEX RANGE SCAN I_STATE_TEST 19 K 1
Optimizer uses now index correctly.

Is this something which is undocumented (doubt so as I read your book) or specific to version of oracle ?

I hope so that this question could be relevant to this thread.
Thanks



Tom Kyte
December 09, 2004 - 12:23 pm UTC

it is an in progress bug, fortunately, it is easy to detect and to work around.

Notice how in the following, the CASE is 'optimized' -- meaning they rewrote it on you during the create index, but it will not perform the same optimization at query time.  The two workarounds follow  the "bad" case:


ops$tkyte@ORA10G> drop table t;
Table dropped.
 
ops$tkyte@ORA10G> create table t ( x int );
Table created.
 
ops$tkyte@ORA10G> create index t_idx on t( case when x=10 then x else null end );
Index created.
 
ops$tkyte@ORA10G> select column_expression from user_ind_expressions where index_name = 'T_IDX';
 
COLUMN_EXPRESSION
------------------------------
CASE "X" WHEN 10 THEN "X" ELSE
 NULL END
 
That is not what we typed in, hence -- when we query, no joy for us
 
ops$tkyte@ORA10G> drop index t_idx;
Index dropped.
 
ops$tkyte@ORA10G> create index t_idx on t( case x when 10 then x else null end );
Index created.
 
ops$tkyte@ORA10G> select column_expression from user_ind_expressions where index_name = 'T_IDX';
 
COLUMN_EXPRESSION
------------------------------
CASE "X" WHEN 10 THEN "X" ELSE
 NULL END
 
Since we used the syntax they will optimize to, it'll work OK
 
ops$tkyte@ORA10G> drop index t_idx;
Index dropped.
 
ops$tkyte@ORA10G> create index t_idx on t( decode(x,10,x,null) );
Index created.
 
ops$tkyte@ORA10G> select column_expression from user_ind_expressions where index_name = 'T_IDX';
 
COLUMN_EXPRESSION
------------------------------
DECODE("X",10,"X",NULL)
 
Or just use the good old DECODE function 

Different case working

Sasa, December 09, 2004 - 4:54 am UTC

Hi,
In my previous example "case" didnt working (index not used) but i changed index definition as follows and it worked:

CREATE INDEX I_STATE_test ON FOSELECTION
(CASE IDFOSELECTIONSUSPENSIONTYPE WHEN 'W3' THEN IDFOSELECTIONSUSPENSIONTYPE ELSE NULL END);

Thanks




Function based index

Yogesh, February 01, 2005 - 12:42 pm UTC

I'm fetching the rows for customer table using following query

select * from customer cus, t1
where
NVL(t1.postcode,'A') = NVL(cus.postcode,'A')
AND t1.surname = cus.surname
and t1.address1 = cus.address1

I have index on postcode, surname, address1. But due to NVL it is not able to use that index, resulting in FTS. What is the work around for these type of conditions?

How function based index can be used here ?





Tom Kyte
February 01, 2005 - 3:34 pm UTC

if it used an index it would be REALLY SLOW

this is a good example of 2 full scans + hash join.

If it is doing anything other than that, then I would be worried!!!!


</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:6749454952894#6760861174154 <code>

if you wanted to see how bad life would be if you used those indexes....

Solomon, February 07, 2005 - 9:44 am UTC

Hi Tom,

we have a function based index on one of our tables. Every now and then this index gets disabled and the batch jobs which poopulate this table fails. Could you please tell me in which all situations a function based index gets disables.

Thanks a lot

Tom Kyte
February 07, 2005 - 10:42 am UTC

you do something to the function upon which it is built -- like recompile it or do something to invalidate it (revoke a priv from the owner for example)

Function based Index

Veeresh, February 15, 2005 - 4:27 am UTC

Hi Tom,
Its Realy help me a lot.
please explain me about this problem
Create table t
( prod varchar2(250)
)
/
Insert Into t values ( 'X'||','||'Y'||','||','||'Z');
I need to create function based index on the first portion of prod means only for 'X'.
How i can do this?
I tried like Create index index1 On t(Substr(prod,1,Instr(prod,',',1)-1)) .
I am getting the error ORA-01450:maximum key length (3118) exceeded.
Please explain me about this.
Thanks in advance

Tom Kyte
February 15, 2005 - 3:24 pm UTC

release... block size....

worked fine in 9ir2 with 8k

however, bear in mind, - that "could" return 4000 characters.

you might need another substr

substr( substr(prod,1,instr()), 1, 250 );

that would let it know "max of 250"

Sorry

A reader, February 15, 2005 - 5:06 am UTC

Hi Tom ,
Sorry for the previous question.
My table is like table t (prod varchar2(2000));
and it has data like i mentioned in above question.

veeresh

veeresh, February 16, 2005 - 5:03 am UTC

Hi Tom,
Thanks !
My table t is like

Create table t (prod varchar2(2000))

This is the reason of getting error ora-01450 :

I have created one function ,Is it passible to create Function based index based on user-defined function ?

Thanks in advance.




Tom Kyte
February 16, 2005 - 8:11 am UTC

create index t_idx on ( SUBSTR( <anything you have returning a string>, 1, MAX_LEN) )

like i demonstrated right above?

Function based index

veeresh, February 17, 2005 - 2:17 am UTC

Hi Tom,
Thanks for the response.

But I am getting error ORA-30553: The function is not deterministic.

Please explain me about this.

Thanks

Tom Kyte
February 17, 2005 - 8:35 am UTC

FBI-Drawbacks?

max, February 18, 2005 - 4:01 am UTC

we 'd like to implement a "deleted" flag for tables expected to contain hugh amounts of data and enforce uniqueness across UNdeleted entries.

instead of introducing that "deleted" flag into primary keys we 'd like to use function based indexes.

are there any reasons to prefer the "conventional approach"?

Tom Kyte
February 18, 2005 - 8:32 am UTC

why not use workspace manager and let it deal with everything?


ops$tkyte@ORA10G> create table emp as select empno, ename, deptno from scott.emp;
ops$tkyte@ORA10G> alter table emp add constraint emp_pk primary key(empno);
ops$tkyte@ORA10G> set feedback on
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> set echo off
Version-enable the table. Specify hist option of VIEW_WO_OVERWRITE so that
the xxx_HIST view contains complete history information.
 
 
ops$tkyte@ORA10G> begin
  2          DBMS_WM.EnableVersioning ('emp',  'VIEW_WO_OVERWRITE');
  3  end;
  4  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> set echo on
ops$tkyte@ORA10G> delete from emp where empno = 7788;
 
1 row deleted.
 
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G> insert into emp (empno,ename, deptno) values ( 7788, 'Tom', 10 );
 
1 row created.
 
ops$tkyte@ORA10G> commit;
 
Commit complete.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> select * from emp;
 
     EMPNO ENAME          DEPTNO
---------- ---------- ----------
      7369 SMITH              20
      7499 ALLEN              30
      7521 WARD               30
      7566 JONES              20
      7654 MARTIN             30
      7698 BLAKE              30
      7782 CLARK              10
      7839 KING               10
      7844 TURNER             30
      7876 ADAMS              20
      7900 JAMES              30
      7902 FORD               20
      7934 MILLER             10
      7788 Tom                10
 
14 rows selected.
 

ops$tkyte@ORA10G> select empno||'/'||ename||'/'||deptno row_data, wm_username, wm_createtime, wm_retiretime from emp_hist where empno = 7788;
 
ROW_DATA
-------------------------------------------------------------------------------
WM_USERNAME
-------------------------------------------------------------------------------
WM_CREATETIME
---------------------------------------------------------------------------
WM_RETIRETIME
---------------------------------------------------------------------------
7788/SCOTT/20
OPS$TKYTE
18-FEB-05 08.25.09.027612 AM -05:00
18-FEB-05 08.25.10.053697 AM -05:00
 
7788/SCOTT/20
OPS$TKYTE
18-FEB-05 08.25.10.053697 AM -05:00
18-FEB-05 08.25.10.150230 AM -05:00
 
7788/Tom/10
OPS$TKYTE
18-FEB-05 08.25.10.150230 AM -05:00
 
 
 
3 rows selected.
 
ops$tkyte@ORA10G> EXEC DBMS_WM.DisableVersioning ('emp' );
 
PL/SQL procedure successfully completed.


<b>but short of that, to enforce uniqueness on a set of columns, for a subset of rows -- the function based index is your only real approach (else you are enforcing uniqueness across *all* rows)</b>
 

Superb

veeresh, February 18, 2005 - 6:01 am UTC

Hi Tom, Your article </code> http://asktom.oracle.com/~tkyte/article1/index.html <code>

Really helps me alot.

Thanks


Function Based unique index Example to enforce uniqueness for undeleted..

pasko, February 18, 2005 - 2:10 pm UTC

Hi Tom,

User max wrote:
"... and enforce uniqueness across UNdeleted entries"

I just used Tom's Trick of Functinal Based index to do exactly what max asked last week.

My table was like this:
Create table test ( uniq_id number ,col1....coln,
deleted_flag date default to_date('01010001' ,'DDMMYYYY')
) ;

When record is deleted , we set delete_flag = sysdate.
we want 'uniq_id' to be unique in this case whenever delete_flag is set to default.

so, to enforce uniqueness for all undeleted Entries,
use Tom's Trick:

create unique index enforce_uniq_id_i on test(
case
when deleted_flag = to_date('01010001' ,'DDMMYYYY')
then uniq_id else null
end
) ;

i hope it works because it was the first time i am using the function based Indexes.

Best Regards.





Tom Kyte
February 18, 2005 - 3:04 pm UTC

yes, we all agree that a function based index can do this. I was pointing out if you are writing your own "flag delete" stuff, you might look into this other thing, it does it an alot more.

oracle

M.Savaadmuthu, February 23, 2005 - 1:08 am UTC

How can i know that primary key is set for particular table?
if i type in orcale editor "desc tablename", it show name,null?, type only. supose if i set primary key for table , if i view that through desc, it show only not null.
how can i know from that primary is set in the table?

Tom Kyte
February 23, 2005 - 2:09 am UTC

ops$tkyte@ORA9IR2> select constraint_name from user_constraints where table_name = 'T' and constraint_type = 'P';
 
CONSTRAINT_NAME
------------------------------
SYS_C003101
 


shows that table T has a primary key constraint 

name constraints ?

Matthias Rogel, February 23, 2005 - 3:07 pm UTC

oops, I thought I sometimes read
"always name your constraints"
on this side - probably not word by word
but this was the sense

anyway: hope you are having a good time in
Ireland

Tiocfaidh Ar La




Tom Kyte
February 24, 2005 - 5:08 am UTC

in real life, in a demo, no -- I'm just going to type things in as fast as I can.

Typical query !!

Charlee, March 01, 2005 - 9:11 pm UTC

Hi tom,

We are facing problem to tune this query. can you pls suggest whatever your findings.
estimated_policy_data contains 70 milion record,
policy_detail contains 400,000 record.

Appreciate your help !!


select a.state_cd, a.item, a.month, a.ssum/b.lsum as slratio
from (
select
a.state_cd,
'STD BY LALE' as item,
to_char(a.policy_effective_date, 'yyyymm') as month,
sqrt(sum(b.variance_loss_and_ale_amt)) as ssum
from policy_detail a, estimated_policy_data b
where a.policy_uid = b.policy_uid
and to_date(to_char(policy_effective_date,'yyyymm'),'yyyymm') between
to_date(to_char(add_months(sysdate,-25),'yyyymm'),'yyyymm') and
to_date(to_char(add_months(sysdate,-1),'yyyymm'),'yyyymm')
group by a.state_cd, 'STD BY LALE', to_char(a.policy_effective_date, 'yyyymm')
) a, (
select
a.state_cd,
'STD BY LALE' as item,
to_char(a.policy_effective_date, 'yyyymm') as month,
sum(b.total_loss_and_ale_amt) as lsum
from mpcf.policy_detail a, mpcf.estimated_policy_data b
where a.policy_uid = b.policy_uid
and b.month_id = to_char(add_months(a.policy_effective_date,180),'yyyymm')
and to_date(to_char(policy_effective_date,'yyyymm'),'yyyymm') between
to_date(to_char(add_months(sysdate,-25),'yyyymm'),'yyyymm') and
to_date(to_char(add_months(sysdate,-1),'yyyymm'),'yyyymm')
group by a.state_cd, 'STD BY LALE', to_char(a.policy_effective_date, 'yyyymm')
) b
where a.month = b.month
and nvl(a.state_cd,'~') = nvl(b.state_cd,'~')



Tom Kyte
March 01, 2005 - 9:35 pm UTC

don't see any sort of relationship to the original question?

try to stop applying functions to database columns

think about how

to_date(to_char(COLUMN,'yyyymm'),'yyyymm') betwen K1 and K2

could be written without applying a single function to COLUMN.... and do that for all dates predicates in the above.



and why the need fo "state_cd" to be nvl'ed? seems like state_cd should be not null (semantically speaking)

ORA-01031: insufficient privileges

A reader, March 09, 2005 - 11:17 pm UTC

I am getting an error creating a function based index.  I don't get any errors creating a normal index.  Are there any privileges in particular required for creating a function based index?

SQL> create table tt(id number(1), name varchar2(10));

Table created.

SQL> ALTER TABLE TT
  2   ADD CONSTRAINT PK_TT
  3   PRIMARY KEY (id);

Table altered.

SQL> insert into tt values (1, 'a');

1 row created.

SQL> insert into tt values (2, 'b');

1 row created.

SQL> commit;

Commit complete.

SQL> create index idx_1 on tt(name);

Index created.


SQL> create index idx_2 on tt(upper(name));
create index idx_2 on tt(upper(name))
                               *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>

I did create function based indexes in the past without any problem.  I am not sure what I am doing wrong now or what I am missing.

Would appreciate your comments.

Thank you
 

Tom Kyte
March 10, 2005 - 7:30 am UTC

Concatenation seems to be different maybe?

Phil, March 30, 2005 - 6:07 am UTC

Hi there Tom,

I have a problem in that I can't alter the base tables but need to do a join based on the concatenation of a couple of columns. The problem is, even after analysing the columns I get a full scan:

SELECT h.*
FROM
tasks t,
history h
WHERE
h.objcode = ref3 || ':' || ref1

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop

SELECT STATEMENT Optimizer Mode=CHOOSE 3 M 2870
HASH JOIN 3 M 304 M 2870
TABLE ACCESS FULL TASKS 303 K 3 M 899
TABLE ACCESS FULL HISTORY 421 K 37 M 716
I have an index on h.objcode and have created a function based index on tasks as so...

CREATE INDEX INDX_CONCAT ON
TASKS(REF3||':'||REF1)
NOLOGGING
TABLESPACE INDX;

But I always get miserable full scan performance! Please advise, thanks

Phil


Tom Kyte
March 30, 2005 - 7:28 am UTC

and what is wrong with full scans precisely?


thank GOODNESS you got a full scan, can you imagine how slow this would be otherwise...


</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:37453890985588 <code>

right index?

Charlie, May 24, 2005 - 11:09 pm UTC

Hi Tom,

create table t (data varchar2(15));

insert into t values('11010000000');
insert into t values('11000000000');
insert into t values('11010010000');
insert into t values('11010010000');
insert into t values('11000001000');
insert into t values('11000001000');
insert into t values('11010010000');
insert into t values('11010010000');
insert into t values('11000010000');
insert into t values('11000010000');

commit;

-- create function

create or replace function compare (p_str1 in varchar2, p_str2 in varchar2)
return number
deterministic
is
    type array is table of number index by binary_integer;
    v_num_arr1 array;
    v_num_arr2 array;
    
    v_and_sum number := 0;
    v_or_sum number := 0;    
    
    -- split a string into a number array
    function split(p_str in varchar2)
    return array
    is
        v_data array;        
    begin
        for i in 1..length(p_str)
        loop
            v_data(i) := to_number(substr(p_str,i,1));                       
        end loop;                
        
        return v_data;         
    end;
begin
    v_num_arr1 := split(p_str1);
    v_num_arr2 := split(p_str2);      
    
    for i in 1..v_num_arr1.count
    loop
        if v_num_arr1(i) = 1
        then           
            v_or_sum := v_or_sum + 1;
            
            if v_num_arr2(i) = 1
            then
                v_and_sum := v_and_sum + 1;
            end if;
        elsif v_num_arr2(i) = 1
        then
            v_or_sum := v_or_sum + 1;
        end if;
    end loop;
        
    if v_and_sum = v_or_sum
    then
        -- handles div by 0 if both bit sums are empty
        return 0;  
    end if;
     
    return round(1 - v_and_sum / v_or_sum, 3);    
end;
/

SQL> var bv varchar2(11);
SQL> exec :bv := '11010010001';

SQL> select * from t where compare(data, :bv) >= 0.5;

DATA
---------------
11000000000
11000001000
11000001000


The problem is that table t might grow to several million records, and because it's calling the function "compare", this query will be a full table scan every time.  I think this is a good candidate for a function based index, but the problem is the second parameter is a bind variable -- will change dynamically.  So how can I use function based index here?  Can application domain index be used here?  If so, how?  Any other solutions?  I need this query run *fast*.

Please shed some light on it!  Always appreciate your solutions!!

thanks, 

Tom Kyte
May 25, 2005 - 7:41 am UTC

well, think about it -- it is sort of like asking for an index on:

where instr( varchar_column, :bv ) > 0;


you would have have an index PER possible value of :bv. Same with your function. You don't know what the answer is until you get the input bind variable.

You can use a function based index when all of the inputs to the function are known at compile time.

I can hypothesize of how we might use N columns and N bitmap indexes to significantly reduce the search space but not knowing too much about the actual question trying to be solved -- cannot really go further (and the bitmaps would only work if the data is relatively static)

right index?

Charlie, May 25, 2005 - 3:23 pm UTC

Hi Tom,

It seems that we cannot use function based index here. Thanks! The story is that we convert our sensitive data into 1/0 bit string, a total of 1024 bits long (length(data)=1024). When the user tries to insert another record, we will compare the input 1024-bit-long string with every single row in the table to find "similarity" using the algorithm (function "compare") I provided here. If you looked at the "compare" function, it works like bitwise operation(bitand and bitor), but I need to loop through this byte array to do bit and/or operation. Is there a better way to do it? As I said, I want this query to run as fast as possible. If you were me, how would you tackle this problem?

Please help!

thanks,


Tom Kyte
May 25, 2005 - 7:39 pm UTC

it seems to be "non-similar"??

round(1 - v_and_sum / v_or_sum, 3);    

SQL> select * from t where compare(data, :bv) >= 0.5;

for compare to be >= 0.5, v_or_sum is bigger than v_and_sum meaning they have "few" 1 bits in common.

are you looking for "equal's" under the covers?  can we use a hash instead?


1024 is getting "insurmountable" with indexes for sure. 

right index?

Charlie, May 25, 2005 - 9:54 pm UTC

Hi Tom,

Good catch! ;) You are absolutely right! Because I have "1 - v_and_sum / v_or_sum", it's "non-similar". I should have coded "return round(v_and_sum/v_or_sum, 3);". The approach I provided here can be used to find two records that are the exact same -- "equal's", but the goal is to find similarity. I don't know how hash can be applied here. Since using index is not good, are there any other Oracle technologies that can solve this problem efficiently and effectively?

thanks again,

Tom Kyte
May 26, 2005 - 8:25 am UTC

I was hoping you were using this to find similar to then find exact-- but that you are using non-similar -- it gets even harder (a hash could be used to find candidates that when you looked at the records field by field would be the same)

short of 1024 compares field by field, I'm not sure what we can do.  We'd ultimately want to get to:

where f(database_column) <operator> f(:bind_variable)

because then we could function base index the database column and use it.  when yu have:

where f(database_column,:bind_variable) -- it gets "hard"

We might be able to use Text (seems strange, but I can see it working) and section searching -- you would "tag" the zeros and ones and index that.  Then you would be doing a document search

where contains( tagged_string, :bind_variable ) > 0

and you could even use "score" to rank them.  Here is a prototype implementation (for testing, I modified your compare to make the input strings fixed width:

begin
    v_num_arr1 := split(lpad(p_str1,1024,'0'));
    v_num_arr2 := split(lpad(p_str2,1024,'0'));

but otherwise used your code as is.  to_bin is a function of my own making:
http://asktom.oracle.com/~tkyte/hexdec/index.html
)

<b>first, we'll create your table and let CTXSYS 'see it' as we'll be indexing a function and CTXSYS must own the function:</b>

ops$tkyte@ORA9IR2> create table t ( id int primary key, x varchar2(1024) );
Table created.
 
ops$tkyte@ORA9IR2> grant select on t to ctxsys;
Grant succeeded.
 
ops$tkyte@ORA9IR2> create or replace function to_tag( p_str in varchar2 ) return varchar2
  2  as
  3      l_out long;
  4      l_str long := lpad(p_str,1024,'0');
  5  begin
  6      for i in 1 .. 1024
  7      loop
  8          if (substr(l_str,i,1)='1')
  9          then
 10              l_out := l_out || '<c'||i||'>1</c'||i||'>';
 11          end if;
 12      end loop;
 13      return '<doc>'||l_out||'</doc>';
 14  end;
 15  /
 
Function created.

<b>that function will turn  your string of zeros and ones into 'XML'

ops$tkyte@ORA9IR2> select to_tag( '101010' ) from dual;
 
TO_TAG('101010')
-------------------------------------------------------------------------------
<doc><c1019>1</c1019><c1021>1</c1021><c1023>1</c1023></doc>
</b>

ops$tkyte@ORA9IR2> grant execute on to_tag to ctxsys;
Grant succeeded.
 
ops$tkyte@ORA9IR2> create or replace function to_query( p_str in varchar2 ) return varchar2
  2  as
  3      l_out long;
  4      l_str long := lpad(p_str,1024,'0');
  5  begin
  6      for i in 1 .. 1024
  7      loop
  8          if (substr(l_str,i,1)='1')
  9          then
 10              l_out := l_out || ',{1}within c' || i;
 11          end if;
 12      end loop;
 13      return ltrim(l_out,',');
 14  end;
 15  /
Function created.

<b>and that function will make the text query:

ops$tkyte@ORA9IR2> select to_query( '101010' ) from dual;
 
TO_QUERY('101010')
-------------------------------------------------------------------------------
{1}within c1019,{1}within c1021,{1}within c1023
</b>
 
ops$tkyte@ORA9IR2> @connect ctxsys/pw

ctxsys@ORA9IR2> create or replace procedure index_str( p_id in rowid, p_str in out clob )
  2  as
  3      l_str ops$tkyte.t.x%type;
  4      l_out long;
  5  begin
  6      select lpad(x,1024,'0') into l_str from ops$tkyte.t where rowid = p_id;
  7      l_out := ops$tkyte.to_tag( l_str );
  8      dbms_lob.writeAppend( p_str, length(l_out), l_out );
  9  end;
 10  /
Procedure created.
 
ctxsys@ORA9IR2> grant execute on index_str to ops$tkyte;
Grant succeeded.

<b>that is the function we'll index (if possible, we could use varchar2 instead of clob as the out parameter but would be limited to 4000 characters -- depends on how many 1's you have</b>
 
ctxsys@ORA9IR2> @connect /

ops$tkyte@ORA9IR2> begin
  2     ctx_ddl.create_preference('my_user_datastore',
  3                               'user_datastore' );
  4
  5     ctx_ddl.set_attribute
  6     ( 'my_user_datastore',
  7        'procedure',
  8        'INDEX_STR' );
  9
 10      ctx_ddl.create_section_group( 'auto', 'AUTO_SECTION_GROUP' );
 11  end;
 12  /
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> insert into t
  2  select rownum, to_bin( abs(trunc(dbms_random.random)) )
  3    from all_users;
28 rows created.

<b>generate some strings of 1's and 0's</b>
 
ops$tkyte@ORA9IR2> create index t_idx on t(x)
  2  indextype is ctxsys.context
  3  parameters( 'datastore my_user_datastore filter ctxsys.null_filter section group auto' );
Index created.
 
ops$tkyte@ORA9IR2> variable BV varchar2(1024)
ops$tkyte@ORA9IR2> begin
  2      select x into :bv from t where rownum = 1;
  3  end;
  4  /
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> column x format a40
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select score(1), compare(x,:bv) c, t.*
  2    from t
  3   where contains( x, to_query(:bv), 1 ) > 0
  4   order by 1 desc;
 
  SCORE(1)          C         ID X
---------- ---------- ---------- ----------------------------------------
        93          0          1 1011101001010110100111000100000
        72         .5         20 1111101001011010100100011110111
        65       .524          6 1000101111010010110111010011010
        57        .64         24 1110111111111011101000101110000
        57       .526          5 1111101001000010100100110000110
        57       .571         11 1001100011100110101010001111001
        50       .667         27 101110110101110110110011100100
        50       .667         19 101110101010101101010111100101
        50       .636          2 110101101111011011001010100000
        50        .68         13 1101100110011001011111001100111
        50       .667         23 1111000000100111110101101011011
        43       .696         15 1000011110000111110001101100110
        43       .708          8 1101001111110001110001010010011
        43       .682         14 1011111110100100011000101100000
        36       .667         28 10010101000100000011010101000
        36       .684          3 1010000100010011010001101100000
        36       .714          4 1110100000000110110011101100
        36        .76         18 100101110110001111100001101011
        36       .727         16 100011000110101100101100011100
        36       .739         26 11000110010011101001010001111
        29        .75         22 10001001101001010110010000001
        29       .737         21 111000001110110001100000
        29       .762          9 11000011000000110001100001111
        22        .84         25 110000000111001011011110001011
        22       .818         12 11000001101000011100100000111
        22       .852          7 100010110111110101000110011011
        22       .826         10 1111000010010001010000101011010
         7       .913         17 101000110100000000001110001110
 
28 rows selected.


<b>Now, this is just a suggestion, an idea, a concept -- please, check out the TEXT documentation -- get familar with it, prototype this -- don't take this as "he said to do it this way".  

But if you do prototype it and go with it, I'd love to hear back as to how it went</b>
 

right index?

Charlie, May 26, 2005 - 11:36 am UTC

Very interesting idea!! I'll definitely test it out and keep you posted.

thanks again,

Is Hamming distance what you are looking to?

Mikito Harakiri, May 27, 2005 - 1:34 pm UTC

Then gogling "hamming distance index" might help

Tom Kyte
May 27, 2005 - 3:21 pm UTC

but, how do you index the data to find the number of vertices that differ, that is the question.

their function computes their concept of "distance", but each search would have to compute the distance for each row.

Therein lies the problem. Closest I could think of was BLAST, as far as indexing -- but that didn't seem to fit here either.

I'm hoping that by constraining on the top-n scores, they can find the first N best matches using text.

Function based index - function recompile

Mohan, January 12, 2006 - 2:16 am UTC

Tom,
Scenario:

I have a function based index on a table(business_entity_key) into which records are inserted through a trigger which fires whenever there is an insert on the table (person), i recompiled the function of the index and inserted few records into the person table, these records didn't get inserted into the business_entity_key table.

Question:
If the function of the Index is recompiled will the index get invalidated thereby the insert through the trigger is failing or someother thing involved with the index causing the problem?

Thanks in advance
Mohan

Tom Kyte
January 12, 2006 - 10:58 am UTC

did not follow you here. example needed

function-based index cound't work with OR-expansion

Steven, January 16, 2006 - 10:52 pm UTC

Function-based index doesn't work with OR-expansion. It seems Oracle has something worried about LNNVL().

Could you get an idea about why OR-expansion is not supported by FBI,please?

Thanks,

Tom Kyte
January 17, 2006 - 8:29 am UTC

FBI on partial column

A reader, January 19, 2006 - 1:30 am UTC

Hi Tom,
There is a function which uses dates and a columns of a table.
The SQL query if executed w/o the function executes very fast and is does less Logical reads. whereas using the function it does a hell of lot of Logical reads and executes in 52 seconds.
The functions syntax is like this:
func_name('date_bind_variable1','date_bind_variable2',table_col)

Now the question is how should I create the FBI?
Should it be func_name(table_col) or the approach should differ as the function is user defined.
Thanks as always..


Tom Kyte
January 19, 2006 - 12:35 pm UTC

without an example, no one can really comment.

Let me show you why:

There is a function which uses dates and columns of a table.
The SQL query if executed w/o the function executes very slowly and does tons of IO. whereas using the function - it goes very fast and does hardly anywork at all!


That is equally true. I have dozens of examples showing that.

So, you need to be a tad more explicit (concise, yet more giving with details)

Sorry...

A reader, January 20, 2006 - 12:54 am UTC

Hi Tom,
sorry for the incomplete information.
Thought the question would suffice.
Well the query is like this:
select week_end, token,
avg_week_diff('01-11-05','02-11-05', token) diff01,
avg_week_diff('01-12-05','02-12-05', token) diff02
from table
where token = <some value>
and...

W/o the function it executes very fast, and the L/O's are like 100 odd. But with the function in the select clause it is like 256466.
Also the query in the function uses the indexes efficiently.
The table has in excess of 100000 rows.

Also can a FBI be combined with a normal non expression columns.
Thanks as always..




Tom Kyte
January 20, 2006 - 10:13 am UTC

so, how does a function based index figure into this at all????


I don't get the crux of the question here.

RBO uses FBI

Zahir M, March 06, 2006 - 11:51 am UTC

I see the following quote from Oracle Application Developers Guide .
The following example seems to be contradicting with the quote in the manual.
According to my example , RBO does use the FBI.

Please advise.

<quote>
Restrictions for Function-Based Indexes
Note the following restrictions for function-based indexes:

Only cost-based optimization can use function-based indexes.

</quote>



SQL> create table tab1
  2  as
  3  select
  4   rownum    rnum,
  5   dbms_random.value(0,200) rand1,
  6   object_name   oname 
  7  from
  8   all_objects
  9  where
 10   rownum <= 3000
 11  ;

Table created.

SQL> create index tab1_idx1 on tab1(rnum, trunc(rand1));

Index created.

SQL> variable row_num number;
SQL> variable n1arg1 number;
SQL> variable n1arg2 number;

SQL> exec :row_num := 25;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
SQL> exec :n1arg1 := 10;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> exec :n1arg2 := 50;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> 

exec :row_num := 25;
exec :n1arg1 := 10;
exec :n1arg2 := 50;

SQL> set autotrace traceonly explain
SQL> explain plan for 
  2  Select oname from tab1 where 
  3  rnum = :row_num 
  4  and trunc(rand1) between :n1arg1 and :n1arg2 ;

Explained.

  1* Select * from table(dbms_xplan.display)
SQL> /
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------




SQL> Select oname from tab1 where 
  2  rnum = :row_num 
  3  and trunc(rand1) between :n1arg1 and :n1arg2 
  4  
SQL> /
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 3506415773

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    43 |     3   (0)| 00:00:01 |
|*  1 |  FILTER                      |           |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| TAB1      |     1 |    43 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | TAB1_IDX1 |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER(:N1ARG1)<=TO_NUMBER(:N1ARG2))
   3 - access("RNUM"=TO_NUMBER(:ROW_NUM) AND TRUNC("RAND1")>=TO_NUMBER(:N1ARG1)
              AND TRUNC("RAND1")<=TO_NUMBER(:N1ARG2))

Note
-----
   - dynamic sampling used for this statement


SQL> Explain plan for  Select
  2   /*+ RULE */
  3   oname from tab1 where
  4   rnum = :row_num
  5   and trunc(rand1) between :n1arg1 and :n1arg2
  6  /

Explained.

Elapsed: 00:00:00.01
SQL> Select * from table(dbms_xplan.display);
Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2137789089

---------------------------------------------------------------------------------------------
| Id  | Operation                         | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |         |  8168 | 16336 |    29   (0)| 00:00:01 |
|   1 |  COLLECTION ITERATOR PICKLER FETCH| DISPLAY |       |       |            |          |
---------------------------------------------------------------------------------------------




  1  Select
  2  /*+ RULE */
  3  oname from tab1 where
  4  rnum = :row_num
  5* and trunc(rand1) between :n1arg1 and :n1arg2
SQL> /
Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1918680838

-------------------------------------------------
| Id  | Operation                   | Name      |
-------------------------------------------------
|   0 | SELECT STATEMENT            |           |
|*  1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |
-------------------------------------------------

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

   1 - filter(TRUNC("RAND1")<=TO_NUMBER(:N1ARG2) AND
              TRUNC("RAND1")>=TO_NUMBER(:N1ARG1))
   2 - access("RNUM"=TO_NUMBER(:ROW_NUM))

Note
-----
   - rule based optimizer used (consider using cbo)
   
     1  Select
  2      oname from tab1 where
  3*   rnum = :row_num
SQL> /

ONAME
------------------------------
V$SQL_PLAN_STATISTICS_ALL

Elapsed: 00:00:00.04

Execution Plan
----------------------------------------------------------
Plan hash value: 1918680838

-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    30 |   900 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |    30 |   900 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |    12 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   2 - access("RNUM"=TO_NUMBER(:ROW_NUM))

Note
-----
   - dynamic sampling used for this statement

SQL> ed
Wrote file afiedt.buf

  1  Select
  2  /*+ RULE */
  3      oname from tab1 where
  4*   rnum = :row_num
SQL> /

ONAME
------------------------------
V$SQL_PLAN_STATISTICS_ALL

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1918680838

-------------------------------------------------
| Id  | Operation                   | Name      |
-------------------------------------------------
|   0 | SELECT STATEMENT            |           |
|   1 |  TABLE ACCESS BY INDEX ROWID| TAB1      |
|*  2 |   INDEX RANGE SCAN          | TAB1_IDX1 |
-------------------------------------------------

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

   2 - access("RNUM"=TO_NUMBER(:ROW_NUM))

Note
-----
   - rule based optimizer used (consider using cbo)


SQL> SElect * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production 

Tom Kyte
March 08, 2006 - 4:08 pm UTC

the rbo can use the non-function part of it, rnum in this case.

see how different the ACCESS lines are - the cbo did the full access using the function, the rbo cannot see the function at all.

index diable

mal, May 18, 2006 - 7:06 pm UTC

Whenever we compile function it disable the index ? What should be the soluition. I tried enable indexing but it
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

Tom Kyte
May 19, 2006 - 11:58 am UTC

er?

How about *an example*, show us what to do to encounter your problem.

FB Index disabled

Sasha, May 20, 2006 - 7:14 am UTC

Hi,
It is an answer to the previous question. It happens when you recompile function fb index relly on.
Just do next:

alter index <index_name> enable;

or just drop the index and recreate again (but beware that could invalidate some objects in the db).


What if Like is used

Murali, June 02, 2006 - 6:07 am UTC

When a function based index exists on a column and the query goes as upper(column) like '%r%' then the function based index will not be used. In such cases of like what is the best solution to address performance issues.

Tom Kyte
June 02, 2006 - 10:57 am UTC

eh?

it can be used, just as a normal index would be used.


ops$tkyte@ORA10GR2> create table t
  2  as
  3  select * from all_objects;
 
Table created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> create index t_idx on t(upper(OBJECT_NAME));
 
Index created.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats(user,'T');
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select upper(object_name) from t where upper(object_name) like '%r%';
 
Execution Plan
----------------------------------------------------------
Plan hash value: 3163761342
 
------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  2544 | 63600 |    59   (6)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T_IDX |  2544 | 63600 |    59   (6)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter(UPPER("OBJECT_NAME") LIKE '%r%')
 
ops$tkyte@ORA10GR2> set autotrace off


Is your question really along the lines of "how can I index in order to use leading wildcards in a like"? 

What if Like is used

Murali, June 05, 2006 - 3:00 am UTC

Tom,

Thanks for the explanation. Well I agree that the index will be used if I am selecting the Upper version of the column object_name in the select, but when the select picks only object_name the index is not utilized. Please find below the results of the statements executed.

SQL> create table t as select * from all_objects;

Table created.

SQL> create index t_idx on t(upper(OBJECT_NAME));

Index created.

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain
SQL> select upper(object_name) from t where upper(object_name)
  2  like '%r%';

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1647 Bytes=39528)                                                                  
                                                                                
   1    0   INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=4 Card=1647 Bytes=39528)                                                   
                                                                                



SQL> select object_name from t where upper(object_name)
  2  like '%r%';

Execution Plan
----------------------------------------------------------                      
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=45 Card=1647 Bytes=39528)                                                                 
                                                                                
   1    0   TABLE ACCESS (FULL) OF 'T' (Cost=45 Card=1647 Bytes=39528)

Thanks 

Tom Kyte
June 05, 2006 - 7:48 am UTC

that is because it has to goto the table to get the object_name and it says "that would be less than efficient to hip hop from the index to the table/index to table over and over and over again"

forget the function based index here - it would happen without a function based index just the same if it has to go to the table:

ops$tkyte@ORA10GR2> /*
ops$tkyte@ORA10GR2> drop table t;
ops$tkyte@ORA10GR2> create table t
ops$tkyte@ORA10GR2> as
ops$tkyte@ORA10GR2> select * from all_objects;
ops$tkyte@ORA10GR2> create index t_idx on t(object_name);
ops$tkyte@ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
ops$tkyte@ORA10GR2> */
ops$tkyte@ORA10GR2>
ops$tkyte@ORA10GR2> set autotrace traceonly explain
ops$tkyte@ORA10GR2> select object_name from t where object_name like '%r%';

Execution Plan
----------------------------------------------------------
Plan hash value: 3163761342

------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       |  2493 | 62325 |    81   (5)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T_IDX |  2493 | 62325 |    81   (5)| 00:00:01 |
------------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME" LIKE '%r%')

ops$tkyte@ORA10GR2> select object_name, owner from t where object_name like '%r%';

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2493 | 77283 |   224   (3)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  2493 | 77283 |   224   (3)| 00:00:02 |
--------------------------------------------------------------------------

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

   1 - filter("OBJECT_NAME" LIKE '%r%')

ops$tkyte@ORA10GR2> set autotrace off
 

What if Like is used

Murali, June 05, 2006 - 9:08 am UTC

Well tom then what should be the best approach for the below case

1. The data has to be stored in the same case as entered at the time of insertion.
2. The search will be performed on a substring of the data entered in step 1. But the search should be case insensitive. So leads to the usage of like on the search string and Upper function on the column being searched.
3. What best can be done to improve the performance.


Possible Solution

John Rowbottom, June 06, 2006 - 3:52 am UTC

If your search is going to be performed on a single column, then you can improve the performance of the query by including the column (as well as upper(column)) in the index. This way you can answer the whole query from the index without needing to go to the table at all.

ie:

SQL> create table t as select * from all_objects;

Table created.

SQL> create index t_idx on t(upper(OBJECT_NAME),object_name);

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

SQL> set autotrace traceonly explain;
SQL> select upper(object_name) from t where upper(object_name) 
  2  like '%r%';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=4091 Bytes=77
          729)

   1    0   INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=5 Car
          d=4091 Bytes=77729)




SQL> select object_name from t where upper(object_name) like '%r%';

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=4091 Bytes=77
          729)

   1    0   INDEX (FAST FULL SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=5 Car
          d=4091 Bytes=77729)


 

Tom Kyte
June 06, 2006 - 8:37 am UTC

but, if you really want to improve the performance of leading wild card queries - Oracle Text linked to above.....

but yes, including the original case of the column at the end of the index will avoid the table access by index rowid.

reader, August 17, 2006 - 9:26 am UTC

After dropping the function-based index, is the invalidation of dependent objects on the table expected behavior? I have used drop index command to drop the function-based index, still it has invalidated all the dependent packages on the table. We use 9.2.0.6 version

I appreciate your response

thank you

Tom Kyte
August 17, 2006 - 10:07 am UTC

this is expected.  function based indexes are implemented under the covers using a "hidden virtual column"

the act of dropping a function based index drops this hidden column - dropping a column from a table invalidates dependent objects.

this is just a demo, do not access the virtual column yourself in "real life"

ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(abs(x));

Index created.

ops$tkyte%ORA10GR2> insert into t values ( 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into t values ( -1 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select name from sys.col$
  2   where obj# = (select object_id
  3                   from user_objects
  4                  where object_name = 'T' );

NAME
------------------------------
SYS_NC00002$
X

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> column name new_val C
ops$tkyte%ORA10GR2> select name from sys.col$
  2   where obj# = (select object_id
  3                   from user_objects
  4                  where object_name = 'T' )
  5     and name <> 'X';

NAME
------------------------------
SYS_NC00002$

ops$tkyte%ORA10GR2> select x, &c from t;
old   1: select x, &c from t
new   1: select x, SYS_NC00002$ from t

         X SYS_NC00002$
---------- ------------
         1            1
        -1            1

ops$tkyte%ORA10GR2> drop index t_idx;

Index dropped.

ops$tkyte%ORA10GR2> select x, &c from t;
old   1: select x, &c from t
new   1: select x, SYS_NC00002$ from t
select x, SYS_NC00002$ from t
          *
ERROR at line 1:
ORA-00904: "SYS_NC00002$": invalid identifier
 

A reader, August 17, 2006 - 10:52 am UTC

Thanks a lot Tom for your detailed explanation! now I know why I had the issue with the function based index.


thx

Query with Function on a CLOB column

A Reader, September 06, 2006 - 1:16 pm UTC

Hi Tom,

We have some queries that need to do "where UPPER(CLOB_COLUMN)=UPPER('USER_INPUT')". We can not build a function based index for the UPPER()with the CLOB column. What's the best way to handle this kind of cases? We don't have to have details, but we want your advice on the directions to go or an example.

Thank you for your help.

Tom Kyte
September 06, 2006 - 4:04 pm UTC

looks like a text index to me

where contains( clob_column, :user_input ) > 0

would be possible after that. See the text guide for that.

Oracle says you can create funcation-based index for LOB?

A Reader, September 06, 2006 - 4:53 pm UTC

Tom,
In oracle document, </code> http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b10759/statements_5010.htm#i2077034 <code>
it says function-based index can be created for LOBs and it even gives an example.

When I did that, I got the following message:
ERROR at line 1:
ORA-02327: cannot create index on expression with datatype LOB

What do I miss? We are in 10.2.0.1.

Thank you for your help.

Tom Kyte
September 07, 2006 - 6:43 am UTC

where did it say a function based index there?

they were discussing domain indexes (eg: as if you were to create your OWN ctxsys.context indextype! which you wouldn't since they already exist)

A reader, September 13, 2006 - 9:27 pm UTC

Hi Tom,

I am trying find a solution where only one combination of key1 and col1
can inserted and duplicate of same is allowed. I would really appreciate
any help in this regard.


create table test1(key1 number, col1 varchar2(2));

select * from test1;


key1 col1
---- -----
1 A
1 A
1 A

1 B-------not alowed

2 A
2 A

3 B
3 B
3 B

3 A-------not allowed


Thanks and regards.

Tom Kyte
September 14, 2006 - 9:08 am UTC

ops$tkyte%ORA10GR2> create table parent_of_test1( key1 number, col1 varchar2(2), primary key(key1,col1));

Table created.

ops$tkyte%ORA10GR2> create table test1( key1, col1, foreign key(key1,col1) references parent_of_test1 );

Table created.
 

Michel Cadot, September 14, 2006 - 10:13 am UTC

Tom,

As far I understand the problem, for each key1 you can have only 1 different value for col1 that can be duplicate.
If so I think your solution does not work as your "parent_of_test1" primary key allows multiple col1 value for each key1 and so forbidden rows can be inserted:

SQL> create table parent_of_test1 ( 
  2    key1 number,
  3    col1 varchar2(2), 
  4    primary key (key1,col1)
  5    )
  6  /

Table created.

SQL> create table test1 (
  2    key1 number,
  3    col1 varchar2(2), 
  4    foreign key (key1,col1) references parent_of_test1
  5    )
  6  /

Table created.

SQL> insert into parent_of_test1 values (1, 'A');

1 row created.

SQL> insert into parent_of_test1 values (1, 'B');

1 row created.

SQL> insert into parent_of_test1 values (2, 'A');

1 row created.

SQL> insert into parent_of_test1 values (3, 'B');

1 row created.

SQL> insert into parent_of_test1 values (3, 'A');

1 row created.

SQL> insert into test1 values (1, 'A');

1 row created.

SQL> insert into test1 values (1, 'A');

1 row created.

SQL> insert into test1 values (1, 'A');

1 row created.

SQL> insert into test1 values (1, 'B');

1 row created.

SQL> insert into test1 values (1, 'B');

1 row created.

SQL> insert into test1 values (2, 'A');

1 row created.

SQL> insert into test1 values (2, 'A');

1 row created.

SQL> insert into test1 values (3, 'B');

1 row created.

SQL> insert into test1 values (3, 'B');

1 row created.

SQL> insert into test1 values (3, 'B');

1 row created.

SQL> insert into test1 values (3, 'A');

1 row created.

SQL> select * from test1;
      KEY1 CO
---------- --
         1 A
         1 A
         1 A
         1 B
         1 B
         2 A
         2 A
         3 B
         3 B
         3 B
         3 A

11 rows selected.

If we change the primary key to only key1 and create a unique constraint on (key1,col1) then we achieve the goal:

SQL> create table parent_of_test1 ( 
  2    key1 number primary key,
  3    col1 varchar2(2), 
  4    unique (key1,col1)
  5    )
  6  /

Table created.

SQL> create table test1 (
  2    key1 number,
  3    col1 varchar2(2), 
  4    foreign key(key1,col1) references parent_of_test1 (key1,col1)
  5    )
  6  /

Table created.

SQL> insert into parent_of_test1 values (1, 'A');

1 row created.

SQL> insert into parent_of_test1 values (1, 'B');
insert into parent_of_test1 values (1, 'B')
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.SYS_C005422) violated


SQL> insert into parent_of_test1 values (2, 'A');

1 row created.

SQL> insert into parent_of_test1 values (3, 'B');

1 row created.

SQL> insert into parent_of_test1 values (3, 'A');
insert into parent_of_test1 values (3, 'A')
*
ERROR at line 1:
ORA-00001: unique constraint (MICHEL.SYS_C005422) violated


SQL> 
SQL> insert into test1 values (1, 'A');

1 row created.

SQL> insert into test1 values (1, 'A');

1 row created.

SQL> insert into test1 values (1, 'A');

1 row created.

SQL> insert into test1 values (1, 'B');
insert into test1 values (1, 'B')
*
ERROR at line 1:
ORA-02291: integrity constraint (MICHEL.SYS_C005424) violated - parent key not found


SQL> insert into test1 values (1, 'B');
insert into test1 values (1, 'B')
*
ERROR at line 1:
ORA-02291: integrity constraint (MICHEL.SYS_C005424) violated - parent key not found


SQL> insert into test1 values (2, 'A');

1 row created.

SQL> insert into test1 values (2, 'A');

1 row created.

SQL> insert into test1 values (3, 'B');

1 row created.

SQL> insert into test1 values (3, 'B');

1 row created.

SQL> insert into test1 values (3, 'B');

1 row created.

SQL> insert into test1 values (3, 'A');
insert into test1 values (3, 'A')
*
ERROR at line 1:
ORA-02291: integrity constraint (MICHEL.SYS_C005424) violated - parent key not found


SQL> select * from test1;
      KEY1 CO
---------- --
         1 A
         1 A
         1 A
         2 A
         2 A
         3 B
         3 B
         3 B

8 rows selected.

Michel

 

Tom Kyte
September 14, 2006 - 10:42 am UTC

doh, you are right. I read it way too fast. thanks.


and your modification - to make key1 the primary key in parent of - is absolutely correct. thanks again.

A Reader, September 14, 2006 - 3:37 pm UTC

Tom/Michel, fantastic solution!!!

Thanks very much.



Any suggestion?

Bobby, October 17, 2006 - 6:26 am UTC

Hello Tom,
I have the following:
Table T1
COL1 VARCHAR2(5)
COL2 VARCAHR2(5)
COL3 VARCHAR2(1)

Created a Primary key on COL1 & COL2

This is the required query:

SELECT *
FROM T1
WHERE COL1 <> :SOMEVALUE
AND LENGTH(TRIM(COL2)) = 1
AND COL3 <> 'X'

I want to create a index on COL1 & LENGTH(TRIM(COL2)). How can I acheive this? Is there any alternative so that index would be used?

Any suggestions.

Thanks

Tom Kyte
October 17, 2006 - 7:18 am UTC

seems like:

create index t_idx on t1( length(trim(col2)), decode(col3,'X',0,1), col1)

and a query:

where length(trim(col2)) = 1 and decode(col3,'X',0,1) = 1 and col1 <> :something


that'll index range scan on the leading edge of t_idx (if that makes sense to do so, if there are few entries that match that) and find the rows where col1 <> :something and then table access by index rowid that data.



Thanks

A reader, October 19, 2006 - 1:15 am UTC

Thanks for the solution :)

Function Based Index

Prasanna, November 30, 2006 - 6:39 am UTC

Hi Tom,

I have one table PhoneHist, which contact effective_start_dt date column. When I query the table for getting max(effective_start_dt), its taking 300 secs to retrieve records.I created normal index on effective_start_dt, still the problem exists.
So I would like to create a function based index. But it is not allowing the group function while creating index.
"create index temp_idx on PhoneHist (Max(effective_start_dt)); "
How to solve this problem?

Please let me know asap.

Thanks,
Prasanna



Tom Kyte
November 30, 2006 - 10:03 am UTC

umm, got full example - cause if you

a) have a simple table with "effective_start_dt" as a date
b) created an index on table(effective_start_dt)
c) query "select max(effective_start_dt) from table"

it would do an index min/max scan and be instantaneous

so, you must be with-holding some big piece of relevant information.

Interesting

Robert Lockard, January 10, 2007 - 4:53 pm UTC

Tom,
Here is an interesting problem on function based indexes. The first example uses our standard dateformat of 'RRRRMMDD'. This returns
the now almost word famous ORA-01743: only pure functions can be indexed. When I canged the RRRR to YYYY the index was created. What
gives. Is RRRR not a pure function and YYYY a pure function?

-Rob

SQL> select version from v$instance;

VERSION
-----------------
9.2.0.6.0

SQL>



SQL> L
1 create index ifsuser.tstatus_timestamp_fidx1 on ifsuser.tstatus(to_date(substr(timestamp,1,8),'RRRRMMDD'))
2* tablespace indx
SQL> /
create index ifsuser.tstatus_timestamp_fidx1 on ifsuser.tstatus(to_date(substr(timestamp,1,8),'RRRRMMDD'))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed


SQL>


SQL> create index ifsuser.tstatus_timestamp_fidx1 on ifsuser.tstatus(to_date(substr(timestamp,1,8),'YYYYMMDD'))
tablespace indx
/
2 3

Index created.

SQL> SQL>

SQL> analyze index ifsuser.tstatus_timestamp_fidx1 compute statistics;

Index analyzed.

SQL>


To: Robert Lockard

Michel Cadot, January 24, 2007 - 7:16 am UTC

Just because RRRR result depends on the current year, so it is not deterministic.

Regards
Michel

How Similar Must SQL String Be?

Richard, February 14, 2007 - 6:02 am UTC

Hi,


*** Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - Prod ***

I have a Function-Based Index (FBI), as follows:
CREATE INDEX my_fbi ON my_table(ROUND((numeric_1* numeric_2) / 100,2));

... where "numeric_1" and "numeric_2" are both numeric columns in my_table.

I created this FBI, as V$SQL shows statements with "ROUND((numeric_1* numeric_2) / 100,2)" appearing many times.

So, my question is: Will this FBI "work" if the statement it is based on changes textually, but not functionally? e.g. an extra whitespace, etc., appears in the issued SQL statement?
Tom Kyte
February 14, 2007 - 8:34 am UTC

yes, it will

but I always suggest using a view to create a virtual column - to make it so that the text is always correct and if you ever change the function - you have one place to do it.

Great! Thanks!

A reader, February 15, 2007 - 7:54 am UTC


FBI,

A reader, April 16, 2007 - 12:20 pm UTC

I have a situation where I select 1-5 rows out of millions from a table. Having index in this situation is ideal. However, the column stores lastname and the values can be upper or lower or both.

If I use UPPER or LOWER function in my WHERE clause, then Oracle doesn't use index and the query runs for long time.

Can I create function based index? Will it impact the loads on that table? Is there any other alternative other than function based index?

Thanks,

Tom Kyte
April 16, 2007 - 1:34 pm UTC

can you create a function based index?
sure

will it impact loads on the table?
not any much more than any other index (the builtin functions are pretty "fast")


You can create a text index (they are case insensitive)
You can use case insensitive searching - a new 10g feature.

Query

dm, April 17, 2007 - 5:30 am UTC

Hi Tom

What is the differnce between these two from the performance point.Please explain:
1) Create Procedure Proc_name as
Cursor cur_name as select * from emp;
..
Begin
Open cursor
for I in cur_name
..
..
...<some operations>
End Proc_name;
2) Create Procedure Proc_name as
..
Begin
for I in (select * from emp)
..
..
...<some operations>
End Proc_name;
Many thanks
DM
Tom Kyte
April 17, 2007 - 10:07 am UTC

In 10g,
begin
    for x in 
    ( select *
        from big_table.big_table
       where rownum <= 10000 )
    loop
        null;
    end loop;
end;



is equivalent to this code:
declare
    type array is table of big_table%rowtype; 
    l_data array;
    cursor c is 
          select * from big_table where rownum <= 1000;
begin
    open c;
    loop
        fetch c bulk collect into l_data limit 100;
        for i in 1 .. l_data.count
        loop
            null;
        end loop;
        exit when c%notfound;
    end loop;
    close c;
end;


so, that implicit cursor for loop is not only MUCH EASIER TO CODE, MORE BUG FREE (less chance of making a mistake) it is more efficient than your (i fixed your code by the way, it wouldn't work as templated):

Create Procedure Proc_name as
  Cursor cur_name as select * from emp;
  ..
  Begin
  Open cursor
  loop
     fetch cursor into ...
     exit when cursor%notfound;
  ..
  ..
...<some operations>
End Proc_name; 



I suggest using explicit cursors like that ONLY WHEN NECESSARY, and when you do, consider using bulk collect

Could You Explian?

Richard Armstrong-Finnerty, May 02, 2007 - 8:09 am UTC

You wrote:
In 10g,

begin
    for x in 
    ( select *
        from big_table.big_table
       where rownum <= 10000 )
    loop
        null;
    end loop;
end;


I am a bit befuddled!

Is the big_table.big_table syntax a new 10g thing? If so, what's it all about?

I am not a Developer, but am interested in anything that might speed things up!

Tom Kyte
May 02, 2007 - 9:09 am UTC

select * from OWNER.TABLE_NAME


i had a table owned by BIG_TABLE.
the name of the table was BIG_TABLE.

it is just a big table I use to demonstrate concepts with.

I see!

Richard Armstrong-Finnerty, May 02, 2007 - 9:16 am UTC

Thanks! I suspected as much, but one never knows!

Function based index getting invalidated

Raghav, May 09, 2007 - 6:00 am UTC

Hi Tom

A function based index is built on a table (deterministic function exists in a package). When ever the package is compiled or under lying objects in the package is altered oracle automatically re-compiles the package and this function based index is getting invalid and because of that, the system is not allowing any DML operations on the table.
"error:ORA-30554 Function based index is disabled"
To over come this problem, we have dropped that index and rebuilt the index which has resolved the issue.

But this is not possible always to drop the index and rebuild the same every time.
My question is whether is it possible to automate this process? or any method to avoid the index becoming invalid automatically?

Thanks in advance
Raghav
Tom Kyte
May 11, 2007 - 9:40 am UTC

i would suggest you break this function OUT and do not change it anymore.

And if you do change it, it would have to be during scheduled maintenance times and your script that creates this function would have the necessary alter index I rebuild.

Query not Using Function-Based Index

Alex, July 09, 2007 - 12:27 am UTC

Hi Tom,

I have a query, which is not using a function-based index. This problem is occuring on a 10g instance. It works just fine (meaning it uses the function-based index) on a 9i instance that contain the same structure.

The query is as follows:
SELECT 
c.*
 FROM 
 ARO.UNET_CLAIM C 
 WHERE 
 SUBSTR(C.CLM_AUD_NBR,1,10) IN ('0000000000')


The index in question is as follows:
CREATE INDEX UNET_CLM_CLM_AUD_NBR_SUBSTR ON UNET_CLAIM
(SUBSTR(CLM_AUD_NBR,1,10))
  TABLESPACE DATAMART_INDX
LOCAL 
NOPARALLEL
compute statistics;

If I just run the explain plan, it shows index range scan of the correct index:
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |  6749 |  4323K|  5115   (1)|       |       |
|   1 |  PARTITION RANGE ALL               |                             |  6749 |  4323K|  5115   (1)|     1 |    14 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| UNET_CLAIM                  |  6749 |  4323K|  5115   (1)|     1 |    14 |
|*  3 |    INDEX RANGE SCAN                | UNET_CLM_CLM_AUD_NBR_SUBSTR |  6749 |       |    49   (0)|     1 |    14 |
-----------------------------------------------------------------------------------------------------------------------

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

   3 - access(SUBSTR("CLM_AUD_NBR",1,10)='0000000000')


However, when I actually run the query, Oracle uses full table scan.

All of the requirements for the use of function based indexes seem to be met:

1. The index was created with the COMPUTE STATISTICS.
2. The statistics on the table were gathered after the index was created. The table statistics were estimated. Could that be the problem? Does it need to be computed?
3. The optimizer is cost-based.
optimizer_dynamic_sampling           integer                          2
optimizer_features_enable            string                           10.2.0.2
optimizer_index_caching              integer                          0
optimizer_index_cost_adj             integer                          100
optimizer_mode                       string                           ALL_ROWS
optimizer_secure_view_merging        boolean                          TRUE

4. The query rewrite parameters are set as follows:
query_rewrite_enabled                string                           TRUE
query_rewrite_integrity              string                           TRUSTED


Compatible is set to 9.2. Could that be the problem? Does it have to be at 10.2?
compatible                           string                           9.2.0.0.0


Do you know why this query is not using the function-based index?

Thanks in advance for your help.

Alex

Tom Kyte
July 09, 2007 - 6:48 am UTC

show us how you determine it is using a full scan.

Re: Query not Using Function-Based Index

Alex, July 09, 2007 - 1:49 pm UTC

Well, I was using v$sql_plan to check the runtime explain plan. I didn't let the query finish if it ran longer than a minute (normal runtime in the 9i environment is 1 second).

Here is the explain plan from v$sql_plan:

SQL> l
  1  select distinct id, parent_id, lpad (' ', depth) || operation operation,
  2       options, object_name, cost
  3       from v$sql_plan
  4       where hash_value = 3165394104
  5*      order by id
SQL> /

        ID  PARENT_ID OPERATION                      OPTIONS    OBJECT_NAME                          COST
---------- ---------- ------------------------------ ---------- ------------------------------ ----------
         0            SELECT STATEMENT                                                            1852980
         1          0  PARTITION RANGE               ALL                                          1852980
         2          1   TABLE ACCESS                 FULL       UNET_CLAIM                        1852980


However, this time, I ran it and with autotrace, it finished in 27 minutes and it said it used the index. However, there is an unusual number logical I/O's required to look up one value.

SQL> SELECT
  2  c.*
  3   FROM
  4   ARO.UNET_CLAIM C
  5   WHERE
  6   SUBSTR(C.CLM_AUD_NBR,1,10) IN ('0000000000') ;

no rows selected

Elapsed: 00:27:31.77

Execution Plan
----------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                        | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                             |  6749 |  4323K|  5115   (1)|       |       |
|   1 |  PARTITION RANGE ALL               |                             |  6749 |  4323K|  5115   (1)|     1 |    14 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID| UNET_CLAIM                  |  6749 |  4323K|  5115   (1)|     1 |    14 |
|*  3 |    INDEX RANGE SCAN                | UNET_CLM_CLM_AUD_NBR_SUBSTR |  6749 |       |    49   (0)|     1 |    14 |
-----------------------------------------------------------------------------------------------------------------------

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

   3 - access(SUBSTR("CLM_AUD_NBR",1,10)='0000000000')

Note
-----
   - 'PLAN_TABLE' is old version


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
    8316098  consistent gets
    8277674  physical reads
        660  redo size
       5077  bytes sent via SQL*Net to client
        231  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed


There are 5340032 distinct_keys in the index.

Why are consistent gets and runtime so high?

Thanks

Tom Kyte
July 09, 2007 - 2:21 pm UTC

so, do you have cursor sharing set to something other than exact??

Re: Query not Using Function-Based Index

Alex, July 09, 2007 - 10:50 pm UTC

Sure enough, this was the problem! cursor_sharing was set to force. As soon as I set cursor_sharing=exact at the session level, it started working as expected.

Could you please explain how/why cursor_sharing was causing this behavior?

Thank you very much for your help!

Tom Kyte
July 10, 2007 - 11:48 am UTC

when cursor_sharing is set to force or similar, the query:

SQL> SELECT
  2  c.*
  3   FROM
  4   ARO.UNET_CLAIM C
  5   WHERE<b>
  6   SUBSTR(C.CLM_AUD_NBR,1,10) IN ('0000000000') ;</b>


becomes
SQL> SELECT
  2  c.*
  3   FROM
  4   ARO.UNET_CLAIM C
  5   WHERE<b>
  6   SUBSTR(C.CLM_AUD_NBR,:bv1,:bv2) IN (:bv3) ;</b>



we no longer "know" substr( column, 1, 10 ) - hence we cannot use that function - because :bv1 and :bv2 could be ANY VALUE, not just 1 and 10

function-based index not being used in 9.2.0.8

VKOUL, September 24, 2007 - 4:57 pm UTC

Consider the test case
*********************************
9.2.0.7
*********************************
SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      TRUSTED
SQL> 
SQL> CREATE TABLE TEST
  2  (
  3    X  NUMBER,
  4    Y  NUMBER
  5  );

Table created.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> CREATE INDEX FBI ON TEST (CASE  WHEN ("X"<>4 AND ("Y"<>3 OR "Y" IS NULL)) THEN 1 END);

Index created.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 1   , 1   ); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 2   , NULL); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( NULL, 3   ); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 4   , 4   ); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 5   , NULL); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( NULL, 6   ); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 7   , 7   );

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>  
SQL> SELECT --+ INDEX(TEST FBI) 
  2         COUNT(*)
  3  FROM   TEST
  4  WHERE  CASE
  5            WHEN x <> 4 AND (y <> 3 OR y IS NULL)
  6            THEN 1
  7         END = 1;

  COUNT(*)
----------
         4

Elapsed: 00:00:00.00
SQL> 
SQL> set autotrace trace explain statistics
SQL> 
SQL> /

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=1 Card=2
          Bytes=52)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
SQL> 
SQL> 
*********************************************************
Consider the test case
*********************************
9.2.0.8
*********************************
SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      TRUSTED
SQL> 
SQL> 
SQL> CREATE TABLE TEST
  2  (
  3    X  NUMBER,
  4    Y  NUMBER
  5  );

Table created.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> CREATE INDEX FBI ON TEST (CASE  WHEN ("X"<>4 AND ("Y"<>3 OR "Y" IS NULL)) THEN 1 END);

Index created.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 1   , 1   ); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 2   , NULL); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( NULL, 3   ); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 4   , 4   ); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 5   , NULL); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( NULL, 6   ); 

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> INSERT INTO TEST ( X, Y ) VALUES ( 7   , 7   );

1 row created.

Elapsed: 00:00:00.00
SQL> 
SQL> 
SQL> commit;

Commit complete.

Elapsed: 00:00:00.00
SQL>  
SQL> SELECT --+ INDEX(TEST FBI) 
  2         COUNT(*)
  3  FROM   TEST
  4  WHERE  CASE
  5            WHEN x <> 4 AND (y <> 3 OR y IS NULL)
  6            THEN 1
  7         END = 1;

  COUNT(*)
----------
         4

Elapsed: 00:00:00.00
SQL> 
SQL> set autotrace trace explain statistics
SQL> 
SQL> /

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=26)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TEST' (Cost=2 Card=1 Bytes=26)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          7  consistent gets
          0  physical reads
          0  redo size
        406  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> 
SQL> 
**********************************************************

Tom Kyte
September 26, 2007 - 8:52 pm UTC

on the road right now, i see what is happening, but don't have the necessary facilities to research it right now. Please utilize support - or ping it again next week.

i can reproduce at 10.2.0.3

martina, September 28, 2007 - 3:42 am UTC

hi vkoul,

i can reproduce your test-case. it works at 9.2.0.7 and fails at 9.2.0.8 but 10.2.0.3 as well.

How did you get to this test-case, because other fbis work at 9.2.0.8 and 10.2.0.3 ?

regards, martina

using an fbi

martina, October 02, 2007 - 2:38 am UTC

Hi Tom,

Are there any plans for the optimizer to analyze the function that underlies the function-based index?

for example: I have a function based index:
"case when aktion_id > 0 then aktion_id end"
the index would be perfect for a query like:
"where aktion_id = 80008269"

The reason is: I can get a developer to write "where case when aktion_id > 0 then aktion_id end = 80008269" but probably not a user. A function based index would be nice as most of the aktion_id are < 0, not null as it is part of the primary key.

thank you,
martina



Tom Kyte
October 03, 2007 - 4:39 pm UTC

create or replace view v
as
select ...., case when aktion_id>0 then aktion_id end new_col
  from t;



I would not hold my breath too long for the optimizer to try to look into the function and rewrite them to see if they could possibly be used.

function based index

kishore, June 04, 2008 - 2:31 pm UTC

Hi tom,

How can I export/import function based index ? I tried to do this. Everything has been imported except function based index. Is there any special way to export/import Function based index ?

Thanks
kishore
Tom Kyte
June 04, 2008 - 4:33 pm UTC

need a tad more detail as to how to reproduce YOUR issue

ops$tkyte%ORA10GR2> create table t ( x varchar2(20) );

Table created.

ops$tkyte%ORA10GR2> create index i on t(upper(x));

Index created.

ops$tkyte%ORA10GR2> !exp userid=/ 'owner=ops$tkyte'

Export: Release 10.2.0.1.0 - Production on Wed Jun 4 16:26:12 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OPS$TKYTE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OPS$TKYTE
About to export OPS$TKYTE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export OPS$TKYTE's tables via Conventional Path ...
. . exporting table                              T          0 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

ops$tkyte%ORA10GR2> drop table t purge;

Table dropped.

ops$tkyte%ORA10GR2> !imp userid=/ full=y

Import: Release 10.2.0.1.0 - Production on Wed Jun 4 16:27:13 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
. importing OPS$TKYTE's objects into OPS$TKYTE
. . importing table                            "T"          0 rows imported
Import terminated successfully without warnings.

ops$tkyte%ORA10GR2> select index_name from user_indexes;

INDEX_NAME
------------------------------
I

ops$tkyte%ORA10GR2> select * from user_ind_expressions;

INDEX_NAME                     TABLE_NAME
------------------------------ ------------------------------
COLUMN_EXPRESSION
-------------------------------------------------------------------------------
COLUMN_POSITION
---------------
I                              T
UPPER("X")
              1


fbi usage in a query

Michal Pravda, July 24, 2008 - 8:02 am UTC

Good morning Tom,

I use an FBI to enforce that only one valid (statud<>'D') record for a given reference exists. And it works as intended. However I thought, that it would be used in a query as well, but I can't make it work.

The most simplified example:
pravda_m@ISD> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE    10.2.0.3.0      Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

pravda_m@ISD> drop table testfbi;

Table dropped.

pravda_m@ISD> create table testfbi
  2  as select object_id , trunc(object_id /3 + 1) reference_id,
  3            decode(mod (object_id, 3) , 0, 'I', 'D') status
  4     from all_objects;

Table created.

pravda_m@ISD> 
pravda_m@ISD> alter table testfbi
  2  add constraint pk_testfbi
  3  primary key (object_id);

Table altered.

pravda_m@ISD> create unique index idx_testfbi
  2  on testfbi (case when status <> 'D' then reference_id end);

Index created.

pravda_m@ISD> analyze table testfbi
  2  compute statistics
  3  for table
  4  for all indexes
  5  for all indexed columns;

Table analyzed.

pravda_m@ISD> set autotrace on
pravda_m@ISD> select * from testfbi where status <> 'D' and reference_id = 50;

 OBJECT_ID REFERENCE_ID S
---------- ------------ -
       147           50 I


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=40 Card=28 Bytes=392)
   1    0   TABLE ACCESS (FULL) OF 'TESTFBI' (TABLE) (Cost=40 Card=28 Bytes=392)




Statistics
----------------------------------------------------------
         60  recursive calls
          0  db block gets
        148  consistent gets
          0  physical reads
          0  redo size
        290  bytes sent via SQL*Net to client
        277  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


Can I somehow enforce the integrity and don't do FTS at the same time. I can, of course, create a separate index on (reference_id) or (reference_id, status), but I would like not to, since all information needed is already in the first index. Or is it not?

Thank you.

Tom Kyte
July 24, 2008 - 11:00 am UTC

you have to use the expression you have indexed - status is not indexed, reference id is not indexed, your query accesses them.


where (case when status <> 'D' then reference_id end) = 50;

would tend to use the index.

it works

Michal Pravda, July 25, 2008 - 4:26 am UTC

I have checked the documentation (again) and have found out that I oversighted one paragraph yesterday. The one saying that compiler compares expression trees when considering FBI.

So as you often say every information is available, just have to find it (and internalize it properly).

Thank you.

FBI Materialization

Salman Syed, February 03, 2009 - 4:57 pm UTC

Tom,

Are Function-based Indexes materialized on commit or when a select is issue against it?

I created a function-based index which needs to do quite a bit of work on only a 2000 row table.

When I just select the 'hidden' column, it takes 25 second to return 1704 rows.

So I issue something like this:

select SYS_NC00045$ from opp_test: 1704 rows and 25.89 seconds

However, if I select any other column, it is much quicker:

select opportunity_id from opp_test: 1704 rows in 0.55 seconds.

There is no one else operating on this table.

Looking forward to a response.
Tom Kyte
February 03, 2009 - 5:25 pm UTC

function based indexes are just b*tree indexes that happen to reference a function instead of a plain old column.

They are maintained in real time, as the modifications happen.

please don't mess around with hidden columns, I'm not going to address that.

Please do:

a) select f(x) from table;
b) select x from table;

and post tkprofs of those. Likely, it is seeing "gotta get all of the rows and f(x) might return NULL so we cannot use the index"


ops$tkyte%ORA10GR2> create or replace function f( p_x in number ) return number
  2  deterministic
  3  as
  4  begin
  5          dbms_lock.sleep(0.1);
  6          return 42;
  7  end;
  8  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t
  2  as
  3  select * from all_users;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx on t( f(user_id) );

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> set timing on
ops$tkyte%ORA10GR2> set autotrace on explain
ops$tkyte%ORA10GR2> select f(user_id) from t;

F(USER_ID)
----------
        42
        42
..
        42
        42

40 rows selected.
<b>
Elapsed: 00:00:04.05
</b>
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    40 |   520 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |    40 |   520 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

ops$tkyte%ORA10GR2> select f(user_id) from t where f(user_id) is not null;

F(USER_ID)
----------
        42
        42
...
        42
        42

40 rows selected.
<b>
Elapsed: 00:00:00.01
</b>
Execution Plan
----------------------------------------------------------
Plan hash value: 2277287974

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     2 |    26 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | T_IDX |     2 |    26 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("OPS$TKYTE"."F"("USER_ID") IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement


only 40 rows selected???

MH, February 04, 2009 - 8:20 am UTC

I actually was expecting 42 rows ;-)

Query plan using FBI

Chris Gould, February 23, 2009 - 12:15 pm UTC

There's been some debate at work recently about function-based-indexes (FBIs) and the query-plans that the optimizer would use if FBIs exist on a table.
One group believe that if a table has an FBI and a query run against the table includes a statement in the WHERE clause that could use that FBI, then the FBI is always used to access the table.
The other view is that an FBI isn't really much different to any other index, and if the optimizer believed that the query would return most of the rows in the table it might still generate a plan with a full-tablescan. Equally, if the where-clause contained another condition that could use an index that was more selective than the condition on the FBI, then it might also choose not to use the FBI and use another index instead.

The upshot is, the group beliving that an FBI is always used think it's never necessary to include a hint in a query to use a FBI (since it would always use it anyway ...)


Could you shed some light on this, please?

(btw - I'm with the latter group).
Tom Kyte
February 23, 2009 - 5:40 pm UTC

ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2> drop table t;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> select *
ops$tkyte%ORA10GR2>   from all_objects;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t add constraint t_pk primary key(object_id);
ops$tkyte%ORA10GR2> create index t_idx on t( lower(owner) );
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec dbms_stats.gather_table_stats( user, 'T' );
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2> @at
ops$tkyte%ORA10GR2> column PLAN_TABLE_OUTPUT format a72 truncate
ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t where object_id between 1 and 200 and lower(owner) = 'sys';

Execution Plan
----------------------------------------------------------
Plan hash value: 3772518221

------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)
------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |    77 |  7546 |     6   (0)
|*  1 |  TABLE ACCESS BY INDEX ROWID| T    |    77 |  7546 |     6   (0)
|*  2 |   INDEX RANGE SCAN          | T_PK |   171 |       |     2   (0)
------------------------------------------------------------------------

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

   1 - filter(LOWER("OWNER")='sys')
   2 - access("OBJECT_ID">=1 AND "OBJECT_ID"<=200)

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2>


that proves premise #1 is wrong (that the FBI will always be used)

However:

... think it's never necessary to include a hint in a query to use a FBI ...

I would back this group - even though they arrived at the conclusion using inaccurate fact.


So, in short, the premise incorrect, but a conclusion I none the less support.

clarification

Chris Gould, February 23, 2009 - 6:36 pm UTC

Thanks for the prompt response.
I'm not quite sure how came to the conclusion that it's not necessary to use a hint for a FBI though. In this particular case the FBI is being used on a column which indicates a record is to be processed (you've given an example earlier where records newly added to a table have a column showing their status as "to be processed" and once they've been processed the status is changed). The FBI is such that for any rows other than those "to-be-processed" the function gives NULL. Given that stats are gathered when the system is idle, there won't usually be any rows "to-be-processed" at that point. I thought in this case it would be sensible to include a hint to use the FBI as otherwise the optimiser might think (based on stats) that it wouldn't find anything useful via that index.
Tom Kyte
February 24, 2009 - 5:56 am UTC

... that it wouldn't find anything useful via that index. ...

and that would be precisely why it would be driven to use the index.

think about it - it will use the index that will return the fewest records as fast as possible. If the optimizer has a choice between using an index it thinks would return 100 records and then have to filter that, versus using an index it thinks would return 1 record and then have to filter that - which index do you think it would use? It better use the one it thinks would return 1 record!!!

eg:

select * from t where f(x) = 5 and y = 6;

Assume index on f(x) and index on Y

if it thinks f(x)=5 will return 1 record (and then must check if y=6)
and it thinks y=6 will return 100 records (and then must check if f(x) = 5)

which index do you think it should use?
the one that is more selective......

If you have valid representative statistics, the need to use hints - few and far between.


So, I again disagree with the premise that an FBI will always be used (proven false)

I agree with the conclusion that hints should not in general be used.
Even if it was arrived at using incorrect assumptions - it is still valid.

hinting index

Chris Gould, February 24, 2009 - 10:37 am UTC

Ah - you said " .. if you have valid representative statistics .. " then the hint is not needed. I agree. But the point is (as I said earlier) that my stats are gathered at a time when the table is not in a representative state : when stats are gathered it has no rows "to-be-processed", but in normal operation it would usually have such rows. Therefore I'm trying to tell it something via the hint that it can't know from the stats : that in normal operation the index will give the best selectivity.

Tom Kyte
February 24, 2009 - 5:02 pm UTC

but actually, you have statistics that say "if you use this index, you'll filter the result set down to 1 row right away (we never think '0')" The statistics you have will very strongly influence the optimizer to think "this index will be fantasic"

There would be no reason to hint to use it, the optimizer will want to use it as it stands. using it would result in filtering the result set down to nothing in no time.


and the way to fix a problem such as you are thinking of - that you have non-representative statistics - would NOT be to hint to 'fix' that, it would be to get representative statistics in place. You don't want to hint.

never knew that!

Chris Gould, February 24, 2009 - 5:54 pm UTC

Thanks - I never realized that the optimizer considered "0" as "1" when considering stats and deciding which index to use. That makes it much clearer, and I agree that the hint is therefore unnecessary.
btw - has that always been the case? I remember years ago (oracle 8.1) having to hint an index for the same reason (wasn't a FBI in that case) in order to get the optimizer to choose it.
Tom Kyte
February 24, 2009 - 9:50 pm UTC

well, even if it thought "0", it would STILL want to use that index. Consider

select * from t where f(x) = 'Y' and y = 6;

If you had statistics that said:

a) f(x) = 'Y' returns 0 records
b) y = 6 returns 10 records

which would be preferable to use? should we

a) use index on f(x) - get zero records and be done with it

b) use index on y, get 6 records, filter each of them with f(x)='Y'

My point is, the optimizer thinks that very few rows will be returned by the FBI, that will be the index it really wants to use to access this table then. 0 or 1 regardless. 0 would be even better than 1.

This is actually an example I use in my seminars when talking about indexing actually.

ops$tkyte%ORA11GR1> create table t as
  2  select 'Y' processed_flag, a.* from stage a;

Table created.

ops$tkyte%ORA11GR1> create or replace view v
  2  as
  3  select t.*,
  4         case when processed_flag = 'N' then 'N'
  5              else NULL
  6          end processed_flag_indexed
  7    from t;

View created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create index t_idx on
  2  t( case when processed_flag = 'N' then 'N'
  3          else NULL
  4      end );

Index created.

ops$tkyte%ORA11GR1> exec dbms_stats.gather_table_stats( user, 'T', cascade=>true );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> update t set processed_flag = 'N'
  2    where rownum <= 100;

100 rows updated.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select num_rows from user_indexes where index_name = 'T_IDX';

  NUM_ROWS
----------
         0

ops$tkyte%ORA11GR1> select num_rows from user_tables where table_name = 'T';

  NUM_ROWS
----------
     67522


ops$tkyte%ORA11GR1> select rowid, object_name
  2    from v
  3   where processed_flag_indexed = 'N'
  4     and rownum = 1;


ROWID              OBJECT_NAME
------------------ ------------------------------
AAATsrAAEAAAc98AAA ICOL$


Execution Plan
----------------------------------------------------------
Plan hash value: 2869526954

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     1 |    38 |     1   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |       |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| T     |     1 |    38 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

   1 - filter(ROWNUM=1)
   3 - access(CASE  WHEN "PROCESSED_FLAG"='N' THEN 'N' ELSE NULL END ='N')

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads



and the optimizer goes right for that index, it knows "few rows"

Join to FBI

Parthiban Nagarajan, May 07, 2009 - 8:14 am UTC

Hello Tom

I have a query that join two tables and I filter it by a user-defined function which takes parameters from both the tables. Like ...

select t1.c1, t2.c2
from t1, t2
where ...
and fun(t1.c1, t2.c2) = ....

Could you advise me on creating an FBI on this function - "fun" ?

Is "M-View" is the [better | only] option available ?

Thanks In Advance.
Tom Kyte
May 11, 2009 - 11:39 am UTC

you cannot create a B*TREE index that crosses two tables like that. A B*tree index can only use attributes of a single table and always points to just those rows in that single table.

You would need a b*tree index that not only spans two tables but points to rows in two tables. That does not exist.


That said, insufficient data (the ... leaves way too much to the imagination here) to say if a materialized view would make sense or not. Probably not if T1 and T2 are joined somehow in the .... that we cannot see.

partial text index

A reader, July 02, 2009 - 11:05 am UTC

Hi Tom,
can FBI and text index be combined ?

I want to text search only a part of the table (with criteria on a nother field) and I dont need to index the enitire table . Paritions also are not available

regards,

sagsag

MAX and normal FBI

Raajesh, July 17, 2009 - 11:06 pm UTC

Hi Tom,

I have a clarification on FBI. Please check the sequence of statements below:


SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

SQL> create table temp as
  2  select rownum as seqnum,to_char(object_id) as objid from dba_objects
  3  /

Table created.

SQL> desc temp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 SEQNUM                                             NUMBER
 OBJID                                              VARCHAR2(40)


SQL> select count(*) from temp
  2  /

  COUNT(*)
----------
     68870


SQL> create index id1 on temp(to_number(objid)) compute statistics;

Index created.

=====================================================================================

SQL> set autotrace on;
SQL> select max(to_number(objid)) from temp
  2  /

MAX(TO_NUMBER(OBJID))
---------------------
                71014


Execution Plan
----------------------------------------------------------
Plan hash value: 670871450

--------------------------------------------------------------------------------

---

| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time
  |

--------------------------------------------------------------------------------

---

|   0 | SELECT STATEMENT           |      |     1 |    13 |    43   (3)| 00:00:0

1 |

|   1 |  SORT AGGREGATE            |      |     1 |    13 |            |
  |

|   2 |   INDEX FULL SCAN (MIN/MAX)| ID1  | 74388 |   944K|            |
  |

--------------------------------------------------------------------------------

---


Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         28  recursive calls
          0  db block gets
         78  consistent gets
          1  physical reads
          0  redo size
        433  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

========================================================================================================================================

<b>I've dropped ID1 at this stage</b>


SQL> create index id2 on temp MAX(to_number(objid)) compute statistics;

Index created.

SQL> set autotrace on;
SQL> select max(to_number(objid)) from temp
  2  /

MAX(TO_NUMBER(OBJID))
---------------------
                71014


Execution Plan
----------------------------------------------------------
Plan hash value: 130965984

--------------------------------------------------------------------------------

---

| Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time
  |

--------------------------------------------------------------------------------

---

|   0 | SELECT STATEMENT           |      |     1 |    13 |    43   (3)| 00:00:0

1 |

|   1 |  SORT AGGREGATE            |      |     1 |    13 |            |
  |

|   2 |   INDEX FULL SCAN (MIN/MAX)| ID2  | 74388 |   944K|            |
  |

--------------------------------------------------------------------------------

---


Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         76  consistent gets
          1  physical reads
          0  redo size
        433  bytes sent via SQL*Net to client
        416  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

======================================================================================================================================== 


Questions:

1) What is the difference between id1 and id2 FBI? In which dictionary table can I get this difference? (the MAX as used in index DDL)

2) The same setup when done on a 9i DB gives by a INDEX FAST FULL SCAN, with a higher cost. Is their anyway to influence it and make the optimizer to use INDEX FULL SCAN(MIN/MAX)?

3) If I have to get the MAX value as given in the SQLs above, which FBI is better?

Thanks.
Tom Kyte
July 24, 2009 - 9:13 am UTC

SQL> create index id1 on temp(to_number(objid)) <b>compute statistics</b>;


the bit in bold is totally unnecessary and in fact deprecated. Since 10g, if the table has data, create index will automagically and always do that.


1) there was no difference. In the context of your create statement MAX is not a function, it is a correlation name - you could put any valid identifier there:


ops$tkyte%ORA11GR1> create table t as select rownum as seqnum,to_char(object_id) as objid from dba_objects;

Table created.

ops$tkyte%ORA11GR1> create index id2 on t <b>HELLO_WORLD</b> (to_number(objid)) ;

Index created.

ops$tkyte%ORA11GR1> select column_expression from user_ind_expressions where index_name = 'ID2';

COLUMN_EXPRESSION
-------------------------------------------------------------------------------
TO_NUMBER("OBJID")





2) the optimizer is made better with each release. Newer releases have more capabilities than old ones. You can use this top-n query technique to read the index descending and stop after the first row, it'll have the same effect:

ops$tkyte%ORA9IR2> set autotrace traceonly explain
ops$tkyte%ORA9IR2> select max(to_number(objid)) from t;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=6)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'ID2' (NON-UNIQUE) (Cost=8 Card=28400 Bytes=170400)



ops$tkyte%ORA9IR2> select * from (select to_number(objid) from t order by to_number(objid) DESC) where rownum = 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=63 Card=1 Bytes=13)
   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=63 Card=28400 Bytes=369200)
   3    2       INDEX (FULL SCAN DESCENDING) OF 'ID2' (NON-UNIQUE) (Cost=63 Card=28400 Bytes=170400)



ops$tkyte%ORA9IR2> set autotrace off


3) they are the same.

Re

Srini, August 05, 2009 - 4:45 am UTC

Thanks for the response Tom.

Thanks,

ORA-01743: in 10G

Rajeshwaran, Jeyabal, February 01, 2010 - 7:33 am UTC

rajesh@IRADSDB> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

Elapsed: 00:00:00.03
rajesh@IRADSDB> create table t(x varchar2(29));

Table created.

Elapsed: 00:00:00.14
rajesh@IRADSDB> insert into t values(sysdate);

1 row created.

Elapsed: 00:00:00.04
rajesh@IRADSDB> commit;

Commit complete.

Elapsed: 00:00:00.07
rajesh@IRADSDB> create index t_ind on t(to_date(x,'yyyy'));
create index t_ind on t(to_date(x,'yyyy'))
                                *
ERROR at line 1:
ORA-01743: only pure functions can be indexed


Elapsed: 00:00:00.06

<Quote>
Expert one-on-one Oracle
Chapter 7 : Indexes
page no : 375


One quirk I have noticed with function-based indexes is that if you create one on the builtin
function TO_DATE, it will not create. For example:
ops$tkyte@ORA8I.WORLD> create index t2 on t(to_date(y,'yyyy'));
create index t2 on t(to_date(y,'yyyy'))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
This is a filed bug and will be fixed in a later release of Oracle (after 8.1.7).
</Quote>

Tom,

Is that above bug is not yet fixed in 10.2.0.1.0? please confirm.

Thanks,
Rajesh.
Tom Kyte
February 01, 2010 - 10:47 am UTC

it is actually a bug - on my part.




that function is not pure.

select to_date( '2010', 'yyyy' ), sysdate from dual;


yyyy returns a date in the current month - every time you run to_date( '2010', 'yyyy' ) it might return a DIFFERENT DATE. It is not deterministic.

So, the database was right, I was wrong :) I corrected that in the next book.

funcation based index

satya, February 25, 2010 - 3:34 pm UTC

I have question on the funcation based index , could you please help me on how to create this


I created a function which will accept five params and return a value . This funcation i am using in the where clause with an outer join. While cheking this on explain plan understand that it is going on full tables scan. Now i have understand that if we use the funcation based index which will improve the performance . Could you please help how do we create the funcation based index on this

Thanks

Tom Kyte
March 01, 2010 - 11:13 am UTC

no, but only because you don't give us an example to work with. We don't even KNOW if you can create a function based index in your case, nor do we know if an index would even be remotely considered a good idea - in your case (full scans are NOT evil, they are NOT evil, they are good - sometimes they are so much better than an index - you would not even believe)

funcation based index -oracle 109

satya, February 25, 2010 - 3:35 pm UTC

I have question on the funcation based index , could you please help me on how to create this


I created a function which will accept five params and return a value . This funcation i am using
in the where clause with an outer join. While cheking this on explain plan understand that it is
going on full tables scan. Now i have understand that if we use the funcation based index which
will improve the performance . Could you please help how do we create the funcation based index on
this

Thanks



funcation based index

satya, March 01, 2010 - 2:55 pm UTC

below is the example I am using on the query

select func_get_prod_values (c1,c2,c3,c4) product_key
,func_get_customer_values (d1,d2,d3,d4)customer_key
, c1
,c2
,c3
,pd.d1
,pd.c6

from data_records t1,
prod_dim pd
where product_key(+) = func_get_prod_values (c1,c2,c3,c4);

c1,c2,c3,c4 are coulmns from the table t1.

I have created funcation based index like below

create index func_idx1 on data_records(func_get_prod_values (c1,c2,c3,c4));

anlayze table data_records compute statistics;


I have about 5 mil records on prod_dim and apporx 30 mi records in data_records. I have to get the appropiate dimension keys for the transaction in "data_records" and put into the fact table.

after doing the explain plan on the above query , i found it is giving the full table scan . how do i resolve the full table scan and populate them in minimum time . It is taking close to 2 days to populate this data.

Could you please kindly help me

Thanks
Tom Kyte
March 02, 2010 - 7:15 am UTC

thank goodness it did not use an index, that would be the slowest way to join two tables.


http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6749454952894#6760861174154


think about how long it would take without the full scans!!!!!!



The problem is you are going to execute that plsql function alot. Now, think about it - if this query returns say 5,000,000 to 30,000,000 records and the function is FAST (takes just 1/1000th of a second), then you are talking about:


ops$tkyte%ORA10GR2> select 3*5000000/1000/60/60, 3*30000000/1000/60/60 from dual;

3*5000000/1000/60/60 3*30000000/1000/60/60
-------------------- ---------------------
          4.16666667                    25


about 4 to 25 HOURS JUST RUNNING THE FUNCTION.

Hence, I would suggest "get rid of the function, just do this in sql, remove the call to func_get_prod_values - PERIOD"

Prove to me that you need to call that function - just use 100% pure SQL.

import function based index into different schema name

Mark Olszowka, April 28, 2010 - 3:35 pm UTC

I am using expdb and impdb to export/import a schema that contains a function based index. It works fine if i import into same schema name but if i try to use impdp with REMAP_SCHEMA option, I can't seem to import the function based index. It seems to hang onto the original schema name in the definition (even if i go into database right before export and remove schema name from definition).
Is it possible to accomplish this?
Tom Kyte
April 28, 2010 - 4:07 pm UTC

http://docs.oracle.com/docs/cd/E11882_01/server.112/e10701/dp_import.htm#SUTIL927


<quote>
Multiple REMAP_SCHEMA lines can be specified, but the source schema must be different for each one. However, different source schemas can map to the same target schema. The mapping may not be 100 percent complete, because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references embedded within the body of definitions of types, views, procedures, and packages.
</quote>


The command impdp would see would be something like:

CREATE INDEX "A"."T_IDX1" ON "A"."T" ("A"."FOO"("X")) PCTFREE 10 INITRANS 2 MAXTRANS 167 NOCOMPRESS LOGGING TABLESPACE "USERS" PARALLEL 1


What impdp is able to remap in this case is A.T_IDX1 and A.T - it knows that the index it should build it new_schema.T_IDX1 and that that index should be on new_schema.T - but the function - it assumes that the function is just a function - no different than any other function and much like it doesn't find them in bodies, types, views, etc - it doesn't look there either.

It is not trying to create 'foo' at this time, it is trying to create an index on a table - that is what it sees to remap.

there is an open enhancement request regarding this (8338628) but as of 11gr2 - that is the way it is implemented.

QUERY_REWRITE_ENABLED - 9iR2

Rajeshwaran, Jeyabal, June 02, 2010 - 2:47 pm UTC

Tom:

 I am reading the Oracle 9iR2 product manuals on using Function-Based Indexes

<quote>
 When QUERY_REWRITE_ENABLED is set to FALSE, then function-based indexes are not used for obtaining the values of an expression in the function-based index
</quote>

<u>http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/data_acc.htm#2185</u>

pwruser@9iR2> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for 32-bit Windows: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

test@9iR2> create table t as select * from all_objects;

Table created.

test@9iR2> create index t_ind on t(initcap(object_name)) nologging;

Index created.

test@9iR2> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T',estimate_percent=>100,cascade=>true);

PL/SQL procedure successfully completed.

test@9iR2> alter session set query_rewrite_enabled=false;

Session altered.

test@9iR2> set autotrace traceonly explain;
test@9iR2> SELECT * FROM t
  2  WHERE INITCAP(object_name) = 'Catalog'
  3  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=2 Bytes=194)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=2 Bytes=194)
   2    1     INDEX (RANGE SCAN) OF 'T_IND' (NON-UNIQUE) (Cost=1 Card=2)

When QUERY_REWRITE_ENABLED is set to FALSE Function based index is still used,Is that a bug with Oracle production documentation?

Tom Kyte
June 08, 2010 - 10:45 am UTC

the behavior changed as we went from 9i to 10g (it was no longer needed) and it looks like they slipped that change into one of the patch sets for 9i.

I do not have an old enough patch release to pinpoint when the change was made exactly though...

SUBSTR(some_not_null_column,1,8) = :B1

Ian, November 09, 2010 - 10:43 am UTC

Tom

I understand why the CBO will normally not use an index on a predicate if it has a function applied to it but not sure why it cannot with SUBSTR(some_not_null_column,1,8) = :B1 for example. The first 8 characters are highly selective and the 1 and 8 are hard-coded values - not binds - so the CBO should be able to use the index on some_not_null_column.

Any reason why the CBO will not do this? I would prefer not to have to create a function based index to cover this given the column is already indexed. And if I hint the SQL to use the index it goes for an Index Full Scan.

Regards

Ian
Tom Kyte
November 11, 2010 - 9:19 am UTC

it can if you index

create index i on t(substr(some_not_null_column,1,8));


the CBO doesn't make true assumptions about what a function is/does. In this case the optimizer only knows that a) it returns 8 characters, b) it is not null. It doesn't peek inside of substr to say "it returns the first 8 characters".


The data in the index just isn't appropriate to be compared to the value - it would have to apply substr() to each index key - the index key right now is something larger than 8 characters.

Function - SQL query within

A reader, November 10, 2010 - 1:01 pm UTC

Can a function based index be used which runs a query in the function using the input data and returning a value?

I've been trying some tests and it didn't seem to work.

If not why not?

Is it because the data might become stale as time wears on, is there a workaround?
Tom Kyte
November 11, 2010 - 1:55 pm UTC

... Can a function based index be used which runs a query in the function using the
input data and returning a value? ...


technically speaking the answer is: yes

but - it would be a really bad idea (I'll use the word stupid in fact).


A function that is indexed MUST be deterministic. That is - given a set of inputs - it will return the same answer FOREVER.

If the function takes the inputs and runs a query using those inputs to derive it's answer - it is NOT deterministic. All I need to do it UPDATE THE TABLE - and the answer can change (given the same inputs).


So, you technically could do it - but it wouldn't work. We would index a row with a return value X and later that return value would change to Y - but we wouldn't know that - so you would lose the ability to "retrieve" that row - you would miss rows you should find, you would find rows you shouldn't - it would be totally broken.


tell us what you are trying to accomplish - not how you are trying to do it and we'll see what we can come up with.

function based index with a SQL query in the function with changing data...

A reader, November 11, 2010 - 12:16 pm UTC

Tom,

Here's a test that I did which you might find interesting in function-based-indexes (FBI), the Function had an SQL query whose fetched data was returned.

I assumed when I created the FBI, it would have created the Index keys with the input value and the output values of the function.

Now I updated one of the key values by updating the table data the Function' SQL queries.

I assumed the Index would NOT have factored in this change and would still return the 'old' data.

But it didn't, although the Explain plan suggests the FBI is used for the Where clause, the query accurately returns the updated data.

How come? Does that NOT make the function deterministic?

Thanks

Ravi



create table t (app_id number primary key);

create table t1 (t_app_id number not null, brn number not null);

alter table t1 add (constraint t1_app_fk foreign key (t_app_id)
references t(app_id));

insert into t select app_id from
applications,
program_implementations,
programs
where pro_POT_code like 'SRDP-RP' and proi_pro_id = pro_id
and app_proi_id = proi_id
and srdp_rp_pay_calc_qry_pkg.get_app_brn(app_id) > 0
and rownum < 100 ORDER BY APP_ID DESC;

select t.*,cdm_brn_func_index.app2brn(app_id) from t;


CREATE OR REPLACE
PACKAGE cdm_brn_func_index AS

/* TODO enter package declarations (types, exceptions, methods etc) here */

function app2brn(app in t.app_id%type) return t1.brn%type
deterministic;

END cdm_brn_func_index;

create or replace
PACKAGE body cdm_brn_func_index AS

/* TODO enter package declarations (types, exceptions, methods etc) here */

function app2brn(app in t.app_id%type) return t1.brn%type
deterministic
is
temp_brn t1.brn%type;
begin
select brn into temp_brn from t1
where t_app_id = app;

return temp_brn;
exception when no_data_found then return null;
end app2brn;

END cdm_brn_func_index;

insert into t1 select app_id, srdp_rp_pay_calc_qry_pkg.get_app_brn(app_id) from
t;

select * from t1;

drop index t_app2_brn;

create index t_app2_brn on t
(cdm_brn_func_index.app2brn(app_id));

ALTER SESSION set query_rewrite_enabled= true;

ALTER SESSION set query_rewrite_integrity = trusted;

select * from t1;



analyze table t compute statistics
for table
-- for all indexed columns
-- for all indexes
;

select app_id from t
where (select cdm_brn_func_index.app2brn(app_id) from dual) = 146060;

update t1 set brn = 123456 where t_app_id = 60094520;

select app_id from t
where (select cdm_brn_func_index.app2brn(app_id) from dual) = 123456;


update t1 set brn = 123456 where t_app_id =60046764;

select app_id from t
where (select cdm_brn_func_index.app2brn(app_id) from dual) = 123456;

select * from user_dependencies where referenced_name ='CDM_BRN_FUNC_INDEX';
Tom Kyte
November 11, 2010 - 3:13 pm UTC

I cannot run your example, if you want me to comment on something - make it so I can run and reproduce it myself.


I see you created an index on T

I see you select from T

I see you update T1

I have no idea what you see on your screen however.

make a small example (no three table join needed, just generate some data). Use as little code as possible (package is overkill, it would be smaller as a function), actually run it and point out what you are seeing versus what you THINK you should be seeing

sort of like I do :)

Reply to your answer from Nov 11

A reader, November 12, 2010 - 7:16 am UTC

/*

Tom,

Here's a working example.
I've got 2 tables T and T1, with T having a Function Based Index (FBI), with the
FBI doing a query to look up data in table T1 and returning values.
My question is based, for this examples on the last 2 queries here, below the
UPDATE statement.
What I do is to update the column value in table T1 (not table T) which is
queried by the FBI on table T.
This update appears to update the Index key values of the FBI on table T.
The question is how did this happen, like how did a data update on T1 cascade
it to the FBI automatically?



*/

drop table t1;

drop table t;

create table t (app_id number primary key);

create table t1 (t_app_id number not null, brn number not null);

alter table t1 add (constraint t1_app_fk foreign key (t_app_id)
references t(app_id));

insert into t select rownum from dual connect by level < 10;

insert into t1 (t_app_id,brn) select
app_id, app_id +10 from t;

select * from t;
select * from t1;

create or replace
function app2brn(app in t.app_id%type) return t1.brn%type
deterministic
is
temp_brn t1.brn%type;
begin
select brn into temp_brn from t1
where t_app_id = app;

return temp_brn;
exception when no_data_found then return null;
end app2brn;
/

drop index t_app2_brn;

create index t_app2_brn on t
(app2brn(app_id));

ALTER SESSION set query_rewrite_enabled= true;

ALTER SESSION set query_rewrite_integrity = trusted;


BEGIN
dbms_stats.gather_table_stats(NULL,tabname=>'t',
method_opt=> 'for all indexed columns for all indexes',CASCADE=>true);
dbms_stats.gather_table_stats(NULL,tabname=>'t1',
method_opt=> 'for all indexed columns for all indexes',CASCADE=>true);
END;
/


select app_id ,(select app2brn(app_id) from dual) brn from t;

select app_id from t
where (select app2brn(app_id) from dual) = 12;

update t1 set brn = 45 where t_app_id = 2;

select app_id from t
where (select app2brn(app_id) from dual) = 12;

select app_id from t
where (select app2brn(app_id) from dual) = 45;

Tom Kyte
November 12, 2010 - 9:11 am UTC

this doesn't use your function based index at all - did you even check the plan to see what it was doing?????

where do you see your fbi being used????

ops$tkyte%ORA11GR2> select app_id ,(select app2brn(app_id) from dual) brn from t;

    APP_ID        BRN
---------- ----------
         1         11
         2         12
         3         13
         4         14
         5         15
         6         16
         7         17
         8         18
         9         19

9 rows selected.

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  5q8czxuj5zbrn, child number 0
-------------------------------------
select app_id ,(select app2brn(app_id) from dual) brn from t

Plan hash value: 2710997458

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |       |       |     1 (100)|          |
|   1 |  FAST DUAL       |              |     1 |       |     2   (0)| 00:00:01 |
|   2 |  INDEX FULL SCAN | SYS_C0017126 |     9 |    27 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------


14 rows selected.

ops$tkyte%ORA11GR2> select app_id from t where (select app2brn(app_id) from dual) = 12;

    APP_ID
----------
         2

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  bbsrwzn8n93hm, child number 0
-------------------------------------
select app_id from t where (select app2brn(app_id) from dual) = 12

Plan hash value: 3624549065

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |       |       |    10 (100)|          |
|*  1 |  INDEX FULL SCAN | SYS_C0017126 |     1 |     3 |     1   (0)| 00:00:01 |
|   2 |   FAST DUAL      |              |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter(=12)


19 rows selected.

ops$tkyte%ORA11GR2> update t1 set brn = 45 where t_app_id = 2;

1 row updated.

ops$tkyte%ORA11GR2> select app_id from t where (select app2brn(app_id) from dual) = 12;

no rows selected

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  bbsrwzn8n93hm, child number 0
-------------------------------------
select app_id from t where (select app2brn(app_id) from dual) = 12

Plan hash value: 3624549065

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |       |       |    10 (100)|          |
|*  1 |  INDEX FULL SCAN | SYS_C0017126 |     1 |     3 |     1   (0)| 00:00:01 |
|   2 |   FAST DUAL      |              |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter(=12)


19 rows selected.

ops$tkyte%ORA11GR2> select app_id from t where (select app2brn(app_id) from dual) = 45;

    APP_ID
----------
         2

ops$tkyte%ORA11GR2> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  gyvf8n1rh8c7g, child number 0
-------------------------------------
select app_id from t where (select app2brn(app_id) from dual) = 45

Plan hash value: 3624549065

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |       |       |    10 (100)|          |
|*  1 |  INDEX FULL SCAN | SYS_C0017126 |     1 |     3 |     1   (0)| 00:00:01 |
|   2 |   FAST DUAL      |              |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter(=45)


19 rows selected.


run your example in a manner that actually USES the function based index, for example


ops$tkyte%ORA11GR2> select t.*, (select app2brn(app_id) from dual) from t where app2brn(app_id) = 12;

    APP_ID (SELECTAPP2BRN(APP_ID)FROMDUAL)
---------- -------------------------------
         2                              12

ops$tkyte%ORA11GR2> update t1 set brn = 45 where t_app_id = 2;

1 row updated.

ops$tkyte%ORA11GR2> select t.*, (select app2brn(app_id) from dual) from t where app2brn(app_id) = 12;

    APP_ID (SELECTAPP2BRN(APP_ID)FROMDUAL)
---------- -------------------------------
         2                              45

ops$tkyte%ORA11GR2> select * from t where app2brn(app_id) = 45;

no rows selected



Now do you see the problem - your function IS NOT DETERMINISTIC, you lose

A reader, November 12, 2010 - 10:07 am UTC

Tom

Sorry for the oversight, but what I was getting at was I assumed the Index data will be stored like this:

for Row ROWID Input to app2brn Output


With app_id =2 XXXXXX 2 12


so for query
select t.*, (select app2brn(app_id) from dual) from t where app2brn(app_id) =
12;


The input app_id 2 will always return 12 (it could be a range scan).

Obviously that's not how it works, I assumed even if the Output value (seen above) had been updated with

update t1 set brn = 45 where t_app_id = 2;

The Index data would have remained as above so when the Query is rerun, it will return the Row pointed by ROWID.

So in the above example, what values get stored in the Index?

Thanks

Tom Kyte
November 12, 2010 - 1:07 pm UTC

when you inserted the row, the function was evaluated and that result + rowid was stored in the index.

that is why you get the WRONG ANSWER from the index (run the query with the /*+ FULL(t) */ hint and you'd get the *right* answer). Because it stored the value of the function during the insert and the update of the table that caused the answer to change (and hence means the function IS NOT deterministic) did not, cannot, will not maintain the index.

what in case of bitmap indexes

A reader, November 17, 2010 - 1:16 pm UTC

Hi,

Is function based index theory also applies to bitmap indexes.
Tom Kyte
November 18, 2010 - 3:23 am UTC

what theory do you talk of?

if you are asking - can I create a function based bitmap index? then the answer is "yes"

ops$tkyte%ORA11GR2> create table t as select * from scott.emp;

Table created.

ops$tkyte%ORA11GR2> create bitmap index t_idx on t(lower(job));

Index created.

blevel and height of the index.

Yasir Hashmi, November 18, 2010 - 4:01 am UTC

Dear Tom,

In your book expert oracle architecture,chapter 11,page-429,you say that every access to index would result in the height number of blocks visits.ie height=consistent gets.
So height=I/O
But in the same example,i am getting different consistent gets.Can you explain why?
SQL> select index_name,blevel,num_rows from user_indexes;

INDEX_NAME                         BLEVEL   NUM_ROWS
------------------------------ ---------- ----------
BIG_TABLE_PK                            2   10000000


SQL>select count(*) from big_table;

  COUNT(*)
----------
  10000000

SQL>select id from big_table where id=41;


Statistics
----------------------------------------------------------
        247  recursive calls
          0  db block gets
         58  consistent gets
          2  physical reads
          0  redo size
        405  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> /


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        405  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>select * from big_table where id=12345;

Execution Plan
----------------------------------------------------------
Plan hash value: 498549126

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    99 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |     1 |    99 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | BIG_TABLE_PK |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("ID"=12345)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          2  physical reads
          0  redo size
       1192  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>select * from big_table where id=1234567;


Execution Plan
----------------------------------------------------------
Plan hash value: 498549126

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    99 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| BIG_TABLE    |     1 |    99 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | BIG_TABLE_PK |     1 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("ID"=1234567)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          1  physical reads
          0  redo size
       1176  bytes sent via SQL*Net to client
        374  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

Tom Kyte
November 18, 2010 - 4:23 am UTC

see the recursive calls - there is parsing going on the first time you run it. You just created the table - the SQL cannot have been already parsed, so you are parsing it.

the second time, you hit ONLY the index - and we did three IO's (root, branch, leaf)

then the next two times you hit the index and table - you did four IO's (root, branch, leaf, table)


you perfectly demonstrated the point - where do you see anything different?

A reader, November 18, 2010 - 4:42 am UTC

Yes, I didn't see the table access.

3i/o for index and 1 for table=4 consistent gets plus there is 1 physical read.
What is that 1 physical read for?
recursive sql?

func based index

sam, February 08, 2011 - 4:03 pm UTC

Tom:

1) I tried to create a FBI for a query that takes 2 seconds but uses 70,000 consistent gets because of the function.

First it said i need to make the function "determinisitc" which i did. Then it isaid i have no privielges.

Does it affect anything if i added the word determinsitc in the function?

do i need the "QUERY_REWRITE" be enabled too to create it?

2) is it worth it sometimes to create an FBC for the above case to reduce consistent gets and a 2 second response.
Tom Kyte
February 10, 2011 - 4:02 pm UTC

sam

no example, no comment.

function based index

sam, March 10, 2011 - 3:25 pm UTC

Tom:

1) Does user need certain privilege for creating function based index? I got permission error.

2) I was thinking of creating an index on a DERIVED filed.
I have a function that calculates quantity in inventory for a given stock. so it read records from many tables.

Can you create an index on function like that.

I think that is same as creating a derived field and storing the QtyonHand value after each shipment transaction.
Tom Kyte
March 10, 2011 - 3:50 pm UTC

1) documentation says....

http://docs.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_5012.htm#i2084975

(it always interests me what the documentation might tell me... doesn't it for you?)

To create a function-based index, in addition to the prerequisites for creating a conventional index, if the index is based on user-defined functions, then those functions must be marked DETERMINISTIC. Also, you must have the EXECUTE object privilege on any user-defined function(s) used in the function-based index if those functions are owned by another user.


2) you cannot read from many tables in a function based index. It would not be deterministic by definition. Meaning given the same inputs (the values in a sinle row - the function based index can only receive as inputs the values of a SINGLE ROW), it always returns the same output. it cannot do that if it reads from many tables to determine the output.

... I think that is same as creating a derived field and storing the QtyonHand
value after each shipment transaction. ...

it is not, but only because what you describe is not a derived field - but an aggregation, a non-scalar thing.

FBI

sam, March 10, 2011 - 4:08 pm UTC

Tom:

Creating normal indexes works fine. but creating any function based index such as TRUNC fails.


SQL> create index test_id on shipment(trunc(creation_date)); 

create index test_id on shipment(trunc(creation_date))
                                       * ERROR at line 1:
ORA-01031: insufficient privileges

Tom Kyte
March 10, 2011 - 4:29 pm UTC

you don't say what version.

I always assume current version.

so, if YOU took the initiative to read YOUR VERSION OF ORACLE documentation - what might it say?

I tried (really I did, but subtlety seems lost...) to tell you to do that:

(it always interests me what the documentation might tell me... doesn't it for you?)


Perhaps you are on 9i, way back then the rules might have been different? did you think perhaps to check it out yourself first?

I just did. I know what the answer is. Now - your turn.

Please Sam, you have to start doing this on your own. You represent more questions - more *repetitive* questions - on this site than anyone else - ever. I appreciate that you find me useful, but it is starting to get a bit out of control, again.



FBI

sam, March 10, 2011 - 4:45 pm UTC

Tom:

Thanks, i did. i need QUERY_REWRITE priv.

optimizer wont use it unless QUERY_REWRITE_ENABLED = TRUE


GRANT CREATE ANY INDEX, QUERY REWRITE TO user;

The database needs to have the QUERY_REWRITE_INTEGRITY = TRUSTED, QUERY_REWRITE_ENABLED = TRUE and COMPATIBLE = 8.1.0.0.0 (or higher) parameters set, by using ALTER SYSTEM or amending the init.ora file.

Tom Kyte
March 10, 2011 - 4:52 pm UTC

ta-dah. Pretty amazing what that documentation turns up, isn't it.

and remember, things change - that isn't true in software written this century anymore.

Alexander, March 11, 2011 - 9:57 am UTC

I always find it interesting that "Sam" is working on 20 different things at once all the time. He always has like 10 different threads going :) Must be a busy place where he works. I know I have days like that too.

FBI 10GR2

Rajeshwaran, Jeyabal, June 14, 2011 - 11:30 pm UTC

<quote>
Additionally, to use a function-based index:

1) The table must be analyzed after the index is created.

2) The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.

</quote>

http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/indexes.htm#sthref2542

drop table t purge;
create table t 
nologging
as
select *
from all_objects;
create index t_ind on t(owner desc ,object_name desc) nologging;


rajesh@ORA10GR2> select index_name, index_type
  2  from user_indexes
  3  where table_name ='T'
  4  /

INDEX_NAME                     INDEX_TYPE
------------------------------ ---------------------------
T_IND                          FUNCTION-BASED NORMAL

Elapsed: 00:00:00.14
rajesh@ORA10GR2>
rajesh@ORA10GR2> alter session set optimizer_dynamic_sampling = 0;

Session altered.

Elapsed: 00:00:00.18
rajesh@ORA10GR2>
rajesh@ORA10GR2> set autotrace traceonly explain;
rajesh@ORA10GR2>
rajesh@ORA10GR2> select *
  2  from t
  3  where owner =user
  4  and object_name = 'T';
Elapsed: 00:00:00.26

Execution Plan
----------------------------------------------------------
Plan hash value: 2098067784

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     7 |   896 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T     |     7 |   896 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_IND |     1 |       |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   2 - access(SYS_OP_DESCEND("OWNER")=SYS_OP_DESCEND(USER@!) AND
              SYS_OP_DESCEND("OBJECT_NAME")=HEXTORAW('ABFF') )
       filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))=USER@! AND
              SYS_OP_UNDESCEND(SYS_OP_DESCEND("OBJECT_NAME"))='T')

rajesh@ORA10GR2>


Tom:

Do you think the point(1) stated above is still valid ?
1) The table must be analyzed after the index is created.
Tom Kyte
June 17, 2011 - 12:06 pm UTC

1) The table must be analyzed after the index is created.


false. You need to be using the cost based optimizer. And as of 10g, there is only the cost based optimizer (supported).

As long as you use the cost based optimizer - you can use a function based index.

2) The query must be guaranteed not to need any NULL values from the indexed expression, since NULL values are not stored in indexes.


false


ops$tkyte%ORA11GR2> create table t as select * from scott.emp;

Table created.

ops$tkyte%ORA11GR2> desc t
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 EMPNO                                                 NOT NULL NUMBER(4)
 ENAME                                                          VARCHAR2(10)
 JOB                                                            VARCHAR2(9)
 MGR                                                            NUMBER(4)
 HIREDATE                                                       DATE
 SAL                                                            NUMBER(7,2)
 COMM                                                           NUMBER(7,2)
 DEPTNO                                                         NUMBER(2)

ops$tkyte%ORA11GR2> create index fbi on t(upper(ename),empno);

Index created.

ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from t where upper(ename) is null;

Execution Plan
----------------------------------------------------------
Plan hash value: 4172963880

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    94 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |    94 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | FBI  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - access(UPPER("ENAME") IS NULL)

Note
-----
   - dynamic sampling used for this statement (level=2)

ops$tkyte%ORA11GR2> set autotrace off



I've not gathered statistics on the table.

I'm interested only in the NULL values...



I've made the doc bug for this.

Function Based Index

A reader, August 30, 2011 - 4:20 pm UTC

Hi Tom,

I have a question regarding Function Based Index.
CREATE TABLE DEMO_TBL(col_1 varchar2(100), var_col varchar2(100));

BEGIN
FOR i in 1..100000
LOOP
INSERT INTO DEMO_TBL VALUES(dbms_random.value(1,7)*10000, dbms_random.string('A', 10));
END LOOP;
END;

Then I create the index over col_1 column:

CREATE INDEX DEMO_IDX_1 ON DEMO_TBL(col_1);

Gather the stats for the table.

Try to select the rows with an IN Operator:

SELECT * FROM DEMO_TBL WHERE COL_1 IN ('3450','12345.6');

It correctly gives me the two rows.

now I create a function based index over col_1:

CREATE INDEX DEMO_IDX_1 ON DEMO_TBL(substr(col_1,1,2), substr(col_1,3,6));

Gather stats for the table.


Try to select the rows with an IN Operator:

SELECT * FROM DEMO_TBL WHERE COL_1 IN ('3450','12345.6');

It correctly gives me the two rows.

Create another index :

CREATE INDEX DEMO_IDX_1 ON DEMO_TBL(col_2, substr(col_1,1,2), substr(col_1,3,6));


Gather stats for the table.

Try to select the rows with an IN Operator:

SELECT * FROM DEMO_TBL WHERE COL_1 IN ('3450','12345.6');


now it doesn't give me two rows. It goes for BITMAP to ROWID Conversion and gives me abnormal rows. Logically, it may be correct but result is wrong.

My question is:

1. Can we index a column and then use to create a function based index like substring over the same index column.

2. Why does Oracle considers the substring part Index when I am looking for the whole column?

3. This problem is solved by using USE_CONCAT hint to make it like UNION ALL but I don't want to use HINTS.

I am on Oracle 10GR1

Thanks for your time
Tom Kyte
August 31, 2011 - 8:48 am UTC

I cannot reproduce on any supported release of the database 9i, 10g, 11g - you'll have to contact support. It could be an issue with 10gr1.

why this is not working?

Ravi B, March 22, 2012 - 1:04 am UTC

Hi Tom,

Could you please let me know why i am getting the following error?

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production



drop table ind_test;
create table ind_test (n number, m number, x number, y number,flag number);
create unique index t_idx on ind_test(case when flag=1 then 'n,m,x,y' else null end);
insert into ind_test values(1,2,3,4,1);
insert into ind_test values(1,2,3,4,9);
insert into ind_test values(1,2,3,4,0);
insert into ind_test values(1,2,3,2,1);

Error starting at line 683 in command:
insert into ind_test values(1,2,3,2,1)
Error report:
SQL Error: ORA-00001: unique constraint (CONTENT.T_IDX) violated
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.

Thanks
Tom Kyte
March 22, 2012 - 8:54 am UTC

well, you just created a unique index on the CHARACTER STRING 'n,m,x,y' whenever flag=1.

So, you can only have one row with flag = 1

if you wanted a unique index on the COLUMNS n,m,x,y when flag=1 then use:

create unique index t_idx on ind_test(
case when flag=1 then n end,
case when flag=1 then m end,
case when flag=1 then x end,
case when flag=1 then y end
);


why this is not working?

Ravi B, March 22, 2012 - 12:10 pm UTC

Thanks Tom!

another FBI how to question

Sal, April 20, 2012 - 9:20 pm UTC

  SELECT q.QUEUE_INST_ID,
         q.QUEUE_NAME,
         (SELECT COUNT (*)
            FROM WO_TASK_INST t
           WHERE t.QUEUE_INST_ID = q.QUEUE_INST_ID AND STATUS_CODE = '4')
            AS READY_TASKS
    FROM QUEUE_INST q, QUEUE_USER_PRIVILEGE p
   WHERE q.QUEUE_INST_ID = p.QUEUE_INST_ID AND p.USER_INST_ID = :b1
         AND (   (SUBSTR (p.PRIV_MASK, 1, 1) = '1')
              OR (SUBSTR (p.PRIV_MASK, 2, 1) = '1')
              OR (SUBSTR (p.PRIV_MASK, 4, 1) = '1')
              OR (SUBSTR (p.PRIV_MASK, 5, 1) = '1')
              OR (SUBSTR (p.PRIV_MASK, 6, 1) = '1'))
ORDER BY q.QUEUE_NAME;

version 10.2.0.4
I would like to put a function based index on the substr. I tried different ways but I keep getting bad syntax. Would you be able to help? I am hesitant to ask for a ready made solution, so even if you give me some hints, it will be helpful :)

Thanks
Tom Kyte
April 22, 2012 - 9:05 am UTC

I'd be more worried about getting rid of that scalar subquery probably - unless you need to optimize this query to get the first rows as fast as possible!!

index:

create index i on t( 
case when substr( priv_mask,1,1) = '1' or 
          substr( priv_mask,2,1) = '1' or 
                                  .... or 
          substr( priv_mask,6,1) = '1') 
     then 1
end );

and query:

where ....
and case when substr( priv_mask,1,1) = '1' or 
          substr( priv_mask,2,1) = '1' or 
                                  .... or 
          substr( priv_mask,6,1) = '1') 
     then 1
end = 1




and then get rid of that scalar subquery

Function based index

Sal, April 22, 2012 - 12:24 pm UTC

Thanks Tom.

Unfortunately I can not change this query right away as it is coming from the vendor. The query gets executed as a first step by most users and is performing poorly, hence I thought of putting an FBI.

I guess if I create an FBI as you suggested and dont change the query, the FBI wont get picked up, right? So if you can please give me an FBI that can work on the existing query it will be most beneficial :)
Tom Kyte
April 22, 2012 - 2:38 pm UTC

there isn't one that would work on the existing query.

there might be FIVE (put user_id, substr() in five separate indexes), but there isn't one.


and do you even know if using an index would make sense at all?

OR expansion on FBI in 10g Release 1

WW, April 22, 2012 - 9:03 pm UTC

>SELECT * FROM DEMO_TBL WHERE COL_1 IN ('3450','12345.6');
>
>now it doesn't give me two rows. It goes for BITMAP to
>ROWID Conversion and gives me abnormal rows. Logically, it
>may be correct but result is wrong.

These is a known issue related to "OR expansion" on function based indexes in 10g Release 1. We've upgraded to 11 now and it's fixed, but you should find it on metalink:

Bug 9776940 - Wrong results with function based indexes

FBI not being picked up by Optimizer

Ted, June 06, 2012 - 10:34 am UTC

Tom,
I have tried several indexes and stats but the optimizer will not pickup the FBI.   Any idea why the optimizer is behaving this way?  Both FBI columns are nullable.
Thanks

SQL> select version from V$instance;

VERSION
-----------------
11.2.0.1.0

SQL> desc PRSC
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CID                                       NOT NULL VARCHAR2(20)
 LST_NM                                             VARCHAR2(40)
 FRST_NM                                            VARCHAR2(40)
 MID_NM                                             VARCHAR2(40)
 NM_SFX                                             VARCHAR2(20)
 NM_PFX                                             VARCHAR2(20)
 TITL                                               VARCHAR2(50)
 FMR_NM                                             VARCHAR2(40)
 PREF_NM                                            VARCHAR2(40)
 EXP_DT                                             DATE
 AUDT_CHG_DT                                        DATE
 BRTH_DT                                            DATE
 AUDT_ADD_DT                                        DATE
 GNDR_CD                                            CHAR(1)
 CHG_RSN_TXT                                        VARCHAR2(200)
 MSTR_ID                                   NOT NULL VARCHAR2(14)
 EFF_DT                                             DATE
 AUDT_ADD_USR_ID                                    VARCHAR2(20)
 AUDT_CHG_USR_ID                                    VARCHAR2(20)
 RX_RSTCTD_IND                                      CHAR(1)
 NO_CNTC_IND                                        CHAR(1)
 MED_PRFN_IND                                       CHAR(1)
 CODS_CHG_DT                                        DATE
 INDV_TYP                                           VARCHAR2(25)

SQL> select count(1) from prsc;

  COUNT(1)
----------
   3802387

SQL> select count(1) from PRSC where PRSC.FRST_NM = UPPER('John') and PRSC.LST_NM= UPPER('smith');

  COUNT(1)
----------
       196

SQL> 
SQL> alter session set query_rewrite_enabled = true;

Session altered.

SQL> alter session set query_rewrite_integrity = trusted;

Session altered.

SQL> CREATE INDEX CUSTOMERDAL.PRSC_F1   ON CUSTOMERDAL.PRSC(UPPER(LST_NM), UPPER(FRST_NM), CID );

Index created.

SQL> begin
  2        dbms_stats.gather_table_stats (ownname    => 'CUSTOMERDAL', tabname    => 'PRSC',
  3           cascade => true, degree => 4, estimate_percent => 100,
  4           method_opt => 'for all columns size 1 for columns (UPPER(LST_NM)), (UPPER(FRST_NM)) si
ze skewonly' );
  5   end;
  6  /

PL/SQL procedure successfully completed.

SQL> select e.extension col_group, t.num_distinct, t.histogram
  2      from user_stat_extensions e, user_tab_col_statistics t
  3      where e.extension_name=t.column_name and t.table_name='PRSC';

COL_GROUP
--------------------------------------------------------------------------------
NUM_DISTINCT HISTOGRAM
------------ ---------------
(UPPER("FRST_NM"))
      183816 HEIGHT BALANCED

(UPPER("LST_NM"))
      649591 NONE


SQL> set autotrace traceonly explain
SQL> select cid, lst_nm from PRSC where PRSC.FRST_NM = UPPER('John') and PRSC.LST_NM= UPPER('smith')
;

Execution Plan
----------------------------------------------------------
Plan hash value: 1778251517

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    25 | 17055   (2)| 00:03:25 |
|*  1 |  TABLE ACCESS FULL| PRSC |     1 |    25 | 17055   (2)| 00:03:25 |
--------------------------------------------------------------------------

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

   1 - filter("PRSC"."LST_NM"='SMITH' AND "PRSC"."FRST_NM"='JOHN')

SQL> 

Tom Kyte
June 06, 2012 - 11:34 am UTC

umm, you don't see what you did? :) You are going to smack your forehead in a moment :)


you created this index:

CREATE INDEX CUSTOMERDAL.PRSC_F1 ON
CUSTOMERDAL.PRSC(UPPER(LST_NM), UPPER(FRST_NM), CID )


and then used this where clause:

where PRSC.FRST_NM = [something] and PRSC.LST_NM= [something else]

you didn't use upper on the database columns, hence the index is not usable for this at all.



Yep, right there

Ted, June 06, 2012 - 12:56 pm UTC

Thanks for the glasses.

index only some of rows with function based index

A reader, July 23, 2012 - 4:45 am UTC

Hi Tom,

In your book 'export oracle database architecture', you mentioned 1 thing for function based index is 'index only some of the rows'.

I tried below test but got confused.

create table test(id int, age number);
insert into test select rownum, rownum from dual connect by level<=10000;
create index ind1 on test( case when age>9980 then age end);
exec dbms_stats.gather_table_stats(USER,'TEST',cascade=>true);
select * from test where age > 9987;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 13 | 104 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TEST | 13 | 104 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------

Why it is not index range scan, anyway, it is 12/10000 = 0.12%?
Tom Kyte
July 30, 2012 - 8:51 am UTC

because you have to reference the function you indexed.




ops$tkyte%ORA11GR2> set autotrace traceonly explain
ops$tkyte%ORA11GR2> select * from test where case when age>9980 then age end > 9987;

Execution Plan
----------------------------------------------------------
Plan hash value: 1389866015

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    14 |   126 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST     |    14 |   126 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | TEST_IDX |    14 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   2 - access(CASE  WHEN "AGE">9980 THEN "AGE" END >9987)

ops$tkyte%ORA11GR2> set autotrace off

why have to reference function

A reader, August 06, 2012 - 4:09 am UTC

Hi Tom,

I understand your point, it is a function based index. So in order to use it, have to reference the function in query.

But i am still feeling strange. Anyway, all age>9980 records are indexed and it is a relateively small portion. condition age>9987 should be able to satisfied from index, why not ORACLE be 1 more step clever to use index?
Tom Kyte
August 17, 2012 - 12:00 pm UTC

because there is nothing telling us it can use the index.



You indexed:

case when age>9980 then age end


you did not index age, you indexed a function. that function might as well be:

foo()



Two indexes on one column

dominik, August 14, 2012 - 5:40 am UTC

Hello,

One of the questions above shows similar scenario:

SQL>
SQL> create table test (text varchar2(16));

Tabela została utworzona.

SQL>
SQL> insert into test values ('A');

1 wiersz został utworzony.

SQL> insert into test values ('a');

1 wiersz został utworzony.

SQL>
SQL> create index test_text on test (nlssort(upper("TEXT"),'nls_sort=''POLISH_CI'''));

Indeks został utworzony.

SQL>
SQL> create index test_text2 on test (upper("TEXT"));

Indeks został utworzony.

SQL>
SQL> set autotrace on explain;
SQL>
SQL> select count(*) from test where upper(text) = upper('a');

  COUNT(*)
----------
         2


Plan wykonywania
----------------------------------------------------------
Plan hash value: 1950795681

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    10 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |    10 |            |          |
|*  2 |   TABLE ACCESS FULL| TEST |     1 |    10 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter(NLSSORT(UPPER("TEXT"),'nls_sort=''POLISH_CI''')=HEXTORAW('
              14000200') )

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>
SQL> drop index test_text2;

Indeks został usunięty.

SQL>
SQL> select count(*) from test where upper(text) = upper('a');

  COUNT(*)
----------
         2


Plan wykonywania
----------------------------------------------------------
Plan hash value: 1828176265

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |    71 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |    71 |            |          |
|*  2 |   INDEX RANGE SCAN| TEST_TEXT |     1 |    71 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------

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

   2 - access(NLSSORT(UPPER("TEXT"),'nls_sort=''POLISH_CI''')=HEXTORAW('
              14000200') )

Note
-----
   - dynamic sampling used for this statement (level=2)

SQL>


I hope nlssort does not obscure this example, but that's my case. Could you explain why in the first try optimizer doesn't use the index?

ORA-01793: maximum number of index columns is 32

Ravi B, January 11, 2013 - 12:08 pm UTC

Hi Tom,

I am getting the following error. Is this a bug on 11g?

select * from v$version;
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

create  unique index SWLICRELEASE_UNIQUE_IDX on SWLICRELEASE_BAK(
  case when is_live=1 then OTHERTYPE        end,
  case when is_live=1 then SWLICTYPE_RID    end,
  case when is_live=1 then SWLICENV_RID     end,
  case when is_live=1 then UNIT             end,
  case when is_live=1 then MIN_UNIT_COUNT   end,
  case when is_live=1 then MAX_UNIT_COUNT   end,
  case when is_live=1 then OTHER_ENV_NAME   end,
  case when is_live=1 then PERIOD_VAL       end,
  case when is_live=1 then PERIOD_UNIT      end,
  case when is_live=1 then MAIN_PERIOD_VAL  end,
  case when is_live=1 then MAIN_PERIOD_UNIT end,
  case when is_live=1 then OTHERLICTERM     end,
  case when is_live=1 then UNMAP_FLAG       end,
  case when is_live=1 then ORGS_RID         end,
  case when is_live=1 then PROD_RID         end,
  case when is_live=1 then EDITIONS_RID     end,
  case when is_live=1 then VERSIONS_RID     end
);

create unique index succeeded.

select * from v$version;

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

create  unique index SWLICRELEASE_UNIQUE_IDX on SWLICRELEASE_BAK(
  case when is_live=1 then OTHERTYPE        end,
  case when is_live=1 then SWLICTYPE_RID    end,
  case when is_live=1 then SWLICENV_RID     end,
  case when is_live=1 then UNIT             end,
  case when is_live=1 then MIN_UNIT_COUNT   end,
  case when is_live=1 then MAX_UNIT_COUNT   end,
  case when is_live=1 then OTHER_ENV_NAME   end,
  case when is_live=1 then PERIOD_VAL       end,
  case when is_live=1 then PERIOD_UNIT      end,
  case when is_live=1 then MAIN_PERIOD_VAL  end,
  case when is_live=1 then MAIN_PERIOD_UNIT end,
  case when is_live=1 then OTHERLICTERM     end,
  case when is_live=1 then UNMAP_FLAG       end,
  case when is_live=1 then ORGS_RID         end,
  case when is_live=1 then PROD_RID         end,
  case when is_live=1 then EDITIONS_RID     end,
  case when is_live=1 then VERSIONS_RID     end
);

Error at Command Line:14 Column:21
Error report:
SQL Error: ORA-00955: name is already used by an existing object
00955. 00000 -  "name is already used by an existing object"
*Cause:    
*Action:

ORA-01793: maximum number of index columns is 32

Ravi B, January 11, 2013 - 1:07 pm UTC

Here is the test case for above question:

create table test_idx
(
    "IS_LIVE"          NUMBER(*,0) DEFAULT 1,
    "OTHERTYPE"        VARCHAR2(500 BYTE),
    "LICTYPES"         VARCHAR2(200 BYTE),
    "LICENVS"          VARCHAR2(200 BYTE),
    "UNIT"             VARCHAR2(500 BYTE),
    "MIN_UNIT_COUNT"   NUMBER,
    "MAX_UNIT_COUNT"   NUMBER,
    "OTHER_ENV_NAME"   VARCHAR2(500 BYTE),
    "PERIOD_VAL"       NUMBER,
    "PERIOD_UNIT"      VARCHAR2(30 BYTE),
    "MAIN_PERIOD_VAL"  NUMBER,
    "MAIN_PERIOD_UNIT" VARCHAR2(30 BYTE),
    "OTHERLICTERM"     VARCHAR2(200 BYTE),
    "UNMAP_FLAG"       NUMBER,
    "ORGS_RID"         NUMBER DEFAULT -1,
    "PROD_RID"         NUMBER DEFAULT -1,
    "EDITIONS_RID"     NUMBER DEFAULT -1,
    "VERSIONS_RID"     NUMBER DEFAULT -1,
    "SWLICENV_RID"     NUMBER DEFAULT -1,
    "SWLICTYPE_RID"    NUMBER DEFAULT -1
  );

create  unique index test_idx_UNIQUE_IDX on test_idx(
  case when is_live=1 then OTHERTYPE        end,
  case when is_live=1 then SWLICTYPE_RID    end,
  case when is_live=1 then SWLICENV_RID     end,
  case when is_live=1 then UNIT             end,
  case when is_live=1 then MIN_UNIT_COUNT   end,
  case when is_live=1 then MAX_UNIT_COUNT   end,
  case when is_live=1 then OTHER_ENV_NAME   end,
  case when is_live=1 then PERIOD_VAL       end,
  case when is_live=1 then PERIOD_UNIT      end,
  case when is_live=1 then MAIN_PERIOD_VAL  end,
  case when is_live=1 then MAIN_PERIOD_UNIT end,
  case when is_live=1 then OTHERLICTERM     end,
  case when is_live=1 then UNMAP_FLAG       end,
  case when is_live=1 then ORGS_RID         end,
  case when is_live=1 then PROD_RID         end,
  case when is_live=1 then EDITIONS_RID     end,
  case when is_live=1 then VERSIONS_RID     end
);

Above will give an error.

create  unique index test_idx_UNIQUE_IDX on test_idx(
  OTHERTYPE,
  SWLICTYPE_RID,
  SWLICENV_RID,
  UNIT,
  MIN_UNIT_COUNT,
  MAX_UNIT_COUNT,
  OTHER_ENV_NAME,
  PERIOD_VAL,
  PERIOD_UNIT,
  MAIN_PERIOD_VAL,
  MAIN_PERIOD_UNIT,
  OTHERLICTERM,
  UNMAP_FLAG,
  ORGS_RID,
  PROD_RID,
  EDITIONS_RID,
  VERSIONS_RID
);

Above would not give any error.

Tom Kyte
January 15, 2013 - 9:47 am UTC

I've created:

Bug 16171178 - FUNCTION BASED INDEX THAT REFERENCES 17 COLUMNS FAILS WITH ORA-1793

please contact support and reference that bug.

temporary workaround:

ops$tkyte%ORA11GR2> create  unique index t_idx on t(
  2    case when c0=1 then c1 ||
  3    '/'|| c2 ||
  4    '/'|| c3 ||
  5    '/'|| c4 ||
  6    '/'|| c5 ||
  7    '/'|| c6 ||
  8    '/'|| c7 ||
  9    '/'|| c8 ||
 10    '/'|| c9 ||
 11    '/'|| c10 ||
 12    '/'|| c11 ||
 13    '/'|| c12 ||
 14    '/'|| c13 ||
 15    '/'|| c14 ||
 16    '/'|| c15 ||
 17    '/'|| c16 ||
 18    '/'|| c17 end
 19  );

Index created.

ORA-01793: maximum number of index columns is 32

Ravi B, January 11, 2013 - 1:33 pm UTC

Sorry, i posted the wrong error message. The correct error message is:

Error at Command Line:50 Column:27
Error report:
SQL Error: ORA-01793: maximum number of index columns is 32
01793. 00000 - "maximum number of index columns is 32"
*Cause:
*Action:

9i not using index in the execution plan

Al Ricafort, January 14, 2013 - 12:55 am UTC

Hi Tom,

Do you know why 9i does not use the index in the execution plan? 

---- 9i -----
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL>
SQL>
SQL> CREATE TABLE T2 (
   ID NUMBER,
   NAME VARCHAR2(10),
   FLAG VARCHAR2(1)
);  4    5

Table created.

SQL> CREATE UNIQUE INDEX FN_UNIQUE_IDX
     ON T2 (CASE WHEN FLAG='Y' THEN ID ELSE NULL END);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T2', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      false
query_rewrite_integrity              string      enforced

SQL> alter session set query_rewrite_enabled=true;

Session altered.

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced

SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL> set autotrace traceonly explain
SQL>
SQL> SELECT * FROM T2 WHERE CASE WHEN FLAG='Y' THEN ID ELSE NULL END = 1234;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=22)
   1    0   TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1 Bytes=22)


---- 11g -----
Connected to:
Oracle Database 11g Release 11.2.0.1.0 - Production

SQL>
CSQL> REATE TABLE T2 (
   ID  2   NUMBER,
  3     NAME VARCHAR2(10),
     4  FLAG VARCHAR2(1)
)  5  ;

Table created.

SQL> CREATE UNIQUE INDEX FN_UNIQUE_IDX
    2     ON T2 (CASE WHEN FLAG='Y' THEN ID ELSE NULL END);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 'T2', cascade => TRUE);

PL/SQL procedure successfully completed.

SQL> show parameter query

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
query_rewrite_enabled                string      TRUE
query_rewrite_integrity              string      enforced
SQL> alter session set cursor_sharing='EXACT';

Session altered.

SQL>
seSQL> t autotrace traceonly explain
SQL> SELECT * FROM T2 WHERE CASE WHEN FLAG='Y' THEN ID ELSE NULL END = 1234;

Execution Plan
----------------------------------------------------------
Plan hash value: 2944660367

--------------------------------------------------------------------------------
-------------

| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU
)| Time     |

--------------------------------------------------------------------------------
-------------

|   0 | SELECT STATEMENT            |               |     1 |    35 |     0   (0
)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| T2            |     1 |    35 |     0   (0
)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | FN_UNIQUE_IDX |     1 |       |     0   (0
)| 00:00:01 |

--------------------------------------------------------------------------------
-------------


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

   2 - access(CASE "FLAG" WHEN 'Y' THEN "ID" ELSE NULL END =1234)

SQL>


9i not using index in the execution plan

Al Ricafort, January 14, 2013 - 1:46 am UTC

Hi Tom,

I got it to work. I got the hint from dba_tab_cols. It's the quote and double quotes. It seems Oracle converted my single quotes to double quotes.

So the SQL has to be like below for 9i.

SELECT * FROM T2 WHERE CASE "FLAG" WHEN 'Y' THEN "ID" ELSE NULL END = 1234;


9i not using index in the execution plan

Al Ricafort, January 14, 2013 - 2:01 am UTC

Hi,

"It's the quote and double quotes. It seems
Oracle converted my single quotes to double quotes."

Sorry the above statement is wrong. I did not look closely.

What Oracle did do is enclosed the columns in double quotes when it stored the virtual column. But in my 'SELECT' statement I do not have the quotes and it seems in 9i it cannot see that the 2 expressions(with and without quotes) are the same.

ORA-01793: maximum number of index columns is 32

Ravi B, January 15, 2013 - 1:39 pm UTC

Hi Tom,

Thanks for providing the workaround.

Could you please explain how concatenating with '/' and using OR works in this case?

Thanks!
Tom Kyte
January 15, 2013 - 2:42 pm UTC

I made an assumption that "/" is a character not present in your data (use something else if it is - or use a fixed width string)


I did this so that if you had say two columns (instead of so many), and had values:

c1             c2
---------      -----------
55             42
5              542


if we just concatenated them - you would get 5542 for both rows. by putting a '/' in there - you get 55/42 and 5/542 - keeping them unique.

Diabled Functional indexes

Namrata, October 03, 2013 - 7:45 pm UTC

If I insert new records to a table with disable Index, I would have to enable and rebuild the index in order to leverage advantage of index.

My question is that is it true for functional indexes as well. Do I have to rebuild my functional indexes to make use of their capabilities?

CREATE INDEX WITH NVL

Samby, November 17, 2015 - 12:47 pm UTC

CAn we create index with NVL function:

CREATE TABLE ABC
(DD_ID NUMBER,
DD_CODE VARCHA2(40),
DD_NAME VARCHA2(60,
UPDATED_DATE DATE,
INSERTED_DATE DATE,
);


CREATE INDEX ON ABC (NVL(UPDATED_DATE,INSERTED_DATE));

I have a scenerio where i need to take UPDATED_DATE first if its null then inserted date.

CAn we create index with NVL function.
Please goude

Chris Saxon
November 18, 2015 - 6:31 am UTC

Well... you cant do anything with DDL you provided...because its wrong

SQL> CREATE TABLE ABC
  2  (DD_ID NUMBER,
  3  DD_CODE VARCHA2(40),
  4  DD_NAME VARCHA2(60,
  5  UPDATED_DATE DATE,
  6  INSERTED_DATE DATE,
  7  );
DD_CODE VARCHA2(40),
               *
ERROR at line 3:
ORA-00907: missing right parenthesis


But assuming all the typos are fixed, then yes.

SQL> CREATE TABLE ABC
  2  (DD_ID NUMBER,
  3  DD_CODE VARCHAR2(40),
  4  DD_NAME VARCHAR2(60),
  5  UPDATED_DATE DATE,
  6  INSERTED_DATE DATE
  7  );

Table created.

SQL>
SQL>
SQL> CREATE INDEX ABC_IX ON ABC (NVL(UPDATED_DATE,INSERTED_DATE));

Index created.



Function based index

Samby, November 18, 2015 - 6:36 am UTC

Hi,

Sorry for the typo. I have created function based index using NVL.

My Question is will it be useful and solve my purpose.

Is it work same way like other function based index bcause i havn't see any function based index using NVL till now.

Thanks in Advance.
Connor McDonald
November 18, 2015 - 6:41 am UTC

Yes, assuming the data is sufficiently selective, and it makes sense for the optimizer to use the index, then it should be of assistance.

Function-based index + Index Organized Tables + FFS + Insert?

hpucky, August 23, 2017 - 2:52 pm UTC

Random question regarding function based indexes and index-organized tables. I've noticed I can't seem to force a fast full scan of a function-based index on an IOT, when it's the source of data for an INSERT -- see the last statement in sample below. Is there any way around this or just a known limitation? Oracle 12.1.0.2.0.

SQL> CREATE TABLE source_heap (A NUMBER(10), B CHAR(5));

Table created.

SQL> CREATE INDEX IX_source_heap ON source_heap (UPPER(B));

Index created.

SQL> CREATE TABLE source_iot (A NUMBER(10), B CHAR(5), CONSTRAINT PK_source_iotPRIMARY KEY (A)) ORGANIZATION INDEX;

Table created.

SQL> CREATE INDEX IX_source_iot ON source_iot (UPPER(B));

Index created.

SQL> CREATE TABLE dest (B CHAR(5));

Table created.

SQL> SET AUTOTRACE ON;
SQL> SELECT /*+ INDEX_FFS(source_heap IX_source_heap) */ UPPER(B) FROM source_heap WHERE UPPER(B) = 'hi';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2515621119

---------------------------------------------------------------------------------------

| Id  | Operation            | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

---------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |                |     1 |     7 |     2   (0)| 00:00:01 |

|*  1 |  INDEX FAST FULL SCAN| IX_SOURCE_HEAP |     1 |     7 |     2   (0)| 00:00:01 |

---------------------------------------------------------------------------------------


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

   1 - filter(UPPER("B")='hi')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

SQL> SELECT /*+ INDEX_FFS(source_iot IX_source_iot) */ UPPER(B) FROM source_iot WHERE UPPER(B) = 'hi';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 2973202493

--------------------------------------------------------------------------------------

| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |               |     1 |     7 |     2   (0)| 00:00:01 |

|*  1 |  INDEX FAST FULL SCAN| IX_SOURCE_IOT |     1 |     7 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------------------


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

   1 - filter(UPPER("B")='hi')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


SQL> INSERT INTO dest (B)SELECT /*+ INDEX_FFS(source_heap IX_source_heap) */ UPPER(B) FROM source_heap WHERE UPPER(B) = 'hi';

0 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 2515621119

-------------------------------------------------------------------------------------------

| Id  | Operation                | Name           | Rows  | Bytes | Cost (%CPU)| Time     |

-------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT         |                |     1 |     7 |     2   (0)| 00:00:01 |

|   1 |  LOAD TABLE CONVENTIONAL | DEST           |       |       |            |          |

|*  2 |   INDEX FAST FULL SCAN   | IX_SOURCE_HEAP |     1 |     7 |     2   (0)| 00:00:01 |

-------------------------------------------------------------------------------------------


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

   2 - filter(UPPER("B")='hi')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold

SQL> INSERT INTO dest (B)SELECT /*+ INDEX_FFS(source_iot IX_source_iot) */ UPPER(B) FROM source_iot WHERE UPPER(B) = 'hi';

0 rows created.


Execution Plan
----------------------------------------------------------
Plan hash value: 2629113429

------------------------------------------------------------------------------------------

| Id  | Operation                | Name          | Rows  | Bytes | Cost (%CPU)|Time     |

------------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT         |               |     1 |     7 |     1   (0)|00:00:01 |

|   1 |  LOAD TABLE CONVENTIONAL | DEST          |       |       |            |         |

|*  2 |   INDEX RANGE SCAN       | IX_SOURCE_IOT |     1 |     7 |     1   (0)|00:00:01 |

------------------------------------------------------------------------------------------


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

   2 - access(UPPER("B")='hi')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - automatic DOP: Computed Degree of Parallelism is 1 because of parallel threshold


SQL> DROP TABLE source_heap;

Table dropped.

SQL> DROP TABLE source_iot;

Table dropped.

SQL> DROP TABLE dest;

Table dropped.

Connor McDonald
August 24, 2017 - 1:15 am UTC

This looks like a new question to me.

REVIEW_WARNING

Oracle dynamic function->No index scan

Aakash Bhattacharya, November 19, 2020 - 8:01 pm UTC

There is a query inside a function like the following:
***in_emp_id is an input parameter and emp_id is the column of the table.

SELECT *
FROM abc
WHERE
NVL2(TRIM(in_emp_id),emp_id,'#') = NVL(TRIM(in_emp_id),'#')
AND
NVL2(TRIM(in_emp_tnos),(emp_tnos),'#') = NVL(TRIM(in_emp_tnos),'#')

The motive is that if one of the input parameter is null then execute the statement '#' = '#' and if not NULL then execute the statement column = value.
The query will be more restrictive when more than one input is provided.

****I have 22 such input parameters and where clause statements, this is designed as an oracle table function to return rows to adhoc Tableau parameter input from users*****

There are indexes on the column emp_id and emp_tnos.

However the query is always performing full table scan because when I see the execution plan it looks like below.

NVL2 (TRIM ( :B23), EMP_ID, '#') = NVL (TRIM ( :B23), '#')
AND
NVL2 (TRIM ( :B22), (EMP_TNOS), '#') = NVL (TRIM ( :B22), '#')

Can you please suggest what kind of functional index can i create to force index scan?
Or is there any other way to achieve the same restrictive output.

The base table has more than 60 Million records and with the search criteria the record counts will be circa 200 max.

Can you please help?

Regards,
Akki



Chris Saxon
November 20, 2020 - 9:50 am UTC

The bind values are supplied at runtime => you can't store them in an index => you can't use a function-based index for this.

If you use clauses like:

col = nvl ( :var, col )


The optimizer can effectively create two subplans - one if the bind is null (using a full table scan) and other if it isn't (using an index on col):

var eid number;
set serveroutput off
select * from hr.employees
where  employee_id = nvl ( :eid, employee_id );

select * 
from   table(dbms_xplan.display_cursor(format => 'BASIC LAST'));

----------------------------------------------------------                  
| Id  | Operation                      | Name            |                  
----------------------------------------------------------                  
|   0 | SELECT STATEMENT               |                 |                  
|   1 |  VIEW                          | VW_ORE_B4851255 |                  
|   2 |   UNION-ALL                    |                 |                  
|   3 |    FILTER                      |                 |                  
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES       |                  
|   5 |      INDEX UNIQUE SCAN         | EMP_EMP_ID_PK   |                  
|   6 |    FILTER                      |                 |                  
|   7 |     TABLE ACCESS FULL          | EMPLOYEES       |                  
----------------------------------------------------------


It won't do this for every combination of subplans if you have many optional parameters though. And col = col only works if it's mandatory (not null).

If you can use dynamic SQL to only add the where clause when the bind is not null, the optimizer can choose the appropriate indexes each time.

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