You Asked
Tom:
i have 8 cpu. i am the only user.
create table p ( p int);
begin
for i in 1..1000000 loop
insert into p values (i);
end loop;
end;
/
set timing on
create index myindex on p(p);
Elapsed: 00:00:43.55
drop index myindex;
create index myindex on p(p) parallel(degree 4) nologging;
Elapsed: 00:00:53.19
why?
and Tom said...
once again, its in scaling UP with parallel stuff.
1,000,000 integers is tiny, small, nothing, not relevant.
It is NOT the number of rows, it is the volume of data.
There is overhead to parallel operations, in coordination, in distributing the work, in setting up the plan.
So, I loaded up your table p and discover that:
ops$tkyte@ORA817.US.ORACLE.COM> select count(*) from p;
COUNT(*)
----------
1000000
ops$tkyte@ORA817.US.ORACLE.COM> exec show_space( 'P' );
Free Blocks.............................1
Total Blocks............................1536
Total Bytes.............................12582912
Unused Blocks...........................24
Unused Bytes............................196608
Last Used Ext FileId....................7
Last Used Ext BlockId...................130761
Last Used Block.........................40
PL/SQL procedure successfully completed.
ops$tkyte@ORA817.US.ORACLE.COM> select (1536-24)*8/1024 from dual;
(1536-24)*8/1024
----------------
11.8125
It is all of 11meg in size. It took longer for us to setup the query plan for parallel four, get the PQ slaves going, getting everyone to work together -- then it would have just to "get the data, sort it and be done with it".
You want to test this? Load up 5 - 10 gig of data. then try it out. Parallel is for big, to test big -- you need "big".
Think of it this way. Suppose someone asked you to do something. You have two choices:
o do it yourself, just sit down and do it yourself from start to finish.
o get a bunch of people together, split up the work, assign the work, coordinate (merge) the results and be done with it.
Well, If I asked to you send an email to someone telling them how to drive to your company -- option 1 would be faster, better, easier.
If I asked you to build an HR system, option two would be faster, better, easier.
Rating
(3 ratings)
Is this answer out of date? If it is, please let us know via a Comment