Here is the output from the preceding example:
Used bytes: 589824
Allocated bytes: 589824
PL/SQL procedure successfully completed.
NOTE
You must use the set serveroutput on command to enable the script??™s
output to be displayed within a SQL*Plus session.
Estimating Space Requirements for Indexes
As of Oracle Database 10g, you can use the CREATE_INDEX_COST procedure of the DBMS_
SPACE package to estimate the space required by an index. The procedure determines the space
required for a table based on attributes such as the tablespace storage parameters, the tablespace
block size, the number of rows, and the average row length. The procedure is valid for both
dictionary-managed and locally managed tablespaces.
For index space estimations, the input variables include the DDL commands executed to
create the index and the name of the local plan table (if one exists). The index space estimates
rely on the statistics for the related table. You should be sure those statistics are correct before
starting the space-estimation process; otherwise, the results will be skewed.
The variables for the CREATE_INDEX_COST procedure are described in the following table:
Parameter Description
ddl The create index command
used_bytes The number of bytes used by the index??™s data
alloc_bytes The number of bytes allocated for the index??™s extents
plan_table The plan table to use (the default is NULL)
Because the CREATE_INDEX_COST procedure bases its results on the table??™s statistics, you
cannot use this procedure until the table has been created, loaded, and analyzed.
Pages:
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270