[intro music]
[theme music]
The third element we are going to show in terms of set-based programming,
and we've just shown loading and deduplication,
is transformation. I don't know how many have you have actually ever tried
to do a bulk update on 10 million rows or a bulk
delete on 10 million rows and realize that they
took a long time to run, and then you type control C,
and then you found you had to wait even longer while it recovered,
and for that period of time the data was off line,
and nothing was getting done, and at the end of the day
probably still haven't got your transformation done, your bulk update.
So in reality, when we get to large data sets
the buffer cache becomes the problem itself.
You can't afford to cache all the data, modify all the data in memory inside the buffer
cache,
and and then write it all back as well as the
associated undo and redo. The challenge we get on this whole problem is that
the buffer cache just cannot push enough blocks through fast enough
and the overhead, the CPU cost of just manipulating blocks into the buffer cache
in and back out again
one block at a time, usually an 8K block at a time,
means that the database writer is working really hard, and the user processes are
working really hard just to
get the volume of buffers through. So again
for manipulating data, we need to start thinking about transformation techniques
that basically redefine the object rather than modify the object in place.
So if I was to think about if you were to write a query
to redefine the data how you would like to see it in the future
and then directed into a table and then you were to change the table names at
the end,
it would take lot less time than if you wanted to manipulate all the data
in place and then fit it.
As an update or delete statement or a merge statement, we're gonna save a lot
of time and effort and redo
and undo, and all those things and you'd be able to take the advantage of core
database parallelism techniques,
bulk i/o techniques such that we read a megabyte and we write a megabyte
in and out at the time rather than 8K at a time.
We can get a huge efficiencies in terms of manipulating data.
And this become extremely important as the data sets get bigger and bigger and
as the datasets get bigger and bigger, it may be the
only way that you can effectively manipulate the data in real-time.
So again we're going to do a similar thing is what we did. We are going to run a race
and we are going to do 101 million rows and what we're going to do is to transform some data.
And one of those things I'm gonna start up the race
and then we're going to talk through how the code looked
and how it was done. Again, all the hardware is identical
but the thing is to notice that we have coded it different ways,
and we're getting a measure how the performance works.
So in this case you can see the set based technique
and this is why this session is on set based programming is
way in advance of everybody else but
again you can see that the row-by-row and and array processing is only using one CPU,
whereas the home-grown parallelism and set-based processing
is using a lot of CPUs and you can see that likewise the set-based
technique has finished way in advance
of everybody else. Let's just recap the times
and 26 seconds to manipulate 101
million rows and
1:52, no sorry, one minute 52 seconds
for home-grown programming parallelism,
11 minutes for the array interface and five hours
for one row at a time. This is
a dramatic way difference in performance, again five hours to
26 seconds as a means of manipulating data.
Let's just go have a look at the code again.
If we look at the row-by-row programming techniques and you can see
with got a cursor loop and we are looping through the data,
applying conditional logic, modifying the data
and then writing it out to potentially two tables. Again,
you can see that a lot of code being written. Processing is done by the row,
one row at a time, and that's how it's working.
Well I won't show you too much detail on the other two because I'm sure you can
guess
what we've done on racer 2. We've used bulk operators to put
and get the data, grab the data as a fetch case out of the
data, we have manipulated the data
in a in a tight loop, one row at a time using bulk
operators to rock it back. With a manual
parallelism we have used that ora_hash function again
to split the data up among 32 query slaves so we can get everything busy.
The last one I'd like to show you, really is the one we want to look at,
and look at this set-based programming. And again,
you can see the code is a lot easier. We are using the Insert
technique and we've got conditional logic associated where it goes on.
But again, what we are starting to do is we know
with starting to build pipelines, okay.
So you can see that we've used in fact the with clause here,
alright, to define a sequel statement, okay,
to define certain things from certain days
and we've also basically used, in this case,
some conditional logic, i.e. a case statement,
on the select list to figure out
what codes we're going to be using
to determine the next stage of the processing.
And then the very simple thing is we're doing a transformation here
and you can see that we're basically using some outer join
techniques, set-based techniques. And outer joins become an incredibly
important thing to learn when manipulating set to data,
so we can see them being used here. And we're doing joins
on this case to manipulate the data.
So, again you can see we are starting to build a pipeline,
and one of the easiest ways to build a pipeline that makes code that is readable
is to use the with clause to have a sequel statement, okay,
and then we basically using conditional logic
on the select list which would represent
a lot of the code you would normally associate with a normal UPDATE statement
and that is being manipulated by a case statement on the select list.
So you can see, we're again, the set-based programmer here
is using a mixture of techniques to pipeline rows
that will be handled by core parallelism through all phases of the transaction.