Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My query is:

SELECT category, count(*)
FROM large_data_table t
WHERE 
(t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 1))
OR
(t.val IN (SELECT val FROM SMALL_TABLE WHERE id = 2))
GROUP BY GROUPING SETS ((category), ());

The resulting execution plan is like this:

SELECT STATEMENT
  SORT (GROUP BY ROLLUP)
    FILTER
      large_data_table  TABLE ACCESS (FULL)
      SMALL_TABLE  TABLE ACCESS (BY INDEX ROWID)
      SMALL_TABLE  INDEX (RANGE SCAN)
      SMALL_TABLE  TABLE ACCESS (BY INDEX ROWID)
      SMALL_TABLE  INDEX (RANGE SCAN)

Where FILTER is, essentially, NESTED LOOP JOIN which never finishes as NESTED LOOPS often do on large tables. (the two subqueries on the small table return, say, 100 rows each). I am looking for a way to tell Oracle to NOT use the stupid FILTER here. I've tried sticking USE_HASH hint everywhere including some places I'd rather not talk about now.... GROUPING SETS seem to play a role in this, but getting rid of them is very difficult here. The one reasonable thing that works is to replace OR with a UNION, but it doesn't cover all cases either.

share|improve this question
    
Why do you use two sub-selects? WHERE t.val IN (SELECT val FROM SMALL_TABLE WHERE id IN (1,2)) should return the same thing. –  a_horse_with_no_name Aug 6 '13 at 21:43
    
@a_horse_with_no_name because the query is generated and the condition might be more complex... –  MK01 Aug 6 '13 at 21:57
    
If you can't get the generator to write better queries, you are left to tuning indexes. Try an index on large_table(val, category) that should result in an index scan and an index on small_table (id, val) will probably help as well –  a_horse_with_no_name Aug 6 '13 at 22:06
    
@a_horse_with_no_name yeah, only problem being that the category can be one of the hundreds of columns :) Sounds like the query generator will be changing. –  MK01 Aug 6 '13 at 22:40
add comment

2 Answers

On 11R2 I usually start with gathering more statistics for the tables:

exec DBMS_STATS.GATHER_TABLE_STATS ('SCHEMA, 'SMALL_TABLE', estimate_percent => '100');
exec DBMS_STATS.GATHER_TABLE_STATS ('SCHEMA, 'LARGE_DATA_TABLE', estimate_percent => '10');

If that helps then table preferences for automatic jobs can be set:

EXEC DBMS_STATS.SET_TABLE_PREFS ('SCHEMA', 'SMALL_TABLE', 'ESTIMATE_PERCENT', '100');
EXEC DBMS_STATS.SET_TABLE_PREFS ('SCHEMA', 'LARGE_DATA_TABLE', 'ESTIMATE_PERCENT', '10');

If large_data_table is really big (tens GB and more) then 1% or something like that may be needed.

And do not believe that in dba_tables sample_size=num_rows. For big tables actual auto sample size is much much lower. I had the SR with Oracle about that. They found actual sample percentage only from session trace file. It was ~0.004% for 170GB table.

share|improve this answer
    
Always reasonable thing to try but of course doesn't help here, all tables are fully analyzed. –  MK01 Aug 6 '13 at 19:58
    
Well like I wrote - auto analyze gathers statistics from relatively small sample. You can try to gather from much bigger percentage. Also if you have Enterprise Manager with Tuning pack you can use SQL Monitoring to check where actual execution differs from optimizer estimations. and where exactly time is spent. –  Mindaugas Riauba Aug 7 '13 at 3:31
add comment

I'd try switching to a join syntax rather than nested queries. You are probably being screwed by the fact that you have two nested queries in your filter predicates and the optimizer doesn't get a fair estimate of the number of "val"s that will be matched in the large_data_table. You might even be able to avoid the full table scan.

Perhaps:

SELECT category, count(*)
FROM large_data_table t
JOIN SMALL_TABLE s on t.val=s.val
WHERE s.id in (1, 2)
group by category;

I am unfamiliar with the "grouping sets" syntax/use, so am unsure of its use/need here.

share|improve this answer
    
A join is not a replacement for a sub-select and might return a completely different reesult. –  a_horse_with_no_name Aug 6 '13 at 21:44
    
If val uniquely identifies a row in SMALL_TABLE then it would produce the same result. It's only different if the join could produce multiple rows in SMALL_TABLE (which would cause multiple rows of BIG_TABLE to be returned back). –  sehrope Aug 7 '13 at 0:09
    
I see what @sehrope is saying. And since the sub-queries aren't requesting a distinct list of 'val' from small_table it could be "reasonable" (even if not absolute) to assume that the value isn't duplicated. –  Brian Efting Aug 7 '13 at 20:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.