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
The USER_TABLES
and 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_FREE
and PCT_USED
, while the USER_INDEXES
view only has a column for the PCT_FREE
value.
Freelists
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_TABLES
and USER_INDEXES
views have information about the number of freelists allocated to their particular objects.
The USER_TABLES
and USER_INDEXES
views also have columns for the NUM_FREELIST_BLOCKS
,
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
foreign key 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:
SELECT DISTINCT
a.owner owner
,a.constraint_name cons_name
,a.table_name tab_name
,b.column_name cons_column
,NVL(c.column_name,'***Check index****') ind_column
FROM dba_constraints a
,dba_cons_columns b
,dba_ind_columns c
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.