Physical DB Design: Relational Databases

Physical Database Design in Relational Databases

• Performance constraints on query invocation times, aiming for under 20 seconds, guide access path attribute priorities
• Time-constrained queries and transactions elevate the priority of selection attributes for primary access structures

Analyzing Update Operation Frequencies

• A minimum number of access paths should be set for frequently updated files
• Updating access paths can slow down update operations

Uniqueness Constraints on Attributes

• Access paths should be specified for candidate key attributes or sets of attributes to guarantee uniqueness
• Indexes can efficiently check uniqueness constraints, using only the index's leaf nodes

Physical Database Design Decisions

• Relational systems represent each base relation as a physical database file
• Access path options include specifying file types and defining attributes for indexes.
• At most one index can be primary or clustered per file
• Multiple secondary indexes are allowed

Indexing Design Decisions

• Attributes used in equality or range conditions and those used in join conditions or keys require access paths
• Query performance hinges on indexes or hashing schemes for selections and joins

Indexing Categories

• Whether to index an attribute is based on that attributes use such as it being a key, or query uses to satisfy selections and joins is present
• Indexes can be used without retrieving full records by scanning indexes

Attributes to Index

• Indexes can be made on one or many attributes.
• Multiple attributes within a relation in several queries warrant a multiattribute index, e.g., (garment_style, color)
• The sequencing of attributes has to correspond to the queries

Clustered Index Setup

• One primary/clustering index is allowed per table, physically ordering the file by that attribute
• CLUSTER keyword specifies this in most RDBMSs
• Primary indexes are created, for key attributes; clustering indexes, for non-key attributes
• The decision of the attribute best benefiting from ordering depends on if keeping the table ordered on the attribute
• Range queries get help from clustering
• Only index searches mean the corresponding index should not be clustered, because the main effect of clustering is achieved through record retrieval

Hash vs Tree Index

• RDBMSs generally use B+-trees for indexing, although ISAM and hash indexes are also available in some systems
• B+-trees support both equality/range queries
• Hash indexes excel in equality conditions, especially during joins to find matches

Dynamic Hashing

• Dynamic hashing is suitable for volatile files that grow and shrink continuously
• Dynamic hashing schemes are not offered by most commercial RDBMSs

Speeding Up Queries

• Denormalization separates logically related attributes into tables to reduce redundancy
• Denormalization can improve query execution speed

Tradeoffs for Execution Speed

• Faster execution is traded with normalization such that frequent queries and transactions can occur.
• Logical design is stored in a weaker normal form, e.g., 2NF or 1NF, instead of BCNF or 4NF
• Attributes needed for answering queries/reports are added to a table, avoiding joins, but reintroducing functional/transitive dependencies and redundancy issues

Denormalization

• Storing extra tables maintains original functional dependencies lost during the decomposition of relations

Database Tuning

• Post deployment, actual database use reveals problem areas unnoticed during initial design
• Revision of inputs from Section 16.1.1. involves the gathering of statistics about usage patterns, and the monitoring of internal DBMS processing, such as, query optimizations
• Bottlenecks involving data or device contention are revealed
• Volumes of activity and data sizes are estimated
• Constant monitoring and revision of the physical design is maintained
• Tuning attempts to reduce query/transaction response times and improve overall throughput

Factors used by the DBMS

• Statistics related to factors are an input to the tuning process, including:
• Sizes of individual tables
• Number of distinct values in a column
• Frequency of query/transaction submission in a time interval
• Times for different query/transaction processing phases

Utilization

• Other statistics create a profile of the data contents, use of the database and include include information by monitoring
• Storage statistics: Data about allocation of storage into tablespaces, indexspaces, and buffer ports
• Total read/write activity (paging) on disk extents and disk hot spots for I/O and device performance
• Query/transaction processing statistics: Execution times of queries and transactions, optimization times during query optimization
• Locking/logging related statistics: Rates of issuing different types of locks, transaction throughput rates, and log records activity
• Index statistics: Number of levels in an index, number of noncontiguous leaf pages, etc

Tuning

• Tuning transactions, concurrency control, and recovery involve:
  • Avoiding excessive lock contention to increase concurrency
  • Minimizing logging overhead and unnecessary data dumping
  • Optimizing buffering and scheduling of processes
  • Allocating resources for efficient utilization of disks, RAM, and processes
• Solutions are tailored to specific systems by the trained DBAs who tune physical DBMS parameters and configurations of devices and operating systems

Tuning Indexes

• Index choices can be revised if:
  • Queries take too long because of missing indexes
  • Indexes are not being utilized
  • Indexes cause excessive overhead because of frequent attribute changes
• DBAs can analyze query execution plans using DBMS commands or trace facilities to understand the utilization of operations

Tuning Goals

• Tuning dynamically evaluates the requirements and attempts indexes reorganization
• Dropping and building indexes can have heavy cost but improvements can be huge

Indexing Costs

• Table updating generally stops while an index is dropped or created; the loss must be accounted for
• Besides dropping or creating indexes and changing from a nonclustered to a clustered index and vice versa, index rebuilding may improve performance.
• Many deletions on the index key can have pages containing wasted space, which may be claimed during a rebuild operation while insertions may cause too many overflows

Clustered Tuning

• Rebuilding a clustered index rearranges the entire table ordered on that key

Options

• Option availability of indexing and its reorganization vary
• Sparse indexes have one index pointer for each page (disk block) in the data file, while dense indexes have an index pointer for each record.
• Some indexes can be sparse indexes with clustering index with B+-trees while others indexes can be sparse with a clustering index using an ISAM file and dense indexes as B+-trees
• In some versions, setting up a clustering index is limited to a dense index, the DBA must work with this limitation

Database Designs

• A possible denormalization is departure from keeping every table a BCNF relation. If a specific design has bad results, it could result in a logical database needing adjustments and remapping of new tables and indexes

Data Requirement

• A database needs to be driven by processing requirements along with data need
• Dynamic processing requirements necessitate conceptual, logical, and physical schema adjustments

Physical design

• Several processes may occur depending on processing requirements:
• Due to the frequency of attributes form two or more tables are brought together, existing tables may be denormalized, reducing the normalization level from BCNF to 3NF, 2NF, or 1NF
• 3NF and 2NF address different problem dependencies that do not relate to each other, that is why normalization address these independently
• For table sets, there may be many possible design choices, which may have one replaced

Common place adjustments

• For attributes R(K,A,B,C,D,...), BCNF can be split into multiple tables also in BCNF
• Example is R1(K,A,B), R2(K,C,D), R3(K...), in each table for accessing.
• Tables with lots of columns may want to vertically partition attributes for access.
• Repeating of attributes in other tables even though potential anomalies may occur
• The main table guarantees updated data, is up-to-date

Data Control

• Slicing takes a table vertically like partitioning.
• Each table in the diagram contains the set of distinct products/tuples in which a specific query or set of transactions apply.
• These changes can be used to meet specific requirements

Bad Queries

• Inappropriate index selection causes bad query performance because commands produce

1. Too many disk accesses occur such as queries scanning all
2. Showing appropriate indexes are not being accessed

Optimizers

• Common issues seen in optimizing can be

1. Optimizers won't use arithmetic
2. Null comparisons aren't easy to optimize
3. Unnecessary distinct operation lead to sorts

Correlated queries

• Bad practice can arise searching the inner table for each outer tuple.
• You can use temporary queries and tables to make things more efficient
• In join conditions use clustered indexes over string comparisons

Clause Order

• Some optimizers are greatly affected with clause order
• Switch this to look at the smaller scanner used by the larger one
• Perform bad in these conditions
• Uncorrelated/Correlated with aggregate/no aggregate subqueries However, the first one above doesn't seem to be a problem that query optimizer evaluate

Additional Query Tuning Guidelines

• Avoid using OR operator as this could prompt optimizer to use indexes; query be split into a union

SQL commands

• Following transformations help:
• Not transforms to positive
• Embedding can lead to join

Rewrites

• Rewrite WHERE to utilise indexes. The syntax to rewrite SQL statements is SELECT REGION#, PROD_TYPE, MONTH, SALES FROM SALES_STATISTICS WHERE REGION# = 3 AND ((PRODUCT_TYPE BETWEEN 1 AND 3) OR (PRODUCT_ TYPE BETWEEN 8 AND 10));

Improvements

• Actions to improve the inefficient queries are by such as the use of temp tables, view and certain avoidance operations
• Techniques will depend upon optimzer

Introduction to Transaction Processing

• Techniques used involves by the concurrency control, and the recovery, The transaction processing includes in some basic concepts of these transaction
• Concurrent execution of transactions and recovery from transaction failures
• Single-user and multiuser database systems and demonstrates how concurrent execution of transactions can take place
• Concept of transaction, read and write operations on basic model

Multi users

• Multi users access computer same time involves in MULTI programming

Interleaving

• I/O operations keeps CPU busy , then prevent the long process
• More CPU involves in the process

Granularity

• Database models and functions which the size of data describes and has more concepts to cover