Unneccessary index recreation occur when doing DDL "Alter table <T> alter <F>
set | drop default" and <F> is indexed field
--------------------------------------------------------------------------------------------------------------------------
Key: CORE-4971
URL: http://tracker.firebirdsql.org/browse/CORE-4971
Project: Firebird Core
Issue Type: Bug
Reporter: Pavel Zotov
When table has data and some of its fields ( say, "S" ) has index, excessive
reads, writes, fetches and marks will occur while doing "alter table alter <S>
set | drop default". Perhaps this is due to recreation of index though set/drop
default value does not require this.
Test:
====
recreate table mon_log(
measure smallint
,description varchar(50)
,dts timestamp
,trn int
,reads int
,writes int
,fetches int
,marks int
);
commit;
create or alter view v_mon_log as
select
current_timestamp dts
,current_transaction trn
,i.mon$page_reads reads
,i.mon$page_writes writes
,i.mon$page_fetches fetches
,i.mon$page_marks marks
from mon$attachments a
left join mon$io_stats i on a.mon$stat_id=i.mon$stat_id
where
a.mon$attachment_id = current_connection
;
commit;
recreate table test(s varchar(1000));
create index test_s on test(s);
insert into mon_log(
measure
,description
,dts
,trn
,reads
,writes
,fetches
,marks
)
select 1, 'After create table & index', v.*
from v_mon_log v;
commit;
alter table test alter s set default 'qwerty';
insert into mon_log(
measure
,description
,dts
,trn
,reads
,writes
,fetches
,marks
)
select 1, 'After alter table alter s add default <value>', v.*
from v_mon_log v;
commit;
alter table test alter s drop default;
insert into mon_log(
measure
,description
,dts
,trn
,reads
,writes
,fetches
,marks
)
select 1, 'After alter table alter s drop default', v.*
from v_mon_log v;
commit;
----------------------------------------------------------------
insert into test select rpad('', 1000, uuid_to_char(gen_uuid())) from
rdb$types,(select 1 i from rdb$types rows 30);
commit;
----------------------------------------------------------------
insert into mon_log(
measure
,description
,dts
,trn
,reads
,writes
,fetches
,marks
)
select 2, 'After insert N rows into table', v.*
from v_mon_log v;
commit;
alter table test alter s set default 'qwerty';
insert into mon_log(
measure
,description
,dts
,trn
,reads
,writes
,fetches
,marks
)
select 2, 'After alter table alter s add default <value>', v.*
from v_mon_log v;
commit;
alter table test alter s drop default;
insert into mon_log(
measure
,description
,dts
,trn
,reads
,writes
,fetches
,marks
)
select 2, 'After alter table alter s drop default', v.*
from v_mon_log v;
commit;
set list on;
select
g.measure
,g.description
,g.dts
,g.trn
,g.reads - lag(g.reads)over(partition by g.measure order by g.dts) diff_reads
,g.writes - lag(g.writes)over(partition by g.measure order by g.dts) diff_writes
,g.fetches - lag(g.fetches)over(partition by g.measure order by g.dts)
diff_fetches
,g.marks - lag(g.marks)over(partition by g.measure order by g.dts) diff_marks
from mon_log g
;
Result:
======
MEASURE 1
DESCRIPTION After create table & index
DTS 2015-10-21 23:48:46.5780
TRN 14
DIFF_READS <null>
DIFF_WRITES <null>
DIFF_FETCHES <null>
DIFF_MARKS <null>
MEASURE 1
DESCRIPTION After alter table alter s add default <value>
DTS 2015-10-21 23:48:46.6400
TRN 18
DIFF_READS 6
DIFF_WRITES 22
DIFF_FETCHES 313
DIFF_MARKS 37
MEASURE 1
DESCRIPTION After alter table alter s drop default
DTS 2015-10-21 23:48:46.7030
TRN 21
DIFF_READS 0
DIFF_WRITES 16
DIFF_FETCHES 300
DIFF_MARKS 33
MEASURE 2
DESCRIPTION After insert N rows into table
DTS 2015-10-21 23:49:54.0780
TRN 26
DIFF_READS <null>
DIFF_WRITES <null>
DIFF_FETCHES <null>
DIFF_MARKS <null>
MEASURE 2
DESCRIPTION After alter table alter s add default <value>
DTS 2015-10-21 23:49:59.7340
TRN 28
DIFF_READS 1666
DIFF_WRITES 1282
DIFF_FETCHES 24837
DIFF_MARKS 4873
MEASURE 2
DESCRIPTION After alter table alter s drop default
DTS 2015-10-21 23:50:05.1400
TRN 31
DIFF_READS 1132
DIFF_WRITES 1282
DIFF_FETCHES 22812
DIFF_MARKS 3848
1. Compare values of DIFF_* columns with the same suffixes for records with:
1) TRN = 18 vs 28 ("After alter table alter s add default <value>")
and
2) TRN = 21 vs 31 ("After alter table alter s drop default")
-- all of these values have too big or even undefined due to zero-division
ratio ( 1666 : 6; 1282 : 22; 24837 : 313 etc).
2. Query:
select rdb$statistics from rdb$indices where rdb$index_name='TEST_S';
-- gives (begin issued after all steps):
RDB$STATISTICS 0.0001312335953116417
This looks like index TEST_S was recreated, because statistics should be zero
(because it does not auto updated when we modify table AFTER its index has been
created - this is well known FB feature).
3. Trace statistics sample for "alter table alter s add default <...>": note
that there was "delay" about 8 (eight) seconds between two adjacent events:
2015-10-21T23:01:39.2340 (1200:01822A08) EXECUTE_STATEMENT_FINISH
e30 (ATT_34, SYSDBA:NONE, NONE, TCPv4:192.168.0.107)
C:\1INSTALL\FIREBIRD\fb30sS\isql.exe:3132
(TRA_0, CONCURRENCY | WAIT | READ_WRITE)
Statement 35:
-------------------------------------------------------------------------------
select rel.rdb$relation_id, rel.rdb$relation_type from rdb$indices idx join
rdb$relations rel using (rdb$relation_name) where idx.rdb$index_name = ? and
rel.rdb$relation_id is not null
param0 = varchar(93), "TEST_S"
1 records fetched
0 ms, 8 fetch(es)
Table Natural Index Update Insert
Delete Backout Purge Expunge
***************************************************************************************************************
RDB$INDICES 1
RDB$RELATIONS 1
2015-10-21T23:01:47.2500 (1200:01822A08) PREPARE_STATEMENT
e30 (ATT_34, SYSDBA:NONE, NONE, TCPv4:192.168.0.107)
C:\1INSTALL\FIREBIRD\fb30sS\isql.exe:3132
(TRA_0, CONCURRENCY | WAIT | READ_WRITE)
Statement 47:
-------------------------------------------------------------------------------
select rel.rdb$relation_id, rel.rdb$relation_type from rdb$indices idx join
rdb$relations rel using (rdb$relation_name) where idx.rdb$index_name = ? and
rel.rdb$relation_id is not null
1 ms
. . .
PS. WI-V3.0.0.32081
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
