|Lesson 10||Freespace in the data dictionary|
|Objective||Learn about freespace parameters from the data dictionary. |
Oracle Data Dictionary Freespace
As with all other attributes of database objects, you can find out more about the way your tables and indexes work with free space through several data dictionary views.
Oracle Database objects
USER_INDEXES data dictionary views have columns that store the values for the storage parameters
that apply to the amount of free space in their particular objects. The
USER_TABLES view contains columns for
PCT_USED, while the
USER_INDEXES view only has a column for the
Freelists are lists of free data blocks for a database table or index. The
USER_SEGMENTS view has information about the number of freelists allocated to a segment for an object, while the
USER_INDEXES views have information about the number of freelists allocated to their particular objects.
USER_INDEXES views also have columns for the
which give the number of blocks on the freelist for the object, and the
AVG_SPACE_FREELIST_BLOCKS, which give the average amount of space for each of the freelist blocks.
Determining if Foreign Key Columns are Indexed
If you are creating an application from scratch, it is fairly easy to create the code and ensure that each foreign key constraint
has a corresponding index. However, if you have inherited a database, it is prudent to check if the
columns are indexed. You can use data dictionary views
to verify if all columns of a foreign key constraint have a corresponding index. The basic idea is to check each foreign key constraint to see if there is a corresponding index. The task is not as simple as it might first seem.
For example, here is a query that gets you started in the right direction:
,NVL(c.column_name,'***Check index****') ind_column
FROM dba_constraints a
WHERE constraint_type = 'R'
AND a.owner = UPPER('&&user_name')
AND a.owner = b.owner
AND a.constraint_name = b.constraint_name
AND b.column_name = c.column_name(+)
AND b.table_name = c.table_name(+)
AND b.position = c.column_position(+)
ORDER BY tab_name, ind_column;
This query, while simple and easy to understand, does not correctly report on un-indexed foreign keys for all situations.
For example, in the case of multi-column foreign keys, it does not matter if the constraint is defined in a different order from the index columns, as long as the indexed columns are in the leading edge of the index. In other words, if the constraint is defined to be COL1 and COL2, then it is okay to have a B-tree index defined on leading edge COL2 and then COL1.
Another issue is that a B-tree index protects you from locking issues, but a bitmap index does not.
In this situation, the query should also check the index type. In these scenarios you will need a more sophisticated query to detect indexing issues related to foreign key columns.
Free Diction - Quiz
Click the Quiz link below to answer a few questions about free space.
Free Diction - Quiz
The next lesson is about the perils of fragmentation of extents within a tablespace.