Oracle Interview Handbook
Schema: Logical container of data structures called schema objects
Schema Objects:
1. Table :
- Basic unit of data storage in oracle database.
- Holds user accessible date
- Stotred in rows and columns
2. Indexes :
- Provides direct and fast access to rows. Frequemtly asked questions on oracle indexes
3. Partitions:
Partitioning is functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, so that these database objects can be managed effectively.
4. Views:
A view is a virtual table, which provides access to a subset of column from one or more table. A view can derive its data from one or more table. An output of query can be stored as a view.A view in oracle is nothing but a stored sql scripts. Views itself contain no data.
5. Sequences:
It is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.
6. Synonyms:
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.
7. Dimension:
8. Users :
9. Roles :
10. Contexts :
11. Directory Objects :
-------------------------------------------------------------------------------------------------------------
Tablespaces : Database is divided into logical storage units called tablespaces.
Used to group related logical structures together.
Materialized views : Basically used to improve the performance of query since it contains results of query.Used for reporting instead of a table for faster execution.
Rollback:
Used to undo work done in the current transaction.
Syntax:
Rollback;
Rollback to savepoint s1;
NULL : Niether 0 nor it is a blank space.It is unknown value uses 4 bytes of space in SQL.
Flashback : Rewinds the database to a target time, SCN or log sequence number.
SCN: The system change number (SCN) is Oracle's clock - every time we commit, the clock increments. The SCN just marks a consistent point in time in the database.
Transaction : Sequence of SQL statements that oracle database treats as single unit.
Partitioning is functionality that allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, so that these database objects can be managed effectively.
4. Views:
A view is a virtual table, which provides access to a subset of column from one or more table. A view can derive its data from one or more table. An output of query can be stored as a view.A view in oracle is nothing but a stored sql scripts. Views itself contain no data.
5. Sequences:
It is used to generate a number sequence. This can be useful when you need to create a unique number to act as a primary key.
6. Synonyms:
A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects. You generally use synonyms when you are granting access to an object from another schema and you don't want the users to have to worry about knowing which schema owns the object.
7. Dimension:
8. Users :
9. Roles :
10. Contexts :
11. Directory Objects :
-------------------------------------------------------------------------------------------------------------
Tablespaces : Database is divided into logical storage units called tablespaces.
Used to group related logical structures together.
Materialized views : Basically used to improve the performance of query since it contains results of query.Used for reporting instead of a table for faster execution.
Rollback:
Used to undo work done in the current transaction.
Syntax:
Rollback;
Rollback to savepoint s1;
NULL : Niether 0 nor it is a blank space.It is unknown value uses 4 bytes of space in SQL.
Flashback : Rewinds the database to a target time, SCN or log sequence number.
SCN: The system change number (SCN) is Oracle's clock - every time we commit, the clock increments. The SCN just marks a consistent point in time in the database.
Transaction : Sequence of SQL statements that oracle database treats as single unit.
Difference between delete and truncate:
- We cannot rollback truncateO
- Delete from tablename; ->DML ->hence slower
- Truncate table tablename -> DDL ->just moves the high water mark - > hence faster
Cluster:
- It is the optional method of storing table data.
- Made up of group of table that shares same datablock
- Tables are grouped together because they share same column and often used together.
e.g. EMP and DEPT table shares deptno column. When EMP and DEPT tables are clustered oracle database physically stores all rows of each department from both EMP and DEPT tables in the same data block .
PROS : Disk I/O reduced and access time improves for joins of clustered tables.
Cluster keys are columns that the clustered table have in common .Cluster keys are stored only once no matter how many rows of different tables contains the value.
CONS: Dont use cluster for tables that are frequently accessed individually.
Hash cluster:
