There is one view ORT_MOLS in xxx_owner schema. I am not sure why it was designed to create such a simple view,but the fact is now the system in production is like this.
CREATE OR REPLACE VIEW OTR_MOLS
(CDBREGNO, CTAB, LILLY_NBR, MOLWEIGHT, MOLFORMULA)
AS
SELECT CDBREGNO,CTAB,LILLY_NBR,MOLWEIGHT,MOLFORMULA FROM OTR_MOLTABLE
CTAB is blob type, and there is one explicit index built on it.
A public synonym also name OTR_MOLS for this view OTR_MOLS.
in another schema xxx_user,there is one script
SELECT * FROM OTR_MOLS
WHERE flexmatch (ctab, 'ZZQlA', 'match=all') = 1
and the performance is very poor.Checking the execution plan,I found it uses full table scan.
while I changed the code by using the table directly instead of the synonym(view),I can get better performance.
SELECT * FROM xxx_owner.OTR_MOLTABLE
WHERE flexmatch (ctab, 'ZZQlA', 'match=all') = 1
if I create a same view OTR_MOLS in this xxx_user schema,the previous script which has poor performance can use index instead of full table scan. My question is whether there is anything with the synonym that make the optimizer doesn't use index.
Thanks.
] 
Blog
Search
Help
Members
Register
Login
Home
