I cannot create index with the
Refer the below query:
CREATE INDEX c_bplocation_isdefault ON c_bpartner_location (ad_client_id,c_bpartner_id,isdefault) WHERE isdefault = 'Y';
You cannot have a
You can create a function-based index (I'm eliminating the
This takes advantage of the fact that Oracle does not index entries where all the columns in the index are
Realistically, you're often better off creating an actual function that you use in both your index definition and in your query so that you're not coding the
IMHO, A better index would be shown below. You want an index to start general then become specific. If you could create an index for certain values, then why would you need that column in the index because it would already be 'Y'. But if you put it as the first columns all of the rows with isdefault='Y' are clustered together, therefore you already have a smaller section of index to look at to get the rows you want. I put partner id before client id assuming that there are fewer partners than clients. Try this and see if it works.
Oracle does not support this concept of partial indexes. This syntax is valid for PostgreSQL versions from 8.0 and higher but not for Oracle.
Oracle does however support indexes on partitions. If you have a license for the partitioning option, you can create a partition for the values of isdefault = 'Y' and index only that partition. I'm not sure which versions of Oracle support this; it could possibly be only 12c and higher.