View vs Index??

[Guest.Visitor]

Someone recently asked me the differences between Indexes and views. Here is what I thought, can someone add anything more to this or explain to me where I'm wrong? LOL Jeff - In what ways are views better than well designed (and correctly used) indexes? Answer (Although I haven't sent it yet cause I hate being wrong) When the Optimizer makes a decision on how to retrieve records it bases that decision on an estimate of the number of rows the request may return, against the number of rows within the table. Utilizing a View of the actual table prior to the request would alter that estimate. The Optimizer will be making it's decision based on the number of rows available through the view. As in the case below this view is substantially smaller than the Physical Table. That saves overhead on the processor and speeds the request along. Whereas an index of the actual table is still a full set of rows keyed to a particular field or even multiple keys within the table. When using an index to retrieve the records for a program into main memory, the system will retrieved deleted records and page them into memory as well as good rows. When using a view where the data is pre-selected on a machine level the deleted records are omitted and never paged into memory, making the program more efficient. In addition to the overhead there is also the benefit of data independence. If your programs are compiled to the tables themselves, any changes to the tables will require a recreation of the programs and indexes to incorporate the changes. By utilizing the View in your program compilation instead of the tables record format, it frees you to make changes to the tables without having to recompile your load libraries. A view is not necessarily an index. A view can be a subselect of one or multiple large tables. The advantage to this can not be correlated with Interactive SQL Vs a Batch request since your statistics will be very different utilizing similar SQL statements against the same table. Although the particular request you added to this email will deliver similar response time whether the index is used or whether a view is created and used, the views offer greater application flexibility overall no matter how the requests are actually written, which is why I suggested their use. HREF="mailto:Jeff_Importico@Vanguard.com">(Jeff_Importico@Vanguard.com)

[Guest.Visitor]

On Tuesday, July 06, 1999, 09:05 AM, David Abramowitz wrote: On Tuesday, July 06, 1999, 07:14 AM, Jeff Importico wrote: I'm not one for wasting valuable time sitting around Vendor booths.

---

I take it then you have very few key chains, T-shirts, and Pens. David Abramowitz

---

No Way !! I make sure to stop by any vendor booth that has a key chain..oh and those cute little mints always get me too. HREF="mailto:Jeff_Importico@Vanguard.com">(Jeff_Importico@Vanguard.com)

[Guest.Visitor]

Debbie, David, Jeff,I have attended both conferences every year for the last 8 years. They are quite different. IBM's has fewer session periods (4 per day), 1 3/4 hrs each, 8:30 to 5, a regular lunch break, and all courses are taught totally by IBM personnel. None are cancelled.COMMON has 8 session periods each day, 1 1/4 hrs each, 8 to 6, no lunch break, some courses taught by IBMers, some taught by folks like us. There are about 25% sessions cancelled because of family sickness, deaths, job changes, you name it.Both have exhibitors. COMMON has the most, and is the most liberal regarding show times. The exibitors are there from Sunday thru Tuesday. You can attend or go to classes. That's elective.Like David pointed out, there is a difference in flavors in the two conferences. IBM tends to drive a curriculum. COMMON has recommended curriculums, but there's so much to learn people tend to skip around. I think David has it reversed though. I think the IBM conference teaches "what it is" and "how to do it". COMMON dwells on highlights of a topic in some instances, the details of "what it is" in other instances. The hands on labs teach how to do it (very popular). Double sessions also add some depth to topics.Hope this helps.BTW, I'll be teaching at COMMON in San Antonio if anyone needs education in working with their auditors.Regards. There is occassional brain deaths at days end for the new ones. They try to hit everything. Jack McGuigan - MIS Manager - American Life Ins. Co.

[Guest.Visitor]

On Tuesday, July 06, 1999, 04:31 AM, Jo Ann Burelle wrote: Can your machine handle 50 million+ records?' - Jo Ann It is bad to suppress laughter, it goes back down and spreads to your hips.

---

Or, can it keep track of 6000 jobs and 1400 active users; Not a chance .......

 
 Bob Hamilton TEXAS BUSINESS SYSTEMS 736 Pinehurst Richardson, Texas 75080 
 

[Guest.Visitor]

On Tuesday, July 06, 1999, 06:43 AM, Ted Holt wrote: One of my clients has a 170. Some of the folks there want to throw it out & replace it with a PC network running NT. I tell them (1) prepare for a lot of downtime & (2) get ready to hire a full-time computer wienie (No, thanks, I'm not going to apply for the job.)

---

Ted ; couple of things;;; You might be obligated to explain the situation to them; You are not obligated to understand it for them... Also, after they don't understand it, help them move to NT; then later on help them move back.... ......

 
 Bob Hamilton TEXAS BUSINESS SYSTEMS 736 Pinehurst Richardson, Texas 75080 
 

[Guest.Visitor]

A VIEW is a logical (re)presentation of the data which can include collation, and an index is merely a collation of data/rows across specific column(s) of the records. Given the real question is actually "VIEW vs Keyed Logical file", then the comparison is basically that with a VIEW you let the optimizer possibly find/build an index to accomplish an ordering/selection task just like for any query request, and with the keyed LF with direct I/O the order/selection available is only what is built into that particular 'index'. Both the VIEW and a DDS LF can effect static definitions to prevent recompiles, just that there are fewer mappings available in a DDS LF. Regards, Chuck Comments provided "as is" with no warranties of any kind whatsoever.