|
(Index By Tables vs Nested Tables vs Varrays)
|
(DDL, DML, TCL, Session and System Control, Embedded SQL)
(Atomicity, Consistency, Isolation, Durability)
|
Delete vs Truncate vs Drop
Delete
|
Truncate
|
Drop
| |
Purpose
|
Deletes some or all rows of a table
|
Deletes all rows of a table
|
Removes all rows and also the table definition, including indexes, triggers, grants, storage parameters
|
Command Type
|
DML
|
DDL
|
DDL
|
Syntax
|
DELETE FROM
or
DELETE FROM
|
TRUNCATE TABLE
|
DROP TABLE
|
Space Usage and Release
|
Uses UNDO space.
Doesn’t deallocate space.
Released blocks that go to the freelist for the table, to be used for subsequent inserts / updates.
|
Doesn’t use UNDO space.
Deallocates all space used by the table except MINEXTENTS.
|
Doesn’t use UNDO space.
Unless the PURGE clause is specified, does not result in space being released.
|
Commit required
|
Yes
|
No
|
No
|
Undo possible
|
Uncommitted deletes can be rolled back
|
Cannot be rolled back -once truncated, gone forever
|
Dropped table can be reinstated from the recycle bin
|
Selective deletion possible
|
Yes.
Filter criteria be specified via WHERE clause
|
No
Filter criteria allowed, removes all rows
|
No
Filter criteria allowed, removes all rows
|
Triggers fired
|
Yes, DELETE triggers fired
|
No triggers fired
|
No triggers fired
|
FKs based on the table exist
|
Can delete data even if FKs are enabled, provided the data violates no FK constraint
|
Cannot delete data if FKs are enabled; FKs need to be disabled / dropped.
Exception: Truncate is possible if the FK is self-referential.
|
Can drop table with the CASCADE constraint option. This will also remove the associated FKs
|
Efficiency
|
DELETE can be slow especially if the table has many triggers, indexes, and other dependencies
|
TRUNCATE is most efficient for deleting all rows, even more than dropping and recreating the table using DROP
|
DROP may not be as efficient as TRUNCATE, as dropping and re-creating the table requires you to re-grant object privileges, re-create indexes, constraints, etc.
|
Privileges required to issue the command
|
DELETE privilege.
DELETE ANY TABLE allows you to delete rows from any table of any schema.
|
DROP ANY TABLE system privilege.
|
DROP ANY TABLE system privilege.
|
Grants
|
DELETE privilege on a specific table can be granted to another user or role.
|
TRUNCATE privilege on a specific table cannot be granted to another user or role.
|
DROP ANY privilege on a specific table cannot be granted to another user or role.
|
Can work outside the user’s schema
|
Yes,
as long as the user has the DELETE privilege on object.
|
No.
table can be truncated in one’s own schema only.
|
No.
table can be dropped in one’s own schema only.
|
Can work on a table that is part of a cluster
|
Yes
|
No.
You will have to truncate the whole cluster, or use either DELETE / DROP.
|
Yes
|
Insert vs Update vs Alter
Insert
|
Update
|
Alter
|
Insert command is used to insert a new row to an existing table
|
Update command is used to update existing records in a DB
|
Alter command is used to modify, delete or add a column to an existing table in a DB
|
INSERT INTO tableName (column1Name, column2Name, …) VALUES (value1, value2, …)
Or
INSERT INTO tableName
VALUES (value1, value2, …)
|
UPDATE tableName
SET column1Name = value1, ….
WHERE columnXName = someValue
Note: WHERE clause is optional
|
ALTER TABLE
Or
ALTER TABLE
|
Insert is a DML statement
|
Update is a DML statement
|
Alter is a DDL statement
|
Insert and Update commands only modify records in a DB or insert records in to a table, without modifying its structure.
|
Alter command modifies the DB schema
| |
Procedure vs Function
Procedure
|
Function
|
Package
|
Procedure is a subprogram that performs a specific action.
|
Function is a subprogram that computes a value.
|
Package is a group of related procedures, functions, cursors, variables, and SQL statements stored together in the DB for continued use as a unit.
|
Procedure / Function is a schema object that consists of a set of SQL statements and other PL/SQL constructs, grouped together, stored in DB, and run as a unit to solve a specific problem / perform a set of related tasks.
Procedures / Functions permit the caller to provide parameters that can be input only, output only, or input and output values.
|
Packages provide a method of encapsulating and storing related procedures, functions, variables and other package contents.
| |
Cannot have a return statement
Procedure may return one / more values through parameters / may not return at all.
|
Will have return statement
Return a single variable by value
| |
Can be executed separately from SQL and can be invoked from some other procedures / functions
|
Can be invoked from a function / procedure / SQL
| |
Can be either stored procedure / an anonymous
|
Cannot be anonymous
|
Cannot be anonymous
|
CREATE or REPLACE PROCEDURE
IS | AS
variable_declaration
BEGIN
….
(EXCEPTION)
….
END
|
CREATE or REPLACE FUNCTION
RETURN
IS | AS
variable_declaration
BEGIN
….
RETURN (
(EXCEPTION)
….
END
|
Package has 2 parts:
· specification declares all public constructs of the package
· body defines all constructs (public and private) of the package.
|
Cursor declared in a procedure is local to that procedure and can’t be accessed by other procedures
|
Cursor declared in a function is local to that function and can’t be accessed by other procedures
|
Cursor declared in a package spec is global and can be accessed by other procedures / functions / triggers in/not in a package.
|
When you create a procedure or function, you may define parameters. 3 types of parameters can be declared.
Parameters
|
IN
|
OUT
|
IN OUT
|
Referenced
|
Yes
|
No
|
Yes
|
Value Overwritten
|
No
|
Yes
|
Yes
|
Stored Procedure vs Trigger
Stored Procedure
|
Trigger
|
It is a method that can be used by applications accessing a relational DB.
|
It is a stored PL/SQL program unit that is executed automatically when some specific events (INSERT / UPDATE / DELETE) occur in a table / view / event.
|
To execute a stored procedure a specific CALL or EXECUTE statement has to be used.
|
Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.
|
Stored procedure information is stored in USER_SOURCE, USER_OBJECTS (current user) tables.
|
Triggers information is stored in USER_SOURCE, USER_TRIGGERS (current user) tables.
|
Debugging triggers can be harder and trickier than debugging stored procedures
| |
Stored procedure can't be inactive
|
Triggers can be inactive.
|
Trigger Events can fire BEFORE / AFTER the triggering event. AFTER data triggers are slightly more efficient than BEFORE triggers
Trigger Event
|
Fires when
|
Insert
|
a row is added to the table_/ view reference
|
Update
|
an UPDATE changes the table / view reference. UPDATE triggers can additionally specify an OF clause to restrict firing to updates OF certain columns.
|
Delete
|
a row is deleted from the table / view reference.
|
Drop
|
a DROP removes an object from the DB. In this context, objects are things such as tables / packages (found in ALL_OBJECTS). Can apply to a single schema / the entire DB.
|
Truncate
|
a TRUNCATE is processed to purge a table or cluster.
|
Create
|
a DB object is created. Doesn’t fire on CREATE CONTROLFILE statements.
|
Alter
|
an ALTER statement changes a DB object. In this context, objects are things such as tables / packages (found in ALL_OBJECTS). Can apply to a single schema or the entire DB.
|
Drop
|
a DROP removes an object from the DB. In this context, objects are things such as tables / packages (found in ALL_OBJECTS). Can apply to a single schema / the entire DB.
|
Analyze
|
the DB collects or deletes statistics / validates the structure of a DB object.
|
Audit
|
the DB records an audit operation
|
NoAudit
|
the DB processes a NOAUDIT statement to stop auditing DB operations.
|
Comment
|
a comment on a DB object is modified.
|
DDL
|
one of the following events occurs:
ALTER, ANALYZE, AUDIT, COMMENT, CREATE, ASSOCIATE STATISTICS, DISASSOCIATE, DROP, GRANT, NOAUDIT, RENAME, REVOKE, or TRUNCATE.
|
Assocaite Statistics
|
the DB associates a statistic type with a DB object.
|
DisAssocaite Statistics
|
the DB disassociates a statistic type from a DB object.
|
Grant
|
a system, role, or object privilege is assigned.
|
Revoke
|
a system, role, or object privilege is rescinded.
|
Rename
|
a RENAME statement changes a DB object name.
|
After LogOn
|
a session is created (user connects to DB).
|
Before LogOff
|
a session is terminated (user disconnects from DB).
|
After Startup
|
the DB is opened.
|
Before Shutdown
|
the DB is closed. Just before the server starts the shutdown of an instance. This lets the cartridge shutdown completely. For abnormal instance shutdown, this event may not be fired.
|
After DB_Role_Change
|
the DB is opened for the first time after a role change.
|
After Suspend
|
After a SQL statement is suspended because of an out-of-space condition. The trigger should correct the condition so the statement can be resumed.
|
After ServerError
|
a server error message is logged. Trigger doesn’t fire on ORA-1034, ORA-1403, ORA-1422, ORA-1423, and ORA-4030 because they are not true errors / are too serious to continue processing. It also fails to fire on ORA-18 and ORA-20 because a process is not available to connect to the DB to record the error.
|
Cursor
|
Trigger
|
Cursor is a handle or name for a private SQL area - an area in memory in which a parsed statement and other information for processing the statement are kept.
|
It is a stored PL/SQL program unit that is executed automatically when some specific events (INSERT / UPDATE / DELETE) occur in a table / view / event.
|
In addition to traversing through records in a DB, cursors also facilitate data retrieval, adding and deleting records. By defining the correct way, cursors can also be used to traverse backwards.
|
Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.
|
Cursor is a handle or name for a private SQL area - an area in memory in which a parsed statement and other information for processing the statement are kept.
Implicit Cursor
|
Explicit Cursor
|
Implicit cursors are automatically created and used every time a SELECT statement is issued in PL/SQL, when there is no explicitly defined cursor.
|
Explicit cursors are defined explicitly by the developer. In PL/SQL an explicit cursor is actually a named query defined using the key word cursor. They can be thought of as a pointer to a set of records and the pointer can be moved forward within the set of records.
|
If an implicit cursor is used, the DBMS will perform the open, fetch and close operations automatically.
|
Explicit cursor should be opened, fetched and closed by the user, thereby providing the user the complete control.
|
Implicit cursors should be used only with SQL statements that return a single row.
If the SQL statement returns more than one row, using an implicit cursor will introduce an error
|
Multiple rows can be fetched using an explicit cursor.
|
SQL statements handled this way include INSERT, UPDATE, DELETE, MERGE, and SELECT INTO.
|
Explicit cursors can take parameters, so that the variables in the cursor can be changed each time it is executed.
Explicit cursors allow you to fetch a whole row in to a PL/SQL record variable.
|
When fetching data using implicit cursors:-
NO_DATA_FOUND exception is raised when the SQL statement returns no data.
TOO_MANY_ROWS exception is raised when the SQL statement returns more than one row.
|
NO_DATA_FOUND and TOO_MANY_ROWS exceptions are not raised when using explicit cursors
|
Implicit cursors are more vulnerable to data errors and provide less programmatic control than Explicit cursors.
| |
Implicit cursors are considered less efficient than Explicit cursors.
| |
Cursor processing is done in several steps:
1. Define the rows you want to retrieve. This is called declaring the cursor.
2. Open the cursor. This activates the cursor and loads the data. Note that declaring the cursor doesn't load data, opening the cursor does.
3. Fetch the data into variables. If multiple rows need to be fetched, the fetching operation needs to be done inside a loop.
4. Close the cursor.
PL/SQL statements used in Cursor processing
1. DECLARE CURSOR cursor_name
2. OPEN cursor_name
3. FETCH cursor_name INTO / Record types
4. CLOSE cursor_name
Advantages of Cursor
By using these cursors you process the query records.
You can easily move the records and you can exit from procedure when you required by using cursor attributes.
Disadvantages of Cursor
Using implicit/explicit cursors are depended by situation.
If the result set is less than 50 / 100 records it is better to go for implicit cursors.
If the result set is large then you should use explicit cursors. Otherwise it will put burden on CPU.
Static Cursor
|
Dynamic Cursor
|
Static cursors, whose SQL statement is determined at compile time.
|
Dynamic cursors, whose SQL statement is determined at runtime.
|
Static cursors are used only for DML statements
|
Dynamic cursors are used for any type of SQL statement including DDL.
|
Static cursors may be explicitly declared and named or may appear inline as implicit cursors.
|
Dynamic cursors are implemented with the EXECUTE IMMEDIATE statement.
|
Cursor attribute is property of the cursor. Using the cursor attribute's we can know the current status of the cursor.
Cursor Attributes
|
Description
|
%ISOPEN
to check whether cursor is open / not
|
Implicit cursor
Always FALSE since the cursor is opened implicitly and closed immediately after the statement is executed.
Explicit cursor
TRUE if cursor is open.
FALSE if cursor is not open.
|
%FOUND
to check whether cursor has fetched any row.
|
TRUE if record was fetched successfully.
FALSE if no row was returned.
NULL before the first fetch.
INVALID_CURSOR is raised if cursor has not been OPENed.
INVALID_CURSOR if cursor has been CLOSEd.
|
%NOTFOUND
to check whether cursor has fetched any row.
|
TRUE if no row was returned.
FALSE if record was fetched successfully.
NULL before the first fetch.
INVALID_CURSOR is raised if cursor has not been OPENed.
INVALID_CURSOR if cursor has been CLOSEd.
|
%ROWCOUNT
number of rows fetched / updated / deleted
|
Returns the number of rows fetched from the cursor.
INVALID_CURSOR is raised if cursor has not been OPENed.
INVALID_CURSOR if cursor has been CLOSEd.
|
1. Cursor variable is a data structure that points to a cursor object, which in turn points to the cursor’s result set. You can use cursor variables to more easily retrieve rows in a result set from client and server programs. You also can use cursor variables to hide minor variations in queries.
2. Cursor variables can be assigned to different statements at runtime. They are similar to PL/SQL variables which can hold different values at runtime where as static cursors are similar to PL/SQL constants as they can be associated with only one runtime query.
3. Cursor variable is a reference type which is similar as a pointer. In order to use reference type, the variable has to be declared and then storage has to be allocated.
4. Ref keyword indicates that the new type will be a pointer to the defined type. The type of the cursor is therefore a ref cursor.
REF_CURSOR syntax TYPE ref_cursor_name IS REF_CURSOR [RETURN record_type];
1. If you do not include a RETURN clause, then you are declaring a “weak” REF_CURSOR.
2. Cursor variables declared from weak REF_CURSORs can be associated with any query at runtime.
3. REF_CURSOR declaration with a RETURN clause defines a "strong" REF_CURSOR.
4. Cursor variable based on a strong REF_CURSOR can be associated with queries whose result sets match the number and datatype of the record structure after the RETURN at runtime.
Restrictions on Cursor Variables
1. Cursor variables cannot be declared in a package since they do not have a persistent state.
2. You cannot use the FOR UPDATE clause with cursor variables.
3. You cannot assign NULLs to a cursor variable
4. You cannot use comparison operators to test for equality, inequality, or nullity.
5. Neither DB columns nor collections can store cursor variables.
6. Cursor variables cannot be used with the dynamic SQL built-in package DBMS_SQL.
7. Cursors and cursor variables are not interoperable; that is, you cannot use one where the other is expected. For ex, you cannot reference a cursor variable in a cursor FOR loop
8. You cannot pass cursor variables to a procedure that is called through a DB link.
9. If you pass a host cursor variable to PL/SQL, you cannot fetch from it on the server side unless you also open it there on the same server call.
10. You cannot store cursor variables in an associative array, nested table, or array.
A column or a set of columns, which can be used to identify or access a row or a set of rows in a DB is called a key.
Primary key (PK)
|
Foreign Key (FK)
|
Unique Key (UK)
|
PK is a column or combination of columns in a table that uniquely identify a row of the table.
|
FK is a referential constraint between two tables.
It identifies a column or a set of columns in one table (referencing table) that refers to a set of columns in another table (referenced table).
|
UK comprises a single column or a set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns if NULL values are not used.
|
A table can have at most one PK.
SQL allows PK to be made up of one or more columns.
|
A table can have multiple FKs and they can reference different tables
|
A table can have multiple UKs
|
PK enforces implicitly UNIQUE and NOT NULL constraint.
|
FK does not enforce UNIQUE and NOT NULL constraint.
|
UK does not enforce NOT NULL constraint.
|
PK column cannot have NULL values
|
FK columns can have NULL values
|
UK columns can have NULL values
|
PK is defined through the PRIMARY KEY constraint
|
FK is defined through the FOREIGN KEY constraint
|
UK is defined through the UNIQUE KEY constraint
|
PK can also be defined when creating the table.
|
FK can be defined when creating the table.
|
FK can be defined when creating the table.
|
Indexing and sorting are two methods that can be used to create an order in a data table.
Indexing
|
Sorting
|
Indexing would create an index file that contains the logical order of rows along with their physical position in the table. Indexing is a method that is used to improve the data retrieval speed in a table of a DB.
|
Sorting is the process / arranging items in a set in a specific order. Sorting a table would create a copy of the table in which the rows may have a different order than the original.
|
Index could be created using a single / more columns in a table and the index is stored in a separate file.
|
Sorting is allowed using multiple fields, such as sorting addresses using the states and then sort using the cities inside the states.
|
Indexing does not change the original order in the table
|
Sorting does change the order of rows
|
Index file requires lesser space than storing a sorted table.
| |
Operation such as linking tables would require having an Index.
| |
Indexing would make the data retrieval more efficient.
|
Storing the new table would require an amount of space similar to that of the original table.
Due to this reason sorting is used less frequently; only used when a new copy of the sorted table is required.
|
Unique indices will prevent the table from containing duplicate values of the index.
Join combines two or more tables to retrieve data from multiple tables. It creates resultant sets, which can be saved as another table.
Inner Join or Equijoin
|
Outer Join
|
Inner join is a join where the join condition uses the equal to (=) operator to relate the rows of two tables.
|
Outer join returns all rows that exist in one table, even though corresponding rows do not exist in the joined table.
|
Inner join may not produce a result if no matches are found
|
Outer join will always produce a resultant table, even with no matching rows
|
Inner join will always return tables with values (if returned).
|
Outer joins may result in tables with null values.
|
Types of Outer Joins
You can use the outer join on only one side of a JOIN condition; however, you can use an outer join on more than one column of the same table in the JOIN condition.The (+) symbol denotes an outer join in a query. The (+) is placed at the end of the table name in the WHERE clause. The table with the (+) should be the table that does not have matching rows.
This differentiation is based on which table’s (left table, right table or both tables) row is retained when unmatched records are found.
- Left outer joins (simply left join) all the rows from left table are returned with matched values from right table (or null values when unmatched). If values from multiple rows from left table are matched with a single row from right table, the row from right table will be repeated as required.
- Right outer join all the rows from right table are returned at least once with matched left table values (and null values for unmatched right values).
- Full outer join is more comprehensive than both left and right outer joins. It results in combining the effect of applying both left and right outer joined together.
- Equijoins or inner joins
- Outer joins
- Non-equijoins: It joins two / more tables based on a specified column value not equaling a specified column value in another table.
- Self joins: The self join joins a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement using a table alias.
- Cross joins: Cross joins are joins without a join condition. Each row of one table is combined with each row of another table. The result is referred to as a Cartesian product. If you select from two or more tables and do not join the tables, your output is all possible rows from all the tables selected. WHERE clause is highly recommended for SQL statements retrieving data from two or more tables.
- Partition outer joins: A new type of join introduced in Oracle 10g that is slated to be part of the next ANSI/ISO SQL standard after SQL:2003. A partition outer join divides your result set into groups, or partitions, and repeats the same outer join for each of these groups. Such joins are extremely handy for generating missing rows.
a. Relational DB is made up of relations (related tables). Tables are made up of columns.
b. If the tables are two large, then DB anomalies can occur.
c. If the tables are two small, it would be inefficient for querying.
d. Normalization and Denormalization are two processes that are used to optimize the performance of the DB.
Normalization
|
Denormalization (reverse of normalization)
|
Normalization is a process of dividing larger tables in to smaller ones to minimize the redundancies that are present in relational databases.
These smaller tables will be related to each other through well defined relationships.
|
Denormalization works by adding redundant data or grouping data to optimize the performance.
|
In a well normalized DB, any alteration / modification in data will require modifying only a single table.
|
Denormalization is sometimes very important process to overcome some of the shortcomings in the relational DB software that may incur heavy performance penalties with normalized DB (even tuned for higher performance).
This is because joining several relations (which are results of normalizing) to produce a result to a query can sometimes be slow depending on the actual physical implementation of the DB systems.
|
Normalization is carried out to prevent databases anomalies
|
Denormalization is usually carried out to improve the read performance of the DB, but due to the additional constraints used for denormalization, writes (i.e. insert, update and delete operations) can become slower. Therefore, a denormalized DB can offer worse write performance than a normalized DB.
|
It is often recommended that you should “normalize until it hurts, denormalize until it works”.
| |
1NF, 2NF, and 3NF was introduced by Edgar F. Codd, who is also the inventor of the relational model and the concept of normalization.
First normal form (1NF)
|
Second normal form (2NF)
|
Third Normal Form (3NF)
|
1NF provides the minimum set of requirements for normalizing a relational DB.
|
2NF is considered as a stronger normal form than 1NF.
|
3NF is considered as a stronger normal form than the 2NF
|
Table that complies with 1NF assures that it actually represents a relation (i.e. it does not contain any records that are repeating), and does not contain any attributes that are relational valued (i.e. all attributes should have atomic values).
|
For a table to comply with 2NF, it should be complied with 1NF and any attribute that is not a part of any candidate key (i.e. non-prime attributes) should fully depend on any of candidate keys in the table.
|
Table is said to be in 3NF, if and only if that table is in 2NF, and every attribute in the table that do not belong to a candidate key, should directly depend on every candidate key of that table.
|
In 1982 Carlo Zaniolo produced a differently expressed definition for 3NF. Tables that comply with the 3NF generally do not contain anomalies that occur when inserting, deleting or updating records in the table.
Third Normal Form 3NF)
|
Boyce-Codd Normal Form BCNF) (3.5NF)
|
3NF is considered as a stronger normal form than the 2NF
|
BCNF is considered as a stronger normal form than the 3NF and it was developed to capture some of the anomalies that could not be captured by 3NF.
|
Table is said to be in 3NF, if and only if that table is in 2NF, and every attribute in the table that do not belong to a candidate key, should directly depend on every candidate key of that table.
|
Table is said to be in BCNF, if and only if, for each of the dependencies of form A Ã B that are non-trivial, A is a super-key.
Decomposing a table that is not in the BCNF normal form does not guarantee the production of tables in the BCNF form (while preserving the dependencies which were present in the original table).
|
- Obtaining a table that complies with the BCNF form will require decomposing a table that is in the 3NF.
- Obtaining a table that complies with the 3NF form will require decomposing a table that is in the 2NF.
- Obtaining a table that complies with the 2NF will require decomposing a table that is in the 1NF.
- However, if a table that complies with 1NF contains candidate keys that are only made up of a single attribute (i.e. non-composite candidate keys), such a table would automatically comply with 2NF.
- Decomposition of tables will result in additional join operations (or Cartesian products) when executing queries.
- This will increase the computational time.
- On the other hand, the tables that comply with stronger NF would have fewer redundancies than tables that only comply with weaker NF.
- Furthermore, most of the time, it is possible to obtain a table that comply with 3NF without hindering dependency preservation and lossless joining. But this is not always possible with BCNF.
Collection is a list of elements of the same type.
Each element in the list has a unique position number or label, called the "subscript".
Index By Tables
(Associate Arrays)
|
Nested Tables
|
Varrays
(Variable Arrays)
| |
Dimensionality
|
Single
|
Single
|
Single
|
Usable in SQL
|
No
|
Yes
|
Yes
|
Usable as a
column datatype
in a table
|
No
|
Yes; data stored “out of line” (in a separate table)
|
Yes; data typically stored “inline” (in the same table)
|
Sparse
|
Yes
|
Initially no; after deletions, yes
|
No
|
Bounded
|
No
|
Can be extended
|
Yes
|
Size
|
Unbounded i.e. the number of elements it can hold is not pre-defined
|
Unbounded i.e. the number of elements it can hold is not pre-defined
|
Bounded i.e. holds a declared number of elements, though this number can be changed at runtime
|
Subscript Characteristics
|
Can be arbitrary numbers or strings. Need not be sequential.
|
Sequential numbers, starting from one
|
Sequential numbers, starting from one
|
Uninitialized state
|
Empty (cannot be NULL); elements are undefined
|
Atomically null; illegal to reference elements
|
Atomically null; illegal to reference elements
|
Initialization
|
Automatic, when declared
|
Via constructor, fetch, assignment
|
Via constructor, fetch, assignment
|
Shrinking size of the collection
|
Automatic
|
TRIM function
|
TRIM function
|
Can be compared for equality
|
No
|
Yes (10g) No (9.2)
|
No
|
DB Storage
|
Index by tables can be used in PL/SQL programs only, cannot be stored in the DB.
|
Can be stored in the DB using equivalent SQL types, and manipulated through SQL.
|
Can be stored in DB using equivalent SQL types, and manipulated through SQL (but with less ease than nested tables)
|
Referencing and lookups
|
Works as key-value pairs.
e.g. Salaries of employees can be stored with unique employee numbers used as subscripts
sal(102) := 2000;
|
Similar to one-column DB tables. Oracle stores the nested table data in no particular order. But when you retrieve the nested table into a PL/SQL variable, the rows are given consecutive subscripts starting at 1.
|
Standard subscripting syntax
e.g. color(3) is the 3rd color in varray color
|
Flexibility to changes
|
Most flexible.
Size can increase / decrease dynamically. Elements can be added to any position in the list and deleted from any position.
|
Almost like index-by tables, except that subscript values are not as flexible. Deletions are possible from non-contiguous positions.
|
Not very flexible.
You must retrieve and update all the elements of the varray at the same time.
|
Collection type to use
|
1. Don’t need to store the collection in the DB
2. Data volume is unknown beforehand
3. Your program needs small lookups
4. Subscript values are flexible (e.g. non-sequential, strings, negative numbers)
5. Collection can be made at runtime in the memory when the package / procedure is initialized
|
1. Data needs to be stored in the DB
2. Number of elements in the collection is not known in advance
3. Elements of the collection may need to be retrieved out of sequence
4. Updates and deletions affect only some elements, at arbitrary locations
5. Your program does not expect to rely on the subscript remaining stable, as their order may change when nested tables are stored in the DB.
|
1. Data needs to be stored in the DB
2. Number of elements of the varray is known in advance
3. Data from the varray is accessed in sequence
4. Updates and deletions happen on the varray as a whole and not on arbitrarily located elements in the varray
|
Mapping with other programming languages
|
Hash tables
|
Sets and bags
|
Arrays
|
SQL (Structured Query Language)
|
PL/SQL (Procedural Language/SQL)
|
It is a DB language for selecting and manipulating set of data.
Users describe in SQL what they want done, and the SQL language compiler automatically generates a procedure to navigate the DB and perform the desired task.
|
PL/SQL is Oracle's procedural language extension to SQL. It provides a server-side, stored procedural language that is easy-to-use, seamless with SQL, robust, portable, and secure
|
SQL allows developer to issue or execute single query at a time
|
PL/SQL allows writing complete program to get done several selects / inserts / updates / deletes at a time
|
SQL is declarative
|
PL/SQL is procedural
|
Basic scope of SQL is to insert data and perform update, delete, schema creation, schema modification and data access control against databases.
|
It offers modern features such as data encapsulation, overloading, collection types, exception handling, and information hiding.
It also offers seamless SQL access, tight integration with the Oracle server and tools, portability, and security.
|
General characteristics of SQL are:
• it supports operations on sets of data, as opposed to row-by-row processing.
• it can access data independently of the data's physical location
• it only describes the data to be retrieved, not the method by which the data is to be retrieved.
|
PL/SQL is
• standard and portable language
• highly performed, highly integrated
• highly accessible, readable, embedded [not stand-alone environment], and structured language
• data manipulation is slightly faster in PL/SQL than in Java and easier to use than Java
|
- DDL - Data Definition Language
- DML - Data Manipulation Language
- Transaction Control
- Session Control
- System Control
- Embedded SQL
DDL vs DML vs TCL
DDL (Data Definition Language)
|
DML (Data Manipulation Language)
|
TCL (Transaction Control Language)
|
DDL are used to define, alter the structure of, or drop DB structure or schema objects.
|
DML are used for managing data within schema objects.
|
TCL manage the changes made by DML statements and group DML statements into transactions.
|
CREATE - to create objects in DB
ALTER - alters structure of the DB
DROP - delete objects from the DB
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object
GRANT - gives user's access privileges to DB
REVOKE - withdraw access privileges given with the GRANT command
AUDIT, NOAUDIT - Turn auditing options on and off
|
SELECT - retrieve data from the a DB
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
MERGE - UPSERT operation (insert or update)
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
CALL - call a PL/SQL / Java subprogram
|
COMMIT - save work done
ROLLBACK - restore DB to original since the last COMMIT, SAVEPOINT
SAVEPOINT - set a point to which you can roll back
SET TRANSACTION - Change transaction options like isolation level and what rollback segment to use
|
Session control statements manage the properties of a particular user's session. For example, they enable you to:
- ALTER SESSION Alter the current session by performing a specialized function, such as enabling and disabling the SQL trace facility
- SET ROLE Enable and disable roles (groups of privileges) for the current session
System Control Statements
System control statements change the properties of the Oracle DB server instance. The only system control statement is ALTER SYSTEM. It enables you to change settings (such as the minimum number of shared servers), kill a session, and perform other tasks.
Embedded SQL Statements
Embedded SQL statements incorporate DDL, DML, and transaction control statements within a procedural language program. They are used with the Oracle precompilers. Embedded SQL statements enable you to:
- DECLARE CURSOR, OPEN, CLOSE Define, allocate, and release cursors
- DECLARE DATABASE, CONNECT Specify a DB and connect to Oracle
- DECLARE STATEMENT Assign variable names
- DESCRIBE Initialize descriptors
- WHENEVER Specify how error and warning conditions are handled
- FETCH Retrieve data from the DB
- PREPARE, EXECUTE, EXECUTE IMMEDIATE Parse and run SQL statements
PL/SQL can run dynamic SQL statements whose complete text is not known until runtime. Dynamic SQL statements are stored in character strings that are entered into, or built by, the program at runtime. This enables you to create general purpose procedures. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.
You can write stored procedures and anonymous PL/SQL blocks that include dynamic SQL in two ways:
Additionally, you can issue DML or DDL statements using dynamic SQL. This helps solve the problem of not being able to statically embed DDL statements in PL/SQL. For example, you can choose to issue a DROP TABLE statement from within a stored procedure by using the EXECUTE IMMEDIATE statement or the PARSE procedure supplied with the DBMS_SQL package.
Decode and Case functions are used within the Oracle DB to transform data values for one value to another.
Decode
|
Case
|
DECODE compares expr to each search value one by one.
• If match is found, then DB returns corresponding result.
• If no match is found, then DB returns default.
• If default is omitted, then DB returns null.
|
Case is an easier form of the Decode.
• In a CASE expression, DB searches for the first WHEN ... THEN pair for which expr is equal to comparison_expr and returns return_expr.
• If none of the WHEN...THEN pairs meet this condition and an ELSE clause exists, then DB returns else_expr. Otherwise, DB returns null.
• You can’t specify the literal NULL for every return_expr and the else_expr.
|
Everything Decode can do, Case can
|
Everything Case can do, Decode cannot
|
Decode can do an equality check only.
To achieve the same effect with Decode, ranges of data had to be forced into discrete form making unwieldy code.
|
Case is capable of more logical comparisons such as < > etc.
|
Decode works with expressions which are scalar values only.
|
Case can work with predicates and sub-queries in searchable form.
|
Decode can work as a function inside SQL only.
|
Case can be a more efficient substitute for IF-THEN-ELSE in PL/SQL.
|
Decode can’t work as a parameter to a procedure call.
|
Case can even work as a parameter to a procedure call
|
DECODE is proprietary to Oracle.
|
CASE complies with ANSI SQL.
|
Decode allows us to create an index on data column values that do not exist in the DB.
View
|
Materialized view
|
View is a logical table based on one / more tables / views. A view contains no data itself.
It is a SQL statement stored in the DB in the system tablespace. Data for a view is built in a table created by the DB engine in the TEMP tablespace.
|
Materialized view is a DB object that contains the results of a query.
The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) / detail tables (a DW term). This reference uses "master tables" for consistency. The DB containing the master tables are called the Master DB.
|
View’s SQL is executed at run-time. The results are fetched from the view’s base tables when the view is queried.
|
Materialized view (snapshot) is a "pre-answered" query – the query is executed when the materialized view is refreshed. Its result is stored in the SB and the query only browses the result.
|
View occupies no space (other than that for its definition in the data dictionary).
|
Materialized view occupies space. It exists in the same way as a table; it sits on a disk and could be indexed or partitioned.
|
View’s output is built on the fly; it shows real-time data from the base tables being queried.
|
Materialized view does not reflect real-time data. The data is only as up to date as the last time the materialized view was refreshed.
|
View is best used when:
• You want to hide the implementation details of a complex query
• You want improve security by restricting access to a set of rows / columns in the base tables
|
Materialized view is best used when:
• You have a really big table and people do frequent aggregates on it, and you want fast response
• You don’t mind the result being a little out of date, or your application data has more queries than updates (as in a BI / data warehousing system)
|
All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.
Updatable View: An updatable view lets you insert, update, and delete rows in the view and propagate the changes to the target master table.
|
3 types of materialized views:
• Read only materialized views
• Updateable materialized views
• Writeable materialized views
|
Updateable views can’t include
• Set Operators (INTERSECT, MINUS, UNION, UNION ALL)
• Aggregate Functions (AVG, COUNT, MAX, MIN, SUM)
• GROUP BY, ORDER BY, CONNECT BY, START WITH Clause
• DISTINCT
• Join Query
• Subquery In A Select List
• Collection Expression In A Select List
• If pseudocolumns are present they can’t be included in an update statement.
Few points about VIEWS
1. View is like a virtual table. It takes the output of a query and treats it like a table.
2. View can be based on one or more tables or other views. These tables/views are called base tables.
3. View takes up no storage space other than for the definition of the view in the data dictionary.
4. View contains no data. All the data it shows comes from the base tables.
5. View can provide an additional level of table security by restricting access to a set of rows or columns of a table.
6. View hides implementation complexity. The user can select from the view with a simple SQL, unaware that the view is based internally on a join between multiple tables.
7. Views that are not updatable can be modified using an INSTEAD OF trigger.
8. Updatable view allows you to insert, update, and delete rows by propagating the changes to the base table. A view can be updatable provided its definition does not contain any of the following constructs: SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a subquery (or collection expression) in a SELECT list.
9. View lets you change the data you can access, applying operators, aggregation functions, filters etc. on the base table.
10. View isolates applications from changes in definitions of base tables. Suppose a view uses two columns of a base table, it makes no difference to the view if other columns are added, modified or removed from the base table.
11. Using views encourages the use of shared SQL, which improves efficiency of frequently invoked SQL.
12. The data dictionary views USER_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and ALL_UPDATABLE_COLUMNS indicate which view columns are updatable.
13. View can be created even if the defining query of the view cannot be executed, using the CREATE FORCE VIEW command. Such a view is called a view with errors. This option can be useful for import/installation tools to create a view before the underlying objects are present.
14. View can be replaced with a CREATE OR REPLACE VIEW statement. The REPLACE option updates the current view definition but preserves the present security authorizations.
15. View lets you reorder columns easily with a CREATE OR REPLACE, rather than going into a messy drop column for the base table with data.
16. To know about the views in your own schema, look up USER_VIEWS.
17. The underlying SQL definition of the view can be read via select text from USER_VIEWS for the view.
18. Oracle does not enforce constraints on views. Instead, views are subject to the constraints of their base tables.
19. Be careful when you define views of views. Don’t do it just because it is easy to code – it may not be the optimal query. Check if you would be better off using the base tables directly instead.
20. To create a view in your own schema, you must have the CREATE VIEW system privilege. To create a view in another user’s schema, you must have the CREATE ANY VIEW system privilege.
Materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally.
The main difference between the IN and EXISTS predicate in subquery is the way in which the query gets executed.
In
|
Exists
|
The inner query is executed first and the list of values obtained as its result is used by the outer query. The inner query is executed for only once.
|
The first row from the outer query is selected, then the inner query is executed and, the outer query output uses this result for checking. This process of inner query execution repeats as many number of times as there are outer query rows. That is, if there are ten rows that can result from outer query, the inner query is executed that many number of times.
|
EXISTS is efficient because, Exists is faster than IN clause.
| |
IN check returns values to main query
|
EXISTS returns Boolean (T / F)
|
If the majority of the filtering criteria is in the subquery, use IN.
i.e. IN for big outer query and small inner query.
|
If the majority of the filtering criteria is in the main query, use EXISTS.
i.e. EXISTS for small outer query and big inner query.
|
IN will get the LAST row (all rows) faster than WHERE EXISTS
|
WHERE EXISTS will find the first row faster in general than IN
|
If both the outer query and inner query were large, either could work well – the choice would depend on indexes and other factors.
In current versions of Oracle, the optimizer (CBO) costs both to produce the best plan.
| |
CHAR vs VARCHAR vs VARCHAR2
CHAR, VARCHAR, and VARCHAR2 are used to store character string values, however, they behave very differently.
CHAR
|
VARCHAR
|
VARCHAR2
|
CHAR should be used for storing fixed length character strings.
|
VARCHAR behaves exactly the same as VARCHAR2.
|
VARCHAR2 is used to store variable length character strings.
|
CHAR(n) will always be n bytes long.
If the string length is
|
However, this type should not be used as it is reserved for future usage.
|
VARCHAR2(n) will be 1 to n bytes long.
Shorter string stored as VARCHAR2 will NOT be blank padded.
|
Max 2000 bytes
|
Max size 4000 bytes
|
Both UNION and UNION ALL concatenate the result sets of two different SQLs. They differ in the way they handle duplicates.
Union
|
UnionAll
|
UNION performs a DISTINCT on the result set, eliminating any duplicate rows.
|
UNION ALL does not remove duplicates, and is therefore faster than UNION.
|
Use UNION only if you absolutely must get rid of duplicates in the result.
Otherwise, use UNION ALL which works faster.
| |
• %TYPE and %ROWTYPE are used to define variables in PL/SQL as it is defined within the DB.
• If the datatype or precision of a column changes, the program automatically picks up the new definition from the DB without having to make any code changes.
• %TYPE and %ROWTYPE constructs provide data independence, reduces maintenance costs, and allows programs to adapt as the DB changes to meet new business needs.
%TYPE
|
%ROWTYPE
|
%TYPE is used to declare a field with the same type as that of a specified table's column.
|
%ROWTYPE is used to declare a record with the same types as found in the specified DB table, view or cursor.
|
• It is a query within a query. It is a query embedded within the WHERE clause of another query to further restrict data returned by the query.
• Subquery returns data that is used in the main query as a condition to further restrict the data to be retrieved.
• Subqueries are employed with the SELECT, INSERT, UPDATE, and DELETE statements.
Types of Subqueries
1. Placement of the Subquery
Nested Subquery: The subquery appears in the WHERE clause of the SQL. Nested sub-query runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query.
Inline View: The subquery appears in the FROM clause of the SQL.
Scalar Subquery: The subquery appears in the SELECT clause of the SQL. It returns exactly one row as output. If the scalar subquery finds no match, it returns NULL. If the scalar subquery finds more than one match, it returns an error.
2. The way Subquery is Parsed
Simple Subquery: A simple subquery is evaluated once only for each table.
Correlated Subquery: This is a type of nested subquery that uses columns from the outer query in its WHERE clause. A Correlated sub-query runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.
Subqueries must follow a few rules:
1. Subqueries must be enclosed within parentheses.
2. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
3. You cannot use an ORDER BY clause in a subquery, although the main query can use an ORDER BY clause.
4. You can use the GROUP BY clause to perform the same function as the ORDER BY clause in a subquery.
5. You can only use subqueries that return more than one row with multiple value operators, such as the IN operator.
6. You cannot use the BETWEEN operator with a subquery; however, you can use the BETWEEN operator within the subquery.
7. You cannot immediately enclose a subquery in a SET function.
8. The SELECT list cannot include references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
1. There is no limit on the number of subquery levels in the FROM clause of the top-level query.
2. Get into the habit of using table alias when you use a subquery – they make a world of difference to the readability of huge SQLs. In case of correlated subqueries, they are critical to keep the SQLs accurate.
3. Oracle resolves unqualified columns in the subquery by first looking at the tables in the subquery, then the tables in the outer query.
Both DBMS and RDBMS are used to store information in physical DB.
DBMS (DB Management System)
|
RDBMS (Relational DB Management System)
|
DBMS is defined as the software program that is used to manage all the databases that are stored on the network or system hard disk. DBMS there are no relationships among tables.
|
The DB system in which the relationships among different tables are maintained is called RDBMS
|
DBMS accepts the ‘flat file’ data that means there is no relation among different data
|
RDBMS does not accept this type of design.
|
Small sets of data can be managed by DBMS
|
RDBMS solution is required when large amounts of data are to be stored as well as maintained.
|
Every DBMS has some fundamental elements.
1. First, is implementation of the modeling language which defines the language used for each DB.
2. Second, DBMS administers the data structures.
3. Third, Data query language.
4. Data structures work with data query language in order to make sure that irrelevant data cannot be entered into the DB used in the system.
|
A relational data model consists of indexes, keys, foreign keys, tables and their relationships with other tables.
|
RDBMS enforces the rules even though foreign keys are support by both RDBMS and DBMS.
| |
In 1970s, Edgar Frank Codd introduced the theory of relational DB. 13 rules were defined by Codd for this relational theory or model.
1. Rule 0: The system must qualify as relational, as a DB, and as a management system. For a system to qualify as a RDBMS, that system must use its relational facilities (exclusively) to manage the DB.
2. Rule 1: The information rule: All information in the DB is to be represented in only one way, namely by values in column positions within rows of tables.
3. Rule 2: The guaranteed access rule: All data must be accessible. This rule is essentially a restatement of the fundamental requirement for primary keys. It says that every individual scalar value in the DB must be logically addressable by specifying the name of the containing table, the name of the containing column and the primary key value of the containing row.
4. Rule 3: Systematic treatment of null values: The DBMS must allow each field to remain null (or empty). Specifically, it must support a representation of "missing information and inapplicable information" that is systematic, distinct from all regular values (for example, "distinct from zero or any other number", in the case of numeric values), and independent of data type. It is also implied that such representations must be manipulated by the DBMS in a systematic way.
5. Rule 4: Active online catalog based on the relational model: The system must support an online, inline, relational catalog that is accessible to authorized users by means of their regular query language. That is, users must be able to access the DB's structure (catalog) using the same query language that they use to access the DB's data.
6. Rule 5: The comprehensive data sublanguage rule: The system must support at least one relational language that
i. Has a linear syntax
ii. Can be used both interactively and within application programs,
iii. Supports data definition operations (including view definitions), data manipulation operations (update as well as retrieval), security and integrity constraints, and transaction management operations (begin, commit, and rollback).
7. Rule 6: The view updating rule: All views that are theoretically updatable must be updatable by the system.
8. Rule 7: High-level insert, update, and delete: The system must support set-at-a-time insert, update, and delete operators. This means that data can be retrieved from a relational DB in sets constructed of data from multiple rows and/or multiple tables. This rule states that insert, update, and delete operations should be supported for any retrievable set rather than just for a single row in a single table.
9. Rule 8: Physical data independence: Changes to the physical level (how the data is stored, whether in arrays or linked lists etc.) must not require a change to an application based on the structure.
10. Rule 9: Logical data independence: Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.
11. Rule 10: Integrity independence: Integrity constraints must be specified separately from application programs and stored in the catalog. It must be possible to change such constraints as and when appropriate without unnecessarily affecting existing applications.
12. Rule 11: Distribution independence: The distribution of portions of the DB to various locations should be invisible to users of the DB. Existing applications should continue to operate successfully:
i. when a distributed version of the DBMS is first introduced; and
ii. when existing distributed data are redistributed around the system.
13. Rule 12: The nonsubversion rule: If the system provides a low-level (record-at-a-time) interface, then that interface cannot be used to subvert the system, for example, bypassing a relational security or integrity constraint.
Schema
|
Table
|
Schema describes the structure and organization of data in a DB system.
Schema is defined as the set of formula that imposes integrity constraints on the tables.
|
Table is a set of data that is organized in to rows and columns.
Number of columns in a table is specified in the DB schema, but it can hold any number of rows.
|
DB schema will describe all tables, column names and data types, indexes, etc. It also defines what columns are defined as PK of a table.
|
Tables contain information such as constraints on the values in the columns and this information are called meta-information.
|
Schema of a DB keeps constant once created, while the actual data in the DB tables may change all the time.
|
In relational DB, a relation is represented using a table.
But a relation and a table are not the same, since a table can have rows that are duplicates (and a relation cannot contain duplicate rows).
|
3 types of schema
· Conceptual schema describes how concepts and relationships are mapped.
· Logical schema defines how entities, attributes and relations are mapped.
· Physical schema is a specific implementation of the aforementioned logical schema.
|
2 types of tables:
· Object tables hold objects of a defined type
· Relational tables hold user data in a relational DB.
|
Schema
|
Database
|
Schema describes the structure and organization of data in a DB system.
Schema is defined as the set of formula that imposes integrity constraints on the tables.
|
DB is a collection of organized data
A system intended for easily organizing, storing and retrieving large amounts of data, is called a DB.
|
DB schema will describe all tables, column names and data types, indexes, etc. It also defines what columns are defined as the PK of a table.
|
DB is classified according to their content, such as document-text, bibliographic and statistical.
|
Schema of a DB keeps constant once created
|
The data in the DB tables may change all the time
|
3 types of schema
1. Conceptual schema describes how concepts and relationships are mapped.
2. Logical schema defines how entities, attributes and relations are mapped.
3. Physical schema is a specific implementation of the aforementioned logical schema.
|
3 levels of abstraction in DB architecture
1. External level defines how the users view the data. A single database can have multiple views.
2. Internal level defines how the data is physically stored.
3. Conceptual level is the communication medium between internal and external levels. It provides a unique view of the DB regardless of how it is stored or viewed.
Various types of DB
Analytical DB / Data warehouses /
Distributed DB / Centralized DB
|
DB contains one / more tables that actually hold the data in the DB. Table holds records (rows), fields (columns), and cells of data.
1. Each table in a DB has a unique name that is used to identify it.
2. Columns (fields) in a DB also have a unique name and a data type associated with it.
3. Rows (records) in a table hold the actual data.
4. Constraints associated with a column such as PK or FK or Unique, etc.
1. An Oracle system is made up of at least one Instance and a DB.
2. The term Oracle DB is used to refer to the whole Oracle DB system (instances and databases).
3. Because of this, there is always some confusion for the beginners between the terms DB and instance.
Instance
|
Database
|
Instance is a collection of processes running on top of the operating system and the related memory that interacts with the data storage.
Instance is the interface between the user and the DB.
Instance needs to open the DB before manipulating data.
Multiple instances can open a single DB, but an instance cannot open multiple databases.
|
DB is the actual storage, which holds the collection of files.
A system intended for easily organizing, storing and retrieving large amounts of data, is called a DB.
DB is classified according to their content, such as document-text, bibliographic and statistical.
|
Processes capable of communicating with the client and accessing DB are provided by the instance.
These processes are background processes and they are not enough to maintain the ACID principle in the DB.
Instance also uses few other components such as memory cache and buffers.
|
3 levels of abstraction in DB architecture
4. External level defines how the users view the data. A single DB can have multiple views.
5. Internal level defines how the data is physically stored.
6. Conceptual level is the communication medium between internal and external levels. It provides a unique view of the DB regardless of how it is stored or viewed.
|
3 parts of Instance
1. SGA (System Global Area): SGA is a temporary shared memory structure, which has a life span of the instance startup to its shutdown.
2. PGA (Program Global Area)
3. Background processes.
|
Various types of DB
· Analytical DB
· Data warehouses
· Distributed DB
· Centralized DB
|
Components of a DB
1. Control files keep track of all the data files and redo files. It also helps keep the DB integrity intact by keeping track of the System Change Number (SCN), timestamps and other critical information such as backup/recovery information.
2. Data files keep the actual data. At the time of DB creation, at least two data files are created. These files are physically seen by the DBA (DB Administrator). File operations such as renaming, resizing, adding, moving or dropping can be carried out on data files.
3. Redo log files (also known as online redo logs), keep the information regarding the changes to the DB with the chronological information. This information is needed in case the user needs to redo all or some of the modifications on the DB.
4. Password files (optional)
It is a set of properties that guarantee DB transactions are processed reliably.
Transaction is a single logical operation on the data.
When a transaction processing system creates a transaction, it will ensure that the transaction will have certain characteristics. The developers of the components that comprise the transaction are assured that these characteristics are in place. They do not need to manage these characteristics themselves. These characteristics are known as the ACID properties.
Atomicity
1. Atomicity requires that DB modifications must follow an "all or nothing" rule.
2. Each transaction is said to be atomic if one part of the transaction fails, the entire transaction fails and the DB state is left unchanged.
3. It is critical that the DBMS maintain the atomic nature of transactions in spite of any application, DBMS, operating system or hardware failure.
4. An atomic transfer cannot be subdivided and must be processed in its entirety or not at all.
5. Any updates that a transaction might affect on a system are completed in their entirety.
6. If for any reason an error occurs and the transaction is unable to complete all of its steps, then system is returned to the state it was in before the transaction was started.
Transactions can fail for several kinds of reasons:
· Hardware failure: A disk drive fails, preventing some of the transaction's DB changes from taking effect.
· System failure: The user loses their connection to the application before providing all necessary information.
· DB failure: E.g., the DB runs out of room to hold additional data.
· Application failure: The application attempts to post data that violates a rule that the DB itself enforces, such as attempting to insert a duplicate value in a unique column.
Example of an atomic transaction is an account transfer transaction.
The money is removed from account A then placed into account B. If the system fails after removing the money from account A, then the transaction processing system will put the money back into account A, thus returning the system to its original state. This is known as a rollback.
Consistency
1. A transaction enforces consistency in the system state by ensuring that at the end of any transaction the system is in a valid state.
2. If the transaction completes successfully, then all changes to the system will have been properly made, and the system will be in a valid state.
3. If any error occurs in a transaction, then any changes already made will be automatically rolled back.
4. This will return the system to its state before the transaction was started.
5. Since the system was in a consistent state when the transaction was started, it will once again be in a consistent state.
6. Consistency states that only valid data will be written to the DB.
If a transaction consisted of an attempt to delete a record referenced by another, each of the following mechanisms would maintain consistency:
· Abort the transaction, rolling back to the consistent, prior state;
· Delete all records that reference the deleted record (this is known as cascade delete); or,
· Nullify the relevant fields in all records that point to the deleted record.
Example of an atomic transaction is an account transfer transaction.
The system is consistent if the total of all accounts is constant. If an error occurs and the money is removed from account A and not added to account B, then the total in all accounts would have changed. The system would no longer be consistent. By rolling back the removal from account A, the total will again be what it should be, and the system back in a consistent state.
Isolation
1. When a transaction runs in isolation, it appears to be the only action that the system is carrying out at one time.
2. If there are two transactions that are both performing the same function and are running at the same time, transaction isolation will ensure that each transaction thinks it has exclusive use of the system.
3. This is important in that as the transaction is being executed, the state of the system may not be consistent.
4. The transaction ensures that the system remains consistent after the transaction ends, but during an individual transaction, this may not be the case.
5. If a transaction was not running in isolation, it could access data from the system that may not be consistent. By providing transaction isolation, this is prevented from happening.
Durability
1. A transaction is durable in that once it has been successfully completed; all of the changes it made to the system are permanent.
2. There are safeguards that will prevent the loss of information, even in the case of system failure.
3. By logging the steps that the transaction performs, the state of the system can be recreated even if the hardware itself has failed.
4. The concept of durability allows the developer to know that a completed transaction is a permanent part of the system, regardless of what happens to the system later on.
Entity-relationship modeling (ERM) is the process of coming up with an abstract and conceptual representation of data.
ER diagrams are the product of entity-relationship modeling. ER diagrams are drawn using entities, attributes and other symbols (such as relationships).
Entity
|
Attribute
|
Entity represents a thing that can exist independently and that can be identified uniquely.
|
Properties of Entities are called Attributes
|
Entity represents a class, group or category of similar objects.
|
Attributes represent a sub group of information of the object represented by the entity.
|
Entities are represented as Tables.
(Each row representing individual instances)
|
Each Column represents the Attributes of these Entities (tables)
|
In order to differentiate between individual instances (rows) of the entity, one or more attribute fields (columns) with unique values (for all instances) can be selected as a key (PK).
Examples:
1) The social security number attribute (which is unique for all employees) is often used as the PK of an Employee table. Sometimes multiple attributes can makeup the PK as well.
2) Should the address of the employee represented as an Attribute or Entity (connected to employee entity through a relationship)?
A general rule of thumb is, if an employee has more than one address, then the address must be an entity (because attributes are not set-valued). Similarly, if the structure of the address is important to capture, then again the address should be an entity (because attributes are atomic).
MySQL and Oracle are owned by Oracle Corporation
MySQL
|
Oracle
|
MySQL is an open source DBMS
|
Oracle DB is a object-RDMS.
|
Latest version 5.6 (released in April 2011)
|
Latest version 11g (released in 2007)
|
MySQL supports more platforms (> 22) as compared to Oracle.
| |
It is highly popular because of its
• high reliability
• ease of use
• high performance
• provides wide variety of DB tools, services, training and support
It is used for many latest applications that are built on Apache, Linux, Perl/PHP etc.
|
Oracle 11g delivers high quality services such as:
1. Doubles DBA productivity
2. Eliminates data center redundancy and maximizes availability.
3. Consolidates and clusters enterprise applications onto scalable, fast and reliable private clouds.
4. Reduces the risk of change by doubling the DBS productivity.
|
1. Enterprise Edition (EE): This edition provides OLTP DB applications and also delivers high quality performance. Its capabilities include rollback, row level locking, full commit and crash recovery. To manage as well as improve performance of large DB systems, DB partitioning is also allowed by this edition. EE includes MySQL Enterprise Backup, Enterprise Monitor, Query Analyzer and MySQL WorkBench.
2. Standard Edition (SE): This edition also provides OLTP applications as well as high performance. It also includes InnoDB that makes it ACID compliant and a transaction-safe DB. To deliver scalable applications and high performance, replication is also allowed by this DB system.
3. Classic Edition: It is the ideal DB system for OEMs, VARs and ISVs that use MyISAM storage engine to develop read intensive applications. It is easy to use and requires low administration. However, this edition is only for VARs, ISVs and OEMs. One can easily upgrade to more advanced editions from the classic edition.
4. Community Edition: is the freely downloadable version. It is available under the GPL license and is supported by a huge and active community of open source developers.
MySQL Community Edition includes: Views, Stored Procedures, Triggers, Information Schema, Pluggable Storage Engine Architecture, Multiple Storage Engines, MySQL Replication, MySQL Partitioning, MySQL Connectors (ODBC, JDBC, .NET, etc), and MySQL Workbench
|
Oracle DB different editions
1. Enterprise Edition (EE): This edition provides scalability, reliability, and security on single or clustered servers running UNIX, Windows and Linux. Its benefits include protection from human error, server failure, planned downtime reduction and site failure. This version also provides online analytic processing, data mining and warehousing.
2. Standard Edition (SE): This edition is affordable as well as full featured DB system. It is easy to manage and can be scaled up easily in case of increase in demand. Oracle RAC is also included in this version.
3. Standard Edition One (SE1): It is also a full featured version but supports up to two sockets. Its functions are similar to the SE and it can be easily upgraded to EE. It gives the fastest performance even at low cost.
4. Express Edition (EX)
5. Oracle DB Lite for mobile devices
|
