Optimize counts in SELECT with JOIN on Oracle

Question

Hello all :) I have two tables that are about 30 millions rows each, and I'm seeking to improve performance when counts are performed.

Here is the query:

SELECT count(*)
FROM VEHICULE v
JOIN CLIENT c ON c.CL_ID = v.VE_CL_ID
WHERE v.VE_BRAND = 'MITSUBISHI'
  AND c.CL_COUNTRY = 'SPAIN';

The foreign key is declared in the VEHICULE table

CONSTRAINT "VEHICULE_CLIENT_FK" FOREIGN KEY ("VE_CL_ID")
    REFERENCES "MY_SCHEMA"."CLIENT" ("CL_ID") ENABLE

And there is an index on the foreign key:

CREATE INDEX "MY_SCHEMA"."VEHICULE_INDEX_CLIENT" ON "MY_SCHEMA"."VEHICULE" ("CL_ID")

There are indexes also on the columns used for the search criteria.

The requests can take up to 40 seconds. I have looked at bitmap joins indexes but I don't know if it will help, as bitmap joins are supposed to be for columns with low cardinalities. Is this the only type of index for joins? I'm totally at a loss at how I can improve the performance.

EDIT:

Here is what the SQL tuning advisor of SQL developer displays (execution plan) The sql for this query is without AND c.CL_COUNTRY = 'SPAIN'

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : staName9168
Tuning Task Owner  : USER
Tuning Task ID     : 12125
Scope              : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status  : COMPLETED
Started at         : 04/23/2013 15:44:35
Completed at       : 04/23/2013 15:44:36


-------------------------------------------------------------------------------
There are no recommendations to improve the statement.

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 3808155432

------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       |     1 |    21 | 54011   (1)| 00:10:49 |        |      |            |
|   1 |  SORT AGGREGATE                     |                       |     1 |    21 |            |          |        |      |            |
|   2 |   PX COORDINATOR                    |                       |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)              | :TQ10001              |     1 |    21 |            |          |  Q1,01 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE                  |                       |     1 |    21 |            |          |  Q1,01 | PCWP |            |
|*  5 |      HASH JOIN                      |                       |   475K|  9745K| 54011   (1)| 00:10:49 |  Q1,01 | PCWP |            |
|   6 |       BUFFER SORT                   |                       |       |       |            |          |  Q1,01 | PCWC |            |
|   7 |        PX RECEIVE                   |                       |   475K|  6497K| 32813   (1)| 00:06:34 |  Q1,01 | PCWP |            |
|   8 |         PX SEND BROADCAST           | :TQ10000              |   475K|  6497K| 32813   (1)| 00:06:34 |        | S->P | BROADCAST  |
|*  9 |          TABLE ACCESS BY INDEX ROWID| VEHICULE              |   475K|  6497K| 32813   (1)| 00:06:34 |        |      |            |
|* 10 |           INDEX RANGE SCAN          | VEHICULE_INDEX_BRAND  |   616K|       |  1621   (2)| 00:00:20 |        |      |            |
|  11 |       PX BLOCK ITERATOR             |                       |    20M|   138M| 21146   (1)| 00:04:14 |  Q1,01 | PCWC |            |
|  12 |        TABLE ACCESS FULL            | CLIENT                |    20M|   138M| 21146   (1)| 00:04:14 |  Q1,01 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - SEL$58A6D7F6
   9 - SEL$58A6D7F6 / VEHICULE@SEL$1
  10 - SEL$58A6D7F6 / VEHICULE@SEL$1
  12 - SEL$58A6D7F6 / CLIENT@SEL$1

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("VE_CL_ID"="CL_ID")
   9 - filter("VE_CL_ID" IS NOT NULL)
  10 - access("VEHICULE"."VE_BRAND"='MITSUBISHI')

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - (#keys=0) COUNT()[22]
   2 - SYS_OP_MSR()[10]
   3 - (#keys=0) SYS_OP_MSR()[10]
   4 - (#keys=0) SYS_OP_MSR()[10]
   5 - (#keys=1) 
   6 - (#keys=0) "VE_CL_ID"[NUMBER,22]
   7 - "VE_CL_ID"[NUMBER,22]
   8 - (#keys=0) "VE_CL_ID"[NUMBER,22]
   9 - "VE_CL_ID"[NUMBER,22]
  10 - "VEHICULE".ROWID[ROWID,10]
  11 - "CL_ID"[NUMBER,22]
  12 - "CL_ID"[NUMBER,22]

-------------------------------------------------------------------------------

Answer 1

Create composite indexes on client (cl_country, cl_id) and vehicule (ve_brand, ve_cl_id) (both in this order).

This way you could get rid of table access on both tables.

If you have but a few countries and brands possible you could also partition the indexes by country and brand so that INDEX FAST FULL SCAN could be used instead of INDEX RANGE SCAN.

You could also consider creating a cluster on client.id which would make the vehicle and client data to be stored in same or nearby data blocks, thus improving I/O.