Oracle database is a relational DBMS that has extended the relational model to an object-relational model, to store business models in an RDBMS. Database Schema: Database schema is a collection of logical data structures Table: It represents the real-world entity and can have integrity constraints on columns Index: Data Access: Structured Query Language (SQL): Oracle RDBMS uses PL/SQL which is an extension which helps store application logic in the DB itself Transaction Management: Oracle RDBMS supports multiuser concurrency. Transaction: Data Concurrency: Oracle RDBMS enforces a statement-level and transaction-level read consistency. This is done to avoid the dirty read problem and based on the level of consistency, DBMS guarantees data returning from the single or multiple queries is consistent and committed. Oracle RDBMS is a set of physical structures like files and applications inside a single physical database can interact with multiple logical databases.
Oracle RDBMS has multitenant architecture, Sharding Architecture: Partition horizontally across multiple physical Oracle RDBMS. Useful for OLTP applications. After sharding, every database(shard) has a dedicated server and resources - CPU, flash, disk and memory and together they make up a single logical database.
Database Storage Structures : Physical Storage Structures: Files storing data on the disk Data files: Oracle RDBMS stores data for logical database structures on physical files Control files: Contains metadata about the physical structure of the database like file locations Online redo log files: consists of redo entries that record changes made to data Local Storage Structures : Data blocks: it represents the number of bytes on disk Extent: number of continuous logical data blocks in a single allocation Segments: number of extents allocated for an object like table or index Tablespaces: a logical container for segments Oracle RDBMS Processes: Client processes, background processes, server processes
In 1977, Larry Ellison, Robert Miner, and Ed Oates founded Software Development Laboratories, which was hired by the United States Central Intelligence Agency (CIA) in order to write a new database system based upon SQL. This system came to be known as Oracle. The company changed its name to Relational Software, Inc. in 1979 and then to Oracle Systems Corporation in 1982.
Oracle follows the industry-accepted standards for SQL. Oracle SQL has many extensions to standard SQL language to provide additional statements. Oracle RDBMS processes SQL statements using a query optimizer which generates execution plans based on access paths and statistics.
Optimization The optimizer generates most of the possible ways to process a query and assigns a cost to each step and finally takes the plan with the lowest cost. The main components are • Query Transformer: changes the form of a query to generate the execution plan • Estimator: estimates the cost of a particular execution plan • Plan Generator: Generates different possible plans, sub plans for nested queries. It uses an adaptive query optimizations feature which changes the plans based on the statistics collected during the statement execution. This optimization uses a dynamic programming plan. Optimizer statistics describe the details of data storage and distribution. It includes table statistics, column statistics, index statistics, system statistics.
Oracle employs MVCC for concurrency control. Data Concurrency and Consistency Oracle uses serializability for transaction isolation. It maintains data consistency by having a multi-version consistency model and the usage of locks and transactions. Oracle can prevent dirty reads, non-repeatable reads, phantom reads as it provides: read committed: The default isolation level where query executed by transaction sees the data committed before the given query and thus avoids reading the commits happening while in the transaction. Even though it provides the read consistency(row, when reread, is the same as before if not committed by other transactions), there is a conflicting write issue when two concurrent transactions try to modify the same item.
serializable isolation: In this, the transaction views the changes committed before the transaction began and not just before the given query. So any commits to the item by another concurrent transaction say t2 is not reflected in the reread of the item by t1 implying read consistency. Oracle allows modifying a row if the changes made to it by another transaction are already committed before this transaction started. Otherwise, it generates an error read-only isolation level: Similar to serializable isolation but data is not permitted to be modified in the transaction. Read consistency is achieved by reconstructing the data from the undo statements.
Locking Mechanism To prevent the incorrect updates of shared data between the concurrent transactions. Oracle has two types of locks (shared and exclusive) that provide data consistency, concurrency, and integrity. Oracle rules for taking locks :
• When in the process of modifying a row, it is locked • When a row is being written by writer A, it blocks the concurrent writer B on the same row. • A reader can never block a writer. • A writer can never block a reader. Oracle automatically uses the lowest application-level lock to provide less restriction so more data is available to be accessed by others. Oracle also allows performs the lock conversions in order to maintain the consistencies. It never escalates locks, which is done to promote the lock level to a higher level when most of the locks are at a given granularity level to decrease the number of locks. Never escalation is done to avoid the probability of deadlocks. Also, locks are automatically released when transaction no longer needs them. In the case of deadlocks, oracle resolves them by rolling back the statements involved in the deadlock and releasing locks.
Types of locks provided by oracle : DML Locks: Row Locks (TX) Table Locks(TM) : Row Share (RS) Row Exclusive Table Lock (RX) Share Table Lock (S) Share Row Exclusive Table Lock (SRX) Exclusive Table Lock (X) DDL Locks System Locks Latches Mutexes Internal Locks
Oracle supports isolation levels of read committed as well as serializable, defaulting with the latter. There is an additional mode available, "read only", which is not part of the SQL standard.
Stored procedures are supported by Oracle.
The additional data structure which is associated with the table and table cluster to speed up data access(rows). Primary keys and unique keys (keys: expression or the set of columns on which index is built), already have indexes
Oracle provides a composite index that is on multiple columns in a given table. Oracle also provides multiple indexes on the same table provided different index types or different partition schemes or different uniqueness properties.
Oracle provides B+ Tree indexing and various others. RDBMS automatically reflects the indexes, the data changes made to the tables. Index Scan: DB retrieves a row by traversing through the index. Basic principles oracle uses is if an SQL query needs only the indexed columns, then DB reads the value from the index but if some access to the non-indexed columns is required then DB uses the row ids to get the rows. Oracle provides : • Full index scan: DB reads entire index in order • Fast full index scan: DB access the data in the index without accessing the table • Index range scan: ordered index scan where 1 or more columns are specified in conditions • Index Unique scan: similar to index range scan but have 0 or 1-row id associated with the index key • Index Skip Scan: uses logical subindex of a composite index Variations of B+ Trees index : • Reverse Key Indexes • Ascending and Descending Indexes Other indexes provided : • Bitmap index: DB stores a bitmap for each index key and each index stores pointers to multiple rows. • Function-Based Indexes: it can be either B+ trees or the bitmap index, where the index computes the function or expression that involves multiple columns to store in the index. • Application Domain Indexes: customized according to the application.
• Foreign Key Constraints: two tables containing common column(s), Oracle can enforce a foreign key constraint between them
Oracle was originally designed as a relational DBMS. It now also supports a variety of data models for storage.
Oracle supports compression at multiple levels within the data, including by row, block, and index. It also supports network compression designed to reduce bandwidth usage and increase network throughput.
Table compression Basic table compression: intended for bulk operation Advanced row compression: intended for an OLTP application
The compressed rows are stored in a row-major format where all columns of a particular row are stored together. Also, the info needed to re-create the uncompressed data from the compressed one is stored in the data block itself.
Hybrid Columnar Compression: stores the same column for a group of rows. Data is stored as a combination of row and columnar storage(stores column data together).