Optimizing Oracle SQL Query

Hello I have a SQL query that I'm trying to optimize. This query here completes in 0.3 seconds, but I need to run the same query for tons开发者_Python百科 of different storeIds -- is there anyway to optimize this query to make it go faster, or change it so it gets all the storeIds at once.

I can always create a new command in C# that concats to the command to make it a union of a tons of different queries.

select /*+ PUSH_SUBQ */ *  
from mytable r
where r.s in (1, 7)
and r.d in (1, 75)
and r.storeid = 1162
and r.period = 20110528
and r.pid in (select /*+ no_unnest qb_name(subq1) */ 
productid from otherTable where itmid=9999)

I've already tried something like this but it takes forever.

select  /*+ PUSH_SUBQ */ * 
    from mytable r
    where r.s in (1, 7)
    and r.d in (1, 75)
    and r.storeid in (1162, 1223, 1231, 51231, 231, ...)
    and r.period = 20110528
    and r.pid in (select /*+ no_unnest qb_name(subq1) */ 
    productid from otherTable where itmid=9999)

MyTable has indexes like this: pid is NON-UNIQUE, PARTITIONED, NO JOIN_INDEX all others columns are UNIQUE, PARTITIONED, NO JOIN_INDEX

Try running an Oracle EXPLAIN PLAN on your query. It should highlight issue areas and may help to narrow bottlenecks, either in the query itself or the tables being queried.

Using EXPLAIN PLAN: http://download.oracle.com/docs/cd/B10500_01/server.920/a96533/ex_plan.htm

If you need this to run for many different stores, you would presumably either specify a list of different StoreID values, i.e.

select *  
  from mytable r
 where r.s in (1, 7)
   and r.d in (1, 75)
   and r.storeid IN( 1162, 1163, 1164, ... )
   and r.period = 20110528
   and r.pid in (select productid 
                   from otherTable 
                  where itmid=9999)

If you want to optimize the query's performance, you'll need to provide the structure of the two tables (MyTable and OtherTable), let us know what indexes exist on the two tables, and give us some idea about the cardinality of the various conditions.

I'd be rather concerned about the presence of hints (particularly the PUSH_SUBQ hint that is going to be ignored because it's in the wrong place. While it may very rarely be appropriate and necessary to add explicit hints to queries, it is almost always the case that when Oracle generates a poor query plan the underlying statistics are misleading the optimizer. If that's the case, you're far better off fixing the statistics than in hinting the query.

Without seeing the execution plan and the actual indexing/partitioning details, I have no idea what to suggest to make the execution of the query faster. However, it seems in this case parse time might be significant. Are you really using literal values for all the conditions as shown in your sample? You should use bind variables; otherwise, you are parsing each individual query, and this not only takes time but forces a bottleneck.

You might want to run an extended SQL trace and either read through the trace manually or run it through a profiler.

Changing the query to get "all the storeids at once" is simple -- remove the condition on storeid entirely. If you actually need the results for every possible storeid, you are quite likely wasting a lot of time revisiting blocks over and over by running one query for each. But maybe by "all the storeids" you meant all of some small set of ids.

My guess would be that using a join instead of the subquery could help. Also select only the columns you will be using:

select r.d, r.storeid, r.period, r.pid /* select only columns you need */
from mytable r, otherTable o
where r.s in (1, 7)
and r.d in (1, 75)
and r.storeid = 1162
and r.period = 20110528
and r.pid = o.productid /* use a join instead of subquery */
and o.itmid=9999

Measure it and see.

 select r.*  
  from mytable r 
   inner join otherTable o on r.pid = o.productid and o.itmid = 9999
  where r.s in (1, 7)
   and r.d in (1, 75)
   and r.storeid IN( 1162, 1163, 1164, ... )
   and r.period = 20110528

Try this query it must take much lesser time.