Module 8 Challenge Part 4

00:00

I welcome back to modulate. This is less than 8.9 challenge for and this challenge. We're going to be implementing partitions as well as fixing the usual problems, which is the correct index targeting removing full table scans. So let's go ahead and take a look at that requirements document

00:19

to get a better idea of what we're doing.

00:22

All right, here we are at the requirements document. This is, ah, modulate course challenge. It's a challenge for optimized query and database architecture. Utilizing the axiomatic in database and the provided query optimized the query and database structure.

00:38

The instructions are as follows. Examine the provided quarry. Remove full table scans, implement appropriate index use when possible, add partitions at all levels, partition in order off module and then company. So that's referring. Teoh the two columns. So if you have the column

00:58

with the module keyword in it,

01:00

use that column.

01:02

If you do not use the company column,

01:04

for example, if both are available, use the Module column.

01:08

Make the number of partitions equal to the unique values. So if you have five different module types on that table, make five petitions.

01:19

My sequel unfortunately, does not support foreign keys with partitions.

01:25

Remove foreign keys If they get in the way. All levels of the query should be using partitions. So at the end of this challenge, your query or the equity that's been provided should be optimized to exclude all full table scans,

01:41

implementing index use where possible and should be using partitions at all levels.

01:46

Now go ahead and pause the video, Review the query and see if you can come up with a solution on your own.

01:53

Now, with that said, let's go ahead and take a look at the query and see if you can't work through it to come up with a solution that satisfies this requirement document.

02:05

All right, here we are at the problem. Query. Let's go ahead and get started. First thing I'm going to do is run the query to see what kind of results I'm looking at. This kind of, Ah, I go off course really bad. I'll be I'll know because the results will look really bad. Okay, so it looks like

02:25

it looks like there has to be a value and either the cash amount or the debit amount.

02:30

Let's go ahead and run the explain command,

02:36

all right,

02:38

and it's like we have two full table scans and an index scan,

02:44

and it's using a temporary table. We'll see if we can get rid of that as we work through it. You do want to try to avoid using temporary if you can, but depending on database architecture and query requirements, sometimes that's just not really possible.

03:00

But we'll see if we could get rid of it.

03:01

So

03:05

first thing I'm going to do is copy this query. So I have a reference to what it used to be.

03:12

It's always a good idea.

03:15

I'm gonna separate this out, and I'm actually gonna bring these two and joined them with the batch table. And that will give me three joins instead of the two joints or the 1 to 2 additional joints, as opposed to just the one joint. I don't think we're getting much out of this type of table expression,

03:35

so let me go ahead and just raise that.

03:38

Don't say join

03:40

se Geo, Jill Tran

03:45

Call that G. L

03:46

on a deal. That company I d equals B, that company I D

03:53

and

03:53

Yale. That batch number

03:57

equals b dot bash number

04:00

and

04:00

jelled module

04:03

equals B That module,

04:08

and we've joined the jail Tran table. So let's join the sea. A Tran Table

04:15

Tran

04:16

C T.

04:18

On c t That company I d equals B that company i D.

04:26

And C t batch number

04:30

equals B that batch number

04:33

and C t that. I think this one's original module equals B dot module.

04:41

All right, so we're going to have to change this.

04:46

So that's going to cash waas on the sea. A trans table so C t dot

04:53

tran amount.

04:55

And that means the debit was on the other table.

04:58

So it'll be g l dot I'm referencing the table expression to check with the column. Should be looks like debit amount,

05:06

right?

05:09

We go ahead and run this. Make sure I'm still working correctly here

05:13

on that appears to be working just fine.

05:17

And let's put in that where

05:23

be dot

05:25

module

05:27

equals AP going. Not use D like statement because that's a bad style, especially when we know this is a two character field.

05:34

It doesn't make sense to use this,

05:38

and we can tell that because we go on, we look at the module, see It's two characters.

05:43

So what was this person thinking? Putting that, like, wild card and then the value? Um, we may never know what continuing on.

05:54

All right, so we're getting some results here,

06:00

okay?

06:03

And what do what else do we need to do? We need to do the company.

06:10

So and

06:12

And I'm gonna

06:13

reverse this because I like to use the company for us. That's part of the

06:17

I think models part of the key to,

06:20

but I like to do it this way.

06:25

So good and run that.

06:28

No. Okay,

06:30

let's go ahead and explain that. See what it looks like? Hopefully we didn't make it worse.

06:36

Okay, it looks like we don't have any full table scans, which is good,

06:42

right? We still have a temporary

06:44

now in the in. This instance, the temporary is being caused by the grouping, so we need to make sure that we're using a

06:51

A a index of company I D and batch number.

06:56

They're all using keys.

07:00

So be is using the primary key

07:04

and C T

07:08

is using the original Doc key.

07:11

What is that?

07:13

So the C T original Doc is original module.

07:17

The modules, part of that company ideas Part of that, but we're missing something.

07:21

So it's kind of like a partial index being used

07:26

because it has this and it has company, but it doesn't have the bash number. Let's go ahead and implement an index. That's all three of these fields

07:34

on the sea, a Tran table. And I think that'll fix that issue of that temporary table

07:41

company I D original module and batch number.

07:46

Let's see where that is. That's right there. Okay, save. Persist.

07:53

Let's go back and run that explain command again.

07:57

And we did. We got rid of that using temporary. So that successful got rid of all the

08:03

the full table scans. All we're missing is the partitions, and we need petitions on all the levels. So let's start with the batch table first.

08:15

So the batch table has the module,

08:18

um, field. So we're supposed to prioritize that as a partition. So that's what we're gonna dio.

08:24

So I'm gonna say, alter

08:28

table.

08:31

And this is, of course, just getting the, um,

08:33

the syntax right, because creating a partition is actually pretty easy.

08:39

Now there is one thing that I need to mention and that is, you do need to get rid of the foreign keys, so I've already done that in this database. So what you'll do is you'll go to the batch table and you'll click the foreign keys and delete them

08:52

and then save it. If you do not do that, you will get in there. That indicates that you can't partition a table with foreign keys.

09:00

So I've already done that, so I didn't need to do that.

09:01

So altar table batch partition

09:07

by key.

09:09

So I believe module is a key on the table, and it is

09:15

all right.

09:16

Key module.

09:18

Now we're supposed to add as many partitions is, there is. There is value. So let's were about to take a look at that.

09:28

So select

09:30

module

09:31

from

09:33

that

09:35

group by module.

09:39

So hopefully that gives us an easy answer.

09:45

And it did. There's nine. So partitions nine.

09:48

It's my calling

09:50

good, and we're on this command.

09:52

Give it a second to run because this has to copy all the data into a new zone on the hard drive, and then it deletes the old zone, which takes a little bit more time. So let's run the explain command on this again,

10:05

and you can see that this is working pretty well. There's nine partitions, and it knows that it needs to go to partition three.

10:13

So this is more efficient. It's going to a smaller data set.

10:16

So let's go ahead and move onto the next table, which is the G l table.

10:20

So, gl Tran,

10:22

have you got any foreign keys on here? I don't see any. If you see any, you will need to get rid of those. We do have the module as an option to partition by so same things. Like module from

10:33

Jill Tran.

10:35

See how many we have? It's probably gonna be nine again, though.

10:39

Yep. Nine.

10:41

So we're just gonna switch that keyword batch of this command to G. L. Tran.

10:46

We're on the partition command,

10:50

but that finish

10:54

it must have been in some extra transactions in that one. That would seem to take a little bit longer.

11:00

Yep. Is that right? Yeah, that's a lot of rose,

11:03

so

11:07

Oh, that looks good again. We're going to one. Partition happens to be P three again,

11:11

and that just leaves the C T table, which is the sea? A trans able Okay. Now working with C A Tran table is actually gonna present an issue. Unfortunately, we won't be a bubble Teoh. You also need to,

11:28

um, delete the foreign key.

11:30

I've already done that.

11:31

But if it's there for you, still you will need to delete that or this won't work.

11:35

We cannot use the original model because it's not a number, and it's not part of the key.

11:43

So we're going to use the company I D. Instead.

11:50

So altar table

11:54

see a tram

11:58

partitioned by key company.

12:01

Now I know there's only one company here, so this partition isn't going to make sense, but it's gonna meet the requirements. One.

12:09

Let's go ahead and run that

12:13

company. I d got a swell the field, right?

12:18

All right, run that it's running. It's done. Let's go ahead and explain.

12:26

Okay, so we have simple, select type. That's good. We're using partitions. That's good.

12:31

And the types are good.

12:35

We have a using temporary

12:39

on C T.

12:43

See if we can resolve that So temporary C company I. D. Batch number original module.

12:52

So it's using the original Doc key.

12:54

I think if we switch the key back to the um,

13:00

this one where we tell it to use this key. Then we'll get rid of the using temporary.

13:07

Okay, so let's see if we can get

13:09

rid of that using temporary by telling the execution plan which key we want to use.

13:16

So

13:18

I need to put it right here.

13:20

Needs it happened before your joints and your filters. If you place us in the wrong area, will not work.

13:28

So it should be used index

13:33

quotes.

13:33

I was gonna go ahead and copy this out

13:39

on Tell it that I specifically want to use this index, which is the index I made that matches the keys that we're using in the joint.

13:48

Let's go in and run this and see if that fixes the issue.

13:52

It looks like it might off.

13:54

And yes, it did. OK,

13:58

so now we've implemented partitions.

14:01

Our types look good, are select types look good

14:03

and our extras look good as well.

14:07

So let's take a look at the results.

14:09

Make sure those look reasonable.

14:16

Andi, I think we're good to go. That completes the requirements of this challenge. So I hope you were able to work through that. Get it working for you. And just as a note again, it's not recommended that you really partition your table

14:31

until you have over a 1,000,000 records on the table. So it's not something you definitely you definitely don't want to be doing it on small tables.