|
03.04.2014, 13:53:21
#38604248
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
Ссылка на профиль пользователя:
|
|
|
Участник
Сообщения: 9 702
Рейтинг:
0
/ 0
|
|
|
|
hi all
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
SQL> recreate table t1(id int primary key, s01 varchar(4), s02 varchar(4));
SQL> create index t1_s01_s02 on t1(s01, s02);
SQL> create index t1_s02_s01 on t1(s02, s01);
SQL> commit;
SQL> insert into t1
CON> select row_number()over(), left(uuid_to_char( gen_uuid() ), 4), left(uuid_to_char( gen_uuid() ), 4)
CON> from rdb$types rows 5;
SQL> commit;
SQL> execute block returns(msg varchar(50), elapsed_ms int) as
CON> declare n int = 100000;
CON> declare t0 timestamp;
CON> begin
CON> t0=cast('now' as timestamp);
CON> msg='update in-place '||n||' times, table T1';
CON> while (n>0) do update t1 set s01=s02, s02=s01 where id=1 returning :n-1 into n;
CON> elapsed_ms = datediff(millisecond from t0 to cast('now' as timestamp));
CON> suspend;
CON> end
CON> ^
MSG update in-place 100000 times, table T1
ELAPSED_MS 2419
SQL> set term ;^
SQL> commit;
SQL> exit;
Других коннектов к базе нет.
Делаю теперь два раза gstat -r -t T1: до и после свипа.
Результат до свипа: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
Database "/var/db/fb30/tmpidxupd.fdb"
Database header page information:
Flags 0
Generation 32
System Change Number 0
Page size 4096
ODS version 12.0
Oldest transaction 22
Oldest active 23
Oldest snapshot 23
Next transaction 24
Sequence number 0
Next attachment ID 12
Implementation HW=AMD/Intel/x64 little-endian OS=Linux CC=gcc
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Apr 3, 2014 13:39:33
Attributes
Variable header data:
Sweep interval: 20000
*END*
Database file sequence:
File /var/db/fb30/tmpidxupd.fdb is the only file
Analyzing database pages ...
T1 (132)
Primary pointer page: 183, Index root page: 187
Total formats: 1, used formats: 1
Average record length: 21.00, total records: 5
Average version length: 21.00, total versions: 1, max versions: 1
Average fragment length: 0.00, total fragments: 0, max fragments: 0
Average unpacked length: 20.00, compression ratio: 0.95
Pointer pages: 1, data page slots: 1
Data pages: 1, average fill: 6%
Primary pages: 1, full pages: 0, swept pages: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Index RDB$PRIMARY9 (0)
Root page: 201, depth: 1, leaf buckets: 1, nodes: 5
Average node length: 4.40, total dup: 0, max dup: 0
Average key length: 3.00, compression ratio: 0.60
Average prefix length: 0.60, average data length: 1.20
Clustering factor: 1, ratio: 0.20
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Index T1_S01_S02 (1)
Root page: 208, depth: 2, leaf buckets: 87, nodes: 50005
Average node length: 3.02, total dup: 50000, max dup: 50000
Average key length: 2.02, compression ratio: 4.96
Average prefix length: 9.98, average data length: 0.02
Clustering factor: 1, ratio: 0.00
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 86
60 - 79% = 1
80 - 99% = 0
Index T1_S02_S01 (2)
Root page: 206, depth: 2, leaf buckets: 87, nodes: 50005
Average node length: 3.02, total dup: 50000, max dup: 50000
Average key length: 2.02, compression ratio: 4.96
Average prefix length: 9.98, average data length: 0.02
Clustering factor: 1, ratio: 0.00
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 86
60 - 79% = 1
80 - 99% = 0
Результат после: 1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
Database "/var/db/fb30/tmpidxupd.fdb"
Database header page information:
Flags 0
Generation 35
System Change Number 0
Page size 4096
ODS version 12.0
Oldest transaction 25
Oldest active 23
Oldest snapshot 23
Next transaction 26
Sequence number 0
Next attachment ID 18
Implementation HW=AMD/Intel/x64 little-endian OS=Linux CC=gcc
Shadow count 0
Page buffers 0
Next header page 0
Database dialect 3
Creation date Apr 3, 2014 13:39:33
Attributes
Variable header data:
Sweep interval: 20000
*END*
Database file sequence:
File /var/db/fb30/tmpidxupd.fdb is the only file
Analyzing database pages ...
T1 (132)
Primary pointer page: 183, Index root page: 187
Total formats: 1, used formats: 1
Average record length: 21.00, total records: 5
Average version length: 0.00, total versions: 0, max versions: 0
Average fragment length: 0.00, total fragments: 0, max fragments: 0
Average unpacked length: 20.00, compression ratio: 0.95
Pointer pages: 1, data page slots: 1
Data pages: 1, average fill: 5%
Primary pages: 1, full pages: 0, swept pages: 0
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Index RDB$PRIMARY9 (0)
Root page: 201, depth: 1, leaf buckets: 1, nodes: 5
Average node length: 4.40, total dup: 0, max dup: 0
Average key length: 3.00, compression ratio: 0.60
Average prefix length: 0.60, average data length: 1.20
Clustering factor: 1, ratio: 0.20
Fill distribution:
0 - 19% = 1
20 - 39% = 0
40 - 59% = 0
60 - 79% = 0
80 - 99% = 0
Index T1_S01_S02 (1)
Root page: 208, depth: 2, leaf buckets: 87, nodes: 50005
Average node length: 3.02, total dup: 50000, max dup: 50000
Average key length: 2.02, compression ratio: 4.96
Average prefix length: 9.98, average data length: 0.02
Clustering factor: 1, ratio: 0.00
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 86
60 - 79% = 1
80 - 99% = 0
Index T1_S02_S01 (2)
Root page: 206, depth: 2, leaf buckets: 87, nodes: 50005
Average node length: 3.02, total dup: 50000, max dup: 50000
Average key length: 2.02, compression ratio: 4.96
Average prefix length: 9.98, average data length: 0.02
Clustering factor: 1, ratio: 0.00
Fill distribution:
0 - 19% = 0
20 - 39% = 0
40 - 59% = 86
60 - 79% = 1
80 - 99% = 0
Почему для индексов остались прежние значения total dup & max dup ? Он же собрать должен был старые версии ключей, разве нет ? |
|
|
