| Oracle9i Function Base index? [message #137203] |
Tue, 13 September 2005 14:50  |
mitra fatolahi
Messages: 38
Registered: October 2002
|
Member |
|
|
Hello,
Our java application is storing data in some of the columns in mix cases. And for some reasons that I am not aware of we have Function-based indexes on some of these columns. In Oracle9i documentation I read that Function-based indexes defined with the UPPER(column_name) or LOWER(column_name) keywords allow case-insensitive searches. I looked into our code and noticed none of the SELECT query statments against these columns use UPPER()or LOWER() functions in the WHERE clause. SQL trace file shows that Oracle9i is not using these indexes.
Do we really need Function-based indexes on columns with mix case data? Should we drop these Function-based indexes and create Normal indexes instead since our code is not using UPPER(column_name) or LOWER(column_name)funcitons in the WHERE clauses? I was told that using functions in our select query statements and having Function-based indexes most likely will have effect on perfromance--yes, no?
Thank you,
Mitra
|
|
|
|
| Re: Oracle9i Function Base index? [message #137206 is a reply to message #137203] |
Tue, 13 September 2005 16:00  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
If you have a regular index, it will store the actual key values in the column. That sounds like mixed case. If a query then does a comparison using the exact same mixed case, then the index would be used (in theory, if cbo determines appropriate). But if your where clause used upper(col) = upper(val), then the index could not be used. But if you indexed upper(col), as in a function based index, then it could.
Perhaps the best way to explain is like this:
MYDBA@ORCL >
MYDBA@ORCL > create table test(a varchar2(30));
Table created.
MYDBA@ORCL >
MYDBA@ORCL > insert into test values ('HEllo');
1 row created.
MYDBA@ORCL > commit;
Commit complete.
MYDBA@ORCL >
MYDBA@ORCL > create index reg_idx on test(a);
Index created.
MYDBA@ORCL > create index fbi_idx on test(upper(a));
Index created.
MYDBA@ORCL >
MYDBA@ORCL > exec dbms_stats.gather_table_stats(user,'test',cascade=>true,method_opt=>'for all columns size 250');
PL/SQL procedure successfully completed.
MYDBA@ORCL >
MYDBA@ORCL > set autotrace on explain;
MYDBA@ORCL >
MYDBA@ORCL > select * from test where a = 'hello';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=6)
1 0 INDEX (RANGE SCAN) OF 'REG_IDX' (INDEX) (Cost=1 Card=1 Bytes=6)
MYDBA@ORCL >
MYDBA@ORCL > select * from test where a = 'HELLO';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=6)
1 0 INDEX (RANGE SCAN) OF 'REG_IDX' (INDEX) (Cost=1 Card=1 Bytes=6)
MYDBA@ORCL >
MYDBA@ORCL > select * from test where a = 'HEllo';
A
------------------------------
HEllo
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=6)
1 0 INDEX (RANGE SCAN) OF 'REG_IDX' (INDEX) (Cost=1 Card=1 Bytes=6)
MYDBA@ORCL >
MYDBA@ORCL > select * from test where upper(a) = 'HEllo';
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=2 Card=1 Bytes=6)
2 1 INDEX (RANGE SCAN) OF 'FBI_IDX' (INDEX) (Cost=1 Card=1)
MYDBA@ORCL >
MYDBA@ORCL > select * from test where upper(a) = 'HELLO';
A
------------------------------
HEllo
1 row selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=6)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST' (TABLE) (Cost=2 Card=1 Bytes=6)
2 1 INDEX (RANGE SCAN) OF 'FBI_IDX' (INDEX) (Cost=1 Card=1)
MYDBA@ORCL >
MYDBA@ORCL > set autotrace off;
MYDBA@ORCL > drop table test;
Table dropped.
MYDBA@ORCL > set echo off;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
