The following
example estimates the space required for a new index on the BOOKSHELF table. The tablespace
designation is part of the create index command passed to the CREATE_INDEX_COST procedure
as part of the ddl variable value.
Chapter 5: Developing and Implementing Applications 147
declare
calc_used_bytes NUMBER;
calc_alloc_bytes NUMBER;
begin
DBMS_SPACE.CREATE_INDEX_COST (
ddl => 'create index EMP_FN on EMPLOYEES '||
'(FIRST_NAME) tablespace USERS',
used_bytes => calc_used_bytes,
alloc_bytes => calc_alloc_bytes
);
DBMS_OUTPUT.PUT_LINE('Used bytes = '||calc_used_bytes);
DBMS_OUTPUT.PUT_LINE('Allocated bytes = '||calc_alloc_bytes);
end;
/
The output of the script will show the used and allocated bytes values for the proposed index
for the employee??™s first name:
Used bytes = 749
Allocated bytes = 65536
PL/SQL procedure successfully completed.
Estimating the Proper Value for pctfree
The pctfree value represents the percentage of each data block that is reserved as free space. This
space is used when a row that has already been stored in that data block grows in length, either by
updates of previously NULL fields or by updates of existing values to longer values. The size of a
row can increase (and therefore move the row within a block) during an update when a NUMBER
column increases its precision or a VARCHAR2 column increases in length.
There is no single value for pctfree that will be adequate for all tables in all databases.
Pages:
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271