December 16, 2011 at 7:22 am
I am exporting a table from Oracle to SQL. The SQL table is for a data warehouse so it is not going to get updated except:
Once a day, I pull from the Oracle table to a staging table in SQL. The SQL table needs to be
1) overwritten with the data
2) insert, update, delete
3) renamed
BUT
I cannot have any down time.
We do the same in Oracle by using a Materialized View, I did some research and found Indexed Views in SQL. MS site is kinds blah with examples, do anyone know of any good examples? Is this the way I should be going? thought?
p.s. Thanks for the help.
December 17, 2011 at 4:38 pm
bryon.howe (12/16/2011)
We do the same in Oracle by using a Materialized View, I did some research and found Indexed Views in SQL. MS site is kinds blah with examples, do anyone know of any good examples? Is this the way I should be going?
Not "materialized views" per-se in SQL Server but index-views do the trick.
Check here... http://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx Queries 11, 12 and 13 show different scenarios.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 19, 2011 at 7:15 am
Thanks for the help. This will get me moving forward. You are the man !!!!!:cool:
December 20, 2011 at 12:34 pm
Byron,
I've found that synonyms can be very useful for this sort of thing also. Extract the data from your source, Oracle, massage it in the staging table, then drop and re-create the synonym so it points to the new table.
All access to the table would use the synonym name.
Todd Fifield
December 20, 2011 at 12:41 pm
Although the phrase Materialized views is not used often with MS SQL the Indexed views are the equivalent. As they must be materialized and stored very much like a table. From my experience with other DBMS platforms the underlying effect is really the same..
CEWII
December 21, 2011 at 3:23 am
Be aware that you cannot have a SQL Server Indexed View of an Oracle table. You must first import the data into a standard SQL Server table.
After you have done this, you should look at what the rest of your processing does. An Indexed View may be of help, but it is not common to need these in ETL code. The most common use case for an Indexed View is in an OLTP or BI system to improve speed of end user queries.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
December 21, 2011 at 9:04 am
Ed,
I largely agree with those statements. You cannot do indexes on a table on a linked server but you can on any local table. Perhaps I misread what was going on..
CEWII
December 21, 2011 at 4:02 pm
Elliott Whitlow (12/20/2011)
Although the phrase Materialized views is not used often with MS SQL the Indexed views are the equivalent. As they must be materialized and stored very much like a table. From my experience with other DBMS platforms the underlying effect is really the same..
I would say "pretty close" but I'm stopping short of saying "the same".
Here is why, in "other" RDBMS "Materialized View" usually goes hand-on-hand with its best friend "Query Rewrite" feature then, when a user writes a query against base tables and the RDBMS notices the query would be better served from the Materialized View then the query is re-written on the fly to point to the Materialized View. This is a very nice feature because the Materialized View is not visible to the final user, the final user don't even know it exists but the data will come from there.
I do not think SS includes such a feature - please correct me if I'm wrong.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.December 21, 2011 at 4:14 pm
PaulB-TheOneAndOnly (12/21/2011)
Elliott Whitlow (12/20/2011)
Although the phrase Materialized views is not used often with MS SQL the Indexed views are the equivalent. As they must be materialized and stored very much like a table. From my experience with other DBMS platforms the underlying effect is really the same..I would say "pretty close" but I'm stopping short of saying "the same".
Here is why, in "other" RDBMS "Materialized View" usually goes hand-on-hand with its best friend "Query Rewrite" feature then, when a user writes a query against base tables and the RDBMS notices the query would be better served from the Materialized View then the query is re-written on the fly to point to the Materialized View. This is a very nice feature because the Materialized View is not visible to the final user, the final user don't even know it exists but the data will come from there.
I do not think SS includes such a feature - please correct me if I'm wrong.
Yup... with NOEXPAND hint.
Per BOL:
You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don't initially reference the view explicitly.
http://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx
December 21, 2011 at 4:27 pm
BTW here is another point of difference between Oracle & SQL Server. The definition of rewrite.
Oracle:
Query rewrite, which transforms a SQL statement expressed in terms of tables or views into a statement accessing one or more materialized views that are defined on the detail tables.
SQL Server:
If you query a view (not Indexed) it Optimizer transforms it to query on detail tables.
So SELECT * FROM VW_EMP is rewritten as SELECT * FROM EMP.
January 23, 2012 at 7:06 am
Dev (12/21/2011)
PaulB-TheOneAndOnly (12/21/2011)
Elliott Whitlow (12/20/2011)
Although the phrase Materialized views is not used often with MS SQL the Indexed views are the equivalent. As they must be materialized and stored very much like a table. From my experience with other DBMS platforms the underlying effect is really the same..I would say "pretty close" but I'm stopping short of saying "the same".
Here is why, in "other" RDBMS "Materialized View" usually goes hand-on-hand with its best friend "Query Rewrite" feature then, when a user writes a query against base tables and the RDBMS notices the query would be better served from the Materialized View then the query is re-written on the fly to point to the Materialized View. This is a very nice feature because the Materialized View is not visible to the final user, the final user don't even know it exists but the data will come from there.
I do not think SS includes such a feature - please correct me if I'm wrong.
Yup... with NOEXPAND hint.
Per BOL:
You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don't initially reference the view explicitly.
http://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx
Well... not the same. BOL note suggest a person has to rewrite the query to reference the view while in the Oracle world "query rewrite" means that Oracle will do it - on the fly - for you.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.January 23, 2012 at 9:09 am
PaulB-TheOneAndOnly (1/23/2012)
Dev (12/21/2011)
PaulB-TheOneAndOnly (12/21/2011)
Elliott Whitlow (12/20/2011)
Although the phrase Materialized views is not used often with MS SQL the Indexed views are the equivalent. As they must be materialized and stored very much like a table. From my experience with other DBMS platforms the underlying effect is really the same..I would say "pretty close" but I'm stopping short of saying "the same".
Here is why, in "other" RDBMS "Materialized View" usually goes hand-on-hand with its best friend "Query Rewrite" feature then, when a user writes a query against base tables and the RDBMS notices the query would be better served from the Materialized View then the query is re-written on the fly to point to the Materialized View. This is a very nice feature because the Materialized View is not visible to the final user, the final user don't even know it exists but the data will come from there.
I do not think SS includes such a feature - please correct me if I'm wrong.
Yup... with NOEXPAND hint.
Per BOL:
You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don't initially reference the view explicitly.
http://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx
Well... not the same. BOL note suggest a person has to rewrite the query to reference the view while in the Oracle world "query rewrite" means that Oracle will do it - on the fly - for you.
Paul,
I think you missed the most important part. NOEXPAND is only necessary for standard edition, for enterprise the indexed view is used automatically. What this really comes down to is that the optimizer will try to rewrite the query in standard edition and not in enterprise, unless NOEXPAND is used. I think that this has been true since SQL 2000..
CEWII
January 23, 2012 at 9:39 am
Following snippet is from the same article I suggested earlier.
Use NOEXPAND if you want to be sure to have SQL Server process a query by reading the view itself instead of reading data from the base tables. If for some reason SQL Server chooses a query plan that processes the query against base tables when you'd prefer that it use the view, consider using NOEXPAND. You must use NOEXPAND in all versions of SQL Server other than Developer and Enterprise editions to have SQL Server process a query against an indexed view directly.
January 23, 2012 at 9:48 am
However it’s irrelevant to SSC (& SQL Server) but to complete the discussion, Oracle provides us the control on refresh rate of the Materialized View. It’s a major feature (as well as bottleneck).
January 23, 2012 at 11:37 am
Elliott Whitlow (1/23/2012)
PaulB-TheOneAndOnly (1/23/2012)
Dev (12/21/2011)
PaulB-TheOneAndOnly (12/21/2011)
Elliott Whitlow (12/20/2011)
Although the phrase Materialized views is not used often with MS SQL the Indexed views are the equivalent. As they must be materialized and stored very much like a table. From my experience with other DBMS platforms the underlying effect is really the same..I would say "pretty close" but I'm stopping short of saying "the same".
Here is why, in "other" RDBMS "Materialized View" usually goes hand-on-hand with its best friend "Query Rewrite" feature then, when a user writes a query against base tables and the RDBMS notices the query would be better served from the Materialized View then the query is re-written on the fly to point to the Materialized View. This is a very nice feature because the Materialized View is not visible to the final user, the final user don't even know it exists but the data will come from there.
I do not think SS includes such a feature - please correct me if I'm wrong.
Yup... with NOEXPAND hint.
Per BOL:
You can use the NOEXPAND hint if you want to force the query processor to use the indexed view. This may require you to rewrite your query if you don't initially reference the view explicitly.
http://msdn.microsoft.com/en-us/library/dd171921(v=SQL.100).aspx
Well... not the same. BOL note suggest a person has to rewrite the query to reference the view while in the Oracle world "query rewrite" means that Oracle will do it - on the fly - for you.
Paul,
I think you missed the most important part. NOEXPAND is only necessary for standard edition, for enterprise the indexed view is used automatically. What this really comes down to is that the optimizer will try to rewrite the query in standard edition and not in enterprise, unless NOEXPAND is used. I think that this has been true since SQL 2000..
CEWII
Thank you Elliott.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply