Basic Concepts

Question 1

Difference between primary key and unique key?

Answer 1

1. PK is used to uniquely identify a row, can only have 1
   UK is used when want to enforce unique constraints on a column/a group of columns, can have several
2. PK does not allow NULL, UK allows
3. PK auto have a clustered index
   UK have a non-clustered index

Question 2

Clustered Index and non Clustered index

Answer 2

CI - is a special type of index that reorders the way records in the table are physically stored. … The leaf nodes of a clustered index contain the data pages.

NON-CI is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk

Question 3

Index and partition

Answer 3

Local partitioned index -

1) Index within each individual partition
2) Are best suited to data warehouse application 0 used can extract that partition for analysis
3) Add/delete partition will not impact the index
4) Index are not have partition column information, not helping much when used want to certain range of data regardless of partition, the engine will need to search in every partition for the rows.

Global index - index cross the whole table
Global partitioned index - index have a different partition scheme than the whole table partition
1) Engine will only need to search the partition that the index range includes
2)will go invalid when the partition changes.

Question 4

How does partition help?

Answer 4

1. Using partition key on where clause, only search in that partition which is much faster

Range Partitioning
List Partitioning
Composite Partitioning

Question 5

What is a View?

Answer 5

1. searchable object(a virtual table) in a database that is defined by a query
2. Can be created from 1 table/multiple tables
3. contains all/subset of the tables based on the query condition
4. View from 1 table can be updated, from multiple tables are usually read only

Adv:
1.Simplyfy complex queries
2. restrict user to certain data
Disadv:
1. Performance issue: every time view is used the back query will be invoked.
2.Structure tightly associated with underlying table
EXAMPLE - 
CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;

Question 6

DB server - Tablespaces and data files

Answer 6

Oracle stores data logically in tablespaces and physically in datafiles associated with the corresponding tablespace

https://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch4.htm

Question 7

Schema

Answer 7

A schema is a collection of schema objects. Examples of schema objects include tables, views, sequences, synonyms, indexes, clusters, database links, procedures, and packages