mysql> select substr(ID,1,4) ID1, r05,count(*)cnt,r03
  -> from ren41
  -> group by substr(ID,1,4),r05,r03 ;
+———+———+————-+———+
| ID1 | r05 | cnt   | r03 |
+———+———+————-+———+
| 4101 | 01   | 3387921 | 1   |
....

| 4114 | 27   |    1 | 2   |
| 4114 | 37   |    1 | 2   |
+———+———+————-+———+
1313 rows in set (2 min 29.40 sec)

2列

select substr(ID,1,4) ID1, r05,count(*)cnt
from ren41
group by substr(ID,1,4),r05;

| 4114 | 27   |    1 |
| 4114 | 37   |    1 |
+———+———+————-+
771 rows in set (1 min 23.23 sec)

1列
select substr(ID,1,4) ID1, count(*)cnt
from ren41
group by substr(ID,1,4);

| 4116 | 9741283 |
| 4114 | 7753844 |
+———+————-+
17 rows in set (1 min 10.29 sec)

不用substr
select r03, r05,count(*)cnt
from ren41
group by r03,r05;

| 1   | 6   |      3 |
| 1   | 8   |      1 |
| 1   | 39   |      3 |
+———+———+—————+
131 rows in set (30.23 sec)

这个还是比oracle慢

oracle
SQL> conn rk/rk
已连接。
SQL> select /*+ parallel(ren41,16)*/ substr(ID,1,4) ID, r05,count(*)cnt,r03
  2 from ren41
  3 group by substr(ID,1,4),r05,r03 ;

ID     R0     CNT R
——————————-
4105   1       27 2
4107   28       25 2
....
4106   16       6 2
4114   38       3 2
4103   28       1 2

已选择1313行。

已用时间:  00: 00: 16.10

select /*+ parallel(ren41c,16)*/ r03, r05,count(*)cnt
from ren41c
group by r03,r05;

2 40       21
1 54       4

已选择131行。

已用时间:  00: 00: 13.26

Hi !

Could you post table DDL and Execution Log ( http://www.infobright.org/wiki/Query_Execution_Log/ ) ?

Generally, expressions make queries slower. While it has been improved in the recent revision, it is still better to create a column holding precomputed substr(ID,1,4).

I can see ID is VAR/CHAR, but it contains numbers. Numerical columns work much faster - try storing ID as a number. The same about any character field containing digits.

orignal
CREATE TABLE HU41
(  H0 VARCHAR(1),
    ID VARCHAR(18),
    H02 VARCHAR(1),
    H031 VARCHAR(2),
    H032 VARCHAR(2),
    H041 VARCHAR(2),
    H042 VARCHAR(2),
    H051 VARCHAR(2),
    H052 VARCHAR(2),
    H061 VARCHAR(2),
    H062 VARCHAR(2),
    H071 VARCHAR(1),
    H072 VARCHAR(1),
    H081 VARCHAR(1),
    H082 VARCHAR(1),
    H09 VARCHAR(2),
    H10 VARCHAR(3),
    H11 VARCHAR(1),
    H12 VARCHAR(1),
    H13 VARCHAR(4),
    H14 VARCHAR(1),
    H15 VARCHAR(1),
    H16 VARCHAR(1),
    H17 VARCHAR(1),
    H18 VARCHAR(1),
    H19 VARCHAR(1),
    H20 VARCHAR(1),
    H21 VARCHAR(1),
    H22 VARCHAR(1),
    H23 VARCHAR(1),
    HA0 VARCHAR(1),
    HA1 VARCHAR(2),
    HA2 VARCHAR(2),
    HA3 VARCHAR(1),
    HA4 VARCHAR(1),
    HA5 VARCHAR(1),
    HA6 VARCHAR(1),
    HA7 VARCHAR(1),
    HA8 VARCHAR(1),
    HA9 VARCHAR(1),
    HA10 VARCHAR(3),
    HA11 VARCHAR(1),
    HA20 VARCHAR(2)
);

  CREATE TABLE REN41
  (  R0 VARCHAR(1),
      ID VARCHAR(18),
      R01 VARCHAR(2),
      R02 VARCHAR(1),
      R03 VARCHAR(1),
      R041 VARCHAR(4),
      R042 VARCHAR(2),
      R05 VARCHAR(2),
      R061 VARCHAR(1),
      R062 VARCHAR(1),
      R063 VARCHAR(2),
      R07 VARCHAR(1),
      R081 VARCHAR(1),
      R082 VARCHAR(2),
      R09 VARCHAR(1),
      R101 VARCHAR(1),
      R102 VARCHAR(2),
      R103 VARCHAR(2),
      R104 VARCHAR(2),
      R11 VARCHAR(1),
      R12 VARCHAR(1),
      R131 VARCHAR(1),
      R132 VARCHAR(2),
      R14 VARCHAR(1),
      R151 VARCHAR(1),
      R152 VARCHAR(1),
      R16 VARCHAR(1),
      R17 VARCHAR(1),
      R18 VARCHAR(1),
      R19 VARCHAR(3),
      R20 VARCHAR(3),
      R211 VARCHAR(1),
      R212 VARCHAR(3),
      R22 VARCHAR(1),
      R23 VARCHAR(1),
      R241 VARCHAR(4),
      R242 VARCHAR(2),
      R251 VARCHAR(1),
      R252 VARCHAR(1),
      R253 VARCHAR(1),
      R254 VARCHAR(1),
      R261 VARCHAR(1),
      R262 VARCHAR(2),
      R263 VARCHAR(1),
      R264 VARCHAR(2),
      R265 VARCHAR(1),
      RA0 VARCHAR(1),
      RA1 VARCHAR(3),
      RA2 VARCHAR(1),
      RA20 VARCHAR(2),
      RA21 VARCHAR(2),
      RA22 VARCHAR(2)
);

now i try
  CREATE TABLE REN41
  (  R0 tinyint,
      ID bigint,
      R01 tinyint,
      R02 tinyint,
      R03 tinyint,
      R041 smallint,
      R042 tinyint,
      R05 tinyint,
      R061 tinyint,
      R062 tinyint,
      R063 tinyint,
      R07 tinyint,
      R081 tinyint,
      R082 tinyint,
      R09 tinyint,
      R101 tinyint,
      R102 tinyint,
      R103 tinyint,
      R104 tinyint,
      R11 tinyint,
      R12 tinyint,
      R131 tinyint,
      R132 tinyint,
      R14 tinyint,
      R151 tinyint,
      R152 tinyint,
      R16 tinyint,
      R17 tinyint,
      R18 tinyint,
      R19 smallint,
      R20 smallint,
      R211 tinyint,
      R212 smallint,
      R22 tinyint,
      R23 tinyint,
      R241 smallint,
      R242 tinyint,
      R251 tinyint,
      R252 tinyint,
      R253 tinyint,
      R254 tinyint,
      R261 tinyint,
      R262 tinyint,
      R263 tinyint,
      R264 tinyint,
      R265 tinyint,
      RA0 tinyint,
      RA1 smallint,
      RA2 tinyint,
      RA20 tinyint,
      RA21 tinyint,
      RA22 tinyint
);

but i can not store only substr(id,1,4) because 4 maybe 2 6 9 etc

tinyint to varchar
load faster

but
select r03, r05,count(*)cnt
from ren41
group by r03,r05;
is not faster than before
orig:
mysql> load data infile ‘f:/soft/hu41.csv’ into table hu41 fields terminated by ‘,’ ;
Query OK, 24590997 rows affected (7 min 58.69 sec)
Records: 24590997 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select count(*) from hu41;
+—————+
| count(*) |
+—————+
| 24590997 |
+—————+
1 row in set (0.00 sec)

mysql> load data infile ‘f:/soft/ren41.csv’ into table ren41 fields terminated by ‘,’ ;
Query OK, 91236854 rows affected (26 min 38.29 sec)
Records: 91236854 Deleted: 0 Skipped: 0 Warnings: 0

文件夹 F:\Infobright\data\test\hu41.bht 175 MB (183,560,579 字节)
文件夹 F:\Infobright\data\test\ren41.bht 438 MB (460,066,448 字节)

now:

mysql> load data infile ‘f:/soft/hu41.csv’ into table hu41 fields terminated by ‘,’ ;
Query OK, 24590997 rows affected (6 min 46.38 sec)
Records: 24590997 Deleted: 0 Skipped: 0 Warnings: 0

mysql> load data infile ‘f:/soft/ren41.csv’ into table ren41 fields terminated by ‘,’ ;
Query OK, 91236854 rows affected (22 min 26.47 sec)
Records: 91236854 Deleted: 0 Skipped: 0 Warnings: 0

hu41 106 MB (111,721,454 字节)
ren41 357 MB (374,849,237 字节)

mysql> select r03, r05,count(*)cnt
  -> from ren41
  -> group by r03,r05;
+———+———+—————+
| r03 | r05 | cnt     |
+———+———+—————+
|  1 |  1 | 46468976 |
|  1 |  3 |  478488 |
...
+———+———+—————+
114 rows in set (30.26 sec)

Hi !

Can you check the size of database on the disk for IB and Oracle? I guess IB can take 10 times less disk space.
Please notice, that in your query there is no condition so the whole table has to be retrieved from disk. And for IB it means decompressing millions of rows. This is the cost of small storage space

BTW. The commercial version IEE can use parallel processing to speed up the execution of such queries.

A notice: The last query result (114 rows) does not correspond to any earlier result with VARCHAR columns..

What is RAM size of your machine and MainHeapSize?
What about query execution log?

the size is about 5% of oracle
what is the fastest way from ice to iee? can just copy the hu41.bht and ren41.bht ?

114 rows because varchar ‘01’ and ‘1’ to number 1

F:\Infobright>bin\mysqld—console
Loading configuration for Infobright instance ...
Option: AllowMySQLQueryPath, value: 0.
Option: AutoConfigure, value: 0.
Option: CacheFolder, value: f:\Infobright\cache.
Option: ControlMessages, value: 0.
Option: InternalMessages, value: 0.
Option: KNFolder, value: BH_RSI_Repository.
Option: KNLevel, value: 99.
Option: LicenseFile, value: <unknown>.
Option: LoaderMainHeapSize, value: 800.
Option: PushDown, value: 1.
Option: ServerMainHeapSize, value: 24000.
Option: UseMySQLImportExportDefaults, value: 0.
Infobright instance configuration loaded.
101215 9:56:07 [ERROR] Plugin ‘BRIGHTHOUSE’ init function returned error.
101215 9:56:07 [ERROR] Plugin ‘BRIGHTHOUSE’ registration as a STORAGE ENGINE failed.
101215 9:56:07 [ERROR] Unknown/unsupported table type: brighthouse
101215 9:56:07 [ERROR] Aborting

101215 9:56:07 [Note] bin\mysqld: Shutdown complete

I run same sql at iee,
tiny int it is faster than ice,still slower than oracle
varchar is slower than ice
select r03, r05,count(*)cnt
from ren41
group by r03,r05;

F:\IEE>sc start infobright

SERVICE_NAME: infobright
      TYPE           : 10 WIN32_OWN_PROCESS
      STATE         : 2 START_PENDING
                      (NOT_STOPPABLE, NOT_PAUSABLE, IGNORES_SHUTDOWN)
      WIN32_EXIT_CODE   : 0 (0x0)
      SERVICE_EXIT_CODE : 0 (0x0)
      CHECKPOINT       : 0x3
      WAIT_HINT       : 0x3a98
      PID           : 12896
      FLAGS         :

i create a db named tiny to save tinyint tables
then
i copy the datafiles to iee\data\test and iee\data\tiny

F:\Infobright>bin\mysql -uroot -P 5027
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-log build number (revision)=IB_3.4.2_DPN128_9255(iee_eval - commercial)

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql> create database tiny;
Query OK, 1 row affected (0.00 sec)

mysql> use tiny;
No connection. Trying to reconnect…
Connection id:  1
Current database: *** NONE ***

Database changed
mysql> show tables;
+————————+
| Tables_in_tiny |
+————————+
| hu41       |
| ren41       |
+————————+
2 rows in set (0.00 sec)

mysql> select r03, r05,count(*)cnt
  -> from ren41
  -> group by r03,r05;
+———+———+—————+
| r03 | r05 | cnt     |
+———+———+—————+
|  2 |  56 |      1 |
|  1 |  39 |      3 |
+———+———+—————+
114 rows in set (21.75 sec)

mysql> select r03, r05,count(*)cnt
  -> from test.ren41
  -> group by r03,r05;
+———+———+—————+
| r03 | r05 | cnt     |
+———+———+—————+

| 1   | 8   |      1 |
| 1   | 39   |      3 |
+———+———+—————+
131 rows in set (33.18 sec)

add where cond. slower
mysql> select r03, r05,count(*)cnt
  -> from test.ren41 where r05<‘3’
  -> group by r03,r05;
+———+———+—————+
| r03 | r05 | cnt     |
+———+———+—————+
...
+———+———+—————+
62 rows in set (37.02 sec)

and oracle slower 1 sec
SQL> select /*+ parallel(ren41c,16)*/ r03, r05,count(*)cnt
  2 from ren41c where r05<‘3’
  3 group by r03,r05;

R R0     CNT
-——————
...
1 28       6
2 14     721

已选择62行。

已用时间:  00: 00: 14.74

Hi !

Copying data files between datadirs can corrupt database. At best the performance of the copy will be worse, because no Knowledge Nodes are present in the copy. You should copy the whole data dir, or load.

As I can see from the log you cannot start IB properly. The idea was to post bh.err with query execution logs…

i do copy the whole data\test and data\tiny includes files and directories

err log is becaue i run sc start infobright first and then run mysqld, but it show the memory size, so i posted it

addition question
why iee is not much faster than ice ,how to enabel parallel query?

i did as following
1. Open the brighthouse.ini configuration file, located in your database directory

2. Set the following option:

ControlMessages = 2

3. Restart the Infobright server to execute the change

then
i got
mysql> select r03, r05,count(*)cnt
  -> from tiny.ren41 where r05<3
  -> group by r03,r05;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id:  1
Current database: tiny

+———+———+—————+
| r03 | r05 | cnt     |
+———+———+—————+
|  1 |  1 | 46468976 |
|  2 |  1 | 43624310 |
|  1 |  2 |  44437 |
|  2 |  2 |  37733 |
+———+———+—————+
4 rows in set (26.46 sec)

mysql> select r03, r05,count(*)cnt
  -> from test.ren41 where length(r05)=1 and r05<‘3’
  -> group by r03,r05;
+———+———+——-+
| r03 | r05 | cnt |
+———+———+——-+
| 2   | 1   | 729 |
| 1   | 1   | 716 |
| 1   | 2   |  2 |
| 2   | 2   |  2 |
+———+———+——-+
4 rows in set (1 min 34.13 sec)

why length() took so many time

bh.err
101216 16:06:01 [Note] f:\IEE\bin\mysqld.exe: Shutdown complete

Loading configuration for Infobright instance ...
Option: AllowMySQLQueryPath, value: 1.
Option: AutoConfigure, value: 0.
Option: CacheFolder, value: f:\IEE\cache.
Option: ControlMessages, value: 2.
Option: InternalMessages, value: 0.
Option: KNFolder, value: BH_RSI_Repository.
Option: KNLevel, value: 99.
Option: LicenseFile, value: <unknown>.
Option: LoaderMainHeapSize, value: 800.
Option: PushDown, value: 1.
Option: ServerMainHeapSize, value: 24000.
Option: UseMySQLImportExportDefaults, value: 0.
Infobright instance configuration loaded.
2010-12-16 19:51:28 [0] Brighthouse engine started, KNs: ok
101216 19:51:28 [Note] Event Scheduler: Loaded 0 events
101216 19:51:28 [Note] Infobright Enterprise Edition 3.4.2 is ready for connections.
socket: ‘’ port: 5027 build number (revision)=IB_3.4.2_DPN128_9255(iee_eval - commercial)
2010-12-16 19:52:20 [1] T:-1 = TABLE_ALIAS(T:0,“ren41”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,GROUP_BY,“r03”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:7))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,GROUP_BY,“r05”,“ALL”)
A:-3 = T:-2.ADD_COLUMN(<null>,COUNT,“cnt”,“ALL”)
VC:-2.2 = CREATE_VC(T:-2,EXPR(“3”))
F:0 = CREATE_FILTER(T:-2,VC:-2.1,<,VC:-2.2,<null>,WHERE,1)
T:-2.FILTER(F:0)
RESULT(T:-2)

2010-12-16 19:52:20 [1] Initial execution plan (non-join):
2010-12-16 19:52:20 [1] Cnd(0):  VC:1(t0a7) BET. VC:3(-inf) AND VC:4(2)    (0)
2010-12-16 19:52:20 [1] Packs/packrows after KN evaluation:
2010-12-16 19:52:20 [1] (t0) Pckrows: 1393, susp. 1393 (0 empty 0 full). Packs opened in 1 cond.: 1393
2010-12-16 19:52:24 [1] Packrows after exact evaluation (WHERE):
2010-12-16 19:52:24 [1] (t0): 1393 all packrows, 1393 to open (including 0 full)
2010-12-16 19:52:24 [1] GroupTable initialized for up to 394 positions, 8+8 bytes.
2010-12-16 19:52:24 [1] Aggregating: 90175456 tuples left, 0 gr. found so far
2010-12-16 19:52:46 [1] Aggregated (4 gr). Omitted packrows: 0 + 0 partially, out of 1393 total.
2010-12-16 19:52:46 [1] Result: 4 rows.
2010-12-16 19:52:46 [1] Total data packs actually loaded (approx.): 4179
2010-12-16 19:52:46 [1]——————————————————————————————————————
2010-12-16 19:54:06 [1] T:-1 = TABLE_ALIAS(T:0,“ren41”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,GROUP_BY,“r03”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:7))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,GROUP_BY,“r05”,“ALL”)
A:-3 = T:-2.ADD_COLUMN(<null>,COUNT,“cnt”,“ALL”)
VC:-2.2 = CREATE_VC(T:-2,EXPR(“length”))
VC:-2.3 = CREATE_VC(T:-2,EXPR(“1”))
F:0 = CREATE_FILTER(T:-2,VC:-2.2,=,VC:-2.3,<null>,WHERE,1)
VC:-2.4 = CREATE_VC(T:-2,EXPR(“3”))
F:0 = AND(F:0,VC:-2.1,<,VC:-2.4,<null>,1)
T:-2.FILTER(F:0)
RESULT(T:-2)

2010-12-16 19:54:06 [1] Initial execution plan (non-join):
2010-12-16 19:54:06 [1] Cnd(0):  VC:1(t0a7) BET. VC:5(<null>) AND VC:4(“3”) (sharp)    (17.63)
2010-12-16 19:54:06 [1] Cnd(1):  VC:2 = VC:3(1)    (99999)
2010-12-16 19:54:06 [1] Packs/packrows after KN evaluation:
2010-12-16 19:54:06 [1] (t0) Pckrows: 1393, susp. 1393 (0 empty 0 full). Packs opened in 2 cond.: 2063
2010-12-16 19:55:38 [1] Packrows after exact evaluation (WHERE):
2010-12-16 19:55:38 [1] (t0): 1393 all packrows, 603 to open (including 0 full)
2010-12-16 19:55:38 [1] GroupTable initialized for up to 2900 positions, 8+8 bytes.
2010-12-16 19:55:38 [1] Aggregating: 1449 tuples left, 0 gr. found so far
2010-12-16 19:55:40 [1] Aggregated (4 gr). Omitted packrows: 0 + 0 partially, out of 603 total.
2010-12-16 19:55:40 [1] Result: 4 rows.
2010-12-16 19:55:40 [1] Total data packs actually loaded (approx.): 3269
2010-12-16 19:55:40 [1]——————————————————————————————————————

more where condition ,slower

mysql> select r03, r05,count(*)cnt
  -> from test.ren41 where (length(r05)=1 and r05<‘3’ ) or (length(r05)=2 and r05<‘03’ )
  -> group by r03,r05;
+———+———+—————+
| r03 | r05 | cnt     |
+———+———+—————+
| 1   | 01   | 46468260 |
| 2   | 01   | 43623581 |
| 1   | 02   |  44435 |
| 2   | 02   |  37731 |
| 2   | 1   |    729 |
| 1   | 1   |    716 |
| 1   | 2   |      2 |
| 2   | 2   |      2 |
+———+———+—————+
8 rows in set (3 min 38.57 sec)

bh.err
2010-12-16 20:14:50 [1] T:-1 = TABLE_ALIAS(T:0,“ren41”)
T:-2 = TMP_TABLE(T:-1)
VC:-2.0 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:4))
A:-1 = T:-2.ADD_COLUMN(VC:-2.0,GROUP_BY,“r03”,“ALL”)
VC:-2.1 = CREATE_VC(T:-2,PHYS_COL(T:-1,A:7))
A:-2 = T:-2.ADD_COLUMN(VC:-2.1,GROUP_BY,“r05”,“ALL”)
A:-3 = T:-2.ADD_COLUMN(<null>,COUNT,“cnt”,“ALL”)
VC:-2.2 = CREATE_VC(T:-2,EXPR(“length”))
VC:-2.3 = CREATE_VC(T:-2,EXPR(“1”))
F:0 = CREATE_FILTER(T:-2,VC:-2.2,=,VC:-2.3,<null>,WHERE,1)
VC:-2.4 = CREATE_VC(T:-2,EXPR(“3”))
F:0 = AND(F:0,VC:-2.1,<,VC:-2.4,<null>,1)
VC:-2.5 = CREATE_VC(T:-2,EXPR(“2”))
F:1 = CREATE_FILTER(T:-2,VC:-2.2,=,VC:-2.5,<null>,WHERE,1)
VC:-2.6 = CREATE_VC(T:-2,EXPR(“03”))
F:1 = AND(F:1,VC:-2.1,<,VC:-2.6,<null>,1)
F:0 = OR(F:0,F:1)
T:-2.FILTER(F:0)
RESULT(T:-2)

2010-12-16 20:14:50 [1] Initial execution plan (non-join):
2010-12-16 20:14:50 [1] Cnd(0):  VC:1(t0a7) BET. VC:7(<null>) AND VC:4(“3”) (sharp)    (17.63)
2010-12-16 20:14:50 [1] Cnd(1):  VC:2 = VC:3(1)    (99999)
2010-12-16 20:14:50 [1] Packs/packrows after KN evaluation:
2010-12-16 20:14:50 [1] (t0) Pckrows: 1393, susp. 1393 (0 empty 0 full). Packs opened in 2 cond.: 2063
2010-12-16 20:16:19 [1] Packrows after exact evaluation (WHERE):
2010-12-16 20:16:19 [1] (t0): 1393 all packrows, 603 to open (including 0 full)
2010-12-16 20:16:19 [1] Initial execution plan (non-join):
2010-12-16 20:16:19 [1] Cnd(0):  VC:1(t0a7) BET. VC:8(<null>) AND VC:6(“03”) (sharp)    (17.63)
2010-12-16 20:16:19 [1] Cnd(1):  VC:2 = VC:5(2)    (99999)
2010-12-16 20:16:20 [1] Packs/packrows after KN evaluation:
2010-12-16 20:16:20 [1] (t0) Pckrows: 1393, susp. 1393 (0 empty 0 full). Packs opened in 2 cond.: 2786
2010-12-16 20:17:55 [1] Packrows after exact evaluation (WHERE):
2010-12-16 20:17:55 [1] (t0): 1393 all packrows, 1393 to open (including 0 full)
2010-12-16 20:17:56 [1] GroupTable initialized for up to 67108864 positions, 8+8 bytes.
2010-12-16 20:17:58 [1] Aggregating: 90175456 tuples left, 0 gr. found so far
2010-12-16 20:18:28 [1] Aggregated (8 gr). Omitted packrows: 0 + 0 partially, out of 1393 total.
2010-12-16 20:18:28 [1] Result: 8 rows.
2010-12-16 20:18:28 [1] Total data packs actually loaded (approx.): 7635
2010-12-16 20:18:28 [1]——————————————————————————————————————

oracle

select /*+parallel (ren41,16)*/ r03, r05,count(*)cnt
from ren41 where (length(r05)=1 and r05<‘3’ ) or (length(r05)=2 and r05<‘03’ )
group by r03,r05;

R R0     CNT
-——————
2 01   43623581
1 1       716
2 2       2
2 1       729
1 01   46468260
1 02     44435
1 2       2
2 02     37731

已选择8行。

已用时间:  00: 00: 50.63

ren41c is basic compressed

SQL> select /*+parallel (ren41c,16)*/ r03, r05,count(*)cnt
  2 from ren41c where (length(r05)=1 and r05<‘3’ ) or (length(r05)=2 and r05<‘03’ )
  3 group by r03,r05;

R R0     CNT
-——————
1 01   46468260
1 02     44435
1 2       2
1 1       716
2 02     37731
2 01   43623581
2 2       2
2 1       729

已选择8行。

已用时间:  00: 00: 16.42

Hi,

Some remarks:

- Inequalities are faster for numerical columns (as nearly everything else). If the data are numerical, there is no gain in storing them as varchar.

- All functions and expressions (except aggregations as SUM, MIN, MAX…) introduce additional performance penalty, sometimes quite big. It’s both because of additional cost of operation and because of not using KNs for expressions.

- OR causes additional slowdown and should be avoided, if only any rewrite is possible, e.g. by using IN, UNION ALL. (Exception: in release 3.4.2 it may be faster to use OR than to use IN on char/varchar values).

Please take a look at the general guidelines and tips how to leverage ICE/IEE capabitilies in our Wiki articles:
http://www.infobright.org/wiki

Regards,

Can you verify what collations are used for VARCHAR columns? Try SHOW CREATE TABLE ren41. Binary collations on 1byte encoding are much faster than national collations and UTF8 encodings.

>varchar is slower than ice
I think it is either misconfiguration, or experiment error.

Just to confirm, your computer has how many GB of RAM?

Can you try
$ ./confman.sh—show-config=yes
to see Option: brighthouse.prefetch.threads, value

| ren41 | CREATE TABLE `ren41` (
  `R0` varchar(1) COLLATE latin1_bin DEFAULT NULL,
  `ID` varchar(18) COLLATE latin1_bin DEFAULT NULL,
  `R01` varchar(2) COLLATE latin1_bin DEFAULT NULL,
....
  `RA21` varchar(2) COLLATE latin1_bin DEFAULT NULL,
  `RA22` varchar(2) COLLATE latin1_bin DEFAULT NULL
) ENGINE=BRIGHTHOUSE DEFAULT CHARSET=latin1 COLLATE=latin1_bin |

computer has 32 GB of RAM,abd oracle sga occupied 24G

how to use ./confman.sh—show-config=yes under windows 2008?