Data Blocks, Extents, Segments
§Data Blocks
•Overhead
Header: block addr, seg type
Table dir: tables having rows
Row dir: actual rows info
•Free space
•Row data
§PCTFREE, PCTUSED, Row Chaining
•PCTFREE: pct of block spc reseved for updates
•PCTUSED: before new rows can be inserted
•Row Chaining: a row spanning more than two data blocks
•Row Migrating: caused by update
§Extents
•Locally managed
-reduce data dictionry contention
-don’t care number of extents a segment acquires
-easier space management
-never fragment with uiniform policy
•Dictionary managed
Estimate orcle table size
•ANALYZE TABLE
•SELECT avg_row_len
FROM dba_tables
WHERE table_name=’
•rough estimate of table size
avg_row_len*number_of_rows*(1+PCTFREE/100)*1.15
Index
– 5% rule:
use idx when accessing < 5% of rows.
- concatenated indexes:
must include leading columns
-DBA_INDEXES
-DBA-IND_COLUMNS
-USER_INDEXES
-USER_IND_COLUMNS
Locally managed tablespace
•available since 8.1.5
•minimum extent size is 64k
•size default to 1m
•default to autoallocate
•ex1
create tablespace local_uniform_size datafile 'e:\ora816\db\local_tbs01.dbf' size 10m extent management local uniform size 10k;
•ex2
create tablespace local_uniform_size datafile 'e:\ora816\db\local_tbs01.dbf' size 10m extent management local autoallocate;
•check tablespace info
select substr(tablespace_name,1,20) Tablespace, initial_extent/1024 "I-Ex(kb)", next_extent/1024 "N-Ex(kb)", min_extlen, extent_management, allocation_type from dba_tablespaces where tablespace_name like 'LOC%';
•Migrate to lmt (available 8.1.6)
execute SYS.dbms_space_admin.tablespace_migrate_TO_local('SAMTABLESPACE');