Define index for sparse column

Question

I have a table with a columns 'A' and 'B'.

'A' is a column with 90% 'null' and 10% different values , and most of the time I query to have record with one or two of these different values.

and 'B' is a column with 90% value='1' and 10% different values and most of the time I query to have record with one or two of these different values.

In this table we have DML transaction most of the time.

now , I don't know define index on these columns is good? if yes which type of index?

Answer 1

In principle Bitmap Index would be the best in such situation. However, due to mulit-user environment they are not suitable - you would slow down your application significantly by table locks and perhaps get even dead-locks.

Maybe you can optimize your application by smart partitioning and usage of Partial Indexes (new feature in Oracle 12c)

CREATE TABLE statements below should be equivalent.

CREATE TABLE YOUR_TABLE (a INTEGER, b INTEGER, ... more COLUMNS)
PARTITION BY LIST (a) SUBPARTITION BY LIST (b) (
    PARTITION part_a_NULL VALUES (NULL) (
        SUBPARTITION part_a_NULL_b_1 VALUES (1) INDEXING OFF,
        SUBPARTITION part_a_NULL_b_other VALUES (DEFAULT) INDEXING ON
    ),
    PARTITION part_a_others VALUES (DEFAULT) (
        SUBPARTITION part_a_others_b_1 VALUES (1) INDEXING OFF,
        SUBPARTITION part_a_others_b_other VALUES (DEFAULT) INDEXING ON
    )   
);

CREATE TABLE YOUR_TABLE (a INTEGER, b INTEGER, ... more COLUMNS)
PARTITION BY LIST (a) SUBPARTITION BY LIST (b) 
    SUBPARTITION TEMPLATE (
        SUBPARTITION b_1 VALUES (1) INDEXING OFF,
        SUBPARTITION b_other VALUES (DEFAULT) INDEXING ON
    )
(
    PARTITION part_a_NULL VALUES (NULL),
    PARTITION part_a_others VALUES (DEFAULT)
);

CREATE INDEX IND_A ON YOUR_TABLE (A) LOCAL INDEXING PARTIAL;
CREATE INDEX IND_B ON YOUR_TABLE (B) LOCAL INDEXING PARTIAL;

By this your index will consume only 10% of entire tablespace. If your WHERE condition is WHERE A IS NULL or WHERE B = 1 then Oracle optimizer would skip such indexes anyway.

Verify with this query

SELECT table_name, partition_name, subpartition_name, indexing
FROM USER_TAB_SUBPARTITIONS
WHERE table_name = 'YOUR_TABLE';

if INDEXING is used on desired subpartitions.

Update

I just see actually this is an overkill because NULL values on column A do not create any index entry anyway. So, it can be simplified to

CREATE TABLE YOUR_TABLE (a INTEGER, b INTEGER, ... more COLUMNS)
PARTITION BY LIST (b) (
    PARTITION part_b_1 VALUES (1) INDEXING OFF,
    PARTITION part_b_other VALUES (DEFAULT) INDEXING ON
);