大家好:)我有两个表,每个表大约有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] -------------------------------------------------------------------------------
解决方法
在客户端(cl_country,cl_id)和vehicule(ve_brand,ve_cl_id)上创建复合索引(按此顺序).
这样你就可以摆脱两个表上的表访问.
如果你只有几个国家和品牌,你也可以按国家和品牌划分指数,以便可以使用INDEX FAST FULL SCAN代替INDEX RANGE SCAN.
您还可以考虑在client.id上创建一个集群,这将使车辆和客户端数据存储在相同或附近的数据块中,从而改善I / O.
