Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 5 of 5 total |
Are views parameterizable? |
Mon, Jan 25 2010 9:40 AM | Permanent Link |
durumdara | 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 |
Mon, Jan 25 2010 8:56 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
Tue, Jan 26 2010 5:01 AM | Permanent Link |
durumdara | 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 |
Tue, Jan 26 2010 5:35 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Tue, Jan 26 2010 6:41 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | << 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 |
This web page was last updated on Monday, May 6, 2024 at 01:09 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |