Index Lowercase

623219

Hi there!

I got a doubt here about index creation and I hope you can help me here, please.

I´m trying to create an index in a table, specifying which this index must be in lowercase.

The table has a lot of columns but I just need the index for three columns only in lowercase.

I tried something:

SQL> create table teste(a varchar2(10),
2 b varchar2(10));

Table created.

SQL> insert into teste values ('teste','Teste');

1 row created.

SQL> insert into teste values ('Teste','testE');

1 row created.

SQL> insert into teste values ('TeSte','tEsTe');

1 row created.

SQL> insert into teste values ('tesTe','TesTe');

1 row created.

SQL> commit;

Commit complete.

------------------------
-- First Attempt --
------------------------

SQL> create index teste_ix on teste (lower(a,b));
create index teste_ix on teste (lower(a,b))
*
ERROR at line 1:
ORA-00909: invalid number of arguments

-----------------------------
-- Second Attempt --
-----------------------------

SQL> create index teste_ix on teste ((lower(a,b)));
create index teste_ix on teste ((lower(a,b)))
*
ERROR at line 1:
ORA-00909: invalid number of arguments

--------------------------
-- Third Attempt --
--------------------------

SQL> create index teste_ix on teste (UPPER(a,b));
create index teste_ix on teste (UPPER(a,b))
*
ERROR at line 1:
ORA-00909: invalid number of arguments

-------------------------------------------------
-- tried to cut off the parenthesis --
-------------------------------------------------

SQL> create index teste_ix on teste lower(a,b);

Index created.


Thanks in advance!

Centinul

Leonardo Souza wrote:
Hi there!

I got a doubt here about index creation and I hope you can help me here, please.

I´m trying to create an index in a table, specifying which this index must be in lowercase.

SQL> create index teste_ix on teste lower(a,b);

This is not actually a function-based index. In this case the term 'lower' is actually interpreted as a table alias for teste. This is created as a normal index as shown below:

SQL> SELECT INDEX_NAME,INDEX_TYPE,FUNCIDX_STATUS FROM USER_INDEXES WHERE INDEX_NAME LIKE 'TESTE_IX%';

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_
------------------------------ --------------------------- --------
TESTE_IX                       NORMAL

A function-based index would return the following results:

INDEX_NAME                     INDEX_TYPE                  FUNCIDX_
------------------------------ --------------------------- --------
TESTE_IX2                      FUNCTION-BASED NORMAL       ENABLED

The LOWER() function only takes ONE argument and that is why you were getting errors above. Your index syntax should have been:

CREATE INDEX TESTE_IX ON TESTE (LOWER(A),LOWER(B));

HTH!

623219

Hi Centinul!

Thanks for your quick reply!

I´ll fix my scripts and try to run it out again.

As soon as I got the response, I´ll paste the results here.

Thx a lot and have a gr8 day!

623219

Hi there!

I got to create the index correctly.

create index teste_ix on teste (lower(a),lower(b))
/

Now, when I query select a,b from teste, I perceived which the index is not being used anymore and the query is doing full scan table.

I changed the parameter query_rewrite_enabled='TRUE' in init.ora and bounced the instance but still doesn´t use the index.

Is there another parameter or something else I can do to use the index, like I used before to create the index as function-based??

Thanks in advance.

Centinul

Did you gather table statistics after you created the Function-Based Index?

exec DBMS_STATS.GATHER_TABLE_STATS(USER,'TESTE',cascade=>true);

When you create a FBI Oracle creates a hidden column on the table to contain the result of the function. However, no statistics are gathered by default if I remember correctly.

If that still doesn't work check out these threads:

{message:id=1812597}

{thread:id=863295}

Finbar Fitzgerald

Hi there,
just an additional point. you may wish to consider the benefits of applying the lower function to data as it is inserted/updated. if you can ensure the data is always entered lowercase as well as consistency you will no longer need to apply the function on the indexes.

regards