Oracle- performing view- creating index on join table

Question

I need to perform this view:

  SELECT BMITEMCART, BMITEMXDSC, BMITEMCSTATO, 
  CASE 
  WHEN SCRECEXCONF = '2'  THEN 'CONFORME'
  WHEN SCRECEXCONF = '3'  THEN 'NON CONFORME'
  WHEN SCRECEXCONF = '4'  THEN 'PRESUNTO CONFORME'
  WHEN SCRECEXCONF = '5'  THEN 'NON APPLICABILE'
  ELSE '???????'
  END AS CERTIF
, BMITEMCTIPART
FROM PROD.BMITEM
LEFT OUTER JOIN PROD.SCRECE
             ON SCRECECCODART  = BMITEMCART AND
                SCRECENIDCERT  = 8          AND
                SCRECETFINE   IS NULL       AND
                SCRECEDFNVAL  IS NULL;

I read that creating indexes on the join tables would help. How should i create the index on the join table SCRECE? Should i create one index on the SCRECECCODART column, one on the SCRECENIDCERT column, one on the SCRECETFINE column and one on the SCRECEDFNVAL column, or what else?

Thank you in advance.

That depends on the number of distinct values in the columns and their usage. You can create the BITMAP index on the column having lower number of distinct values and you can create the B-Tree index on the other columns combined. — Popeye, Jun 25 '20 at 07:35
Please note that based on the information you provided *close to nothing* can be recommended. You must at least add *alias* to the columns in the join predicate that we can see the source table. Also the number of rows in both sides of the join is relevant. See [here](https://stackoverflow.com/a/34975420/4808122) for further suggestion, what info is needed. — Marmite Bomber, Jun 25 '20 at 10:20
This advice @Tejash should be always accompanied with a warnig of "Never use BITMAP index on heavy updated tables!" — Marmite Bomber, Jun 25 '20 at 10:24

score 1 · Answer 1 · answered Jun 25 '20 at 11:50

You should take those general advice Index is good, Full Table Scan is bad! with with some care.

It always depends from the context of the usage.

I reformulate your example removing the not relevant parts and using neutral column names

I also added alias to qualify the columns, so it is clear from which table the columns are taken.

SELECT a.id, a.col1, a.col2, a.col3,
       b.col4
FROM a
LEFT OUTER JOIN b
             ON a.id  = b.fk_id AND
                b.col1 = 8 AND
                b.col2 IS NULL  AND
                b.col3 IS NULL

Note, that in the join you selects all rows from the tables A - which means no index for access on table A will help. You have to full scan the table and process all rows.

You will see a HASH JOIN as a used join operation which is the prefered option if you join large datasets.

Check here how you can get the execution plan of the query.

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      | 10000 |  1044K|    25   (4)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|      | 10000 |  1044K|    25   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL   | B    |     2 |   110 |    16   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | A    | 10000 |   507K|     8   (0)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."ID"="B"."FK_ID"(+))
   2 - filter("B"."COL3"(+) IS NULL AND "B"."COL2"(+) IS NULL AND 
              "B"."COL1"(+)=8)

A different situation will be if you limit the processed rows from the table A to a very small number, say using the same query with additional WHERE condition.

You will use a nested loop join and index access to get only the required rows.

SELECT a.id, a.col1, a.col2, a.col3,
       b.col4
FROM a
LEFT OUTER JOIN b
             ON a.id  = b.fk_id AND
                b.col1 = 8 AND
                b.col2 IS NULL  AND
                b.col3 IS NULL 
where a.id = 8   --<<<< here you select only a few rows

Now you can profit from defining indices for

access to table A with the WHERE predicate
access to table B with the join columns

In our case it will be

create index a_idx on a (id);
create index b_idx on b (fk_id,col1, col2, col3);

The execution plan that you should expect will be the nested loop outer join

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |       |     2 |   214 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |       |     2 |   214 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| A     |     1 |    52 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | A_IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID| B     |     2 |   110 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | B_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("A"."ID"=8)
   5 - access("B"."FK_ID"(+)=8 AND "B"."COL1"(+)=8 AND "B"."COL2"(+) IS NULL 
              AND "B"."COL3"(+) IS NULL)
       filter("B"."COL3"(+) IS NULL)

Dear Marmite, thank you very much for your answer. Just a clarification: in the first query you wrote in your answer (the one without the where clause), it is unuseful to create the index b_idx on b (fk_id,col1, col2, col3), isn't it? — Sara_Marp, Jul 02 '20 at 14:13
Yes, if you need to select all data from a table a *full scan* is better than an *index access*. — Marmite Bomber, Jul 02 '20 at 14:19

Oracle- performing view- creating index on join table

1 Answers1