RE: Question on views
I think that's a fair characterization. What makes them cool (and dangerous) is that you can set Oracle up to automatically treat queries like SELECT * from MyTable as if they said SELECT * FROM MyMaterializedView instead. That's the query rewrite feature. If the MV is based on a query that takes a long time to run (and that's generally where you're using them) then you can get blindingly fast perf where you'd otherwise get bogged down. But at the cost of storage space and (depending on how you have them set up) some currency. And, there are a couple of different types of MVs--some of them are 'fast refreshable' (meaning a refresh doesn't require completely ditching the existing data recomputing all of it from scratch) and some aren't. For a fast refreshable MV, you can even specify that it should be updated transactionally along with its dependant tables. All this more are in the OLAP docs: http://download-west.oracle.com/docs/cd/A91202_01/901_doc/server.901/a90 237/mv.htm#33614 Cheers, -Roy P.S. I think mssql's analogue to this is the 'indexed view', but am not sure. Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 13, 2003 7:34 AM To: Multiple recipients of list ORACLE-L Thank you very much. Come to find our the MV the ICDB folks were using did not have 'stale' data in it, but it flat out had the wrong data in it because they used the wrong selection parameters. So essentially a MV is a SQL select stored to a table, kinda of like: SELECT * FROM MyTable INTO MyOtherTable v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Pardee, Roy E [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 6:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: Question on views From the concepts manual: quote Materialized Views A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment. /quote Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 8:25 AM To: Multiple recipients of list ORACLE-L It is confirmed it is a materialized view. How can they go stale? What is the difference in them and a traditional view? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Question on views Stephen, Traditional views don't go stale though they can be made invalid if the underlying objects change. This shouldn't really be happening on a regular basis unless the schema is changing rapidly. He may be using a materialized view. These can go stale. Before going any further ask him which of these is the cause and get the name of the view. I'd have thought that if a materialized view was created by his application then it's his responsibility. Cheers, Mike Hately, Consultant DBA -Original Message- [mailto:[EMAIL PROTECTED] Sent: 12 August 2003 13:40 To: Multiple recipients of list ORACLE-L I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that met diabetic conditions stored in the database over the past year, how can a view go 'stale
RE: Question on views
you mixed a lot of functional information in your question, so its difficult to follow the exact 'oracle' question you are asking. Sorry, but I'm still learning. if you are using a 'materialized view' you will need to refresh the snapshot? this is used if you have calculations(like sums, etc...) that you want to pre-compute to speed things up and its easy to refresh. Yes the contractor is using a 'materialized view', I have never heard of that kind of view, is like a parameterized view with computed columns or what? Is the term 'materialized' an Oracle specific term? see my embedded comments below... and you may want to question the 'consultant' about his level of experience. I've never heard of anyone using the term 'stale'. From: Wolfe Stephen S GS-11 6 MDSS/SGSI [EMAIL PROTECTED] Date: 2003/08/12 Tue AM 09:39:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Question on views I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. has your logic changed? No, the view should return columns (existing and computed) of static data residing across three tables. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that same thing happens in Oracle. Oracle uses rollbacks to achieve this, SQL Server locks the rows. Has the same effect in answering the question Understood. met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? In oracle a standard view is a select statement. what is the background of this 'consultant'. Is he a vendor specific guy or is he an oracle guy? what does he mean by 'stale'? Its hard to judge what your asking. There is too much functional information in your question. Since Im not familiar with your system its hard for me to follow. I have never heard of the term 'stale' either ... I think they are just trying to buy time to fix the problem and I keep bugging them with the 'how' type of questions. Thanks, your response has been helpful. v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question on views
Thank you very much. Come to find our the MV the ICDB folks were using did not have 'stale' data in it, but it flat out had the wrong data in it because they used the wrong selection parameters. So essentially a MV is a SQL select stored to a table, kinda of like: SELECT * FROM MyTable INTO MyOtherTable v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Pardee, Roy E [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 6:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: Question on views From the concepts manual: quote Materialized Views A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment. /quote Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 8:25 AM To: Multiple recipients of list ORACLE-L It is confirmed it is a materialized view. How can they go stale? What is the difference in them and a traditional view? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Question on views Stephen, Traditional views don't go stale though they can be made invalid if the underlying objects change. This shouldn't really be happening on a regular basis unless the schema is changing rapidly. He may be using a materialized view. These can go stale. Before going any further ask him which of these is the cause and get the name of the view. I'd have thought that if a materialized view was created by his application then it's his responsibility. Cheers, Mike Hately, Consultant DBA -Original Message- [mailto:[EMAIL PROTECTED] Sent: 12 August 2003 13:40 To: Multiple recipients of list ORACLE-L I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 ** ** E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street
Re: Question on views
- Original Message - 'view' crafted for that detection is going 'stale'. sounds fishy to me... getting a smoke screen as I suspect? Bingo! Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question on views
It is confirmed it is a materialized view. How can they go stale? What is the difference in them and a traditional view? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Question on views Stephen, Traditional views don't go stale though they can be made invalid if the underlying objects change. This shouldn't really be happening on a regular basis unless the schema is changing rapidly. He may be using a materialized view. These can go stale. Before going any further ask him which of these is the cause and get the name of the view. I'd have thought that if a materialized view was created by his application then it's his responsibility. Cheers, Mike Hately, Consultant DBA -Original Message- [mailto:[EMAIL PROTECTED] Sent: 12 August 2003 13:40 To: Multiple recipients of list ORACLE-L I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 ** ** E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 ** ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: Question on views
you mixed alot of functional information in your question, so its difficult to follow the exact 'oracle' question you are asking. if you are using a 'materialized view' you will need to refresh the snapshot? this is used if you have calculations(like sums, etc...) that you want to pre-compute to speed things up and its easy to refresh. see my embedded comments below... and you may want to question the 'consultant' about his level of experience. Ive never heard of anyone using the term 'stale'. From: Wolfe Stephen S GS-11 6 MDSS/SGSI [EMAIL PROTECTED] Date: 2003/08/12 Tue AM 09:39:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Question on views I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. has your logic changed? I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that same thing happens in Oracle. Oracle uses rollbacks to achieve this, SQL Server locks the rows. Has the same effect in answering the question met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? In oracle a standard view is a select statement. what is the background of this 'consultant'. Is he a vendor specific guy or is he an oracle guy? what does he mean by 'stale'? Its hard to judge what your asking. There is too much functional information in your question. Since Im not familiar with your system its hard for me to follow. v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question on views
Stephen, Traditional views don't go stale though they can be made invalid if the underlying objects change. This shouldn't really be happening on a regular basis unless the schema is changing rapidly. He may be using a materialized view. These can go stale. Before going any further ask him which of these is the cause and get the name of the view. I'd have thought that if a materialized view was created by his application then it's his responsibility. Cheers, Mike Hately, Consultant DBA -Original Message- [mailto:[EMAIL PROTECTED] Sent: 12 August 2003 13:40 To: Multiple recipients of list ORACLE-L I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: Question on views
If they're using a materialized view without a refresh then the data in the materialized view will not stay in synch with the data in the tables that it was built from. They need to either manually refresh the materialized view or build a refresh group to do it on a regular basis. Wolfe Stephen S GS-11 6 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] MDSS/SGSI cc: Stephen.Wolfe Subject: RE: Question on views @macdill.af.mil Sent by: ml-errors 08/12/2003 11:24 AM Please respond to ORACLE-L you mixed a lot of functional information in your question, so its difficult to follow the exact 'oracle' question you are asking. Sorry, but I'm still learning. if you are using a 'materialized view' you will need to refresh the snapshot? this is used if you have calculations(like sums, etc...) that you want to pre-compute to speed things up and its easy to refresh. Yes the contractor is using a 'materialized view', I have never heard of that kind of view, is like a parameterized view with computed columns or what? Is the term 'materialized' an Oracle specific term? see my embedded comments below... and you may want to question the 'consultant' about his level of experience. I've never heard of anyone using the term 'stale'. From: Wolfe Stephen S GS-11 6 MDSS/SGSI [EMAIL PROTECTED] Date: 2003/08/12 Tue AM 09:39:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Question on views I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. has your logic changed? No, the view should return columns (existing and computed) of static data residing across three tables. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that same thing happens in Oracle. Oracle uses rollbacks to achieve this, SQL Server locks the rows. Has the same effect in answering the question Understood. met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? In oracle a standard view is a select statement. what is the background of this 'consultant'. Is he a vendor specific guy or is he an oracle guy? what does he mean by 'stale
Re: Question on views
I think that your contractor is used to working with RDBMSs that produce static views. In Oracle, that would be called a snapshot or materialized view. Oracle views, on the other hand, are dynamic views. They are simply logical constructs that rely on the underlying tables for their data. Therefore, Oracle views do not go stale. The first problem is one of vocabulary. Once you and the contractor are speaking the same language then you can address the real problem. Good luck. Wolfe Stephen S GS-11 6 To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] MDSS/SGSI cc: Stephen.Wolfe Subject: Question on views @macdill.af.mil Sent by: ml-errors 08/12/2003 09:39 AM Please respond to ORACLE-L I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a
Re: RE: Question on views
Im dont know much about other databases so I dont know how they implement materialized views. All you have to do to make a materialized view 'current is to refresh it. I dont remember the exact syntax. Its on OTN. This could take a while and should be done during off-hours. The reasoning behind a materialized view is that computing such as SUMS, division, adding, calculations etc... are expensive. So you create a structure that already computes the data for you based off of your existing tables. This way your queries are faster. However this is 'static'. If your data changes the materialized view does not dynamicly change(dont know if you can set it to dynamicly recalculate or not). see if your data changes, you need to 'refresh' the view. Couldnt you run a batch to refresh this nightly or weekly? I dont know what the data dictionary views are for materialized views off the top of my head. can someone help him out here? When you get the names of those views, go see if the view_name in question is there. If so then its a materialized view, if not he is pulling your leg. From: Wolfe Stephen S GS-11 6 MDSS/SGSI [EMAIL PROTECTED] Date: 2003/08/12 Tue AM 11:24:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Question on views you mixed a lot of functional information in your question, so its difficult to follow the exact 'oracle' question you are asking. Sorry, but I'm still learning. if you are using a 'materialized view' you will need to refresh the snapshot? this is used if you have calculations(like sums, etc...) that you want to pre-compute to speed things up and its easy to refresh. Yes the contractor is using a 'materialized view', I have never heard of that kind of view, is like a parameterized view with computed columns or what? Is the term 'materialized' an Oracle specific term? see my embedded comments below... and you may want to question the 'consultant' about his level of experience. I've never heard of anyone using the term 'stale'. From: Wolfe Stephen S GS-11 6 MDSS/SGSI [EMAIL PROTECTED] Date: 2003/08/12 Tue AM 09:39:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Question on views I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. has your logic changed? No, the view should return columns (existing and computed) of static data residing across three tables. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that same thing happens in Oracle. Oracle uses rollbacks to achieve this, SQL Server locks the rows. Has the same effect in answering the question Understood. met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? In oracle a standard view is a select statement. what is the background of this 'consultant'. Is he a vendor specific guy or is he an oracle guy? what does he mean by 'stale'? Its hard to judge what your asking. There is too much functional information in your question. Since Im not familiar with your system its hard for me to follow. I have never heard of the term 'stale' either ... I think they are just trying to buy time to fix the problem and I keep bugging them with the 'how' type of questions. Thanks, your response has been helpful. v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB
RE: Question on views
From the concepts manual: quote Materialized Views A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data, a materialized view contains the rows resulting from a query against one or more base tables or views. A materialized view can be stored in the same database as its base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment. /quote Roy Pardee Programmer/Analyst/DBA SWFPAC Lockheed Martin IT Extension 8487 -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 8:25 AM To: Multiple recipients of list ORACLE-L It is confirmed it is a materialized view. How can they go stale? What is the difference in them and a traditional view? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -Original Message- From: Hately, Mike (LogicaCMG) [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 10:04 AM To: Multiple recipients of list ORACLE-L Subject: RE: Question on views Stephen, Traditional views don't go stale though they can be made invalid if the underlying objects change. This shouldn't really be happening on a regular basis unless the schema is changing rapidly. He may be using a materialized view. These can go stale. Before going any further ask him which of these is the cause and get the name of the view. I'd have thought that if a materialized view was created by his application then it's his responsibility. Cheers, Mike Hately, Consultant DBA -Original Message- [mailto:[EMAIL PROTECTED] Sent: 12 August 2003 13:40 To: Multiple recipients of list ORACLE-L I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 ** ** E mail Disclaimer You agree that you have read and understood this disclaimer and you agree to be bound by its terms. The information contained in this e-mail and any files transmitted with it (if any) are confidential and intended for the addressee only. If you have received this e-mail in error please notify the originator. This e-mail and any attachments have been scanned for certain viruses prior to sending but CE Electric UK Funding Company nor any of its associated companies from whom this e-mail originates shall be liable for any losses as a result of any viruses being passed on. No warranty of any kind is given in respect of any information contained in this e-mail and you should be aware that that it might be incomplete, out of date or incorrect. It is therefore essential that you verify all such information with us before placing any reliance upon it. CE Electric UK Funding Company Lloyds Court 78 Grey Street Newcastle upon Tyne NE1 6AF Registered in England and Wales: Number 3476201 ** ** -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Hately, Mike (LogicaCMG) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name
RE: Question on views
Stephen, What does he mean by stale? Never heard of it. And what problem are you experiencing? Slow response? Missing Data? Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED] Sent: Tuesday, August 12, 2003 9:40 AM To: Multiple recipients of list ORACLE-L I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: RE: Question on views
i think its DBA_MVIEWS though I could be wrong. see what is in there. if your view is there then you are using materialized views. From: Wolfe Stephen S GS-11 6 MDSS/SGSI [EMAIL PROTECTED] Date: 2003/08/12 Tue AM 11:24:42 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: RE: Question on views you mixed a lot of functional information in your question, so its difficult to follow the exact 'oracle' question you are asking. Sorry, but I'm still learning. if you are using a 'materialized view' you will need to refresh the snapshot? this is used if you have calculations(like sums, etc...) that you want to pre-compute to speed things up and its easy to refresh. Yes the contractor is using a 'materialized view', I have never heard of that kind of view, is like a parameterized view with computed columns or what? Is the term 'materialized' an Oracle specific term? see my embedded comments below... and you may want to question the 'consultant' about his level of experience. I've never heard of anyone using the term 'stale'. From: Wolfe Stephen S GS-11 6 MDSS/SGSI [EMAIL PROTECTED] Date: 2003/08/12 Tue AM 09:39:36 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: Question on views I just had a contractor tell me that the problem with my Diabetic detection and Listing 'feature' in their product (Integrated Clinical Database, ICDB) is because the 'view' crafted for that detection is going 'stale'. has your logic changed? No, the view should return columns (existing and computed) of static data residing across three tables. I admit that most my SQL design experience is in MSSQL's T-SQL although I'm starting to come up to speed on SQL*Plus, anyway, the above explanation provided to be from the TriCare guy seems bogus. Simply put there are four or five good Lab Chemistry parameters one could use in a SQL select statement to determine if a patient is diabetic or a diabetic candidate without having specific ICD9 diagnosis coding that declares the patient diabetic. A select statement returns a cursor of data that meets the selection criteria and on MSSQL is a static snapshot of what is in the database at the time the query was executed. Thus, if a patient had parameters that same thing happens in Oracle. Oracle uses rollbacks to achieve this, SQL Server locks the rows. Has the same effect in answering the question Understood. met diabetic conditions stored in the database over the past year, how can a view go 'stale'? Is this just a Oracle peculiarity or am I getting a smoke screen as I suspect? In oracle a standard view is a select statement. what is the background of this 'consultant'. Is he a vendor specific guy or is he an oracle guy? what does he mean by 'stale'? Its hard to judge what your asking. There is too much functional information in your question. Since Im not familiar with your system its hard for me to follow. I have never heard of the term 'stale' either ... I think they are just trying to buy time to fix the problem and I keep bugging them with the 'how' type of questions. Thanks, your response has been helpful. v/r Stephen S. Wolfe, GS-11, DAFC Data Services Manager [EMAIL PROTECTED] (813) 827-9974 DSN 651-9974 -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfe Stephen S GS-11 6 MDSS/SGSI INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
