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!

How to create the effective index In Oracle

Rajan SwSep 2 2013 — edited Sep 2 2013

Hi Guys,

I have a column customer_id for the bank .This customer_id is NVARCHAR2(32) datatype.The customer id is nothing but the combination of two ids one is the control no and another is the customer no .So for the small bank the control no which is initiall 20 characters are same allmost for all the customer ids.So under the load Oracle optimizer is not using the index instead going for the full table scan which for handful no of users load the cpu consumption is going very high.

So it it possible to have any effective methods to handle this situation with out splitting the column into two like control no and cusotmer id

We have the following options ,Please let me know if any body have any information regarding these below

Is there any option in Oracle to be set so that the optimizer will consider and use the index even if the first 20 characters are same

can any body see the improvement if the datatype is changed from nvarchar to nchar and the index is rebuilt

Please provide me some ideas.

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

Regards,

Papi

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 30 2013

Added on Sep 2 2013

7 comments

390 views