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

大家好:)我有两个表,每个表大约有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开发人员的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]

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

解决方法

这样你就可以摆脱两个表上的表访问.

如果你只有几个国家和品牌,你也可以按国家和品牌划分指数,以便可以使用INDEX FAST FULL SCAN代替INDEX RANGE SCAN.

（编辑：威海站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!