Optimization of Oracle SQL Query

Question

Hello, I have an SQL query that I am trying to optimize. This request here completes in 0.3 seconds, but I need to run the same request for several different repositories. In any case, in order to optimize this request so that it accelerates or changes it, it immediately receives all the repositories.

I can always create a new team in C # that integrates with the team to make it a union of many different requests.

select /*+ PUSH_SUBQ */ * from mytable r where rs in (1, 7) and rd 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 already tried something like this, but it lasts forever.

 select /*+ PUSH_SUBQ */ * from mytable r where rs in (1, 7) and rd 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 these indices: pid NOT UNIQUE, PLACED, NO JOIN_INDEX all other columns UNIQUE, PARTITIONED, NO JOIN_INDEX

Answer 1

Try running Oracle EXPLAIN PLAN for your request. It should highlight problem areas and can help reduce bottlenecks either in the query itself or in table queries.

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

Answer 2

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

 select * from mytable r where rs in (1, 7) and rd 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 query performance, you need to provide the structure of two tables (MyTable and OtherTable), tell us which indexes exist in the two tables, and give us some idea about the power of the various conditions.

I would rather be bothered by the presence of prompts (in particular, the PUSH_SUBQ hint, which will be ignored because it is not in the right place. Although it is very rarely advisable and necessary to add explicit prompts to queries, this is almost always the case when Oracle creates a bad query plan, basic statistics are confusing to the optimizer, and if so, it’s much better to record statistics than to hint at a query.

Answer 3

Without seeing the execution plan and the actual indexing / splitting data, I don’t know what to offer in order to speed up the execution of the request. However, it seems that in this case, the analysis time can be significant. Are you really using literal values ​​for all conditions, as shown in your example? You must use bind variables; otherwise, you analyze each individual request, and this not only takes time, but also creates a bottleneck.

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

Changing the request to get "all repositories at once" is simple - completely remove the condition on storeid. If you really need results for every possible storage, you are likely to spend a lot of time reusing blocks again and again, running one query for each. But maybe "all repositories" meant the whole small set of identifiers.

Answer 4

I guess using a connection instead of a subquery might help. Also select only those columns that you will use:

 select rd, r.storeid, r.period, r.pid /* select only columns you need */ from mytable r, otherTable o where rs in (1, 7) and rd 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 look.

Answer 5

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

Try this request, it will take much less time.