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

asked 17 Aug '11, 16:50

ivankb's gravatar image

ivankb
265101121
accept rate: 50%


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.

permanent link

answered 26 Sep '11, 16:04

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

Please post the view definition and the query.

permanent link

answered 18 Aug '11, 07:22

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

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

(24 Sep '11, 15:17) ivankb
Replies hidden

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...

(24 Sep '11, 16:04) Volker Barth

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?

(24 Sep '11, 16:59) ivankb
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×15

question asked: 17 Aug '11, 16:50

question was seen: 3,052 times

last updated: 26 Sep '11, 16:04