Parallel index with Non-partitioned Noparallel table [message #552876] |
Mon, 30 April 2012 00:12 |
|
orapratap
Messages: 134 Registered: November 2011 Location: Canada
|
Senior Member |
|
|
Hello
If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint
Does this 'parallelism' works on the index search in the query as well?
In which situations non-parallel non-partitioned table but parallel index (degree>2) will help a query?
Thanks and Regards
Orapratap
|
|
|
|
|
|
|
Re: Parallel index with Non-partitioned Noparallel table [message #553508 is a reply to message #552887] |
Sat, 05 May 2012 20:31 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
Michel Cadot wrote on Mon, 30 April 2012 16:10Yes it applies on all operations related to the table.
Just to be clear, the Oracle 10g Data Warehousing Guide tells us that:
Oracle 10.2 Data Warehousing GuideYou can use parallel execution for any of the following:
Access methods
- Some examples are table scans, index full scans, and partitioned index range scans.
...
So, since the index you are talking about is Partitioned, your index range scans can be partitioned. But this would not be the case with a Non-Partitioned index range scan.
Further, the degree of parallelism may not be optimal. The degree will probably be either the number of partitions or a factor of that number.
Further still, if the partitions are skewed in size, the workload will not be evenly distributed, resulting in even more sub-optimal execution.
What I am saying is that you may not be able to expect the same sort of improvements that you get from Full Table Scans (where Oracle can optimise the degree and evenly allocate blocks).
Ross Leishman
|
|
|