Bitmap Index

Bitmap indexes are the most suitable for columns having very few unique values (very low cardinality). Ideally to choose the bitmap indexes as the right choice for a column the cardinality should be less than or equal to 0.05%.

Table should have no insert, update , delete or very few dml statements can act as good choice for bitmap indexes.

Bitmap indexes are highly compressed and usually take very less space when compared to B-tree indexes and are extensively used in data warehouses.

Advantage of Bitmap Indexes
Due to the nature of highly compressed structure of an bitmap index, Data can be accessed much faster than traditional B-tree indexes.

Bitmap index represent a good trade-off between disk usage and Cpu cost. A bitmap index requires more cpu cycles to decompress but requires less amount of work from disk I/O.

Disadvantages of Bitmap Indexes
Bitmap Indexes are traditionally meant to be developed for data warehouses. A modification to a bitmap index requires more work than a modification to b-tree indexes.

Deadlock’s On bitmap indexes
Bitmap indexes are not suitable for large concurrent single row Dml operations.

If there are 3 or more sessions simultaneously working to insert into a table which is bitmap indexed a dead lock situation can occur.

use_hash hint

The use_hash hint is the most useful for joining a medium size table with a large table. The throughput is high for use_hash hint when compared to Use_NL hint. use_hash hint is mostly used in datawarehousing environments where the overall query execution should be faster returning all rows rather than first row response time.

The use_has hint requests a hash join against the specified tables. Essentially a hash join is a technique wherey Oracle loads the rows from the driving table (the smallest table,after the where clause) into user global area( RAM) defined by the hash_area_size in the init.ora file.

Oracle then uses a hashing technique to locate the rows in the Larger second table.

The following query is an example of using Use_hash hint

Select /*+ use_hash( e,b) */ e.ename, b.bon, hiredate, from emp e, bonus b where e.ename=b.ename;

Source:http://www.expertsharing.com/2008/01/25/use_hash-hint-in-oracle/

ROWID in Oracle

For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row:

The data object number of the object

The data block in the datafile in which the row resides

The position of the row in the data block (first row is 0)

The datafile in which the row resides (first file is 1). The file number is relative to the tablespace.

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

Rowid values have several important uses:

They are the fastest way to access a single row.

They can show you how the rows in a table are stored.

They are unique identifiers for rows in a table.

SELECT ROWID, last_name
FROM employees
WHERE department_id = 20;

Joining Conditions in Oracle

If we have so many tables in FROM clause its very hard to keep track of joining conditions in WHERE Clause. So here is the simple way to remember it.
If we have N tables in FROM Clause, we need at least N-1 jion conditions in WHERE Clause to avoid a Cartesian product.

SELECT E.EMPID,E.ENAME,D.DEPTNO,D.DNAME
FROM EMPP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO

You can see two tables and 1 condition...

Triggers in Oracle

Triggers can be used for many things, including maintaining Complex Integrity constraints, auditing information and so many things. But every one will take a moment to tell how many types of triggers can we create on a table.

The major parts of the trigger are
Category Values Count
Statement INSERT, DELETE, UPDATE 3
Timing BEFORE or AFTER 2
Level ROW or Statement 2

So total types of trigger we can have on a table is 3*2*2 = 12.

Next Time onwords you dont have to remember anything, just follow the logic

Operators in PL/SQL

PL/SQL offers the same math operators like +, -, * and /, comparison operators, and logic constructs that SQL offers. The same operator precedence that exists in SQL also exists in PL/SQL, and that precedence can also be short-circuited using parentheses; so remember PEMDAS and you should be fine.

Some people use the following mnemonic to remember PEMDAS: Please Excuse My Dear Aunt Sally. It stands for Parentheses, Exponents, Multiplication, Division, Addition, and Subtraction.

Version numbering

Since version 7, Oracle's RDBMS release numbering has used the following codes:

Oracle7: 7.0.16 — 7.3.4
Oracle8 Database: 8.0.3 — 8.0.6
Oracle8i Database Release 1: 8.1.5.0 — 8.1.5.1
Oracle8i Database Release 2: 8.1.6.0 — 8.1.6.3
Oracle8i Database Release 3: 8.1.7.0 — 8.1.7.4
Oracle9i Database Release 1: 9.0.1.0 — 9.0.1.5 (patchset as of December 2003)
Oracle9i Database Release 2: 9.2.0.1 — 9.2.0.8 (patchset as of April 2007)
Oracle Database 10g Release 1: 10.1.0.2 — 10.1.0.5 (patchset as of February 2006)
Oracle Database 10g Release 2: 10.2.0.1 — 10.2.0.4 (patchset as of April 2008)
Oracle Database 11g Release 1: 11.1.0.6 — 11.1.0.7 (patchset as of September 2008)

The version-numbering syntax within each release follows the pattern: major.maintenance.application-server.component-specific.platform-specific.

For example, "10.2.0.1 for 64-bit Solaris" means: 10th major version of Oracle, maintenance level 2, Oracle Application Server (OracleAS) 0, level 1 for Solaris 64-bit.

Welcome

Hi

This is my first blog on Oracle. I would like to post Oracle information on this blog. I have one more blog on Microsoft Technologies where i got MVP award for my contribution to this IT world.

Keep watching...many things to come


---------------Vj