Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I cannot create index with the where clause.

Refer the below query:

CREATE INDEX c_bplocation_isdefault 
ON c_bpartner_location 
WHERE isdefault = 'Y';
share|improve this question
What do you mean by "I cannot"? Does your computer crash? Did you try to check the manual for correct syntax? – mustaccio May 27 '14 at 18:49

You cannot have a WHERE clause on an index in Oracle.

You can create a function-based index (I'm eliminating the isdefault third column from the index since we know that must be Y so it's not adding any selectivity to the index)

CREATE INDEX c_bplocation_isdefault ON c_bpartner_location (
  case when isdefault = 'Y' then ad_client_id else null end,
  case when isdefault = 'Y' then c_bpartner_id else null end

This takes advantage of the fact that Oracle does not index entries where all the columns in the index are NULL in order to reduce the size of the index. In order for your queries to use the index, however, you'd need to use the same expression that you used to create the index. Something like

  FROM c_bpartner_location 
 WHERE (case when isdefault = 'Y' 
             then ad_client_id 
             else null 
         end) = <<some value>>
   AND (case when isdefault = 'Y' 
             then c_bpartner_id  
             else null 
         end) = <<another value>>
   AND isdefault = 'Y'

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 CASE statement in multiple locations.

share|improve this answer
Argh, still not as nice as the concise partial index syntax that PostgreSQL supports. Let's hope Oracle adds it someday. – Colin 't Hart May 27 '14 at 19:15

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.

CREATE INDEX c_bplocation_isdefault 
    ON c_bpartner_location 
     ( isdefault, c_bpartner_id, ad_client_id );
share|improve this answer

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.

share|improve this answer
You could easily emulate the required functionality with a functional index – Phil May 27 '14 at 18:28
But wouldn't this require rewriting queries, whereas it wouldn't with PostgreSQL partial indexes and Oracle partition local (partial) indexes? Please write up an answer on how to do this! – Colin 't Hart May 27 '14 at 18:42
The other point is that the functional index would still contain one entry for each row, whereas with a partial index, the index could be very tiny if there are only very few rows having isdefault = 'Y'. – Colin 't Hart May 27 '14 at 18:43
Not if you used decode with NULLs for the other values – Phil May 27 '14 at 18:57

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.