Hi!

I saw that Tim wrote into this forum: we must avoid view with parameters if possible.

But manager allow to create them.

1.)

How can I call a parameterized view?
view getsum: select main.code, sum(item.value), count(item.value) from main, item where main.code=item.code and
main.code=:viewcode  group by code;

select * from getsum(viewcode=5)

2.)

If views are not parameterizable, can EDB use the base indexes on the views?
view getsum: select main.code, sum(item.value), count(item.value) from main, item where main.code=item.code group
by code

select * from getsum where code in (5, 12, ......)

So if main have index on "code" that can this select use it?

If not that what is the meaning of view?
Because everything is static, and it can supports only slow select... I can use stored procs...

3.)

What happening when I use t.* in the view?
The * is uses all fields, and other db systems are not allowed this, or use all fields on creation.

But if I add a new field to the table t, this not appears in the view - except if I recompile it.

What is the concept of EDB in this question?

Thanks for your help.

Sincerely:
  dd

<< But manager allow to create them. >>

Ignore that - it's a bug.  You can't use parameters in views.

<< If views are not parameterizable, can EDB use the base indexes on the
views?

So if main have index on "code" that can this select use it? >>

In this case, EDB will use the index that is created by the GROUP BY.  EDB
cannot use the base table's indexes when the view generates an insensitive
result set.  If it generates a sensitive result set, then it can use the
base table's indexes.

<< If not that what is the meaning of view? >>

Um, SQL doesn't even have the concept of indexes, so indexes have absolutely
nothing to do with the definition of a view.

<< Because everything is static, and it can supports only slow select... >>

Not true, see above.

<< What happening when I use t.* in the view?
The * is uses all fields, and other db systems are not allowed this, or use
all fields on creation.

But if I add a new field to the table t, this not appears in the view -
except if I recompile it. >>

EDB used to be this way, but it was changed back in 2.02.  Views now adjust
automatically if the base table columns change.

--
Tim Young
Elevate Software
www.elevatesoft.com

Dear Tim!

"Tim Young [Elevate Software]" wrote:
<< But manager allow to create them.
Ignore that - it's a bug.  You can't use parameters in views.  >>

Thanks! Ok, I forget it!

<< If views are not parameterizable, can EDB use the base indexes on the
views? So if main have index on "code" that can this select use it?
In this case, EDB will use the index that is created by the GROUP BY.  EDB
cannot use the base table's indexes when the view generates an insensitive
result set.  If it generates a sensitive result set, then it can use the
base table's indexes.>>

What is the meaning of the words "sensitive/insensitive" in your answer?
Possible do you spoke about select queries that cannot use indexes for example: select * from a where upper(x) = upper
(:nev) and in other cases when functions in "where" are manipulate data?

<< If not that what is the meaning of view?
Um, SQL doesn't even have the concept of indexes, so indexes have absolutely
nothing to do with the definition of a view. >>

Example:
I have complex query in the view.
I open it with sql query: select * from this_view where .....
Many of RDBMS trying to use indexes to find the faster way to make this query.
The simple way: RDBMS making the view into a temp table, and later it start a query on the collected data (without
indexes).
Sometimes it is better if I collect these datas to temp table, make the indexes, and make the final query.
But many RDBMS trying to find a way to it can use indexes that defined in the table used by view.

So it is different:

1. I can parameterize view
2  I make a "static select" in the view, and later I make parameterized query on view.

Thanks:
   dd

durumdara

>What is the meaning of the words "sensitive/insensitive" in your answer?

sensitive = live = you can edit the query and the changes are saved when you post - essentially you're looking at a table with a filter

insensitive = canned = you have to save any changes yourself using program code

>Possible do you spoke about select queries that cannot use indexes for example: select * from a where upper(x) = upper
>(:nev) and in other cases when functions in "where" are manipulate data?

The main things that make a query insensitive (canned) are the use of other tables in the query or not having an exact match to the ORDER BY clause in an index

Roy Lambert [Team Elevate]

<< Thanks! Ok, I forget it! >>

That was easy.

<< What is the meaning of the words "sensitive/insensitive" in your answer?
>>

See here for more information:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=0&topic=17

<< Possible do you spoke about select queries that cannot use indexes for
example: select * from a where upper(x) = upper (:nev) and in other cases
when functions in "where" are manipulate data? >>

No, the optimizability of the WHERE clause is independent of whether a
SELECT statement can return a sensitive result set.

<< Many of RDBMS trying to use indexes to find the faster way to make this
query. The simple way: RDBMS making the view into a temp table, and later it
start a query on the collected data (without indexes). Sometimes it is
better if I collect these datas to temp table, make the indexes, and make
the final query. But many RDBMS trying to find a way to it can use indexes
that defined in the table used by view. >>

We don't allow index creation on views that return insensitive result sets
right now, but intend to add it shortly, and that will allow you to make
sure that certain indexes exist for a given view when the view is created.
Right now, the best way to do so is to use a specific GROUP BY or ORDER BY,
and that will make sure that the insensitive result set that is created for
the view has an index on a specific set of columns.  However, this is
somewhat limited.

<< 1. I can parameterize view
2  I make a "static select" in the view, and later I make parameterized
query on view. >>

Just use a parameterized query (TEDBQuery component) on the view, like this:

SELECT * FROM MyView
WHERE MyViewColumn=:MyParameter

Provided that you set the TEDBQuery.RequestSensitive property to True, the
execution time will only consist of the time it takes to execute the
underlying view.  And that is only if the view hasn't already been opened
elsewhere in the same session.

--
Tim Young
Elevate Software
www.elevatesoft.com