Forum Stats

  • 3,783,355 Users
  • 2,254,764 Discussions
  • 7,880,372 Comments

Discussions

Index Lowercase

623219
623219 Member Posts: 16
edited Feb 22, 2010 8:18AM in General Database Discussions
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!
Tagged:

Answers

  • Centinul
    Centinul Member Posts: 6,871 Bronze Crown
    edited Feb 5, 2010 12:17PM
    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!
    Centinul
  • 623219
    623219 Member Posts: 16
    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
    623219 Member Posts: 16
    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
    Centinul Member Posts: 6,871 Bronze Crown
    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}
    Centinul
  • 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
This discussion has been closed.