Improve a select query with MINUS

[MyPlanet]

In follwoing two tables cas.cass_siebel@cas_lnk and CASS_HISTORY, there are about 500,000 records in each table. The following query returns the records in cas.cass_siebel@cas_lnk, but it will not return the records with exact match in CASS_HISTORY.

This query is quite slow. I wonder if there is way to speed up this query. For example, adding some more indexes in both tables is helpful or not.

Any suggestions? Thanks.


SELECT casss.record_type,
casss.fiscal_year,
casss.sequence_number,
casss.office_number,
substr(trim(casss.file_name), 1, 40) "FILE_NAME",
substr(trim(casss.consumer_name), 1, 35) "CONSUMER_NAME",
FNC_GET_COUNTY_CODE(casss.county_code) "COUNTY_CODE", /* county_code is county_name in cas.cass_siebel */
substr(trim(casss.insured_name), 1, 40) "INSURED_NAME",
decode(length(trim(casss.company_code)), 5, trim(casss.company_code), NULL) "COMPANY_CODE",
substr(trim(casss.company_name), 1, 40) "COMPANY_NAME",
substr(trim(casss.agent_name), 1, 40) "AGENT_NAME",
substr(trim(casss.agent_ssn), 1, 9) "AGENT_SSN",
substr(trim(casss.agency_name), 1, 40) "AGENCY_NAME",
to_date(casss.date_closed, 'YYYY-MM-DD') "DATE_CLOSED",
to_date(casss.date_opened, 'YYYY-MM-DD') "DATE_OPENED",
DECODE(casss.date_closed, NULL, 'Open', 'Closed') "STATUS",
casss.origin_code,
casss.COMPLAINANT_TYPE_CODE "COMPLAINT_TYPE_CODE",
casss.general_coverage_code,
casss.detail_coverage_code,
casss.reason_code,
casss.disposition_code,
casss.service_request_employee,
substr(trim(casss.category), 1, 2) "CATEGORY",
NULL "REFERRAL_TEXT",
NULL "REASON_TEXT",
NULL "referral_code_a",
NULL "referral_code_b",
NULL "referral_num_a",
NULL "referral_num_b",
to_date(casss.date_added, 'YYYY-MM-DD') "DATE_ADDED",
casss.company_complaint_ind "COMPANY_COMPLAINT_INDICATOR"
FROM cas.cass_siebel@cas_lnk casss
WHERE casss.date_opened IS NOT NULL

MINUS

SELECT CH.* FROM CASS_HISTORY CH

[jiezhi]

你的查詢當然會比較慢：

1.在字段上使用了大量的substr、trim、to_date函數
2、使用了dblink，通過網絡訪問數據
3.表里面的數據也比較多。

[MyPlanet]

Is there a way to speed up it.