title: Force use of primary key in Oracle during search
body: <p>I have scenario where i need to search &amp; display records from huge tables with lots  of rows.  I have pre-defined search criteria for my tables for which user can provide the filter  &amp; click search .  </p>
  
  <p>Considering a sample table :</p>
  
  <pre><code>CREATE TABLE suppliers 
  ( supplier_name varchar2(50) NOT NULL,
    address varchar2(50),
    city varchar2(50) NOT NULL,
    state varchar2(25),
    zip_code varchar2(10),
    CONSTRAINT "suppliers_pk" PRIMARY KEY (supplier_name, city)
  );
  
  
  INSERT INTO suppliers VALUES ('ABCD','XXXX','YYYY','ZZZZ','95012');
  INSERT INTO suppliers VALUES ('EFGH','MMMM','NNNN','OOOO','95010');
  INSERT INTO suppliers VALUES ('IJKL','EEEE','FFFF','GGGG','95009');
  </code></pre>
  
  <p>I have provided the user with search fields as the primary key - supplier_name, city</p>
  
  <p>If he enters both the fields, my query performance will be good since it goes for index scan</p>
  
  <pre><code>SELECT supplier_name, address, city, state, zip_code FROM suppliers where supplier_name = 'ABCD' and city = 'ZZZZ';
  
  | Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
  --------------------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT            |              |     1 |   102 |     1   (0)| 00:00:01 |
  |   1 |  TABLE ACCESS BY INDEX ROWID| SUPPLIERS    |     1 |   102 |     1   (0)| 00:00:01 |
  |*  2 |   INDEX UNIQUE SCAN         | suppliers_pk |     1 |       |     1   (0)| 00:00:01 |
  </code></pre>
  
  <p>However, if he enters only one of the search field, my query performance will go bad since it goes for full table scan</p>
  
  <pre><code>SELECT supplier_name, address, city, state, zip_code FROM suppliers where supplier_name = 'ABCD' ;
  
  | Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  -------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT  |           |     1 |   102 |     3   (0)| 00:00:01 |
  |*  1 |  TABLE ACCESS FULL| SUPPLIERS |     1 |   102 |     3   (0)| 00:00:01 |
  </code></pre>
  
  <p>Is there a way to force oracle to think it is a primary key search when i don't have all of the key fields in search , something like below ( which obviously is not working ) </p>
  
  <pre><code>SELECT supplier_name, address, city, state, zip_code FROM suppliers where supplier_name = 'ABCD' and city = city;
  </code></pre>
  
  <p>Thanks.</p>