Explain: Integrity Constraints, Index, Extenta.) Integrity Constraints
It can be called as a declarative way in order to define a business rule for a table's column
- It can be called as an optional structure which is associated with a table for direct access to the rows
- Index can be created for one or more columns in a table
- It can be defined as a specific number of contiguous data blocks in single allocation.
- It is used to store a specific type of information.
What is ANALYZE command used for?ANALYZE command is used to perform various functions on index, table, or cluster, as listed below:
- It helps in dentifying migrated and chained rows of the table or cluster.
- It helps in validating the structure of the object.
- It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.
- It helps in deleting statistics used by object from the data dictionary.
a.) List the types of joins used in writing SUBQUERIES.- Self join
- Outer Join
b.) List the various Oracle database objects.- TABLES
a. Explain i.) Rename ii.) Alias.- Rename - It is a permanent name provided to a table or column.
- Alias - It is a temporary name provided to a table or column which gets over after the execution of SQL statement.
b.) What is a view?- It is virtual table which is defined as a stored procedure based on one or more tables.
a.) What are the varoius components of physical database structure of Oracle database?Oracle database comprises of three kinds of files:
- Redo log files,
- Control files.
b.) List out the components of logical database structure of Oracle database.- Tablespaces
- Database's schema objects.
a.) What do you mean by a tablespace?- These are the Logical Storage Units into which a database is divided.
- It is used to group together the related logical structures.
b.) What is Control File used for?Control File is used for:
- Database recovery.
- Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database operation to go ahead.
a.) What is a synonym? What are its various types?A synonym can be called as an alias for a table, view, sequence or program unit.
It is basically of two types:
- Private - Only the owner can access it.
- Public - Can be accessed by any database user.
b.) What are the uses of synonyms?- Mask the real name and owner of an object.
- Provide public access to an object
- Provide location transparency for tables, views or program units of a remote database.
- Simplify the SQL statements for database users.
What do you mean by a deadlock?- When two processes are waiting to update the rows of a table which are locked by another process, the situation is called a deadlock.
- The reasons for it to happen are:
- lack of proper row lock commands.
- Poor design of front-end application
- It reduces the performance of the server severely.
- These locks get automatically released automatically when a commit/rollback operation is performed or any process is killed externally.
a.) What suggestions do you have to reduce the network traffic?Following are some of the actions which can be taken to reduce the network traffic:
- Use snapshots to replicate data.
- Use remote procedure calls.
- Replicate data in distributed environment.
b.) What are the various types of snapshots ?There are two types of snapshots:
- Simple snapshots - Based on a query that does not contain GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.
- Complex snapshots- which contains atleast any one of the above.
What is a sub query? What are its various types?- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
- A sub query is added in the where clause of the main query.
There can be two types of subqueries:
a.) Correlated sub query
- It can reference column in a table listed in the from list of the outer query but is not as independent as a query. .
b.) Non-Correlated sub query
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query