CODEWITHSUNDEEP
databaseoracle-databaseindexing
Joetjah
Joetjah

Reputation: 6132

Bitmap indexing in database not speeding up the query

I'm trying to prove a statement for my databases-class. I've stated that using bitmap indexing, a WHERE-query can be executed faster on a table with columns containing only enum values (like gender) than when not using the bitmap indexing.

Additionally to this, we try to prove it won't help on a table with columns containing a lot of different, random values.

We created two tables like described above in Oracle SQLDeveloper, filled with information as explained above in 10000 rows. The version of Oracle is v11.

We used a procedure to store information in the tables. While the enum values randomly are set to 'S', 'M' or 'L', the values for the other table are totally random (numbers and letters).

At first, we ran the statement: 

set timing on
SELECT * FROM INDEXTEST2 WHERE GENDER = 'M' AND MARRIED = 'N' AND CHILDREN = 'Y'

Repeatingly running this script ended up on taking 00.016 seconds. After this, we created an index by rightclicking the table, then creating an index. We have selected all columns and checked Bitmap. After that, we ran the query again a few times, just to encounter the same speed: around 00.015 seconds. We also tried dropping the index, with no results. Are we missing out on something here, or did we do everything correctly and you just can't see a difference in speed?

Upvotes: 1

Views: 2475

Answers (4)

Tamsin
Tamsin

Reputation: 11

In addition to having a large enough dataset, have a look at clearing the cache before executing each query

Upvotes: 1

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

Try selecting fewer rows (relative to all the rows in the table). If a query selects a large proportion of rows in the table, Oracle's query optimizer might (correctly) decide that just doing the full table scan is in fact quicker - if it's going to have to load most or all the blocks of the table heap, it might just as well do it directly.

That aside, your test set is way too small. Try a set large enough (add a few zeros) to push the execution time beyond the resolution of your stopwatch (typically 16 ms on a Windows machine). Even better, pick a size that doesn't completely fit into cache.

But even if you do all that, you might still get some weird results because of the overall "cleverness" of Oracle. For example, Oracle 11g has introduced the query result cache which can completely skew the execution times of repeated queries.

Upvotes: 2

wolφi
wolφi

Reputation: 8361

The query is likely very fast even without indexes. So you cannot demonstrate the performance gain with a stop watch.

I've create a test table with 1.000.000 rows. A full table scan without any indexes takes 0.2 seconds on our machine. With bitmap indexs, it takes 0.09 seconds. However, without indexes, it scans all 2000 database blocks, and using bitmap indexes, it needs to read 60-70 blocks.

CREATE TABLE indextest (
  id       NUMBER      NOT NULL,
  gender   VARCHAR2(1) NOT NULL CHECK (gender   IN ('M','F')),
  married  VARCHAR2(1) NOT NULL CHECK (married  IN ('Y','N')),
  children VARCHAR2(1) NOT NULL CHECK (children IN ('Y','N'))
) NOLOGGING;

INSERT /*+ APPEND */ 
  INTO indextest (id, gender, married, children)
WITH 
  q1 AS (SELECT          level AS x1 FROM dual CONNECT BY level <= 1000),
  q2 AS (SELECT 1000*(level-1) AS x2 FROM dual CONNECT BY level <= 1000),
  q3 AS (SELECT x2+x1 AS id FROM q1, q2)
SELECT id, 
       CASE WHEN dbms_random.value < 0.5   THEN 'M' ELSE 'F' END as gender,
       CASE WHEN dbms_random.value < 0.3   THEN 'Y' ELSE 'N' END as married,
       CASE WHEN dbms_random.value < 0.2   THEN 'Y' ELSE 'N' END as children
  FROM q3;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS(user, 'indextest');
ALTER TABLE indextest ADD CONSTRAINT pk_indextest PRIMARY KEY (id);
CREATE BITMAP INDEX bi_indextest_gender   ON indextest(gender);
CREATE BITMAP INDEX bi_indextest_married  ON indextest(married);
CREATE BITMAP INDEX bi_indextest_children ON indextest(children);

Now, if you show the query stats, for instance in SQL*Plus, you can show that using the indexes takes 60-70 blocks reads ("consistent gets"):

SET TIMING ON
SET LINE 300
SET AUTOTRACE TRACE EXPLAIN STAT
SELECT count(*) FROM indextest WHERE gender = 'M' AND married = 'N' AND children = 'Y';

Abgelaufen: 00:00:00.07

------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |     1 |     6 |    63   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |                       |     1 |     6 |            |          |
|   2 |   BITMAP CONVERSION COUNT    |                       |   125K|   732K|    63   (0)| 00:00:01 |
|   3 |    BITMAP AND                |                       |       |       |            |          |
|*  4 |     BITMAP INDEX SINGLE VALUE| BI_INDEXTEST_CHILDREN |       |       |            |          |
|*  5 |     BITMAP INDEX SINGLE VALUE| BI_INDEXTEST_GENDER   |       |       |            |          |
|*  6 |     BITMAP INDEX SINGLE VALUE| BI_INDEXTEST_MARRIED  |       |       |            |          |
------------------------------------------------------------------------------------------------------

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

   4 - access("CHILDREN"='Y')
   5 - access("GENDER"='M')
   6 - access("MARRIED"='N')


Statistiken
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         67  consistent gets
          0  physical reads
          0  redo size
        235  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

If you hide the indexes, Oracle needs to scan the whole table:

ALTER INDEX bi_indextest_gender   INVISIBLE;
ALTER INDEX bi_indextest_married  INVISIBLE;
ALTER INDEX bi_indextest_children INVISIBLE;
SELECT count(*) FROM indextest WHERE gender = 'M' AND married = 'N' AND children = 'Y';

Abgelaufen: 00:00:00.15

--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |     6 |   512   (6)| 00:00:07 |
|   1 |  SORT AGGREGATE    |           |     1 |     6 |            |          |
|*  2 |   TABLE ACCESS FULL| INDEXTEST |   125K|   732K|   512   (6)| 00:00:07 |
--------------------------------------------------------------------------------

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

   2 - filter("GENDER"='M' AND "MARRIED"='N' AND "CHILDREN"='Y')


Statistiken
----------------------------------------------------------
        292  recursive calls
          0  db block gets
       2289  consistent gets
          0  physical reads
          0  redo size
        235  bytes sent via SQL*Net to client
        252  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          1  rows processed

Upvotes: 2

David Aldridge
David Aldridge

Reputation: 52346

  1. Create one bitmap index per column.
  2. Test with a decent amount of data.
  3. Use DBMS_Xplan to view the execution plans

Upvotes: 1

Related Questions