Creating a function based Index in Oracle DB using comparison operator

Question

I'm using Oracle DB (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production) and my table definition is like this:

Create table CardInfo (
Id Number not null,
BalanceInfo Decimal,
...
...
);

A lot of times this table will be queried like this:

select Id, ... from CardInfo where BalanceInfo > 0;

So, I'm trying to create a function based index like this:

CREATE INDEX balance_ix ON CardInfo (BalanceInfo > 0);

However, above statement gives following error:

Error starting at line : 1 in command -
CREATE INDEX balance_ix ON CardInfo (BalanceInfo > 0);
Error report -
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
Cause:
Action:

So I changed my create index statement to following:

CREATE INDEX balance_ix ON CardInfo (BalanceInfo);

This executed fine and created the index. However, when I did a Explain Plan for my sql statement like this:

EXPLAIN PLAN 
  SET statement_id = 'ex_plan1' FOR
SELECT * FROM CardInfo WHERE BalanceInfo > 0;;

SELECT PLAN_TABLE_OUTPUT 
  FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'ex_plan1','BASIC'));

It produced the following output:

Plan hash value: 97071123

-------------------------------------------
| Id  | Operation         | Name          |
-------------------------------------------
|   0 | SELECT STATEMENT  |               |
|   1 |  TABLE ACCESS FULL| CardInfo      |
-------------------------------------------

This means that it is not using the index that I created.

Could someone help, how can i create an index so that my sql statement uses it ? Thanks in advance.

BalanceInfo seems to be a number column as you are comparing it with zero. Why not just index BalanceInfo using normal index?. Create index CardInfoIdx on CardInfo(BalanceInfo); If you have large number of values satisfying BalanceInfo > 0, then FTS is better than, Index Scan. — ArtBajji
– ArtBajji, Commented Mar 20, 2018 at 11:43
The short answer to your question is somewhat hidden in Wernfried Domscheit's answer. Namely: assuming a relatively high fraction of balances will be greater than zero most of the time (as they are in real life), you will always get full table scans. Why? Because using an index is inefficient when a relatively large fraction (say, more than 20%) of the rows in the table will satisfy the WHERE clause. — user5683823
– user5683823, Commented Mar 20, 2018 at 21:05

Wernfried Domscheit · Accepted Answer · 2018-03-22 10:37:21Z

1

If you insist on Function-Based-Index then you may use

CREATE INDEX balance_ix ON CardInfo ( SIGN(BalanceInfo) );

Result of SIGN(BalanceInfo) can be -1, 0 and 1.

However, unless you create a BITMAP index (i.e. CREATE BITMAP INDEX ... Oracle will hardly use it because of cardinality of this index. But creating a BITMAP index also have other effects.

Another approach could be to make a partitionized table (List or Range partition). One partition for BalanceInfo = 0 and one partition for the rest. Would be as this:

CREATE TABLE CardInfo (
    ID NUMBER NOT NULL,
    BalanceInfo DECIMAL)
PARTITION BY LIST (BalanceInfo) (
    PARTITION P_zero_balance VALUES (0) INDEXING OFF,
    PARTITION P_other_balances VALUES (DEFAULT)
    )
ENABLE ROW MOVEMENT;

CREATE INDEX CARDINFO_BalanceInfo ON CARDINFO (BalanceInfo) LOCAL INDEXING PARTIAL;

Note, you don't need to create index entry for a constant value (i.e. BalanceInfo = 0), so I set INDEXING OFF.

If you prefer RANGE partition you could use

CREATE TABLE CARDINFO (
    ID NUMBER NOT NULL,
    BalanceInfo DECIMAL)
PARTITION BY RANGE (BalanceInfo) (
    PARTITION P_negative_balances VALUES LESS THAN (0),
    PARTITION P_zero_balance VALUES LESS THAN (0.001) INDEXING OFF,
    PARTITION P_positive_balances VALUES LESS THAN (MAXVALUE)
    )
ENABLE ROW MOVEMENT;

CREATE INDEX CARDINFO_BalanceInfo ON CARDINFO (BalanceInfo) LOCAL INDEXING PARTIAL;

edited Mar 22, 2018 at 10:37

answered Mar 20, 2018 at 13:58

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adhyatmik Over a year ago

Hi, Thank you for your reply. I tried using the suggested 2nd approach, but wasn't able to figure out how to create a partition for BalanceInfo = 0. In this link :

CREATE INDEX cost_ix ON sales (amount_sold)    GLOBAL PARTITION BY RANGE (amount_sold)       (PARTITION p1 VALUES LESS THAN (1000),        PARTITION p2 VALUES LESS THAN (2500),

, but how to create a partition for greater than and other partition for value equal to 0 ?

Collectives™ on Stack Overflow

Creating a function based Index in Oracle DB using comparison operator

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related