DOES ANYONE KNOW BENEFITS OF FUNTION BASED INDEX
CAN I USE INDEX WHERE
NVL(COLUMN name)= value
trunc(sysdate)=value
what will be the effect if i pass bind variable in value and hard core value in value.
Thanks
UKDBA
Printable View
DOES ANYONE KNOW BENEFITS OF FUNTION BASED INDEX
CAN I USE INDEX WHERE
NVL(COLUMN name)= value
trunc(sysdate)=value
what will be the effect if i pass bind variable in value and hard core value in value.
Thanks
UKDBA
U can't use nvl, sysdate ... functions directly, because its not deterministic.
But u can create ur own deterministic function for it.
For example:
SQL> create index i_nvl_date on jobs(nvl(CLOSE_DATE, trunc(sysdate)));
create index i_nvl_date on jobs(nvl(CLOSE_DATE, trunc(sysdate)))
*
ERROR at line 1:
ORA-01743: only pure functions can be indexed
---------------------------------------------------
create or replace function idx_sysdate(p_date date,
def_date date default sysdate) return date
DETERMINISTIC
is
begin
if p_date is null then return trunc(def_date);
else return p_date;
end if;
end;
/
Function created.
SQL> create index i_nvl_date on jobs(idx_sysdate(CLOSE_DATE,'01-JAN-3001'));
Index created.
SQL> drop index i_nvl_date;
Index dropped.
SQL> create index i_nvl_date on jobs(idx_sysdate(CLOSE_DATE));
Index created.
And remember to collect histograms with FBIs in order to help the optimezer.
i mean this
Standard B-Tree index on SURNAME with cost based optimizer
create index non_fbi on sale_contacts (surname);
analyze index non_fbi compute statistics;
analyze table sale_contacts compute statistics;
SELECT count(*) FROM sale_contacts
WHERE UPPER(surname) = 'ELLISON';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'SALES_CONTACTS' (Cost=3 Card=16 Bytes=272)
Now we use a function based index
create index fbi on sale_contacts (UPPER(surname));
analyze index fbi compute statistics;
analyze table sale_contacts compute statistics;
SELECT count(*) FROM sale_contacts WHERE UPPER(surname) = 'ELLISON';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=17)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'FBI' (NON-UNIQUE) (Cost=2 Card=381 Bytes=6477)
you can use function in index creation in 8i.
I get the same error:
"ORA-01743: only pure functions can be indexed"
when I try to do this:
create index date_index
on mytable(to_date(date_column, 'MM/DD/RR'))
tablespace process_index;
See, this "date_column" is not of "date" datatype, rather of varchar, and, it's not consistent...meaning sometimes it's '8/6/02' and sometimes it's '8/06/02'...so implicit conversion doesn't work, therefore I have to use the to_date function in my queries.
I need to index this column becasue there's over 20 million rows in this table, but when I try to create a function based index with the above statement, I get the error.
Help...any sugestions?
The "RR" is non-deterministic because it's evaluation depends on sysdate.
You want fast access with a predicate that involves a function that checks for the first letter of the last name is 'A'. Here is the first query with the execution plan.
set autotrace traceonly explain;
select count(*) from emp_test where regexp_like( last_name, '^A' );
Execution Plan
----------------------------------------------------------
Plan hash value: 982230031
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 3040 (1)| 00:00:37 |
| 1 | SORT AGGREGATE | | 1 | 7 | | |
|* 2 | TABLE ACCESS FULL| EMP_TEST | 53500 | 365K| 3040 (1)| 00:00:37 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( REGEXP_LIKE ("LAST_NAME",'^A'))
Without and index, a full table scan is performed. Here is a function and a function based index to improve the performance. I chose a bitmap index since the cardinality is 2, very low, either 0 or 1.
create or replace function check_name( name in varchar2 ) return number
DETERMINISTIC
is
begin
if regexp_like( name, '[A]' ) then
return 1;
end if;
return 0;
end;
/
set autotrace off
create bitmap index emp_test_ck_name_bmidx on emp_test( check_name( last_name ) );
set autotrace on traceonly explain;
select count(*) from emp_test where check_name( last_name ) = 1;
Execution Plan
----------------------------------------------------------
Plan hash value: 1380382320
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 17 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | BITMAP CONVERSION COUNT | | 10700 | 135K| 17 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| EMP_TEST_CK_NAME_BMIDX | | | | |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
3 - access("TEST"."CHECK_NAME"("LAST_NAME")=1)
Notice that the, very small, bitmap index is scanned and not the table.
There you have it.
David Bradford
Thank you for the contribution David, just a couple of notes.
1- Thread was going to be 10 years old on September 19th - now we will have to wait other 10 years for the big party!
2- Function Based Indexes are primarily used to avoid predicate invalidation when there is no way of avoiding the use of a built-in-function e.g. truncate(), etc.
3- Bitmap indexes are not designed to be used alone, bitmap indexes perform better when a number of them exist and are used on the predicate e.g. you want to select a RED car of the COMPACT type that has to have AUTOMATIC transmition and, has to have A/C - In this particular case four bitmap indexes would work together to return the few that comply with the search criteria.
Hope this helps.