-
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.
Oracle Certified Master
Oracle Certified Professional 6i,8i,9i,10g,11g,12c
email: ocp_9i@yahoo.com
-
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.
-
can you use to_date function in index creation
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.
-
Good Use for Function Based Index
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|