Home » RDBMS Server » Performance Tuning » poor performance of selecting from view.
poor performance of selecting from view. [message #248379] Thu, 28 June 2007 15:07
eggplant
Messages: 8
Registered: June 2007
Junior Member
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.
Previous Topic: Execution Plan
Next Topic: implications of merging 17 tables into 1...
Goto Forum:
  


Current Time: Fri Dec 02 20:51:45 CST 2016

Total time taken to generate the page: 0.41299 seconds