Using wrong index

[maesb]

i've got 2 different users with the same set of database, but when i run the same query on both users, they seem to be using different indexes to run the query. How do i make them use the same index?

Following is my query :
select a.cust,(b.dorq-b.qrtn) as qnty
from soinno a, ptdord b, socrsd c
where a.crid=b.crid and a.dono=b.dono
and b.crid=c.crid and b.refn=c.sono and b.oprt=c.part
and (a.crid>='AA' and a.crid<='ZZ')
and a.pmth >= '10410' and a.pmth <= '10412'
and a.cust not like 'ZZ%' and a.refn not like 'JS%'

one user is running on index with the following field
CRID
DONO

the other user is running on following index
CRID
CUST

 

[SantaMufasa]

Maesb,

Have you confirmed that both users have identical permissions to the same objects involved?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[maesb]

Hi Musafa,
The database structure of both users are the same as i exported user1 and imported the tables into user2. So i assume the permission should be the same? The only difference i know of is that user1 and user2 are using a different table_space. does that answer your question?

 

[sem]

Oracle CBO takes into account a lot of parameters thus if that tables contain different data it may choose different plans. BTW is statistics on both tables is up to date? If so and you wish to make execution plans to be the same you may use stored outlines. But I'm about to be sure that this was due to the lack of/outdated statistics on one of the tables.

Regards, Dima

http://seminihin.narod.ru

 

[sbix]

Normally when you execute an import, statistics are computed.
So, if your user2 has a better response time, try to update statistics onto user1's objects