如何优化以下 Oracle 10g 查询的响应时间？
如何优化以下查询的响应时间：
SELECT
/*+parallel */
cc.customer_id AS customer_id,
cc.title1 AS title1,
cc.forename1 AS forename1,
cc.forename2 AS forename2,
cc.surname1 AS surname1,
cc.surname2 AS surname2,
cc.company_flag AS company_flag,
cc.COMPANY_NAME AS COMPANY_NAME,
ext_customer_code AS code,
cc.customer_type AS category,
ca.address1 AS address1,
ca.address2 AS address2,
ca.address3 AS address3,
ca.address4 AS address4,
ca.address5 AS address5,
ca.postcode AS postcode,
ca.postcode_prefix AS postcode_prefix,
ca.country_code AS country_code,
ca.town AS town,
ca.county AS county,
cc.client_id AS client_id,
cc.location_id AS location_id,
cc.data_source_id AS dataSource_id,
cc.SALUTATION AS salutation,
cc.ADDRESS_MATCHKEY AS addressMatchKey,
ccv.vehicle_id AS vehicle_id,
cc.customer_name AS customer_name,
cv.REG_NUM AS REG_NUM
FROM OEM.CDB_CUSTOMERS cc,
OEM.CDB_ADDRESSES ca,
OEM.CDB_CUSTOMER_VEHICLES ccv,
OEM.CDB_VEHICLES cv
WHERE cc.client_id = ca.client_id
AND cc.address_matchkey = ca.address_matchkey
AND cc.location_id = ca.location_id
AND cc.customer_id = ccv.customer_id
AND cc.client_id = ccv.client_id
AND cc.LOCATION_ID = ccv.LOCATION_ID
AND ccv.vehicle_id = cv.vehicle_id
AND ccv.client_id = cv.CLIENT_ID
AND ccv.LOCATION_ID = cv.LOCATION_ID
AND cv.LOCATION_ID = 1
AND ccv.rejection_flag = 'N'
AND ccv.owner_status IS NOT NULL
AND cc.client_id = 1776
AND ca.client_id = 1776
AND ca.country_code ='UK'
AND ca.area_id IN
( SELECT start_code postcode_id FROM MDB_FORMAT WHERE NAME = 'PC%1776'
)
AND cv.model_id IN
( SELECT m.start_code model_id FROM MDB_FORMAT m WHERE m.NAME = '10_RWMOD'
)
ORDER BY customer_name,
town,
postcode
这是执行计划：
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 317 | 320 (1)| 00:00:04 |
| 1 | SORT ORDER BY | | 1 | 317 | 320 (1)| 00:00:04 |
| 2 | NESTED LOOPS | | 1 | 317 | 319 (1)| 00:00:04 |
| 3 | NESTED LOOPS | | 1 | 299 | 319 (1)| 00:00:04 |
| 4 | NESTED LOOPS | | 1 | 273 | 319 (1)| 00:00:04 |
|* 5 | HASH JOIN | | 8 | 1992 | 314 (1)| 00:00:04 |
| 6 | TABLE ACCESS BY INDEX ROWID| CDB_ADDRESSES | 10 | 1060 | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 501 | 62124 | 30 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | MDB_FORMAT_PK | 48 | 864 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | CDB_ADDRESSES_SMM_IDX1 | 10 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| CDB_CUSTOMERS | 28544 | 3484K| 283 (0)| 00:00:04 |
|* 11 | INDEX RANGE SCAN | CDB_CUSTOMERS_UK1 | 28544 | | 38 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | CDB_CUSTOMER_VEHICLES | 1 | 24 | 1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | CDB_CUSTOMER_VEHICLES_PK | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | CDB_VEHICLES | 1 | 26 | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | CDB_VEHICLES_PREF_IND | 1 | | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | MDB_FORMAT_PK | 1 | 18 | 1 (0)| 00:00:01 |
谓词信息（由操作 id 标识）：
5 - access("CC"."CLIENT_ID"="CA"."CLIENT_ID" AND "CC"."ADDRESS_MATCHKEY"="CA"."ADDRESS_MATCHKEY"
AND "CC"."LOCATION_ID"="CA"."LOCATION_ID")
8 - access("NAME"='PC%1776')
9 - access("CA"."CLIENT_ID"=1776 AND "CA"."AREA_ID"=TO_NUMBER("START_CODE") AND
"CA"."COUNTRY_CODE"='UK' AND "CA"."LOCATION_ID"=1)
11 - access("CC"."CLIENT_ID"=1776 AND "CC"."LOCATION_ID"=1)
12 - filter("CCV"."OWNER_STATUS" IS NOT NULL AND "CCV"."REJECTION_FLAG"='N')
13 - access("CCV"."CLIENT_ID"=1776 AND "CC"."CUSTOMER_ID"="CCV"."CUSTOMER_ID" AND
"CCV"."LOCATION_ID"=1)
filter("CCV"."LOCATION_ID"=1)
15 - access("CCV"."VEHICLE_ID"="CV"."VEHICLE_ID" AND "CV"."CLIENT_ID"=1776 AND
"CV"."LOCATION_ID"=1)
16 - access("M"."NAME"='10_RWMOD')
filter("CV"."MODEL_ID"=TO_NUMBER("M"."START_CODE"))
41 rows selected
How to optimize the response time for the following query:
SELECT
/*+parallel */
cc.customer_id AS customer_id,
cc.title1 AS title1,
cc.forename1 AS forename1,
cc.forename2 AS forename2,
cc.surname1 AS surname1,
cc.surname2 AS surname2,
cc.company_flag AS company_flag,
cc.COMPANY_NAME AS COMPANY_NAME,
ext_customer_code AS code,
cc.customer_type AS category,
ca.address1 AS address1,
ca.address2 AS address2,
ca.address3 AS address3,
ca.address4 AS address4,
ca.address5 AS address5,
ca.postcode AS postcode,
ca.postcode_prefix AS postcode_prefix,
ca.country_code AS country_code,
ca.town AS town,
ca.county AS county,
cc.client_id AS client_id,
cc.location_id AS location_id,
cc.data_source_id AS dataSource_id,
cc.SALUTATION AS salutation,
cc.ADDRESS_MATCHKEY AS addressMatchKey,
ccv.vehicle_id AS vehicle_id,
cc.customer_name AS customer_name,
cv.REG_NUM AS REG_NUM
FROM OEM.CDB_CUSTOMERS cc,
OEM.CDB_ADDRESSES ca,
OEM.CDB_CUSTOMER_VEHICLES ccv,
OEM.CDB_VEHICLES cv
WHERE cc.client_id = ca.client_id
AND cc.address_matchkey = ca.address_matchkey
AND cc.location_id = ca.location_id
AND cc.customer_id = ccv.customer_id
AND cc.client_id = ccv.client_id
AND cc.LOCATION_ID = ccv.LOCATION_ID
AND ccv.vehicle_id = cv.vehicle_id
AND ccv.client_id = cv.CLIENT_ID
AND ccv.LOCATION_ID = cv.LOCATION_ID
AND cv.LOCATION_ID = 1
AND ccv.rejection_flag = 'N'
AND ccv.owner_status IS NOT NULL
AND cc.client_id = 1776
AND ca.client_id = 1776
AND ca.country_code ='UK'
AND ca.area_id IN
( SELECT start_code postcode_id FROM MDB_FORMAT WHERE NAME = 'PC%1776'
)
AND cv.model_id IN
( SELECT m.start_code model_id FROM MDB_FORMAT m WHERE m.NAME = '10_RWMOD'
)
ORDER BY customer_name,
town,
postcode
Here's execution plan:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | 1 | 317 | 320 (1)| 00:00:04 |
| 1 | SORT ORDER BY | | 1 | 317 | 320 (1)| 00:00:04 |
| 2 | NESTED LOOPS | | 1 | 317 | 319 (1)| 00:00:04 |
| 3 | NESTED LOOPS | | 1 | 299 | 319 (1)| 00:00:04 |
| 4 | NESTED LOOPS | | 1 | 273 | 319 (1)| 00:00:04 |
|* 5 | HASH JOIN | | 8 | 1992 | 314 (1)| 00:00:04 |
| 6 | TABLE ACCESS BY INDEX ROWID| CDB_ADDRESSES | 10 | 1060 | 1 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 501 | 62124 | 30 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | MDB_FORMAT_PK | 48 | 864 | 1 (0)| 00:00:01 |
|* 9 | INDEX RANGE SCAN | CDB_ADDRESSES_SMM_IDX1 | 10 | | 1 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| CDB_CUSTOMERS | 28544 | 3484K| 283 (0)| 00:00:04 |
|* 11 | INDEX RANGE SCAN | CDB_CUSTOMERS_UK1 | 28544 | | 38 (0)| 00:00:01 |
|* 12 | TABLE ACCESS BY INDEX ROWID | CDB_CUSTOMER_VEHICLES | 1 | 24 | 1 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | CDB_CUSTOMER_VEHICLES_PK | 1 | | 1 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | CDB_VEHICLES | 1 | 26 | 1 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | CDB_VEHICLES_PREF_IND | 1 | | 1 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | MDB_FORMAT_PK | 1 | 18 | 1 (0)| 00:00:01 |
Predicate Information (identified by operation id):
5 - access("CC"."CLIENT_ID"="CA"."CLIENT_ID" AND "CC"."ADDRESS_MATCHKEY"="CA"."ADDRESS_MATCHKEY"
AND "CC"."LOCATION_ID"="CA"."LOCATION_ID")
8 - access("NAME"='PC%1776')
9 - access("CA"."CLIENT_ID"=1776 AND "CA"."AREA_ID"=TO_NUMBER("START_CODE") AND
"CA"."COUNTRY_CODE"='UK' AND "CA"."LOCATION_ID"=1)
11 - access("CC"."CLIENT_ID"=1776 AND "CC"."LOCATION_ID"=1)
12 - filter("CCV"."OWNER_STATUS" IS NOT NULL AND "CCV"."REJECTION_FLAG"='N')
13 - access("CCV"."CLIENT_ID"=1776 AND "CC"."CUSTOMER_ID"="CCV"."CUSTOMER_ID" AND
"CCV"."LOCATION_ID"=1)
filter("CCV"."LOCATION_ID"=1)
15 - access("CCV"."VEHICLE_ID"="CV"."VEHICLE_ID" AND "CV"."CLIENT_ID"=1776 AND
"CV"."LOCATION_ID"=1)
16 - access("M"."NAME"='10_RWMOD')
filter("CV"."MODEL_ID"=TO_NUMBER("M"."START_CODE"))
41 rows selected
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
步骤1：确保查询正确。我不确定您的数据模型是什么，但我注意到您说“SELECT start_code postcode_id”和“SELECT m.start_code model_id”。您的意思是为两个目的为同一字段设置别名吗？
第二步：分析表格。过时的统计信息可能会导致优化器选择错误的计划。
步骤 3：在 FROM 子句中写入连接条件（例如，“table1 JOIN table2 ON (table1.id = table2.id)）。这使得您、优化器以及任何阅读您代码的人都清楚您正在尝试的内容重新安排已经组织好的事情会更容易。
步骤 4：重复步骤 1。
步骤 5：当你到达那里时我们会讨论这个。
Step 1: Make sure the query is correct. I'm not sure what your data model is, but I noticed you say "SELECT start_code postcode_id" and "SELECT m.start_code model_id". Do you mean to alias the same field for two purposes?
Step 2: Analyze the tables. Out-of-date statistics might cause the optimizer to chose a bad plan.
Step 3: Write your join conditions in the FROM clause (e.g., "table1 JOIN table2 ON (table1.id = table2.id)). This makes it obvious to you, the optimizer, and anyone reading your code what you're trying to do. It's easier to rearrange something that's already organized.
Step 4: Repeat Step 1.
Step 5: We'll talk about that when you've gotten there.
通常屏蔽客户信息是个好主意，例如将公司名称更改为 CO 或 XXX，您甚至不需要架构名称来优化查询。
Usually its a good idea to mask your customer's info, like change the company name to CO or XXX, you don't even need the schema name to optimize the query.
“根据过滤条件，它会返回大约 18000 到 8000 行。”
解释计划表明它期望返回 1 行（这通常是因为它从不期望返回 0 行）。
我预计一些基数估计是错误的。可能会造成混淆的一项是，
第 9 项和第 11 项都具有 1776 过滤器，但第 5 项确保它们匹配。这是多余的，但可能会影响基数估计。
"It returns about 18000 to 8000 rows depending on filter criteria."
The explain plan indicates that it expects to return 1 row (and that's generally because it NEVER expects to return 0 rows).
I expect some of the cardinality estimates are wrong. One item that may confuse it is this
Items 9 and 11 both have the 1776 filter, but then item 5 makes sure they match. This is redundant, but may be affecting the cardinality estimate.