Re: and optymalistaion NLSSORT,like

Hi there!

You can find some documentation about function-based indexes at:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/state10c.htm#2063358

Common pitfalls:

• Table and index not analyzed action: analyze table and indexes
• QUERY_REWRITE_ENABLED is false action: alter session set QUERY_REWRITE_ENABLED=true or modify init.ora

BTW if i'm not wrong, if NLS_SORT set to POLISH you may write:
> SELECT * FROM sometable
> WHERE nazwa like 'something%'
> ORDER BY nazwa

If something% is selective enough you have a good chance of using the function-based index, maybe you have to add 'and nazwa is not null'. We had similar problems with NLS_SORT=HUNGARIAN, but we managed to solve it.
BTW nlssort function returns bad value according to hungarian alphabet, maybe you'll have better luck with POLISH.

HTH, Regards

   Flatline

"Marcin Zawadzki" <marcinz_at_kompakt.pl> wrote in message news:<aqd6d2$p5a$1_at_news.tpi.pl>...
> Greetings ALL
>
> I have problem with optymalistation of nlssort
>
> I have question:
> SELECT * FROM sometable
> WHERE nazwa like 'something%'
> ORDER BY nlssort(nazwa,'nls_sort=POLISH')
>
> and index on NLSSORT(nazwa,'nls_sort=POLISH')
>
> I cannot force Oracle (9i) to use INDEX RANGE SCAN in optymalistation.
>
> Any hints?
Received on Sat Nov 16 2002 - 08:04:59 CST