1

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.

Improve this question
4
  • 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. Commented Aug 6, 2013 at 21:43
  • @a_horse_with_no_name because the query is generated and the condition might be more complex... Commented Aug 6, 2013 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 Commented Aug 6, 2013 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. Commented Aug 6, 2013 at 22:40

2 Answers 2

Reset to default
1

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.

Improve this answer
2
  • Always reasonable thing to try but of course doesn't help here, all tables are fully analyzed. Commented Aug 6, 2013 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. Commented Aug 7, 2013 at 3:31
0

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.

Improve this answer
3
  • A join is not a replacement for a sub-select and might return a completely different reesult. Commented Aug 6, 2013 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). Commented Aug 7, 2013 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. Commented Aug 7, 2013 at 20:29

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.