How do I parallelise a simple SELECT query?

Question

Say I have the classic:

select * from employees where dob < to_date('10/10/1985', 'DD/MM/YYYY');

The table is huge, so I want to parallelise this query.

Going by what I can see here:

http://docs.oracle.com/cd/B10500_01/server.920/a96524/c20paral.htm#13255

Essentially what we're wanting to do is arbitarily chop the table into n parts, and run our select statement on each chunk on a different thread, then join them together at the end.

Is parallelisation appropriate here?
How would I write the query?

You would just put an index on `dob` and let the engine figure out how to run the query. — Gordon Linoff, Jun 19 '14 at 02:54
@GordonLinoff Is an index essential for parallel processing? — dwjohnston, Jun 19 '14 at 02:55
No. But it is the most reasonable way to improve performance. — Gordon Linoff, Jun 19 '14 at 03:03
@GordonLinoff Sure. But given that I don't have admin privileges, that option's out. — dwjohnston, Jun 19 '14 at 03:05
Just a side issue, with long running parallel query, you might use up lot of resources, making the db slower. — Maheswaran Ravisankar, Jun 19 '14 at 03:56

score 4 · Answer 1 · edited May 23 '17 at 11:57

4

Try this:

select /*+ PARALLEL(4) */ * from employees 
where dob < to_date('10/10/1985', 'DD/MM/YYYY');

See more from Oracle Hint.

See also this answer to see why PARALLEL did not applied on your SQL statement.

edited May 23 '17 at 11:57

Community

1
1

answered Jun 19 '14 at 03:20

MinhD

1,790
11
14

Thanks Minh. This is what I thought the correct solution is. The trouble I'm having at the moment is ascertaining whether it's actually giving me an improvement. – dwjohnston Jun 19 '14 at 03:24
Just need to try. It's the only way for performance tuning :) – MinhD Jun 19 '14 at 03:27
When you say a full scan, do you mean a full scan by a single thread? or does breaking the table into chunks and having a full scan on each chunk count? – dwjohnston Jun 19 '14 at 03:59
It means when you EXPLAIN_PLAN the sql, you have a "TABLE ACCESS FULL" plan. This is simple to try, just run the SQL and see if it is improved? – MinhD Jun 19 '14 at 04:01
The difficulty of course is: I can't see an explain plan on this table. And the performance of the DB is pretty sporadic, so simply running the query, it's ahrd to tell if it's a genuine improvement. But yeah, thanks for you help, it's stuff I can take back to email the DBAs with. – dwjohnston Jun 19 '14 at 04:24
1

You CAN see an explain plan on SQL: run `EXPLAIN PLAN select /*+ PARALLEL(4) */ * from employees .......` – MinhD Jun 19 '14 at 04:26
1

and `select * from table(dbms_xplan.display)` will query the plan table, after running `EXPLAIN PLAN`... `set pages 0` and `set linesize 4000` in `sqlplus` would make it read better. – Maheswaran Ravisankar Jun 19 '14 at 04:35
The section of the manual you quoted is almost completely wrong - see my answer to a very similar question [here](http://stackoverflow.com/q/9728558/409172). – Jon Heller Jun 19 '14 at 22:55

How do I parallelise a simple SELECT query?

1 Answers1

Linked