Oracle - Types of Objects/Entities

Types of tables

• Relational table
• Object  table
• Temporary table
• Index organized table
• Cluster
• External table
• XMLtype table
• Partitioned table

Types of Constraints

• Not Null
• Unique (Why Unique key doesn't have index by default)
• Check
• Primary Key (Unique and Not Null)
• Foreign Key
• REF (Constrain -> New to me)

Types of Index

• BTree (Balanced tree)
• Bitmap
• Function Based
• Index Organized Table
• Cluster
• Bitmap Join
• Domain

Types of Joins

• Cross Join / Cartesian product
• Natural (Find the same column in two table by default) / Inner Join
• (USING (act_id)  | ON (a.act_id = b.act_id)
• Outer Join
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Self Join
• Equi / Anti (Full table scan) / Range Join (use index but not good as equi)
• Mutable (3 or more table join) / Complex Join  (also apply filters)