We help IT Professionals succeed at work.

Oracle Function Based Index and Non-Function Based Index on Same Column

iBinc
iBinc asked
on
167 Views
Last Modified: 2017-04-18
Is it possible to have both a function based and non-function based index on the same column?

LOWER(COL_A) AND COL_A indexes?
Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes

easy to verify - just try it

Author

Commented:
Hi, I did try it and the LOWER() works if the non-lower index does not exist. As soon as the second index is added, neither index works.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
can you be more specific than "doesn't work"  ?

error (if so, what is the error?) wrong results (if so, what are the results)? no results? etc
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
both indexes work for me

SQL> create table testtable(col_a varchar2(30));

Table created.

SQL> insert into testtable(col_a) select table_name from all_tables;

2712 rows created.

SQL> commit;

Commit complete.

SQL> create index test_index1 on testtable(col_a);

Index created.

SQL> create index test_index2 on testtable(lower(col_a));

Index created.

SQL> explain plan for select * from testtable where col_a='TESTTABLE';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3804344156

--------------------------------------------------------------------------------
| Id  | Operation        | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |             |     1 |    17 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| TEST_INDEX1 |     1 |    17 |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

   1 - access("COL_A"='TESTTABLE')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

17 rows selected.

SQL> explain plan for select * from testtable where lower(col_a)='testtable';

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
Plan hash value: 2054925693

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     1 |    34 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TESTTABLE   |     1 |    34 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | TEST_INDEX2 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
---------------------------------------------------

   2 - access(LOWER("COL_A")='testtable')

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

18 rows selected.

SQL>

Open in new window

Author

Commented:
Sure, sorry...


TEST 1
query after the two indexes have been created and the table/indexes analyzed...
A) test query using LOWER()
SELECT * FROM TABLE WHERE LOWER(INDEXED_COL) LIKE ('%SOMEVALUE%')

QUERY TAKES 30 SECONDS AND DOES FULL TABLE SCAN


B) test query not using LOWER()
SELECT * FROM TABLE WHERE INDEXED_COL LIKE ('%SOMEVALUE%')

QUERY TAKES 30 SECONDS AND DOES FULL TABLE SCAN

TEST 2
DROP THE SECOND INDEX (NON-FUNCTIONED INDEX) AND ANALYZE TABLE/INDEXES

A) test query using LOWER()
SELECT * FROM TABLE WHERE LOWER(INDEXED_COL) LIKE ('%SOMEVALUE%')

QUERY TAKES 2 SECONDS AND DOES NOT DO FULL TABLE SCAN


B) test query not using LOWER()
SELECT * FROM TABLE WHERE INDEXED_COL LIKE ('%SOMEVALUE%')

QUERY TAKES 30 SECONDS AND DOES FULL TABLE SCAN

Author

Commented:
I actually said that wrong... does not do a full table scan when both indexes are there but runs for 30 seconds vs. 2 seconds without the 2nd index.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
LOWER(INDEXED_COL) LIKE ('%SOMEVALUE%')


that should always return no values, it doesn't make sense.  It's legal syntax, but it doesn't make sense to search for capital letters on a lower case result.

BUT...  your condition is a mid-substring search.  An index can't be applied effectively to that.


 INDEXED_COL LIKE ('%SOMEVALUE%')

this has the same problem - you can't use an index to do a middle substring search.

As an analogy - pick up a dictionary, go find all words with "val" somewhere in the middle of the word.
You can't do it easily because there is no way to find a starting point on your search. You have to simply start at the beginning and read all the way through.


The reason your queries are running faster sometimes is either the optimizer is recognizing that your query can't possibly return anything or your data is already in buffer cache or result cache (or both) from previous attempt so now it's fast.

Try conducting your tests with conditions that can use an index.

Author

Commented:
sorry my example is not exact...can't share the sql...forgot to include the LOWER in LIKE function. The actual data is coming from a table col not hard coded.

SELECT * FROM TABLE WHERE LOWER(INDEXED_COL) LIKE LOWER('%SOMEVALUE%')

Author

Commented:
It does return results and nothing is in buffer cache. As soon as you drop the 2nd index it works fine every time and as soon as you add the 2nd index it runs slow.
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
LOWER(INDEXED_COL) LIKE LOWER('%SOMEVALUE%')

that makes sense now but still can't use an index.


you can't search for the middle of a string with an index.

Author

Commented:
I hear you and I've read that ... here is the sample code snip generated by COGNOS

AND LOWER (TABLE.OBJECT) LIKE
   CASE
      WHEN (CASE
                    WHEN (LOWER ('901M') IS NULL)
                     THEN
                              NULL
                     ELSE
                                 ('%'|| LOWER('901M'))
                   END
                        IS NULL)
         THEN
                 NULL
         ELSE
                   (  CASE
                            WHEN (LOWER ('901M') IS NULL)
                                     THEN
                                             NULL
                                     ELSE
                                    ('%' || LOWER ('901M'))
                                     END
                          || '%')
                      END
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
LOWER(INDEXED_COL) LIKE '%SOMEVALUE%'

Open in new window

or
INDEXED_COL LIKE '%SOMEVALUE%'

Open in new window


cannot use any index...

if you make it starts with ie "like 'SOMEVALUE%'" it uses the index...

Author

Commented:
I hear you but it is using the index as long as it is the only index. If I remove it, it runs for 30 to 50 seconds and does a full table scan. If I keep the index it runs in 2 seconds and does not do a full table scan.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
to make everything faster, create an index on "LOWER(COL_A)"

and then your query

select * from yourTable where LOWER(COL_A) like '%' || lower(SOMEVALUE) || '%'

Open in new window


will be fast...

Author

Commented:
That is exactly what I am doing but you didn't read the issue.

The issue is this works great and fast as long as you have JUST ONE index on the column. Problem is sometimes we need this same column indexed without the LOWER. When you add another index, everything runs slow.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you can have two index

COL_A
LOWER(COL_A)

and your queries will be fast (no full table scan)

if you have just regular index on COL_A, then you should not use LOWER function in your query, or any other function here, which will invalidate your index...

select * from yourTable where COL_A like '%SOMEVALUE%'

Open in new window

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
then use hint

/*+ INDEX(table_alias indexname) */

in your query if oracle cannot decide...
or analyze table and all index and columns...
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
what about this

with s as (select id from yourTable where colA like '%SOMEVALUE%')
select * from yourTable t inner join s on s.ID=t.ID

Open in new window

Author

Commented:
I tried using the hint but this didn't work ... both types of queries still slow, though no full table scan...just slow not 2 seconds, the way it runs with only the LOWER index. I've analyzed and I've used the hint neither works. Also, can't use hints in COGNOS query.

I can't explain why both types of queries slow down when both indexes are applied.

Author

Commented:
Cannot change the query as the query is generated from COGNOS.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
so, you cannot modify the query!
you cannot add hint
qry is created dynamically
and you can only add indexes...

is this right?

Author

Commented:
yep that is correct. I just don't understand why adding the regular index messes it up.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
ok, I found a way...

create 2 indexes

TRIM(COL)
LOWER(TRIM(COL))

and use these in your queries...
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
I guess oracle thinks fullscan is better instead of using the index...
and you cannot add index...

please test this structure with your test case... it should use the index, not table scan

select /*+ index(t ix_colA) */ * from yourTable t where colA like '%SOMEVALUE%'

Open in new window

Author

Commented:
ok, I will give that a try

Author

Commented:
The structure stays the same, the speed is the issue...takes about 15 times longer.
Sr. System Analyst
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>>>> I hear you but it is using the index as long as it is the only index

how do you know?

If you're just going by execution time that doesn't mean it's doing what you think it is

using a hint doesn't make sense here because, even if the optimizer observes the hint and uses the index you force, what is that going to do?
best case it must do a full index scan, which might be better than a table scan, but in many cases it won't be.  Since we don't have your actual query it's not possible to say.

Author

Commented:
Well, just looking at the Explain Plan I can see both indexes, one Index fast full scan and the other index range scan.

Author

Commented:
Well I cannot explain it but Huseyin's suggestion worked!!!!

TRIM(COL)
LOWER(TRIM(COL))


Adding another function index worked. Apparently you have to have the same type of index (function in my case) on a single column.

Author

Commented:
The solution was adding another function index on the column.

TRIM(COL)
LOWER(TRIM(COL))
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
good :)
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
10053 trace should explain what is happening but that can be difficult to go through.

but, without the actual query and plans it's hard to make a real suggestion.

I'm glad you have a work around, but I wouldn't suggest adding dummy indexes as a general purpose solution.  I also wouldn't expect it to be reliable solution going forward.  Simply recalculating statistics as your data grows and/or changes may be sufficient to make the hack fail.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
what happening here is:

oracle checks query see "like" operation and thinks it invalidates the index, and decides not to use the index and go full table scan
but when a function is used, and there is function index, oracle uses index
since index is small compared to actual table, it takes very little time compared to actual table, especially there are lots of columns on table

and actually, using hint will solve the issue, but op said, he cannot change the query since it is created dynamically...
Geert GOracle dba
CERTIFIED EXPERT
Top Expert 2009

Commented:
well,
there is a lot of variables on using indexes

SPM is also such a variable

explain plan for "your query"
would help
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
>> oracle checks query see "like" operation and thinks it invalidates the index

LIKE conditions can use indexes

middle-substring searches can't  (CTX indexes are an exception to this but they don't apply here)
as noted above, best case would be a full index scan.  Which "might" be faster than a table scan, but even if it happens to be for one particular index and query is not reliable.

I'm not saying it can't work, I'm trying to reinforce that it should not be taken as a general tuning technique.  It's simply something that happened to work here but is not likely to work elsewhere.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.