8. DB OBJECTS
1. NORMAL TABLE:
Create table
(
Eno number
, Column_name varchar2(10)
) ORGANIZATION HEAP;
2. HEAP TABLE :
Create table
(
Eno number
, Column_name varchar2(10)
) ORGANIZATION HEAP;
3. GLOBAL TEMPORARY TABLE :
Create table
(
Eno number
, Column_name varchar2(10)
) ON COMMIT DELETE ROWS; -- TRANSACTION LEVEL TABLE means once commit data will delete.
Create table
(
Eno number
, Column_name varchar2(10)
) ON COMMIT PRESERVE ROWS; -- -- SESSION LEVEL TABLE means once session is closed. Data will auto deleted.
Structure is permanent but data is temporary in GTT. We can create synonym, view, index, constraint.
4. EXTERNAL TABLE
Only structure will be stored in database. The data is outside the database. We use oracle loader like to load data in external table.
Create table emp_ext
(
Eno number
, ename varchar2(10)
) ORGANIZATION EXTERNAL
(DEFAULT DIRECTORY DIR1
ACESS PARAMETERS (RECORDS DELIMITED BY ","
FIELDS TERMINATED BY ",")
LOCATION ('ABC TXT')
);
5. INDEX ORGANIZED TABLE
Create table emp_ext
(
Eno number PRIMARY KEY
, ename varchar2(10)
)
ORGANIZATION TABLE;
6. PARTITION TABLE
Create table emp_ext
(
Eno number PRIMARY KEY
, ename varchar2(10)
)
PARTITION BY RANGE(eno) -- number column
PARTITION P1 VALUES LESS THAN (10),
PARTITION P2 VALUES LESS THAN (20),
PARTITION P3 VALUES LESS THAN (30)
);
7. CLUSTURED TABLE
Create cluster c_cluster(c1 number);
8. HASH TABLE
ORACLE COMPILER INTERNALLY CREATE SUCH TABLE. WE AS A USER CAN NOT CREATE HASH TABLE.
9. OBJECT TABLE
CREATE OR REPLACE TYPE address_type AS OBJECT
(
ENO NUMBER(3),
STREET VARCHAR2(5),
CITY VARCHAR(5)
)
> Type created
10. CREATE TABLE emp_type
(
ENO NUMBER(3),
ENAME VARCHAR2(5),
ADDRESS address address_type, -- OBJECT TYPE
SAL NUMBER(5)
);
> Table created
11. PL/SQL TABLE
12. NESTED TABLE
CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
Create table nested table
(
Id number,
, coll my_tab_t
)
NESTED TABLE coll STORE AS coll_tab;
13. VERTUAL TABLE
8.1. VIEWS
• What is a view?
• Importance of View.
• Types of Views
• Simple views
Simple views with WITH CHECK OPTION
Simple views with WITH READ ONLY option
• Composite views
• Inline views
• Materialized views
Read Only Materialized view
Updatable Materialized view
Complex Materialized view
• Force views
8.2. INDEXES
• What is a INDEX
• Necessity of Index
• Types of Indexes
Simple Index
Composite Index
Bitmap Index
Function based index
TYPES OF INDEXES
1. B-TREE INDEX
2. NORAMAL INDEX
3. UNIQUE INDEX
4. COMPOSITE INDEX
5. BITMAP INDEX
6. BITMAP JOIN INDEX
7. FUNCTION BASED NORMAL INDEX
8. FUNCTION BASED BITMAP INDEX
9. REVERSE KEY INDEX
10. ASCENDING INDEX
11. DESCENDING INDEX
12. GLOBAL INDEX
13. LOCAL INDEX
14. CLSTERED INDEX
15. IOT INDEX ORGANIZED TABLE
16. COMPRESSED INDEX
17. INVISIBLE INDEX
18. SORT OR NOSORT INDEX
19. UNUSABLE INDEX
20. VIRTUAL OR NOSEGMENT INDEX
• Use of cluster
• Creation of cluster
• Advantages of clusters
8.4. SEQUENCES
• Creating sequence
• Uses of sequences
Inserting sequence values
Updating column with sequence values
Altering sequence
Using sequence value as DEFAULT value
[ from oracle 12c ]
• Use of Synonym
• Types of Synonyms(PUBLIC,PRIVATE)
• How and when to create synonyms?