4

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 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

Improve this question
4
  • What do you mean "all other columns are UNIQUE"? Each column is unique on its own (I assume not)? Or there is a unique index covering all the columns except pid? Or something else? If there are multi-column indexes, what is the column order in each index? The indexes are partitioned, is the table as well? What's the execution plan for your current query? Commented Jun 10, 2011 at 22:42
  • Can you post the actual execution plan? You can get it with DBMS_XPLAN. Commented Jun 11, 2011 at 0:53
  • Also, what version of Oracle are you using? Commented Jun 11, 2011 at 4:43
  • If you need to run this query for "tons of storeids" then you may need to rethink the architecture. If this query is inside a loop you should instead "push the query up" a level. That is, maybe get ALL storeids, and then iterate over the larger result set. Sure, the single query may seem to take a long time, but iterating over the results might go much faster. Commented Jun 11, 2011 at 20:27

5 Answers 5

Reset to default
5

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

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Even better, run the query then use DBMS_XPLAN to view the execution plan that was actually used.
1

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.

Improve this answer

Comments

1

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.

Improve this answer

Comments

0

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.

Improve this answer

3 Comments

charlie, 1989 called they want their implicit where join back.
@Johan: Transforming the join into the JOIN ON syntax is left as an exercise for the reader :)
This was the original query, it is much much slower.
0 
 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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.