Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Won't Fix
-
1.1.1
-
None
Description
Generate a large date file and load this to a table loans_table:
mcmsysql test
|
|
|
create table loan_test (
|
loanid varchar(40),
|
PRODUCTTYPE varchar(120),
|
Center varchar(120)
|
) engine=columnstore;
|
exit;
|
|
|
for i in $(seq 0 8981822); do echo "loanid_$i|PRODUCTTYPE_$i|Center"; done > loan_test.tbl
|
cpimport test loan_test loan_test.tbl
|
The following create table will be very slow (hours):
create table tempdata_error as select distinct a.center from loan_test a inner join loan_test b on (a.loanid = b.loanid);
|
Change this to insert select and it will be fast:
create table tempdata_error(Center varchar(120)) engine=MyISAM;
|
insert into tempdata_error select distinct a.center from loan_test a inner join loan_test b on (a.loanid = b.loanid);
|
I believe what is happening is that the create table is getting executed as effectively vtable mode 0 so the joins are done as nested loop joins assuming indexes which is very slow. The insert into select is correctly pushed down to columnstore and so executes at a more appropriate speed.