On 5ÔÂ9ÈÕ, ÏÂÎç11ʱ50·Ö, mirof007 <mirof...@gmail.comwrote:
rawu, what version of DB2 are you using? I believe prior to DB2 9
there was a limitation in the number of rows DB2 for LUW could store
on a single data page, I believe the limit was 256. Creating an index
requires a sort of the data, which will likely spill to the temporary
tablespace, given your data volume. Because your row width is so
narrow, you might end up with not fully populated temp pages when sort
is spilling. What is your temporary tablespace page size? If you can't
add more space to the tabelspace containers, then you could try using
a smaller page size for your temp.
Hope this helps,
Miro
thx .. my DB version is V8.5
I have another try yesterday ..
Given the whole data of this table cost about 7G of tablespace
I enlarge the temporary tablespaces size to 8G .
And this time , i create the index successfully .
And I get that , the peak consumption of temporary tablespaces is
67% .
That means it requires temp tablepace size up to 1:1 of data of table
to create index .
And I guess , db2 runs like that : copy all columes rather than the
columes included by index to temporary tablespace , and then sort ,
and then copy data to index tablespace . ...
So .. i need size as much as the data table .
So .. no matter how many columns index includes , it requires the same
size of temporary tablespace ..
Am I right ??