Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:01,440 --> 00:00:04,710
In this lesson, we're
looking at temporary tables.
2
00:00:04,710 --> 00:00:08,580
So a temporary table, we could
call it a transient table.
3
00:00:08,580 --> 00:00:11,520
It's a table that is
visible to all sessions,
4
00:00:11,520 --> 00:00:13,600
but the data is not.
5
00:00:13,600 --> 00:00:17,040
So a temporary table can be
very useful in situations
6
00:00:17,040 --> 00:00:20,400
where we need to
perhaps combine data
7
00:00:20,400 --> 00:00:22,320
from a number of
different tables,
8
00:00:22,320 --> 00:00:26,220
or we need to execute some
kind of function on a table,
9
00:00:26,220 --> 00:00:28,980
we need to kind of
gather data together
10
00:00:28,980 --> 00:00:31,710
for the purpose of
resolving an issue,
11
00:00:31,710 --> 00:00:34,740
but we don't need
to store the data.
12
00:00:34,740 --> 00:00:37,890
So we combine it
for that action,
13
00:00:37,890 --> 00:00:40,790
but then we don't
store the data.
14
00:00:40,790 --> 00:00:42,440
And a temporary
table is actually
15
00:00:42,440 --> 00:00:47,600
stored in a different place than
regular heap organized tables
16
00:00:47,600 --> 00:00:49,160
would have their data stored.
17
00:00:49,160 --> 00:00:51,110
So a temporary table
will be stored actually
18
00:00:51,110 --> 00:00:54,890
in temporary segments in
the temporary tablespace.
19
00:00:54,890 --> 00:00:57,380
So, when we create
a temporary table,
20
00:00:57,380 --> 00:01:02,150
we use the syntax create
global temporary table, then
21
00:01:02,150 --> 00:01:04,370
the definition of
the table, and then
22
00:01:04,370 --> 00:01:07,070
we have a clause
that says on commit.
23
00:01:07,070 --> 00:01:09,500
And, at that point,
we have two choices.
24
00:01:09,500 --> 00:01:13,670
So we can say on
commit, delete rows,
25
00:01:13,670 --> 00:01:15,800
and so that is a
temporary table that
26
00:01:15,800 --> 00:01:20,580
is specific to a transaction,
or preserve rows,
27
00:01:20,580 --> 00:01:22,670
which is specific to a session.
28
00:01:22,670 --> 00:01:27,110
So when we do on commit
delete rows, anytime a commit
29
00:01:27,110 --> 00:01:30,410
occurs in our session,
the rows will go away.
30
00:01:30,410 --> 00:01:31,880
They'll disappear.
31
00:01:31,880 --> 00:01:35,720
If we create our table
with preserve rows,
32
00:01:35,720 --> 00:01:39,530
then the table data will
exist for the entire session,
33
00:01:39,530 --> 00:01:42,980
at least until the user that
creates the temporary table
34
00:01:42,980 --> 00:01:44,360
logs out.
35
00:01:44,360 --> 00:01:46,970
So one is to delete
the rows on a commit
36
00:01:46,970 --> 00:01:49,760
and the next is to
preserve those rows.
37
00:01:49,760 --> 00:01:55,790
So let's do an example and look
at how this behavior works.
38
00:01:55,790 --> 00:02:01,060
Create global temporary table.
39
00:02:01,060 --> 00:02:02,340
Test temp 1.
40
00:02:04,930 --> 00:02:05,430
Col1.
41
00:02:08,740 --> 00:02:13,160
So, up to this point, a standard
way of defining a table.
42
00:02:13,160 --> 00:02:15,950
And then we say on commit.
43
00:02:15,950 --> 00:02:19,560
And, in this case,
we'll say delete rows.
44
00:02:22,540 --> 00:02:26,870
Change the name here.
45
00:02:26,870 --> 00:02:29,590
Right, so our test
temp 3 is created
46
00:02:29,590 --> 00:02:32,370
now with on commit delete rows.
47
00:02:32,370 --> 00:02:34,500
So let's look at
the behavior of it.
48
00:02:34,500 --> 00:02:39,200
Do a select star
from test temp 3.
49
00:02:39,200 --> 00:02:40,990
You see there's no data.
50
00:02:40,990 --> 00:02:48,670
Insert into test temp 3 values.
51
00:02:48,670 --> 00:02:53,290
We run that insert, and
we select from the table,
52
00:02:53,290 --> 00:02:55,520
and we see the values there.
53
00:02:55,520 --> 00:02:59,750
So we did this table with
on commit delete rows.
54
00:02:59,750 --> 00:03:02,410
So let's execute a commit.
55
00:03:02,410 --> 00:03:04,700
The commit occurs.
56
00:03:04,700 --> 00:03:07,300
And we select the data.
57
00:03:07,300 --> 00:03:09,920
And, again, since it's
on commit delete rows,
58
00:03:09,920 --> 00:03:11,910
those rows are now gone.
59
00:03:11,910 --> 00:03:15,770
If we want to create a temporary
table with on commit delete
60
00:03:15,770 --> 00:03:19,270
rows, we have to have a lot of
control over our transactions.
61
00:03:19,270 --> 00:03:21,020
We have to make sure
that a commit doesn't
62
00:03:21,020 --> 00:03:23,660
occur at the wrong time.
63
00:03:23,660 --> 00:03:27,800
So let's look at the behavior,
change a few things here.
64
00:03:27,800 --> 00:03:31,760
On commit reserve rows.
65
00:03:31,760 --> 00:03:35,260
So now we're going to create
another temporary table
66
00:03:35,260 --> 00:03:39,490
with the behavior preserve
rows instead of delete rows.
67
00:03:39,490 --> 00:03:43,850
And we'll look at the table.
68
00:03:43,850 --> 00:03:46,530
There are no rows.
69
00:03:46,530 --> 00:03:50,260
We'll insert some data.
70
00:03:50,260 --> 00:03:53,290
We select from the table.
71
00:03:53,290 --> 00:03:55,570
And we see our rows.
72
00:03:55,570 --> 00:03:58,380
So now, since it's an
on commit preserve rows,
73
00:03:58,380 --> 00:04:02,500
and we execute a
commit, we'll expect
74
00:04:02,500 --> 00:04:04,900
the rows to still be there.
75
00:04:04,900 --> 00:04:06,460
And, indeed, they are.
76
00:04:06,460 --> 00:04:10,180
So a temporary table with
preserve rows on a commit
77
00:04:10,180 --> 00:04:14,920
requires less discipline as far
as controlling the transaction
78
00:04:14,920 --> 00:04:17,990
in order to use the data
that's in the table.
6023
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.