Quoting column names changes Query Plan????

[cmmrfrds]

Can somebody tell me why there is a dramatic difference in the explain for the 2 queries below. Only difference in the SQL is double quotes on table and column names (generated from Crystal). We are Oracle 10G.


SELECT "PATIENT"."PAT_MRN_ID", "PATIENT"."PAT_NAME", "V_MSHO"."HEALTH_PLAN",
"CLARITY_POS"."POS_NAME", "PAT_ENC"."APPT_PRC_ID",
"PAT_ENC"."CONTACT_DATE", "PATIENT"."PAT_ID",
"V_MSHO"."CARE_COORDINATOR", "CLARITY_EMP"."NAME",
"PAT_ENC"."VISIT_PROV_ID", "PAT_ENC"."ENC_TYPE_C",
"PAT_ENC"."APPT_STATUS_C", "PATIENT"."DEATH_DATE"
FROM "CLARITY"."CLARITY_POS" "CLARITY_POS"
INNER JOIN
("CLARITY"."PATIENT" "PATIENT"
INNER JOIN
("CLARITY"."PAT_ENC" "PAT_ENC"
LEFT OUTER JOIN
("CLARITY"."CLARITY_EMP" "CLARITY_EMP" INNER JOIN "CLARITY"."V_MSHO" "V_MSHO"
ON "CLARITY_EMP"."USER_ID" = "V_MSHO"."CARE_COORDINATOR")
ON "PAT_ENC"."PAT_ID" = "V_MSHO"."PAT_ID")
ON "PATIENT"."PAT_ID" = "V_MSHO"."PAT_ID")
ON "CLARITY_POS"."POS_ID" = "V_MSHO"."NURSING_HOME_FACILITY"
WHERE "PAT_ENC"."APPT_PRC_ID" = 261
AND "PAT_ENC"."VISIT_PROV_ID" = '699'
AND ( "PAT_ENC"."ENC_TYPE_C" = 50
OR "PAT_ENC"."ENC_TYPE_C" = 101
OR "PAT_ENC"."ENC_TYPE_C" = 120
OR "PAT_ENC"."ENC_TYPE_C" = 200
OR "PAT_ENC"."ENC_TYPE_C" = 611
OR "PAT_ENC"."ENC_TYPE_C" = 624
OR "PAT_ENC"."ENC_TYPE_C" = 632
OR "PAT_ENC"."ENC_TYPE_C" = 633
OR "PAT_ENC"."ENC_TYPE_C" = 100001
)
AND ("PAT_ENC"."APPT_STATUS_C" = 2 OR "PAT_ENC"."APPT_STATUS_C" = 6)
AND "PATIENT"."DEATH_DATE" IS NULL
ORDER BY "CLARITY_POS"."POS_NAME",
"V_MSHO"."CARE_COORDINATOR",
"V_MSHO"."HEALTH_PLAN",
"PATIENT"."PAT_ID",
"PATIENT"."PAT_NAME",
"PAT_ENC"."CONTACT_DATE"

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

SELECT PATIENT.PAT_MRN_ID, PATIENT.PAT_NAME, V_MSHO.HEALTH_PLAN,
CLARITY_POS.POS_NAME, PAT_ENC.APPT_PRC_ID,
PAT_ENC.CONTACT_DATE, PATIENT.PAT_ID,
V_MSHO.CARE_COORDINATOR, CLARITY_EMP.NAME,
PAT_ENC.VISIT_PROV_ID, PAT_ENC.ENC_TYPE_C,
PAT_ENC.APPT_STATUS_C, PATIENT.DEATH_DATE
FROM CLARITY.CLARITY_POS CLARITY_POS
INNER JOIN
(CLARITY.PATIENT PATIENT
INNER JOIN
(CLARITY.PAT_ENC PAT_ENC
LEFT OUTER JOIN
(CLARITY.CLARITY_EMP CLARITY_EMP INNER JOIN CLARITY.V_MSHO V_MSHO
ON CLARITY_EMP.USER_ID = V_MSHO.CARE_COORDINATOR)
ON PAT_ENC.PAT_ID = V_MSHO.PAT_ID)
ON PATIENT.PAT_ID = V_MSHO.PAT_ID)
ON CLARITY_POS.POS_ID = V_MSHO.NURSING_HOME_FACILITY
WHERE PAT_ENC.APPT_PRC_ID = 261
AND PAT_ENC.VISIT_PROV_ID = '699'
AND ( PAT_ENC.ENC_TYPE_C = 50
OR PAT_ENC.ENC_TYPE_C = 101
OR PAT_ENC.ENC_TYPE_C = 120
OR PAT_ENC.ENC_TYPE_C = 200
OR PAT_ENC.ENC_TYPE_C = 611
OR PAT_ENC.ENC_TYPE_C = 624
OR PAT_ENC.ENC_TYPE_C = 632
OR PAT_ENC.ENC_TYPE_C = 633
OR PAT_ENC.ENC_TYPE_C = 100001
)
AND (PAT_ENC.APPT_STATUS_C = 2 OR PAT_ENC.APPT_STATUS_C = 6)
AND PATIENT.DEATH_DATE IS NULL
ORDER BY CLARITY_POS.POS_NAME,
V_MSHO.CARE_COORDINATOR,
V_MSHO.HEALTH_PLAN,
PATIENT.PAT_ID,
PATIENT.PAT_NAME,
PAT_ENC.CONTACT_DATE

 

[cmmrfrds]

Got the answer. Our DBA had created an "SQL Profile" on the query with the double quotes.