Oracle B Tree and Bitmap Index | Internal working

Oracle B-tree indexes
B-tree indexes are ordered lists of values divided into ranges with a key associated with a row or range of rows, thereby providing excellent retrieval performance for queries such as exact match and range searches.
This type of index contains 3 types of blocks - the root block, one or more branch blocks and one or more leaf blocks. The root block holds keys and pointers to the branch blocks which in turn hold pointers to the leaf blocks which contain the key (data) values that have been indexed and the rowids of the rows in the Oracle database table associated with each key value.
Branch blocks hold the minimum key prefix needed to be able to choose the required branch and a pointer to the child block containing the key. The number of keys and pointers is limited by the block size. Index leaf blocks are double linked - each block contains pointers to the next and previous leaf blocks.
B-tree indexes are always balanced because the height of the index (the number of levels in the index) is the same throughout the index. In other words, the number of blocks that have to be read to find the rowid associated with any particular index value is not dependent on that value. For example if you had an index on the last_name column of the employee table in the sample Oracle database the number or blocks that would need to be read to find the rowid associated with "Ernst" would be the same as for "King".
The height of a b-tree index is the number of blocks required to go from the root block to a leaf block. For an index with one level of branch blocks the height is 3 (1 root block + 1 branch block + 1 leaf block).

Oracle bitmap indexes
Bitmap indexes are completely different to b-tree indexes. Whereas b-tree indexes are ideal for storing highly selective/unique values (low cardinality), bitmap indexes are designed to store values of low cardinality where the same value can be found in a large proportion of the table. An example of this might be the part number column in the sales table, or gender in the employee table.
With this type of index, each distinct value of the column is associated with a set of bits representing the rows in the table (one bit for each row in the table). Each bit has a value of 0 or 1. If the bit is set then then the row associated with that bit has that value in the column.
Bitmap indexes are expensive to maintain as the whole bitmap has to be rebuilt after any DML operation (insert/update/delete) on the table.

Oracle Triggers

Triggers are an important part of Oracle. And I know only them to be the objects which are event-driven. 

That means they cannot be executed by the plsql code and rather they depend upon some event to occur. That event can be any DML operation a table or a DDL level operation on the database.

Definition - Triggers are procedures which are event-driven.

Timing points(Events which trigger triggers) - BEFORE | AFTER

ROW LEVEL | STATEMENT LEVEL

So if there are multiple triggers defined on a table, from the above, we can define the order of the trigger execution. 

Before statement >>> Before each row >>> DML >>> After each row >>> After statement.

FOLLOWS - If there are multiple triggers defined at the same timing point for a table, then the order in which they will be executed, is not defined; and is decided by Oracle server.

But, in Oracle 11g, there a new keyword FOOLOWS created. through this keyword, we can define which trigger is to follow which trigger.

COMPOUND TRIGGER - We define a trigger for a particular event, like before the row of after the statement, but Oracle 11g, brought this new way to define a single trigger which can define code blocks to run at different events. This is compound trigger.

Compound trigger have many benefits over normal triggers. And they have many rules as well.

|||||||||||||||||||||||||||||||||||||||||||||||

BEFORE and AFTER triggers are available for tables; INSTEAD OF triggers is available for views only.

Up to 3 triggering events can be connected and specified using the keyword OR. INSERT OR UPDATE OR DELETE

UPDATE can be optionally followed by OF; specifying the columns whose update will run the trigger.

the special columns NEW and OLD are only available if the trigger is row-level.
CREATE TRIGGER trig_tt
AFTER INSERT
REFERENCING NEW AS newrec
OR UPDATE
ON tab_tt
FOR EACH ROW
WHEN(newrec.a1 <= 100)
BEGIN
   INSERT INTO tem VALUES (:newrec.b1, :newrec.c1);
END trig_tt;

As you can see from the above code, the special variable NEW is used without colon(:) in the WHEN clause.

|||||||||||||||||||||||||||||||||||||||||||||||
To check the errors in a trigger, use:
SHOW ERRORS TRIGGER trigger_name
|||||||||||||||||||||||||||||||||||||||||||||||
list all user created triggers -
SELECT trigger_name FROM USER_TRIGGERS;
|||||||||||||||||||||||||||||||||||||||||||||||
 MUTATING TABLE ERRORS
when the trigger queries or tries to modify a table which is mutating already; Oracle will report a mutating trigger error.

To Aviod mutating table errors:
> A row-level trigger must not query or modify a mutating table. NEW and OLD values are still accessible.
> A statement-level trigger must not query or modify a mutating table of the trigger is fired as the result of a CASCADE DELETE.

|||||||||||||||||||||||||||||||||||||||||||||||
FUN FACTS
Oracle puts a size limit of 32 kb to triggers. This size is big enough to write over 4000 characters. But if you still need more code, then the best idea is to create named procedures and call them in the code of triggers.

Oracle fires all triggers of a type together and then triggers of another type.
We can't control which type will be fired when.