Apache Phoenix Performance Result :: Thu Mar 06 18:24:33 PST 2014
HBase 0.94.14, r1543222, Hadoop 1.0.4, r1393290, 2 RS + 1 Master, 8GB per RS heap.
Queries by Category


Index

select count(*) from INDEXED_TABLE
0.55s BEFORE-PHOENIX-113-COMMIT-C3F2
0.52s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(*) from INDEXED_TABLE
0.91s BEFORE-PHOENIX-113-COMMIT-C3F2
0.8s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE ids1 ids2 ids3 ids4) */ count(*) from INDEXED_TABLE
0.3s BEFORE-PHOENIX-113-COMMIT-C3F2
0.28s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE
0.83s BEFORE-PHOENIX-113-COMMIT-C3F2
0.89s PHOENIX-SNAPSHOT

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.2s BEFORE-PHOENIX-113-COMMIT-C3F2
0.2s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
1.55s BEFORE-PHOENIX-113-COMMIT-C3F2
1.36s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core IN (1,100)
0.11s BEFORE-PHOENIX-113-COMMIT-C3F2
0.14s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TABLE where core IN (1,100)
0.04s BEFORE-PHOENIX-113-COMMIT-C3F2
0.04s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TABLE where core IN (1,100)
0.06s BEFORE-PHOENIX-113-COMMIT-C3F2
0.06s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core IN (1,100)
1.03s BEFORE-PHOENIX-113-COMMIT-C3F2
1.11s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core IN (1,25,50,75,100)
0.13s BEFORE-PHOENIX-113-COMMIT-C3F2
0.13s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TABLE where core IN (1,25,50,75,100)
0.1s BEFORE-PHOENIX-113-COMMIT-C3F2
0.1s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TABLE where core IN (1,25,50,75,100)
0.1s BEFORE-PHOENIX-113-COMMIT-C3F2
0.1s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core IN (1,25,50,75,100)
1.1s BEFORE-PHOENIX-113-COMMIT-C3F2
1.13s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core < 10 and db < 200
0.18s BEFORE-PHOENIX-113-COMMIT-C3F2
0.19s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TABLE where core < 10 and db < 200
0.66s BEFORE-PHOENIX-113-COMMIT-C3F2
0.64s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TABLE where core < 10 and db < 200
0.2s BEFORE-PHOENIX-113-COMMIT-C3F2
0.16s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core < 10 and db < 200
1.26s BEFORE-PHOENIX-113-COMMIT-C3F2
1.36s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core < 100 and db < 2000
0.5s BEFORE-PHOENIX-113-COMMIT-C3F2
0.49s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core < 100 and db < 2000
1.51s BEFORE-PHOENIX-113-COMMIT-C3F2
1.46s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core > 90
0.18s BEFORE-PHOENIX-113-COMMIT-C3F2
0.15s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core > 90
1.08s BEFORE-PHOENIX-113-COMMIT-C3F2
1.08s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core > 0
0.42s BEFORE-PHOENIX-113-COMMIT-C3F2
0.42s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core > 0
1.23s BEFORE-PHOENIX-113-COMMIT-C3F2
1.31s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE where db >5 and db <7 order by db
0.06s BEFORE-PHOENIX-113-COMMIT-C3F2
0.09s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE where db >5 and db <7 order by db
1.93s BEFORE-PHOENIX-113-COMMIT-C3F2
2.05s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE order by db limit 10
0.08s BEFORE-PHOENIX-113-COMMIT-C3F2
0.1s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE order by db limit 10
1.52s BEFORE-PHOENIX-113-COMMIT-C3F2
1.49s PHOENIX-SNAPSHOT

select count(*) from INDEXED_TABLE_MUTABLE
0.9s BEFORE-PHOENIX-113-COMMIT-C3F2
0.94s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(*) from INDEXED_TABLE_MUTABLE
1.07s BEFORE-PHOENIX-113-COMMIT-C3F2
1.11s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(*) from INDEXED_TABLE_MUTABLE
0.46s BEFORE-PHOENIX-113-COMMIT-C3F2
0.54s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE_MUTABLE
0.78s BEFORE-PHOENIX-113-COMMIT-C3F2
0.83s PHOENIX-SNAPSHOT

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.32s BEFORE-PHOENIX-113-COMMIT-C3F2
0.4s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
1.39s BEFORE-PHOENIX-113-COMMIT-C3F2
1.3s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)
0.13s BEFORE-PHOENIX-113-COMMIT-C3F2
0.15s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)
0.05s BEFORE-PHOENIX-113-COMMIT-C3F2
0.05s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)
0.13s BEFORE-PHOENIX-113-COMMIT-C3F2
0.13s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)
0.99s BEFORE-PHOENIX-113-COMMIT-C3F2
0.99s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)
0.15s BEFORE-PHOENIX-113-COMMIT-C3F2
0.16s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)
0.07s BEFORE-PHOENIX-113-COMMIT-C3F2
0.08s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)
0.14s BEFORE-PHOENIX-113-COMMIT-C3F2
0.14s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)
0.99s BEFORE-PHOENIX-113-COMMIT-C3F2
1.02s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200
0.42s BEFORE-PHOENIX-113-COMMIT-C3F2
0.46s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200
0.4s BEFORE-PHOENIX-113-COMMIT-C3F2
0.4s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200
0.42s BEFORE-PHOENIX-113-COMMIT-C3F2
0.42s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200
1.26s BEFORE-PHOENIX-113-COMMIT-C3F2
1.22s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core < 100 and db < 2000
0.94s BEFORE-PHOENIX-113-COMMIT-C3F2
1.06s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core < 100 and db < 2000
1.55s BEFORE-PHOENIX-113-COMMIT-C3F2
1.45s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core > 90
0.17s BEFORE-PHOENIX-113-COMMIT-C3F2
0.19s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core > 90
1.08s BEFORE-PHOENIX-113-COMMIT-C3F2
1.02s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core > 0
0.51s BEFORE-PHOENIX-113-COMMIT-C3F2
0.6s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core > 0
1.26s BEFORE-PHOENIX-113-COMMIT-C3F2
1.22s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE_MUTABLE where db >5 and db <7 order by db
0.17s BEFORE-PHOENIX-113-COMMIT-C3F2
0.18s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE_MUTABLE where db >5 and db <7 order by db
2.18s BEFORE-PHOENIX-113-COMMIT-C3F2
2.23s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE_MUTABLE order by db limit 10
1.19s BEFORE-PHOENIX-113-COMMIT-C3F2
1.19s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE_MUTABLE order by db limit 10
1.64s BEFORE-PHOENIX-113-COMMIT-C3F2
1.57s PHOENIX-SNAPSHOT


Index Creation After Data

CREATE INDEX idx5 ON INDEXED_TABLE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
36.65s BEFORE-PHOENIX-113-COMMIT-C3F2
38.97s PHOENIX-SNAPSHOT

CREATE INDEX midx5 ON INDEXED_TABLE_MUTABLE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
37.28s BEFORE-PHOENIX-113-COMMIT-C3F2
38.54s PHOENIX-SNAPSHOT


Load Data

LOAD DATA INDEXED_TABLE [2000000 ROWS]
447.84s BEFORE-PHOENIX-113-COMMIT-C3F2
450.64s PHOENIX-SNAPSHOT

LOAD DATA INDEXED_TABLE_MUTABLE [2000000 ROWS]
653.23s BEFORE-PHOENIX-113-COMMIT-C3F2
657.57s PHOENIX-SNAPSHOT




Queries by Table



INDEXED_TABLE


Indexing test. Phoenix 2.0+ feature. See explain plan by hovering over version label to see which index table is used.

ROWS 2000000 [2M]

DDL CREATE TABLE $TABLE (HOST CHAR(2) NOT NULL,DOMAIN VARCHAR NOT NULL,FEATURE VARCHAR NOT NULL,DATE DATE NOT NULL,USAGE.CORE BIGINT,USAGE.DB BIGINT,STATS.ACTIVE_VISITOR INTEGER CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE)) IMMUTABLE_ROWS=true,MAX_FILESIZE=30485760;CREATE INDEX idx1 ON $TABLE (CORE);CREATE INDEX idx2 ON $TABLE (DB);CREATE INDEX idx3 ON $TABLE (DB,ACTIVE_VISITOR);CREATE INDEX idx4 ON $TABLE (CORE,DB,ACTIVE_VISITOR);CREATE INDEX ids1 ON $TABLE (CORE) SALT_BUCKETS=16;CREATE INDEX ids2 ON $TABLE (DB) SALT_BUCKETS=16;CREATE INDEX ids3 ON $TABLE (DB,ACTIVE_VISITOR) SALT_BUCKETS=16;CREATE INDEX ids4 ON $TABLE (CORE,DB,ACTIVE_VISITOR) SALT_BUCKETS=16;

DATA GENERATOR
STRING :: Values: NA|CS|EU
STRING :: Values: Salesforce.com|Apple.com|Google.com|Yelp.com
STRING :: Values: Login|Report|Dashboard|Sales|UI
DATE :: Values: SEQUENTIAL
INTEGER :: Max. Value: 100 Values: RANDOM
INTEGER :: Max. Value: 2000 Values: RANDOM
INTEGER :: Max. Value: 10000 Values: RANDOM

select count(*) from INDEXED_TABLE
0.55s BEFORE-PHOENIX-113-COMMIT-C3F2
0.52s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(*) from INDEXED_TABLE
0.91s BEFORE-PHOENIX-113-COMMIT-C3F2
0.8s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE ids1 ids2 ids3 ids4) */ count(*) from INDEXED_TABLE
0.3s BEFORE-PHOENIX-113-COMMIT-C3F2
0.28s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE
0.83s BEFORE-PHOENIX-113-COMMIT-C3F2
0.89s PHOENIX-SNAPSHOT

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.2s BEFORE-PHOENIX-113-COMMIT-C3F2
0.2s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
1.55s BEFORE-PHOENIX-113-COMMIT-C3F2
1.36s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core IN (1,100)
0.11s BEFORE-PHOENIX-113-COMMIT-C3F2
0.14s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TABLE where core IN (1,100)
0.04s BEFORE-PHOENIX-113-COMMIT-C3F2
0.04s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TABLE where core IN (1,100)
0.06s BEFORE-PHOENIX-113-COMMIT-C3F2
0.06s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core IN (1,100)
1.03s BEFORE-PHOENIX-113-COMMIT-C3F2
1.11s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core IN (1,25,50,75,100)
0.13s BEFORE-PHOENIX-113-COMMIT-C3F2
0.13s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TABLE where core IN (1,25,50,75,100)
0.1s BEFORE-PHOENIX-113-COMMIT-C3F2
0.1s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TABLE where core IN (1,25,50,75,100)
0.1s BEFORE-PHOENIX-113-COMMIT-C3F2
0.1s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core IN (1,25,50,75,100)
1.1s BEFORE-PHOENIX-113-COMMIT-C3F2
1.13s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core < 10 and db < 200
0.18s BEFORE-PHOENIX-113-COMMIT-C3F2
0.19s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE idx1 idx2 idx3 idx4) */ count(core) from INDEXED_TABLE where core < 10 and db < 200
0.66s BEFORE-PHOENIX-113-COMMIT-C3F2
0.64s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE ids1 ids2 ids3 ids4) */ count(core) from INDEXED_TABLE where core < 10 and db < 200
0.2s BEFORE-PHOENIX-113-COMMIT-C3F2
0.16s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core < 10 and db < 200
1.26s BEFORE-PHOENIX-113-COMMIT-C3F2
1.36s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core < 100 and db < 2000
0.5s BEFORE-PHOENIX-113-COMMIT-C3F2
0.49s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core < 100 and db < 2000
1.51s BEFORE-PHOENIX-113-COMMIT-C3F2
1.46s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core > 90
0.18s BEFORE-PHOENIX-113-COMMIT-C3F2
0.15s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core > 90
1.08s BEFORE-PHOENIX-113-COMMIT-C3F2
1.08s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE where core > 0
0.42s BEFORE-PHOENIX-113-COMMIT-C3F2
0.42s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE where core > 0
1.23s BEFORE-PHOENIX-113-COMMIT-C3F2
1.31s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE where db >5 and db <7 order by db
0.06s BEFORE-PHOENIX-113-COMMIT-C3F2
0.09s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE where db >5 and db <7 order by db
1.93s BEFORE-PHOENIX-113-COMMIT-C3F2
2.05s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE order by db limit 10
0.08s BEFORE-PHOENIX-113-COMMIT-C3F2
0.1s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE order by db limit 10
1.52s BEFORE-PHOENIX-113-COMMIT-C3F2
1.49s PHOENIX-SNAPSHOT

CREATE INDEX idx5 ON INDEXED_TABLE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
36.65s BEFORE-PHOENIX-113-COMMIT-C3F2
38.97s PHOENIX-SNAPSHOT

LOAD DATA INDEXED_TABLE [2000000 ROWS]
447.84s BEFORE-PHOENIX-113-COMMIT-C3F2
450.64s PHOENIX-SNAPSHOT


INDEXED_TABLE_MUTABLE


Indexing test. Phoenix 2.0+ feature. See explain plan by hovering over version label to see which index table is used.

ROWS 2000000 [2M]

DDL CREATE TABLE $TABLE (HOST CHAR(2) NOT NULL,DOMAIN VARCHAR NOT NULL,FEATURE VARCHAR NOT NULL,DATE DATE NOT NULL,USAGE.CORE BIGINT,USAGE.DB BIGINT,STATS.ACTIVE_VISITOR INTEGER CONSTRAINT PK PRIMARY KEY (HOST, DOMAIN, FEATURE, DATE)) MAX_FILESIZE=30485760;CREATE INDEX midx1 ON $TABLE (CORE);CREATE INDEX midx2 ON $TABLE (DB);CREATE INDEX midx3 ON $TABLE (DB,ACTIVE_VISITOR);CREATE INDEX midx4 ON $TABLE (CORE,DB,ACTIVE_VISITOR);CREATE INDEX mids1 ON $TABLE (CORE) SALT_BUCKETS=16;CREATE INDEX mids2 ON $TABLE (DB) SALT_BUCKETS=16;CREATE INDEX mids3 ON $TABLE (DB,ACTIVE_VISITOR) SALT_BUCKETS=16;CREATE INDEX mids4 ON $TABLE (CORE,DB,ACTIVE_VISITOR) SALT_BUCKETS=16;

DATA GENERATOR
STRING :: Values: NA|CS|EU
STRING :: Values: Salesforce.com|Apple.com|Google.com|Yelp.com
STRING :: Values: Login|Report|Dashboard|Sales|UI
DATE :: Values: SEQUENTIAL
INTEGER :: Max. Value: 100 Values: RANDOM
INTEGER :: Max. Value: 2000 Values: RANDOM
INTEGER :: Max. Value: 10000 Values: RANDOM

select count(*) from INDEXED_TABLE_MUTABLE
0.9s BEFORE-PHOENIX-113-COMMIT-C3F2
0.94s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(*) from INDEXED_TABLE_MUTABLE
1.07s BEFORE-PHOENIX-113-COMMIT-C3F2
1.11s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(*) from INDEXED_TABLE_MUTABLE
0.46s BEFORE-PHOENIX-113-COMMIT-C3F2
0.54s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(*) from INDEXED_TABLE_MUTABLE
0.78s BEFORE-PHOENIX-113-COMMIT-C3F2
0.83s PHOENIX-SNAPSHOT

select round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
0.32s BEFORE-PHOENIX-113-COMMIT-C3F2
0.4s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ round(date, 'day',50), sum(core)+2 from INDEXED_TABLE_MUTABLE where (CORE<10 or CORE>90) group by round(date, 'day',50), host
1.39s BEFORE-PHOENIX-113-COMMIT-C3F2
1.3s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)
0.13s BEFORE-PHOENIX-113-COMMIT-C3F2
0.15s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)
0.05s BEFORE-PHOENIX-113-COMMIT-C3F2
0.05s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)
0.13s BEFORE-PHOENIX-113-COMMIT-C3F2
0.13s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,100)
0.99s BEFORE-PHOENIX-113-COMMIT-C3F2
0.99s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)
0.15s BEFORE-PHOENIX-113-COMMIT-C3F2
0.16s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)
0.07s BEFORE-PHOENIX-113-COMMIT-C3F2
0.08s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)
0.14s BEFORE-PHOENIX-113-COMMIT-C3F2
0.14s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core IN (1,25,50,75,100)
0.99s BEFORE-PHOENIX-113-COMMIT-C3F2
1.02s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200
0.42s BEFORE-PHOENIX-113-COMMIT-C3F2
0.46s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE midx1 midx2 midx3 midx4) */ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200
0.4s BEFORE-PHOENIX-113-COMMIT-C3F2
0.4s PHOENIX-SNAPSHOT

select /*+ INDEX(INDEXED_TABLE_MUTABLE mids1 mids2 mids3 mids4) */ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200
0.42s BEFORE-PHOENIX-113-COMMIT-C3F2
0.42s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core < 10 and db < 200
1.26s BEFORE-PHOENIX-113-COMMIT-C3F2
1.22s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core < 100 and db < 2000
0.94s BEFORE-PHOENIX-113-COMMIT-C3F2
1.06s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core < 100 and db < 2000
1.55s BEFORE-PHOENIX-113-COMMIT-C3F2
1.45s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core > 90
0.17s BEFORE-PHOENIX-113-COMMIT-C3F2
0.19s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core > 90
1.08s BEFORE-PHOENIX-113-COMMIT-C3F2
1.02s PHOENIX-SNAPSHOT

select count(core) from INDEXED_TABLE_MUTABLE where core > 0
0.51s BEFORE-PHOENIX-113-COMMIT-C3F2
0.6s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ count(core) from INDEXED_TABLE_MUTABLE where core > 0
1.26s BEFORE-PHOENIX-113-COMMIT-C3F2
1.22s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE_MUTABLE where db >5 and db <7 order by db
0.17s BEFORE-PHOENIX-113-COMMIT-C3F2
0.18s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE_MUTABLE where db >5 and db <7 order by db
2.18s BEFORE-PHOENIX-113-COMMIT-C3F2
2.23s PHOENIX-SNAPSHOT

select db from INDEXED_TABLE_MUTABLE order by db limit 10
1.19s BEFORE-PHOENIX-113-COMMIT-C3F2
1.19s PHOENIX-SNAPSHOT

select /*+NO_INDEX*/ db from INDEXED_TABLE_MUTABLE order by db limit 10
1.64s BEFORE-PHOENIX-113-COMMIT-C3F2
1.57s PHOENIX-SNAPSHOT

CREATE INDEX midx5 ON INDEXED_TABLE_MUTABLE (CORE) INCLUDE (DB,ACTIVE_VISITOR)
37.28s BEFORE-PHOENIX-113-COMMIT-C3F2
38.54s PHOENIX-SNAPSHOT

LOAD DATA INDEXED_TABLE_MUTABLE [2000000 ROWS]
653.23s BEFORE-PHOENIX-113-COMMIT-C3F2
657.57s PHOENIX-SNAPSHOT



Note: Hover over version label to see Phoenix Query Explain Plan.