urgent help

[meghna]

Hi,

I want an urgent help in tuning this query.

select "a","b","c" frm vw_arc where ( nbr in ( 1,2,3,4,5,.......,2000) or nbr in (2001,.....,4000)
or nbr in(4001,....60001) or nbr in(5000,.......7000) or nbr in(7001,.......9001))
order by d,e ;

Here the columns selected are almost 100 in number
and the nbrs are not sequentials and almost 500 in each set and the numbers in each set is exclusive to the other set,
so this query is very log and it is taking a lot hell of time.
Any tuning tip on this ???
vw_arc is a view on the main table.

[jayeshdba]

Hi,

First use parallel hint in ur query .
Second decides order of columns in where clause properly .
It is decending in query .
Third discuss with that fellow how he has organised table and index .
Has he run dbms_stat package and analyze table ?
Also check it with optimizer mode to first_rows .

There are so many things can impact on query performance .
From above one u might get some idea.
Jayesh

[chrisrlong]

Originally posted by jayeshdba
Hi,

First use parallel hint in ur query .
Second decides order of columns in where clause properly .

I would not suggest going blindly into a parallel query - read up on them, make sure you are set up properly for them and test, test, test.

As for the order of the columns - as long as you are using the CBO, that is irrelevant.

I would like to see the explain plan on this one. I would suggest putting all those nbr values into a temp table and joining to that temp table in the query - that will give the optimizer the best chance of making something out of that mess. All the ORs and INs are pretty much guaranteeing a table scan. I'm assuming, of course, that you have an index on nbr.

Finally... why??? Why in the world are you building a query like this? Where are these lists of numbers coming from? There's something that strikes me as very wrong about this query. I suspect that the process that leads up to you selecting on tons of lists with tons of entries is logically flawed and should be re-examined.

Just my .02

- Chris

[meghna]

The execution plan looks like this :

SELECT STATEMENT cost = 1119
SORT ORDER BY
TABLE ACCESS FULL CAR_HOPPER_DET

CAR_HOPPER_DET is the mail table.
There is a primary key index on the nbr field in the main table which does not get picked up while running query.
May be because here fields are getting selected from the view not from the mail table (not sure).