3.10 Purpose and Limitations

00:00

I welcome back the Module three sequel programming. This is less than three views and this is the first sub lesson 3.2 purpose and limitation of views. Now let's first described what a view is. A view is a virtual table that is created from a query. Now let me show you what I mean by that.

00:19

So if we look at this creative you statement,

00:22

we will see that it starts with a sequel. Command create view, followed by the name of the view that we're creating

00:30

fold by a query.

00:33

So what this will do this is will create a view that becomes a table that looks like the contents of the following query. Now this quarry is based on the AP employees position K B E x t table that we were that we worked on in a previous lesson.

00:51

So let's go ahead and run this statement

00:56

and left clicked on the views folder Click Refresh. Now we have this view which looks just like a table icon except that it has a little I on i on ite sorry and the EI icon on it as well. If we give that a double click, we will see that it gives us

01:15

options that are similar to when we just click on a table.

01:19

With a couple of exceptions, one being this source, we can see that the source is a query.

01:25

But if we click on the columns tab, we will see that this is this. Defines the column names with the data types, just like a table would. If we click on the data tab, we can see the data that's found inside this view, just like we would with a table.

01:42

And if we click on the E R diagram, we see a

01:46

table representation in the diagram section as well.

01:51

So if we wanted to drop the view or delete the view, it be very easy. It's just dropped view with view name

02:00

If I run that,

02:01

then left clicked on views

02:04

and click Refresh. Now it's gone.

02:07

Now when I'm creating views, I like to combine these two ideas together,

02:13

and that's to create or replace statement. So if I run this statement, create or replace view followed by the Vue name

02:20

by the query,

02:23

it will do the same thing as when I ran the create statement.

02:28

Now The nice thing about this statement is that I can make changes to the query

02:31

and just run it again.

02:38

Make more changes and run it again, and it will continue to run now. If you don't use the creator replace statement, you'll have to balance between the create statement and the drop statement.

02:50

For example, if you try to run just a great statement when you've already created the view, you'll get in there,

02:54

which is a object already exist air, which makes sense because you've already created it.

03:01

So what purpose does of you serve well? It serves two purposes. One It serves as a way to enforce security. For example, you might have specific tables that are considered sensitive information. For example, you might have salary information on a table

03:21

where you also need to get,

03:23

or you also need to make other information available.

03:27

So what a sequel programmer might do is creative you off that table that contains on Lee the infinite information needed by the other programmer thing. That programmer can use that view that was created to get the data that he or she needs now, another

03:45

purpose of a view is too

03:46

abstract, some complexity out of sight.

03:51

So if you have a pretty complicated query that is binding a bunch of tables together in, ah, odd way to produce data that somebody is interested in, well, you can create that you can take that complex query and put it in a view, and then the view will just look like a table. So then,

04:10

when somebody needs the

04:13

data related to that, they just go to the view, which looks like they're just pulling from a regular table.

04:19

Now, one of the limitations of view is that if they're used incorrectly, they can

04:26

start to really slow down performance wise, and they can become very inefficient.

04:30

And one of my past experiences, I had to deal with a view. There was actually a composition of other views. So it was view created by other views, and this view slowed down quite a bit. It was taken somewhere in the ballpark of 20 seconds to resolve, so to fix that, I went through all the views that

04:50

comprised the main view,

04:53

found all the tables in the data that was being pulled out of it, and rearrange that rear angels tables into one larger, complex query that I put behind one view. And the result of that was that the view became much more efficient and had about a second resolution, time of leave.

05:11

So to recap what we discussed here,

05:14

what is a view? A view is a virtual data table created from a query.

05:19

What is the strength of your view? Well, you can enforce security with a view. You can hide complexity with the view.

05:27

Um, what is a limitation? Well, if you aren't careful with views, they can start to drag on performance. And they could be. But they could become inefficient if the programmers don't take care to make sure that they aren't doing odd things like making views that depend on other views.