Home » RDBMS Server » Performance Tuning » Parallel index with Non-partitioned Noparallel table (Oracle 10.2.0.4)
Parallel index with Non-partitioned Noparallel table [message #552876] Mon, 30 April 2012 00:12 Go to next message
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 #552887 is a reply to message #552876] Mon, 30 April 2012 01:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Does this 'parallelism' works on the index search in the query as well?


Yes it applies on all operations related to the table.

Quote:
In which situations non-parallel non-partitioned table but parallel index (degree>2) will help a query?


In all situations where you have enough IO and CPU to support it and enough blocks to handle so that it is cheaper to parallelize than to not.

Regards
Michel
Re: Parallel index with Non-partitioned Noparallel table [message #552900 is a reply to message #552887] Mon, 30 April 2012 02:08 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Michel Thanks for your reply

Unfortunately I am unable understand your reply

you said
Quote:
Yes it applies on all operations related to the table


does it include 'index access or index walk along brach/leaf blocks etc to get block address'?

And if this is done by the 'parallel' hint alone then which condition would demand making an index parallel on a non-partitioned nonparallel table?
I can't imagine sitautaion other than create index, rebuild index.

You have said
Quote:
In all situations where you have enough IO and CPU to support it and enough blocks to handle so that it is cheaper to parallelize than to not


This is what we shall do ideally. But it does not tells me which query condition would demand it (i.e. index with degree>1 on nonparallel non-partitioned table)

Thanks and Regards
Orapratap


Re: Parallel index with Non-partitioned Noparallel table [message #552913 is a reply to message #552900] Mon, 30 April 2012 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
does it include 'index access or index walk along brach/leaf blocks etc to get block address'?


"all" includes everything.

Quote:
But it does not tells me which query condition would demand it


General question, general answer.
No condition would demand it. Some conditions may take profit of it.

Parallel hint overrides parallel parameter of table or index and does not care about it.

Regards
Michel
Re: Parallel index with Non-partitioned Noparallel table [message #553264 is a reply to message #552913] Wed, 02 May 2012 23:17 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Thanks Michel

It's much clear now

Regards
Orapratap
Re: Parallel index with Non-partitioned Noparallel table [message #553508 is a reply to message #552887] Sat, 05 May 2012 20:31 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Michel Cadot wrote on Mon, 30 April 2012 16:10
Yes 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 Guide
You 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
Previous Topic: How we can find %age completion of query when is in running status
Next Topic: unexplained performance degradation of PL/SQL procedures
Goto Forum:
  


Current Time: Thu Mar 28 10:14:52 CDT 2024