How does index created on separate columns works internally?

Question

I have a table Employee which has 10 columns . In my query where clause i am using column1 and column2 . If i create index on both columns i.e. column1 and column2 . Is there a possibility both index can be utilized ?

There can be two approaches :-

Approach1: For first where clause index is used and for second where clause result return by first clause is further filtered without using second index
Approach2: For first where clause index is used , get row ids. For second where clause index is used , get row ids and do intersection of both rowIds

How does index works here ?

update:-

If strategy for MySQL or Oracle is different then please consider oracle as DB

For Oracle possible duplicate to this question. And yes such behavior is definitively highly dependent on the RDBMS. — Marmite Bomber
– Marmite Bomber, Commented Oct 27, 2017 at 14:03

Marmite Bomber · Accepted Answer · 2017-10-27 15:35:54Z

For Oracle it depends all on the selectivity of the used where predicates.

In case that one predicate has a high selectivity and the other low, only one index will be used and the second predicate will be filtered on the table.

Here an example of the execution plan

 select * from tab where a = 250 and b = 2; --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 207 | 4 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 1 | 207 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | IDX_A | 1 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("B"=2) 2 - access("A"=250)

here the predicate on A return only few records(1, but predicate on B returns 501 rows) and only the index on column A is used.

select sum(case when a = 250 and b = 2 then 1 end) as cnt_ab, sum(case when a = 250 then 1 end) as cnt_a, sum(case when b = 2 then 1 end) as cnt_b from tab2; CNT_AB CNT_A CNT_B ---------- ---------- ---------- 1 1 501

In case that both indexes are not very selective, but the combination of the predicates is selective Oracle can used either conversion to BITMAP index or index join. Which access path is selected depends on the table statistics and optimizer settings. In my case I got the bitmap conversion and used INDEX_JOIN hint to get the other plan. Note that the index join is only possible if the query returns only the columns defined in indexes.

 select * from tab where a = 105 and b = 23;

Predicate selectivity

select sum(case when a = 105 and b = 23 then 1 end) as cnt_ab, sum(case when a = 105 then 1 end) as cnt_a, sum(case when b = 23 then 1 end) as cnt_b from tab; CNT_AB CNT_A CNT_B ---------- ---------- ---------- 200 21700 100000

Conversion to BITMAP

--------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 829 | 167K| 434 (1)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB | 829 | 167K| 434 (1)| 00:00:01 | | 2 | BITMAP CONVERSION TO ROWIDS | | | | | | | 3 | BITMAP AND | | | | | | | 4 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 5 | INDEX RANGE SCAN | IDX_A | 21552 | | 45 (0)| 00:00:01 | | 6 | BITMAP CONVERSION FROM ROWIDS | | | | | | |* 7 | INDEX RANGE SCAN | IDX_B | 21552 | | 190 (1)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 5 - access("A"=105) 7 - access("B"=23)

Index Join

 select /*+ INDEX_JOIN(a idx_a idx_b) */ a,b from tab where a = 105 and b = 23; --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 829 | 5803 | 235 (1)| 00:00:01 | |* 1 | VIEW | index$_join$_001 | 829 | 5803 | 235 (1)| 00:00:01 | |* 2 | HASH JOIN | | | | | | |* 3 | INDEX RANGE SCAN| IDX_A | 829 | 5803 | 45 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN| IDX_B | 829 | 5803 | 190 (1)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("A"=105 AND "B"=23) 2 - access(ROWID=ROWID) 3 - access("A"=105) 4 - access("B"=23)

Rick James · Accepted Answer · 2020-01-13 00:42:54Z

(MySQL)

The "rowid" approach is very rare in MySQL. This is probably because the PRIMARY KEY is used for such, and it goes directly to the row(s).

You are asking about a "composite" index of, say, INDEX(a, b)

WHERE a=1 AND b=2 -- Good index WHERE b=1 AND a=2 -- Good index (order does not matter _in the WHERE_) WHERE a=1 AND b>2 -- Good index WHERE b=1 AND a>2 -- Will ignore `b` in the index, but still use `a`

More: http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Where row-id might be used: http://mysql.rjweb.org/doc.php/index1 , but less efficient than a composite index.

Collectives™ on Stack Overflow

How does index created on separate columns works internally?

2 Answers 2

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Linked

Related