Hi All,



Consider the view: create view my_view as select ...whatever... from
....these tables... where ...stuff...



What is the difference between: select * from my_view;

and using the select on which the view is based: ie . just doing select
....whatever... from ...these tables... where ...stuff...



I thought the answer would be nothing. They should both come up with the
same query plan and execute identically.

I did not expect the select * from my_view to take several minutes and
the simple select to take a few seconds. The QEPs between the two are
radically different with the select from view having D/C numbers several
orders of magnitude higher than those of the simple select.



Martin Bowes

On Apr 23, 2009, at 7:12 AM, Martin Bowes wrote:

Marty,

This is identical to what happens in Oracle. I find this
extremely frustrating, in both databases.

I don't think we want Ingres to be emulating these sort of Oracle
features ;-)

Michael Leo
Director of Managed Services
Kettle River Consulting

MLeo (AT) KettleRiverConsulting (DOT) com
+1 612-859-2108 (cell)
+1 612-605-1978 (fax)

On Apr 23, 2009, at 8:12 AM, Martin Bowes wrote:

It really shouldn't be that way.

I suspect it's because my_view is not "mergeable" at parse time,
and survives as a view into the optimizer. At that point, opa
gets its grubby hands on things, and who knows what happens;
it may even materialize the entire view and then apply the
select on top of that. That in turn may cause sorting
strategies to be used, because at present Ingres only
has a sorted-agg-to-temp-file, it doesn't have a
hash-agg-to-temp-file. (agg-to-file is pretty rare,
and doing the hashed version of the FAGG action has
never made it off the do-someday list.)

All of the above is an excuse, rather than a reason,
and if your view is sufficiently simple it might not even
be a valid excuse. Care to share a test case?

Karl

PS I don't know if there is any simple way to tell if
the parser has merged a view or not; that's all psyview
crap and I've stayed as far from it as I can.

Damn straight!



Marty

On Apr 23, 2009, at 10:41 AM, Martin Bowes wrote:
It's refusing to do the NOT IN -> outer join transform, and using the
old quel-based flattening model.

I'm going to spend another few minutes with it to see if there is
some simple reason for it not doing this transform.

Karl

This e-mail and any files transmitted with it are the property of the
London Borough of Havering, are confidential, may be subject to legal privilege
and are intended only for the person(s) or organisation(s) named above.
Any unauthorised use, retention, distribution, copying or disclosure
is strictly prohibited. If you receive this email in error, please notify
the sender immediately and delete this e-mail from your system.
WARNING: It is your responsibility to take all necessary steps to
ensure this e-mail and any attachments to it are free from viruses.

On Apr 24, 2009, at 11:55 AM, Karl & Betty Schendel wrote:

The OJ transform is applied to the original query, *before* going
through the range table and expanding out any non-mergeable
views that are part of the range table.

Given the screwy way opa operates, I suspect that the simplest fix
is to have the OJ transformer run through the query's global range
table, looking for views. I don't know if it would have to do it
recursively or not. (e.g. v1 is select * from v2, v2 is your query
as a view, we'd like both select from v1 and select from v2
to transform.)

I'd open an issue on this one, there might be a reasonably simple fix
that doesn't break anything.

Karl