Assignment 6 Exercise 1 2

ASSIGNMENT 6 EXERCISE 1 & 2 Stefan Lederer | 0761467

Exercise 1 - Physical Structure 

Explain the Oracle-concepts segment, extent, block. Interpret the following statement:

create table test ( a number (8), s varchar2 (1000) ) tablespace USERS storage ( initial 1M next 100K minextents 1 maxextents 100 pctincrease 50 );

Physical Structure 



Physical Design begins after the SQL tables have been defined and normalized. Goal is to maximize performance of the database. 

Performance is measured by query answer times, update times, etc.

Tablespace

physical file

segment

Tablespace 

 



A tablespace consists of at least one datafile located in the operating system. Default tablespace is SYSTEM Tablespace stores database objects like tables, indexes, … Creating of new tablespaces  CREATE

TABLESPACE DATAFILE < file_path> … [ Extent Management ]

Segment, Block, Extend 



Tablespaces are subdivided into segments Segments are containers for objects like tables, indexes, etc.

Block 



Data blocks are the smallest unit of data used by a database. Data block size is specified at database initialization 



Should be a multiple of the operating system‘s block size to avoid unnecessary I/O.

Two space management parameters, PCTFREE and PCTUSED, enable to control the use of free space for inserts and updates in all data blocks of a particular segment.  Parameters

can be specified when creating or altering table, index, …

PTFREE 

PCTFREE sets the minimum of a data block to be reserved as free space for possible updates to rows already existing in the block.

PCTUSED 

PCTUSED sets the minimum percentage of a block that can be used for row data before new rows are added to the block.

Extends 



An extent is a logical unit of database storage space consisting of a number of contiguous data blocks. Most CREATE commands contain an optional STORAGE clause.

With the help of the STORAGE clause one can specify… 

An initial size for the first extent 



This initial extent is created on CREATE TABLE / INDEX

The size of extents that are allocated if the initial extent is full  Size

of this extent is specified in the NEXT clause  Can be given in absolute or as relative factor (PCTINCREASE) 



A minimal / maximal number of extents that must be used for the given object can be specified with MINEXTENTS / MAXEXTENTS Ensure that each extent contains at least five database blocks.

PCTINCREASE 



PCTINCREASE (0 to 100 ) refers to the percentage by which each next extent (beginning with the third extend) will grow. The size of each subsequent extent is equal to the size of the previous extent plus this percentage increase.  PCTINCRESE

1: 100K, 100K, 101K, 102K …  PCTINCRESE 100: 100K, 100K, 200K, 400K ...

Four segment-classes 

Data-segments contain one of the following data A table that is not partitioned or clustered  A partition of a partitioned table  A cluster of tables 



Index-segments are used to store indexes  Setting

the storage parameters directly affects the efficiency of data retrieval and storage

Four segment-classes 

Temporary-segments: Temporary memory for  Creating

an index (sorting…)  Set operations (union, intersect, …)  Sub-queries 

Rollback-Segments  The

undo is stored in rollback segments.

create table test ( a number (8), s varchar2 (1000) ) tablespace USERS storage ( initial 1M next 100K minextents 1 maxextents 100 pctincrease 50 );

     

Table is created in tablespace USERS Initial Extend has the size 1 M 2nd Extend has 100 K 3rd Extend has 150 K … Minimum 1 Extend is created Maximum 100 Extends are allowed

Excercise 2 – Table Analysis  

create_part.sql Create Table PART with 20 000 Entries

Table Analyze 

ANALYZE TABLE part COMPUTE STATISTICS; OR



EXEC DBMS_STATS.GATHER_TABLE_STATS(‘db t_yxx’, ‘part’);

Why Analyze a Table? 

It is used by the CBO (Cost Based Optimizer)

…number of rows in this table 

Select num_rows from user_tables where table_name = ‚PART‘;

…average size of a record. 

Select avg_row_len from user_tables where table_name = ‚PART‘;

…number of distinct values for each column 

Select num_distinct, column_name from user_tab_columns where table_name = ‚PART‘;

…the density of each column Select density, column_name from user_tab_columns wehre table_name =‚PART‘;

by the way: what is the density? 

Density is a column statistic and provides selectivity estimates for equi-join predicates (e.g. and A.COL1 = B.COL1) and  equality predicates (e.g. COL1 = 5). 



The density is expressed as a decimal number between 0 and 1.  Values

close to 1 indicate that this column is unselective  Values close to 0 indicate that this column is highly selective

by the way: what is the density? 



The more selective a column, the less rows are likely to be returned by a query referencing this column in its predicate list. (Simple) Calculation:  Density

values

= 1 / Number of distinct NON null

The End 

Thank you for your attention!