Which one got problem? MDC or the optimizer ...

[Fan Ruo Xin]

Hi,
I have been watching MDC for a long time ... I have seen it from
INDEX-SCAN to INDEX ONLY for the DISTINCT (block index columns) operator
....
But the Elapsed time shows MDC still run slower. This did confuse me. Of
course I expected it should run faster, because of the small block index
tree, compare with the RID index tree.

select distinct(int(l_ shipdate)/100)
from lineitem;

MDC Non-MDC
real 2m26.26s real 1m34.73s
user 0m0.01s user 0m0.00s
sys 0m0.01s sys 0m0.03s
IX-ONLY (l_yymm) IX-ONLY (l_shipdate)

I did a little change for the table LINEITEM.

MDC table:
CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL,
....
L_SHIPDATE DATE NOT NULL,
....
L_COMMENT VARCHAR(44) NOT NULL,
l_yymm int not null generated always as (int(l_shipdate )/100) )
ORGANIZE BY (l_yymm)
IN SP1;
CREATE UNIQUE INDEX L_OKLN ON LINEITEM
(L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
alter table lineitem add primary key (l_orderkey, l_linenumber);
create index l_idx on lineitem(l_ship date);

Non-MDC table
CREATE TABLE LINEITEM2 ( L_ORDERKEY BIGINT NOT NULL,
....
L_SHIPDATE DATE NOT NULL,
....
L_COMMENT VARCHAR(44) NOT NULL))
IN SP1;
CREATE UNIQUE INDEX L_OKLN ON LINEITEM
(L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
alter table lineitem add primary key (l_orderkey, l_linenumber);
create index l_idx on lineitem(l_ship date);

Regards,
Fan

[temporary_10]

mdc is index-mostly - not all blocks are accessed, only first one in a
select distinct for each cell (access plan should show that). Prevent
phantom values.

Fan Ruo Xin wrote:

Hi,
I have been watching MDC for a long time ... I have seen it from
INDEX-SCAN to INDEX ONLY for the DISTINCT (block index columns) operator
...
But the Elapsed time shows MDC still run slower. This did confuse me. Of
course I expected it should run faster, because of the small block index
tree, compare with the RID index tree.

select distinct(int(l_ shipdate)/100)
from lineitem;

MDC Non-MDC
real 2m26.26s real 1m34.73s
user 0m0.01s user 0m0.00s
sys 0m0.01s sys 0m0.03s
IX-ONLY (l_yymm) IX-ONLY (l_shipdate)

I did a little change for the table LINEITEM.

MDC table:
CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL,
...
L_SHIPDATE DATE NOT NULL,
...
L_COMMENT VARCHAR(44) NOT NULL,
l_yymm int not null generated always as (int(l_shipdate )/100) )
ORGANIZE BY (l_yymm)
IN SP1;
CREATE UNIQUE INDEX L_OKLN ON LINEITEM
(L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
alter table lineitem add primary key (l_orderkey, l_linenumber);
create index l_idx on lineitem(l_ship date);

Non-MDC table
CREATE TABLE LINEITEM2 ( L_ORDERKEY BIGINT NOT NULL,
...
L_SHIPDATE DATE NOT NULL,
...
L_COMMENT VARCHAR(44) NOT NULL))
IN SP1;
CREATE UNIQUE INDEX L_OKLN ON LINEITEM
(L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
alter table lineitem add primary key (l_orderkey, l_linenumber);
create index l_idx on lineitem(l_ship date);

Regards,
Fan

[Fan Ruo Xin]

Thank you.

I think it will make more sense if we can remove the INDEX ONLY from the
dynexpln output report for SELECT DISTINCT.

And if using block index, the db2 agent still need to access the blocks (in
worst case, the number I/O reads may be equal to # of cells) to figure out the
DISTINCT value. Then if I suppose that using RID INDEX-ONLY (l_shipdate) is
better than Block INDEX-SCAN (l_yymm), does that make sense? Does the
optimizer estimate the cost of MDC overhead correctly?

Regards,
FRX

te**********@ho tmail.com wrote:

mdc is index-mostly - not all blocks are accessed, only first one in a
select distinct for each cell (access plan should show that). Prevent
phantom values.

Fan Ruo Xin wrote:

Hi,
I have been watching MDC for a long time ... I have seen it from
INDEX-SCAN to INDEX ONLY for the DISTINCT (block index columns) operator
...
But the Elapsed time shows MDC still run slower. This did confuse me. Of
course I expected it should run faster, because of the small block index
tree, compare with the RID index tree.

select distinct(int(l_ shipdate)/100)
from lineitem;

MDC Non-MDC
real 2m26.26s real 1m34.73s
user 0m0.01s user 0m0.00s
sys 0m0.01s sys 0m0.03s
IX-ONLY (l_yymm) IX-ONLY (l_shipdate)

I did a little change for the table LINEITEM.

MDC table:
CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL,
...
L_SHIPDATE DATE NOT NULL,
...
L_COMMENT VARCHAR(44) NOT NULL,
l_yymm int not null generated always as (int(l_shipdate )/100) )
ORGANIZE BY (l_yymm)
IN SP1;
CREATE UNIQUE INDEX L_OKLN ON LINEITEM
(L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
alter table lineitem add primary key (l_orderkey, l_linenumber);
create index l_idx on lineitem(l_ship date);

Non-MDC table
CREATE TABLE LINEITEM2 ( L_ORDERKEY BIGINT NOT NULL,
...
L_SHIPDATE DATE NOT NULL,
...
L_COMMENT VARCHAR(44) NOT NULL))
IN SP1;
CREATE UNIQUE INDEX L_OKLN ON LINEITEM
(L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
alter table lineitem add primary key (l_orderkey, l_linenumber);
create index l_idx on lineitem(l_ship date);

Regards,
Fan

[temporary_10]

I understand. Yes, you can see more what is happening from the db2exfmt output. I
understand your point from dynexpln. It is an index mostly access, so not pure
index, nor ifetch and or rid-list prefetch. Yes, it is costed correctly.

Fan Ruo Xin wrote:

Thank you.

I think it will make more sense if we can remove the INDEX ONLY from the
dynexpln output report for SELECT DISTINCT.

And if using block index, the db2 agent still need to access the blocks (in
worst case, the number I/O reads may be equal to # of cells) to figure out the
DISTINCT value. Then if I suppose that using RID INDEX-ONLY (l_shipdate) is
better than Block INDEX-SCAN (l_yymm), does that make sense? Does the
optimizer estimate the cost of MDC overhead correctly?

Regards,
FRX

te**********@ho tmail.com wrote:

mdc is index-mostly - not all blocks are accessed, only first one in a
select distinct for each cell (access plan should show that). Prevent
phantom values.

Fan Ruo Xin wrote:

Hi,
I have been watching MDC for a long time ... I have seen it from
INDEX-SCAN to INDEX ONLY for the DISTINCT (block index columns) operator
...
But the Elapsed time shows MDC still run slower. This did confuse me. Of
course I expected it should run faster, because of the small block index
tree, compare with the RID index tree.

select distinct(int(l_ shipdate)/100)
from lineitem;

MDC Non-MDC
real 2m26.26s real 1m34.73s
user 0m0.01s user 0m0.00s
sys 0m0.01s sys 0m0.03s
IX-ONLY (l_yymm) IX-ONLY (l_shipdate)

I did a little change for the table LINEITEM.

MDC table:
CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL,
...
L_SHIPDATE DATE NOT NULL,
...
L_COMMENT VARCHAR(44) NOT NULL,
l_yymm int not null generated always as (int(l_shipdate )/100) )
ORGANIZE BY (l_yymm)
IN SP1;
CREATE UNIQUE INDEX L_OKLN ON LINEITEM
(L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
alter table lineitem add primary key (l_orderkey, l_linenumber);
create index l_idx on lineitem(l_ship date);

Non-MDC table
CREATE TABLE LINEITEM2 ( L_ORDERKEY BIGINT NOT NULL,
...
L_SHIPDATE DATE NOT NULL,
...
L_COMMENT VARCHAR(44) NOT NULL))
IN SP1;
CREATE UNIQUE INDEX L_OKLN ON LINEITEM
(L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
alter table lineitem add primary key (l_orderkey, l_linenumber);
create index l_idx on lineitem(l_ship date);

Regards,
Fan

[Fan Ruo Xin]

Sometimes I am not sure if optimizer estimates the cost of MDC overhead correctly,
especially if the query need to touch almost all the blocks (either only a small
number of rows of the block, or all the rows of the block), and through Block Index
Scan.
For my case, maybe it is because of the Cell size of the MDC which I created is
relatively bigger.
Thanks,
FRX

te**********@ho tmail.com wrote:

I understand. Yes, you can see more what is happening from the db2exfmt output. I
understand your point from dynexpln. It is an index mostly access, so not pure
index, nor ifetch and or rid-list prefetch. Yes, it is costed correctly.

Fan Ruo Xin wrote:

Thank you.

I think it will make more sense if we can remove the INDEX ONLY from the
dynexpln output report for SELECT DISTINCT.

And if using block index, the db2 agent still need to access the blocks (in
worst case, the number I/O reads may be equal to # of cells) to figure out the
DISTINCT value. Then if I suppose that using RID INDEX-ONLY (l_shipdate) is
better than Block INDEX-SCAN (l_yymm), does that make sense? Does the
optimizer estimate the cost of MDC overhead correctly?

Regards,
FRX

te**********@ho tmail.com wrote:

mdc is index-mostly - not all blocks are accessed, only first one in a
select distinct for each cell (access plan should show that). Prevent
phantom values.

Fan Ruo Xin wrote:

> Hi,
> I have been watching MDC for a long time ... I have seen it from
> INDEX-SCAN to INDEX ONLY for the DISTINCT (block index columns) operator
> ...
> But the Elapsed time shows MDC still run slower. This did confuse me. Of
> course I expected it should run faster, because of the small block index
> tree, compare with the RID index tree.
>
> select distinct(int(l_ shipdate)/100)
> from lineitem;
>
> MDC Non-MDC
> real 2m26.26s real 1m34.73s
> user 0m0.01s user 0m0.00s
> sys 0m0.01s sys 0m0.03s
> IX-ONLY (l_yymm) IX-ONLY (l_shipdate)
>
> I did a little change for the table LINEITEM.
>
> MDC table:
> CREATE TABLE LINEITEM ( L_ORDERKEY BIGINT NOT NULL,
> ...
> L_SHIPDATE DATE NOT NULL,
> ...
> L_COMMENT VARCHAR(44) NOT NULL,
> l_yymm int not null generated always as (int(l_shipdate )/100) )
> ORGANIZE BY (l_yymm)
> IN SP1;
> CREATE UNIQUE INDEX L_OKLN ON LINEITEM
> (L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
> alter table lineitem add primary key (l_orderkey, l_linenumber);
> create index l_idx on lineitem(l_ship date);
>
> Non-MDC table
> CREATE TABLE LINEITEM2 ( L_ORDERKEY BIGINT NOT NULL,
> ...
> L_SHIPDATE DATE NOT NULL,
> ...
> L_COMMENT VARCHAR(44) NOT NULL))
> IN SP1;
> CREATE UNIQUE INDEX L_OKLN ON LINEITEM
> (L_ORDERKEY ASC, L_LINENUMBER ASC) PCTFREE 0 ;
> alter table lineitem add primary key (l_orderkey, l_linenumber);
> create index l_idx on lineitem(l_ship date);
>
> Regards,
> Fan