Materialized view not being used according to plan

[Former Member]

Hi

I have created a materialized view that looks exactly like a select I am doing, minus most of the where clause but including those fields in the select.

The plan does not use it though. The message in the planner, Advanced Details, is materialized view considered but "Base table mismatch". Which is not the case as far as I can see.

Is there something special I should know?

Thanks Ivan

[Former Member]

The only reasons you get table mismatch with the definitions above is that the column E.INSERTDATE in the query (where E is the base table "Companies") is not in the view's SELECT list: we cannot cover the columns needed in the queries with the columns projected out by the materialized view.

Note also that the ORDER BY in the materialized view definition is ignored by the server: the materialized view is stored just as a base table and order of the rows is not guarantee for base tables.

[Former Member]

Please post the view definition and the query.

[Former Member]

Hi

The view is:

CREATE MATERIALIZED VIEW "DBA"."ivantest"( / view_column_name, ... / ) IN "system" AS SELECT "DBA"."Candidates".CANDIDATEID, "DBA"."Candidates".FIRSTNAME, "DBA"."Candidates".LASTNAME, "DBA"."Candidates".TITLEID, "DBA"."Candidates".JOBTITLEID, "DBA"."Candidates".CONTRACTPAYMENTCOMPANYID, "DBA"."Candidates".CANSOURCEID, "DBA"."Candidates".STATUS, "DBA"."Candidates".DATEOFBIRTH, "DBA"."Candidates".HOMEPOSTCODE, "DBA"."Candidates".HOMEPHONE, "DBA"."Candidates".MOBILEPHONE, "DBA"."Candidates".EMAILADDRESS, "DBA"."Candidates".EMAIL2ADDRESS, "DBA"."Candidates".INSERTDATE,"DBA"."Candidates".INSERTCONSULTANTID, "DBA"."Candidates".LASTUPDATECONSULTANTID, "DBA"."Candidates".ARCHIVED,
"DBA"."Candidates".AVAILABILITYDATE, "DBA"."Candidates".LASTSALARY, "DBA"."Candidates".EXPECTEDSALARYC, "DBA"."Candidates".EXPECTEDSALARYP, "DBA"."Candidates".CVTEXTRTFDOCUMENTID, "DBA"."Documents".DOCUMENTID, "DBA"."Documents".LINKID, "DBA"."Documents".LINKTYPE, "DBA"."Documents".LASTUPDATEDATE, "DBA"."Documents".LASTUPDATETIME, "DBA"."JobTitles".JOBTITLENAME, "DBA"."Consultants".CONSULTANTID, "DBA"."Consultants".NAME, "DBA"."Consultants".TEAMID, "DBA"."Consultants".SECURITYGROUPID, "DBA"."Companies".COMPANYID, "DBA"."Companies".COMPANYNAME, "DBA"."Companies".COMPANYTYPE

FROM ( "DBA"."Candidates" LEFT OUTER JOIN "DBA"."Documents" ON "DBA"."Candidates".CVTEXTRTFDOCUMENTID= "DBA"."Documents".DOCUMENTID LEFT OUTER JOIN "DBA"."JobTitles" ON "DBA"."Candidates".JOBTITLEID= "DBA"."JobTitles".JOBTITLEID LEFT OUTER JOIN "DBA"."Consultants" ON "DBA"."Candidates".INSERTCONSULTANTID= "DBA"."Consultants".CONSULTANTID LEFT OUTER JOIN "DBA"."Companies" ON "DBA"."Candidates".CONTRACTPAYMENTCOMPANYID= "DBA"."Companies".COMPANYID )

WHERE ("DBA"."Candidates".Archived = 0 OR "DBA"."Candidates".Archived IS NULL)

ORDER BY "DBA"."Candidates".LASTNAME, "DBA"."Candidates".FIRSTNAME, "DBA"."Candidates".CANDIDATEID

The query is:

SELECT A.CANDIDATEID, A.FIRSTNAME, A.LASTNAME, A.TITLEID, A.JOBTITLEID, A.CONTRACTPAYMENTCOMPANYID, A.CANSOURCEID, A.STATUS, A.DATEOFBIRTH, A.HOMEPOSTCODE, A.HOMEPHONE, A.MOBILEPHONE, A.EMAILADDRESS, A.EMAIL2ADDRESS, A.INSERTDATE, A.INSERTCONSULTANTID, A.LASTUPDATECONSULTANTID, A.ARCHIVED, A.AVAILABILITYDATE, A.LASTSALARY, A.EXPECTEDSALARYC, A.EXPECTEDSALARYP, A.CVTEXTRTFDOCUMENTID, B.DOCUMENTID, B.LINKID, B.LINKTYPE, B.LASTUPDATEDATE, B.LASTUPDATETIME, C.JOBTITLEID, C.JOBTITLENAME, D.CONSULTANTID, D.NAME, D.JOBTITLEID, D.TEAMID, D.SECURITYGROUPID, E.COMPANYID, E.COMPANYNAME, E.COMPANYTYPE, E.INSERTDATE FROM ( Candidates A LEFT OUTER JOIN Documents B ON A.CVTEXTRTFDOCUMENTID= B.DOCUMENTID LEFT OUTER JOIN JobTitles C ON A.JOBTITLEID= C.JOBTITLEID LEFT OUTER JOIN Consultants D ON A.INSERTCONSULTANTID= D.CONSULTANTID LEFT OUTER JOIN Companies E ON A.CONTRACTPAYMENTCOMPANYID= E.COMPANYID )
WHERE ( (A.LastName LIKE 'peter pan%' OR (A.LastName LIKE 'peter%' AND A.FirstName LIKE 'pan%') OR A.FirstName LIKE 'peter pan%' OR (A.LastName LIKE 'pan%' AND A.FirstName LIKE 'peter%')) AND (A.Archived = 0 OR A.Archived IS NULL) )
ORDER BY A.LASTNAME, A.FIRSTNAME, A.CANDIDATEID

Thanks

[VolkerBarth]

Adding this as an answer and keeping the format similar between view and query seems worthwhile, methinks.

If you do queries for names split differently in first and last names regularly, I would think this attempt with several ORed LIKE predicates is usually slow, as it can't use an index on LastName because of the disjuntions (at least with older SA versions). I remember to have used UNIONs of selects with a sargable predicate instead.

Another attempt would be to add a computed field "Name" that would consist of string(LastName, ' ', FirstName) and could be indexed. Then you could query just this computed field. - But given that names apparently can be entered as last name, first name or vice versa will make this method not too helpful, either. - One might use two different computed columns with different order then and UNION a search on both...

[Former Member]

Hi Not sure if it is an answer though?

Version of SA is 12.0.1. The query is actually generated out of a 4GL so difficult to modify in some ways.

The key question though for me is, why is the plan not using the materialized view - I think it should be?