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.
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).