07 May 20 · rajneesh4u · #general ·   Bookmark   ×

How indexing works in DB for given example?

Hello Experts,

Let me seek your help to understand how indexing works through below example:

 I have 2 tables : Table1 and Table 2.

I have created indexes -

on table 1

index(Column1, column2, column3)

on table 2

index(field1)

Now my SQL is

select table1.column 5, table1.column 6

from table1, table2

where

table1.column1='XYZ'

and table1.column2=table2.field1

My queries:

1) Indexing will be used even column 3 is not used in join?

2) There will be only one index table space for table1?

3) table 1 and table2 index space will be stored on disc?

4) Every time query is being executed there will be 2 i/o operation as below?

a) Bring index space for table1 from disc to memory and post query execution -memory to disc = 1 I/O

b) Bring index space for table2 from disc to memory and post query execution -memory to disc = 1 I/O

Now I changed the approach as below and created indexes as below:

on table 1

index(Column1), Index(column2), Index(column3)

on table 2

index(field1)

Let;s say same sql is being executed:

select table1.column 5, table1.column 6

from table1, table2

where

table1.column1='XYZ'

and table1.column2=table2.field1

My queries related to this approach:

1) Indexing will be used even column 3 is not used in join?

2) There will be 3 index table space for table1 (than one in previous approach)?

3) Every time query is being executed there will be 3 i/o operation (than 2 in previous case) as below?

a) 2 i/o (one for index-column1 + one for index-column2) from disc to memory and post query execution -memory to disc

b) Bring index space for table2 from disc to memory and post query execution -memory to disc =1 i/o

Kindly bear with me if queries are very basic in nature and any ambiguity , in case.

rajneesh4u

posted on 07 May 20

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds




I am not very familiar with Oracle. Lets see if anyone from the community can answer this

Also the question seems to be ambiguous to me, not very clear on what you are looking for

Thanks npack, regret for ambiguity, I have modified query with an attempt to avoid it. However please bear with me if it is still ambiguous as it is generic in nature.


Community Software by Hittly