Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Linguistic index with LIKE + LOWER

PauloDec 17 2013 — edited Dec 18 2013

Hi,

It seems that Oracle 11.2.0.1.0 is not able to use linguistic index when using the LIKE operator + LOWER function...

ALTER SESSION SET NLS_COMP='LINGUISTIC';

ALTER SESSION SET NLS_SORT='FRENCH_AI';

CREATE INDEX test_fai_idx

ON mytable(NLSSORT(LOWER(somefield), 'NLS_SORT=FRENCH_AI'));

SELECT *

FROM mytable

WHERE LOWER(somefield) LIKE 'gue%';

-- Table access FULL

DROP INDEX test_fai_idx;

ALTER SESSION SET NLS_COMP='BINARY';

CREATE INDEX test_bin_idx

ON mytable(LOWER(somefield));

SELECT *

FROM mytable

WHERE LOWER(somefield) LIKE 'gue%';

-- Index Range Scan

Questions:

-Will an upgrade to 11.2.0.2, 11.2.0.3 or 11.2.0.4 could solve that problem? (if anyone of you who have access to such a db and could make the test and report the result here, that will be much appreciated)

-Other ideas to help me?

I understand that using LOWER + AI seem strange since AI means accent-insensitive AND case-insensitive but, the software which run on the db use LOWER in all his SQL...

Thanks.

Paulo

Comments

KanchDev
Hi Jumbo,

You can consider an incremental rman backup from production database and recover DR database.

This method is available from 10g. otherwise you need to recreate the Standby database.


Cheers,
Kanchana.
CKPT
If the DR is not functioning from last 6 months, According to database size you can rebuild standby database.

if the data difference is not so huge, You can perform incremental roll forward.

For 10g, refer this below link

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/scenarios.htm#CIHIAADC
Shivananda Rao
As said, you can recreate the standby database if the size of the production database is quite less. If not, then make use of the incremental or rollforwarding method.

Refer this MOS ID *Steps to perform for Rolling forward a standby database using RMAN incremental backup when primary and standby are in ASM filesystem [ID 836986.1]*
SITI
Hi SRP,

DB status:

1.*No ASM storage.*

2.*oracle 9i.*

3.*Archive logs deleted on both Primary and Standby.*

my question:

1.without recreate standby.*is their any method to recover the standby database?*

Regards,
Jumbo.
Shivananda Rao
Since you have lost the archives on the primary database, it is not possible for you to recover your standby database.

The only option is to recreate it fully (if the size of the primary database is small) or to go with the roll forwarding method.
Ganadeva
Hi,

Try this for 9i

Rolling a Standby Forward using an RMAN Incremental Backup in 9i [ID 290817.1]

Please let us know if this works...

Regards,
Ganadeva
SITI
thank you for all.
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jan 15 2014
Added on Dec 17 2013
2 comments
470 views