Home » SQL & PL/SQL » SQL & PL/SQL » Index question (Oracle 9i)
Index question [message #280241] Mon, 12 November 2007 23:18 Go to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Hello experts,

I have question regarding index.

I have a query which used to work fine. But recently the data changed in one of the tables due to which one of the index is not used. Is there any way we can force that indexed to be used?

For example:

one of the join condition was

a.id=b.id

Previously precision of both the columns is 11. Now precision of b.id became 15 because of which i had to modify the query as

a.id=substr(b.id,1,11) which is causing index not to be used.
I tried using INDEX(<table_name>, <index_name>) hint, but dint help.

Is there any workaroud?

thnaks
Re: Index question [message #280242 is a reply to message #280241] Mon, 12 November 2007 23:25 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Function based index might be helpful

Thumbs Up
Rajuvan
Re: Index question [message #280245 is a reply to message #280242] Mon, 12 November 2007 23:30 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
Well, i am thinking on the same lines. The table is a partitioned table. What kind of index is suggested.

thanks.
Re: Index question [message #280264 is a reply to message #280241] Tue, 13 November 2007 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Are you aware that you are breaking the first normal form: each field must be atomic?

Quote:

What kind of index is suggested

on "substr(id,1,11)"

Regards
Michel
Re: Index question [message #280450 is a reply to message #280245] Tue, 13 November 2007 12:20 Go to previous messageGo to next message
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
Member
We have data coming from different sources for table A and table B. In one of the sources, the id column is padded as id+encoded value. I am taking the id out by using substring. Here no integrity constraint is violated. The idea is to get a resultant table using table A and table B.

Thanks.
Re: Index question [message #280453 is a reply to message #280450] Tue, 13 November 2007 12:27 Go to previous message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Anyway, this is a bad design

Regards
Michel
Previous Topic: problem with Function
Next Topic: Very time consuming query or procedure
Goto Forum:
  


Current Time: Sun Dec 11 08:02:22 CST 2016

Total time taken to generate the page: 0.08317 seconds