Invalid create index syntax I cannot create index with the where clause.

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 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

SELECT *
  FROM c_bpartner_location 
 WHERE (case when isdefault = 'Y' 
             then ad_client_id 
             else null 
         end) = &lt;&lt;some value&gt;&gt;
   AND (case when isdefault = 'Y' 
             then c_bpartner_id  
             else null 
         end) = &lt;&lt;another value&gt;&gt;
   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.