Thanks for the question, abhishek.
Asked: March 15, 2012 - 9:46 am UTC
Last updated: March 15, 2012 - 9:50 am UTC
Version: 11g
Viewed 1000+ times
You Asked
Hi Tom,
I tried creating a Local INDEX on one of my Partioned Table, It failed due to TEMP Tablespace space issue. One of my team mate suggested using PARALLEL 10, which will help in 2 ways.
1. Speed up the creation process ( i agree)
2. Using Parallel will not use same amount of TEMP Tablespace, infact it will reduce the TEMP tablespace usage.
I tried creating it, and to my surprise, it worked.
My understanding was, TEMP Tablespace usage will remain same even if i use PARALLEL , it only speed up the create index.
Paralle really reduce the TEMP tablespace usage?
Syntax used:
CREATE INDEX "SCOTT"."IDX_HIST_SCP_CHK_DEF" ON "SCOTT"."HISTORY_SCOPE"
(
"CHECK_ID",
"DEFINING_ID"
)
TABLESPACE "SCOTT_INDEX"
parallel 10 local;
Regards
Abhi
and Tom said...
1) it might make it go faster, slower or not change a thing
2) it might make it use much more temp, the same temp or less temp, it depends
You did not demonstrate that it used more temp (you should have measured it), you just demonstrated that when you first ran it - there was insufficient FREE temp space (something else could have been using it) and when you ran it later there was sufficient free temp space (whatever was using it, wasn't using it anymore)
But - it could have reduced it as you would have 10 processes with 10 PGA sort areas - which possibly could have buffered the information where a single process with a single PGA sort area was not able to.
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment