hi,
i have a database with SHEAPTHRES_SHR set to 50000 and SORTHEAP set to 5000.
For the database 200MB of sort space in total for database and 20 MB per sort operation.
I have a table with 1.8GB of data approx. [db2 "CREATE TABLE STAFF_EXCEPTIONS(STAFFID INTEGER NOT NULL,NAME VARCHAR(50 OCTETS),SALARY INTEGER ,INC INTEGER,NETSAL INTEGER)"]
Scenario 1:
db2 +c "alter table staff activate not logged initially"
db2 +c "create index myidx on staff(staffid)"
Monitor shared sort heap consumption is 5000 pages top. it reaches around 19.6 MB
Total number of sort is only one.
db2 drop myidx
db2 reset monitor all
Scenario 2:
db2 "select staffid from staff order by staffid"
Monitor shared sort heap consumption is 10000 pages top. it reaches around 40 MB
Total number of sort is only one.
why select query consumes twice the amount of sort memory? where as create index also uses sort memory but only consumes half of that.
i performed the same test case in 9.7, in both scenarios it takes only 5000 pages of sort memory. man this is bad because it takes same time to complete the task but double the memory.
thanks,
Harish P
------------------------------
Harishkumar Pathangay
------------------------------
#Db2