How does it work? The bitmap index stores the column values in bits.

Each bit represents a single value. For example, the gender column has two possible values: Male and Female. three bit will be used in the bitmap to capture the index on the gender column. A good example can be seen in reference 1. So the more distinct value is, the more space is required to store the bitmap.Internally, the database engine, like Oracle, uses a map function to converts the bit location to the distinct value. (See reference #2) Many bitmap indexes can be used together since database can merge it, so this can improve the response time. (See Reference #3 for the example of merging the index on Marital Status and Region) When to use it? 1. Low cardinality Some dabase vendor, like Oracle, provides very practical suggestion -(See Reference #3 and 4)

If the number of distinct values of a column is less than 1% of the number of rows in the table, or if the values in a column are repeated more than 100 times, then the column is a candidate for a bitmap index. B-tree indexes are most effective for high-cardinality data: that is, data with many possible values, such as CUSTOMER_NAME or PHONE_NUMBER. There are 100 or more rows for each distinct value in the indexed column. When this limit is met, the bitmap index will be much smaller than a regular index, and you will be able to create the index much faster than a regular index.

2. No or little insert/update Updating bitmap indexes take a lot of resources. Here are the suggestions: (See Reference 5)

Building and maintaining an index structure can be expensive, and it can consume resources such as disk space, CPU, and I/O capacity. Designers must ensure that the benefits of any index outweigh the negatives of index maintenance. Use this simple estimation guide for the cost of index maintenance: each index maintained by an INSERT, DELETE, or UPDATE of the indexed keys requires about three times as much resource as the actual DML operation on the table. What this means is that if you INSERT into a table with three indexes, then it will be approximately 10 times slower than an INSERT into a table with no indexes. For DML, and particularly for INSERT-heavy applications, the index design should be seriously reviewed, which might require a compromise between the query and INSERT performance.3. Multiple Columns =One of the advantage is that multiple bitmap indexes can be merged and the column does not have to selective!

More than one column in the table has an index that the optimizer can use to improve performance on a table scan. (See reference 6) Combining bitmap indexes on non-selective columns allows efficient AND and OR operations with a great number of rowids with minimal I/O. (Reference 5)

JOINS IN ORACLE-different joins in oracle with examples

1. The purpose of a join is to combine the data across tables. 2. A join is actually performed by the where clause which combines the specified rows of tables. 3. If a join involves in more than two tables then oracle joins first two tables based on the joins condition and then compares the result with the next table and so on. 1 2 3 4 5 6 TYPES Equi join Non-equi join Self join Natural join Cross join Outer join

Left outer Right outer Full outer

7 8

Inner join Using clause 9 On clause Assume that we have the following tables. SQL> select * from dept;

DEPTNO 10 20 30

DNAME INVENTORY FINANCE HR

LOC HYBD BGLR MUMBAI

SQL> select * from emp;

EMPNO 111 222 333 444

ENAME saketh sudha jagan madhu

JOB analyst clerk manager engineer

MGR 444 333 111 222

DEPTNO 10 20 10 40

1. EQUI JOIN A join which contains an equal to = operator in the joins condition. Ex: SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno;

EMPNO 111 333 222

ENAME saketh jagan sudha

JOB analyst manager clerk

DNAME INVENTORY INVENTORY FINANCE

LOC HYBD HYBD BGLR

Using clause SQL> select empno,ename,job ,dname,loc from emp e join dept d using(deptno);

EMPNO 111 333 222

ENAME saketh jagan sudha

JOB analyst manager clerk

DNAME INVENTORY INVENTORY FINANCE

LOC HYBD HYBD BGLR

On clause SQL> select empno,ename,job,dname,loc from emp e join dept d on(e.deptno=d.deptno);

EMPNO 111 333 222

ENAME saketh jagan sudha

JOB analyst manager clerk

DNAME INVENTORY INVENTORY FINANCE

LOC HYBD HYBD BGLR

2. NON-EQUI JOIN A join which contains an operator other than equal to = in the joins condition. Ex: SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno > d.deptno;

EMPNO 222 444 444 444

ENAME sudha madhu madhu madhu

JOB clerk engineer engineer engineer

DNAME INVENTORY INVENTORY FINANCE HR

LOC HYBD HYBD BGLR MUMBAI

3. SELF JOIN Joining the table itself is called self join. Ex:

SQL> select e1.empno,e2.ename,e1.job,e2.deptno from emp e1,emp e2 where e1.empno=e2.mgr;

EMPNO 111 222 333 444

ENAME jagan madhu sudha saketh

JOB analyst clerk manager engineer

DEPTNO 10 40 20 10

4. NATURAL JOIN Natural join compares all the common columns. Ex: SQL> select empno,ename,job,dname,loc from emp natural join dept;

EMPNO 111 333 222

ENAME saketh jagan sudha

JOB analyst manager clerk

DNAME INVENTORY INVENTORY FINANCE

LOC HYBD HYBD BGLR

5. CROSS JOIN This will gives the cross product. Ex: SQL> select empno,ename,job,dname,loc from emp cross join dept;

EMPNO 111 222 333 444 111 222 333 444 111 222 333 444

ENAME saketh sudha jagan madhu saketh sudha jagan madhu saketh sudha jagan madhu

JOB analyst clerk manager engineer analyst clerk manager engineer analyst clerk manager engineer

DNAME INVENTORY INVENTORY INVENTORY INVENTORY FINANCE FINANCE FINANCE FINANCE HR HR HR HR

LOC HYBD HYBD HYBD HYBD BGLR BGLR BGLR BGLR MUMBAI MUMBAI MUMBAI MUMBAI

6. OUTER JOIN Outer join gives the non-matching records along with matching records. LEFT OUTER JOIN This will display the all matching records and the records which are in left hand side table those that are not in right hand side table. Ex: SQL> select empno,ename,job,dname,loc from emp e left outer join dept d on(e.deptno=d.deptno); Or SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno=d.deptno(+);

EMPNO 111 333 222 444

ENAME saketh jagan sudha madhu

JOB analyst manager clerk engineer

DNAME INVENTORY INVENTORY FINANCE

LOC HYBD HYBD BGLR

RIGHT OUTER JOIN This will display the all matching records and the records which are in right hand side table those that are not in left hand side table. Ex: SQL> select empno,ename,job,dname,loc from emp e right outer join dept d on(e.deptno=d.deptno); Or SQL> select empno,ename,job,dname,loc from emp e,dept d where e.deptno(+) = d.deptno;

EMPNO 111 333 222

ENAME saketh jagan sudha

JOB analyst manager clerk

DNAME INVENTORY INVENTORY FINANCE HR

LOC HYBD HYBD BGLR MUMBAI

FULL OUTER JOIN This will display the all matching records and the non-matching records from both tables. Ex: SQL> select empno,ename,job,dname,loc from emp e full outer join dept d on(e.deptno=d.deptno);

EMPNO 333 111 222 444

ENAME jagan saketh sudha madhu

JOB manager analyst clerk engineer

DNAME INVENTORY INVENTORY FINANCE HR

LOC HYBD HYBD BGLR MUMBAI

7. INNER JOIN This will display all the records that have matched. Ex: SQL> select empno,ename,job,dname,loc from emp inner join dept using(deptno);

EMPNO 111 333 222

ENAME saketh jagan sudha

JOB analyst manager clerk

DNAME INVENTORY INVENTORY FINANCE

LOC HYBD HYBD BGLR

SUBSTR is used to extract a set of characters from a string by specificing the character starting position and end position and length of characters to be fetched. example substr('hello',2,3) will return 'ell' INSTR is used to find the position of any particular character in a word which returns numeric value. instr('hello','e') - will return the position of 'e' as 2

Hi Rajeev, Find the venue details. Timing: 3:00 PM.

Contact person : Manish Verma

TECH MAHINDRA / Resource Management Group
Plot 58 A & B, NSEZ, Phase-2, Noida, UP - 201305, INDIA Telephone: +91 120 6176000 - 4680 Mobile: +91 9999 661949

Carry your resume and valid id proof.