CODEWITHSUNDEEP
oracle-databasecomposite-primary-key
pats4u
pats4u

Reputation: 271

Force use of primary key in Oracle during search

I have scenario where i need to search & 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 & click search .

Considering a sample table :

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');

I have provided the user with search fields as the primary key - supplier_name, city

If he enters both the fields, my query performance will be good since it goes for index scan

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 |

However, if he enters only one of the search field, my query performance will go bad since it goes for full table scan

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 |

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 ) 

SELECT supplier_name, address, city, state, zip_code FROM suppliers where supplier_name = 'ABCD' and city = city;

Thanks.

Upvotes: 1

Views: 4799

Answers (3)

James
James

Reputation: 3411

You are thinking about this in the wrong way.

The query optimiser will choose what it thinks best execution plan for the query based on the information available at the time the query is parsed (or sometimes when the parameters changed). Generally - if you give it the right information in terms of stats etc, it usually will do a good job.

You might think that you know better than it, but remember that you won't be monitoring this for the life of the database. The data changes, you want the database to be able to react and change the execution plan when it needs to.

That said, if you are set on forcing it to use the index, you can use a hint: 

SELECT /*+ INDEX(suppliers suppliers_pk) */
supplier_name, address, city, state, zip_code FROM suppliers where   
supplier_name = 'ABCD' ;

Upvotes: 1

Kacper
Kacper

Reputation: 4818

Index is organised first on supplier_name second on city so it is not possible to use that index for query based on city only. Please create second index based only on city. This will help your query.

Upvotes: 0

Drunix
Drunix

Reputation: 3343

A full table scan is not necessarily bad. You have only a few rows in your table, so the optimizer thinks it is better to do a FTS than an index range scan. It will start using the PK index a soon as the RDBMS thinks it is better, i.e. you have lots a rows and the restriction on a certain supplier reduces the result significantly. If you want to search on city only instead of supplier you need another index with city only (or at least starting with city). Keep in mind that you might have to update the table statistics after you have loaded your table with bulk data. It is always important to test query performance with somehow realistic amounts of data.

Upvotes: 0

Related Questions