oracle - 在 Oracle 上使用 JOIN 优化 SELECT 中的计数

Question

大家好:)我有两个表，每个表大约有 3000 万行，我正在寻求提高执行计数时的性能。

这是查询:

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

外键在 VEHICULE 表中声明

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

并且外键上有一个索引:

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

用于搜索条件的列上也有索引。

请求最多可能需要 40 秒。我查看了位图连接索引，但我不知道它是否有帮助，因为位图连接应该是 for columns with low cardinalities 。这是连接的唯一索引类型吗？我完全不知道如何提高性能。

编辑:

这是SQL Developer的SQL调优顾问显示的内容(执行计划)此查询的 sql 不包含 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

在client (cl_country, cl_id) 和vehicule (ve_brand, ve_cl_id)(均按此顺序)创建复合索引。

这样您就可以摆脱对两个表的表访问。

如果您只有几个国家和品牌，您还可以按国家和品牌对索引进行分区，以便可以使用 INDEX FAST FULL SCAN 代替 INDEX RANGE SCAN >.

您还可以考虑在 client.id 上创建一个集群，这将使车辆和客户端数据存储在相同或附近的数据 block 中，从而提高 I/O。