OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Bitmap join index (DB19.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Bitmap join index [message #682185]

Thu, 08 October 2020 04:17

John Watson
Messages: 8960
Registered: January 2010
Location: Global Village

Senior Member

I'm trying to make a few demonstrations of bitmap indexes, and I cannot get bitmap join indexes to work as I think they should. Using one is much lower cost than the alternative plan, but to use it I have to hint. Here's an example, in HR. The idea is to count the number of employees in Europe:

orclz>
orclz> select count(*) from employees join departments using(department_id)
  2  join locations using(location_id)
  3  join countries using (country_id)
  4  join regions using (region_id)
  5  where region_name='Europe';

  COUNT(*)
----------
        36


Execution Plan
----------------------------------------------------------
Plan hash value: 1515338176

------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                   |     1 |    36 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                        |                   |     1 |    36 |            |          |
|   2 |   NESTED LOOPS                         |                   |    27 |   972 |     7   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                        |                   |     7 |   231 |     7   (0)| 00:00:01 |
|*  4 |     HASH JOIN                          |                   |     6 |   156 |     5   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                      |                   |    23 |   276 |     2   (0)| 00:00:01 |
|   6 |       VIEW                             | index$_join$_004  |    23 |   138 |     2   (0)| 00:00:01 |
|*  7 |        HASH JOIN                       |                   |       |       |            |          |
|   8 |         INDEX FAST FULL SCAN           | LOC_COUNTRY_IX    |    23 |   138 |     1   (0)| 00:00:01 |
|   9 |         INDEX FAST FULL SCAN           | LOC_ID_PK         |    23 |   138 |     1   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN                | COUNTRY_C_ID_PK   |     1 |     6 |     0   (0)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL                 | REGIONS           |     1 |    14 |     3   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS       |     1 |     7 |     1   (0)| 00:00:01 |
|* 13 |      INDEX RANGE SCAN                  | DEPT_LOCATION_IX  |     4 |       |     0   (0)| 00:00:01 |
|* 14 |    INDEX RANGE SCAN                    | EMP_DEPARTMENT_IX |     4 |    12 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("COUNTRIES"."REGION_ID"="REGIONS"."REGION_ID")
   7 - access(ROWID=ROWID)
  10 - access("LOCATIONS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID")
  11 - filter("REGIONS"."REGION_NAME"='Europe')
  13 - access("DEPARTMENTS"."LOCATION_ID"="LOCATIONS"."LOCATION_ID")
  14 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")

Note
-----
   - this is an adaptive plan

orclz> create bitmap index emp_reg on employees (r.region_name)
  2  from employees e, departments d, locations l, countries c, regions r
  3  where
  4  e.department_id=d.department_id and
  5  d.location_id=l.location_id and
  6  l.country_id=c.country_id and
  7  r.region_id=c.region_id;

Index created.

orclz> select count(*) from employees join departments using(department_id)
  2  join locations using(location_id)
  3  join countries using (country_id)
  4  join regions using (region_id)
  5  where region_name='Europe';

  COUNT(*)
----------
        36


Execution Plan
----------------------------------------------------------
Plan hash value: 1515338176

------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                   |     1 |    36 |     7   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE                        |                   |     1 |    36 |            |          |
|   2 |   NESTED LOOPS                         |                   |    13 |   468 |     7   (0)| 00:00:01 |
|   3 |    NESTED LOOPS                        |                   |     7 |   231 |     7   (0)| 00:00:01 |
|*  4 |     HASH JOIN                          |                   |     6 |   156 |     5   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                      |                   |    23 |   276 |     2   (0)| 00:00:01 |
|   6 |       VIEW                             | index$_join$_004  |    23 |   138 |     2   (0)| 00:00:01 |
|*  7 |        HASH JOIN                       |                   |       |       |            |          |
|   8 |         INDEX FAST FULL SCAN           | LOC_COUNTRY_IX    |    23 |   138 |     1   (0)| 00:00:01 |
|   9 |         INDEX FAST FULL SCAN           | LOC_ID_PK         |    23 |   138 |     1   (0)| 00:00:01 |
|* 10 |       INDEX UNIQUE SCAN                | COUNTRY_C_ID_PK   |     1 |     6 |     0   (0)| 00:00:01 |
|* 11 |      TABLE ACCESS FULL                 | REGIONS           |     1 |    14 |     3   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID BATCHED| DEPARTMENTS       |     1 |     7 |     1   (0)| 00:00:01 |
|* 13 |      INDEX RANGE SCAN                  | DEPT_LOCATION_IX  |     4 |       |     0   (0)| 00:00:01 |
|* 14 |    INDEX RANGE SCAN                    | EMP_DEPARTMENT_IX |     2 |     6 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("COUNTRIES"."REGION_ID"="REGIONS"."REGION_ID")
   7 - access(ROWID=ROWID)
  10 - access("LOCATIONS"."COUNTRY_ID"="COUNTRIES"."COUNTRY_ID")
  11 - filter("REGIONS"."REGION_NAME"='Europe')
  13 - access("DEPARTMENTS"."LOCATION_ID"="LOCATIONS"."LOCATION_ID")
  14 - access("EMPLOYEES"."DEPARTMENT_ID"="DEPARTMENTS"."DEPARTMENT_ID")

Note
-----
   - this is an adaptive plan

orclz>
orclz> ed
Wrote file afiedt.buf

  1  select /*+ index(employees emp_reg) */ count(*) from employees join departments using(department_id)
  2  join locations using(location_id)
  3  join countries using (country_id)
  4  join regions using (region_id)
  5* where region_name='Europe'
orclz> /

  COUNT(*)
----------
        36


Execution Plan
----------------------------------------------------------
Plan hash value: 69607573

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |     3 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |         |     1 |     3 |            |          |
|   2 |   BITMAP CONVERSION COUNT   |         |    54 |   162 |     1   (0)| 00:00:01 |
|*  3 |    BITMAP INDEX SINGLE VALUE| EMP_REG |       |       |            |          |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("EMPLOYEES"."SYS_NC00012$"='Europe')

orclz>

If I hint employees as the leading table, I also get the bitmap join index access path..

The object statistics are all there, including histograms on all skewed columns and the indexes, as you can see from the estimates. It does not seem possible to gather a histogram on the virtual column used for the bitmap index, but there are of course statistics on the index itself: Oracle knows that there are two distinct values for region_name in emp_reg, and therefore guesses 54 rows (half the table) will be returned.

I have tried messing about with dynamic sampling and the optimizer_index_% parameters, gathered stat's a zillion times, no difference.

So my question is, why does the CBO refuse to use my bitmap join index, unless I tell it to?

I did generate a 10053 trace which I shall attach, the index is mentioned in it but I cannot see why it isn't used.

Thank you for any insight.

Attachment: orclz_ora_15528_BMJ.trc
(Size: 560.08KB, Downloaded 3283 times)

Report message to a moderator

Previous Topic:	Scalar subquery merging
Next Topic:	Application Performance Issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Dec 03 11:32:46 CST 2024