Oracle sql case sensitive index

Question

In my project database present table Products, one column called 'Name' So, this table can contain 'Product A' and 'Product a' Both products should be selected in simple select query.

I am created index: CREATE INDEX IDX_PRODUCT_NAME ON PRODUCTS(NAME)

And after that ran SQL query: SELECT /*+ INDEX(PRODUCTS IDX_PRODUCT_NAME)*/ * FROM PRODUCTS WHERE NAME='Product_a'

What I did incorrect?

P.S. I know about variant with changing session flags: NLS_COMP=ANSI; NLS_SORT=BINARY_CI; But for me interesting variant without changes to db settings, does it possible? Or e.g. change this flag only for my query.

Please, take a look picture

UPDATE: William Robertson helped me in comments. CREATE INDEX IDX_PRODUCT_NAME ON PRODUCTS(UPPER(NAME))

After that run query: SELECT * FROM PRODUCTS WHERE **UPPER**(NAME)='PRODUCT_A' And this variant works fine!

What is the issue? Did you get an error, an unexpected behaviour? — Aleksej, Feb 12 '18 at 15:16
After `SELECT /*+ INDEX(PRODUCTS IDX_PRODUCT_NAME)*/ * FROM PRODUCTS WHERE NAME='Product_a'` i haven't any output and plan shown that index is not been used, but if my index just for name, without LOWER function and I ran simple select, plan shown that index have been used — Ihor Yatsenko, Feb 12 '18 at 15:19
Look at a function based index, create index on products(upper(name)), the use upper(name) in your predicate. — unleashed, Feb 12 '18 at 15:21
Index with UPPER func also not work. Check my comment above, I changed it. — Ihor Yatsenko, Feb 12 '18 at 15:25
Generally the hint may not be required if you have latest statistics in place.Oracle is smart to understand if indeed it is cost-effective to use the index to retrieve the result. Also as suggested by unleashed, it could be better to have an index on `UPPER(name)` in your case. But it may or may not improve performance, if that is what you are looking for, because the optimizer could ignore it when it's a sub optimal execution plan. — Kaushik Nayak, Feb 12 '18 at 15:28
Please, take a look picture in question or by link https://i.stack.imgur.com/QQymC.png Result is empty — Ihor Yatsenko, Feb 12 '18 at 15:40
It isn't a case-sensitive query unless you specify `upper(name)` in your `where` clause. — William Robertson, Feb 12 '18 at 16:07
Sorry @WilliamRobertson, question for Kaushik Nayak He wrote me in comments next: > . Also as suggested by unleashed, it could be better to have an index on > UPPER(name) in your case. And I want understand why UPPER is better than LOWER, as for me it's looks like the same if we talks about performance — Ihor Yatsenko, Feb 12 '18 at 16:54
@KaushikNayak Please answer for question in comment above. Thanks — Ihor Yatsenko, Feb 12 '18 at 16:55

William Robertson · Answer 1 · 2018-02-12T16:18:14.097

One approach would be to add a virtual column:

create table demo_products
( id          integer not null
, name        varchar2(20)
, name_upper  generated always as (upper(name)) );

create index demo_prod_upper_name_ix on demo_products(name_upper);

insert all
    into demo_products (id, name) values (1, 'Prod A')
    into demo_products (id, name) values (2, 'Prod a')
    into demo_products (id, name) values (3, 'Prod B')
    into demo_products (id, name) values (4, 'Prod b')
    into demo_products (id, name) values (5, 'prod A')
    into demo_products (id, name) values (6, 'Cheese')
    into demo_products (id, name) values (7, 'Bananas')
select * from dual;

commit;

select count(*) from demo_products where name_upper = 'PROD A';

  COUNT(*)
----------
         3

Plan hash value: 4158816492

---------------------------------------------------------------------------------------------
| Id  | Operation         | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                         |     1 |    12 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |                         |     1 |    12 |            |          |
|*  2 |   INDEX RANGE SCAN| DEMO_PROD_UPPER_NAME_IX |     1 |    12 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("NAME_UPPER"='PROD A')

Can't you `create index foo on demo_products( upper(name) )` and then `select ... where upper(name) = upper('prod a')` in oracle? (you can have function-based indexes in postgres - not sure about oracle) — giorgiga, Feb 12 '18 at 16:18
Yes, that is another approach that will work. However, it can be convenient to wrap the expression in a virtual column, especially if it is more complex than this example. — William Robertson, Feb 12 '18 at 16:20

Oracle sql case sensitive index

1 Answers1