Hi,
I am new H2 user, and am facing issue with a query which is taking a
long time. The table has 13600 records.
The same query is taking 8000ms in H2 whereas in Oracle it takes 6ms.
Analyzing the query further I noticed that the query is not using the
index (the explain plan shows that it uses tablescan).
Is there some setting that needs to be done so that the query will
start making use of indexes?
I have a lot of queries like these in my application, so currently
using H2 is taking more time than using Oracle.
Following is the query:
---------------------------------------------------------------------------
SELECT DISTINCT col_3
FROM VW1_DUMMY_XX outer
WHERE ( outer.fx_col_1 = '11' OR outer.fx_col_1 = '@@' )
AND ( outer.fx_col_2 = 'X6' OR outer.fx_col_2 = '@@' )
AND outer.rowid = (
SELECT rowid FROM VW2_DUMMY_XX
WHERE ( fx_col_1 = '11' OR fx_col_1 = '@@' )
AND ( fx_col_2 = 'X6' OR fx_col_2 = '@@' )
AND outer.fx_col_3 = fx_col_3
AND outer.fx_col_4 = fx_col_4
AND outer.fx_col_5 = fx_col_5
AND outer.fx_col_6 = fx_col_6
AND rownum < 2
);
---------------------------------------------------------------------------
The setup is as following:
---------------------------------------------------------------------------
CREATE TABLE TBL_DUMMY_XX (
col_status VARCHAR(20),
col_1 VARCHAR(8),
col_2 VARCHAR(8),
col_3 VARCHAR(256),
col_4 VARCHAR(256),
col_5 VARCHAR(256),
col_6 VARCHAR(256),
fx_col_1 VARCHAR AS NVL(col_1,'@@'),
fx_col_2 VARCHAR AS NVL(col_2,'@@'),
fx_col_3 VARCHAR AS NVL(col_3,'@@'),
fx_col_4 VARCHAR AS NVL(col_4,'@@'),
fx_col_5 VARCHAR AS NVL(col_5,'@@'),
fx_col_6 VARCHAR AS NVL(col_6,'@@'),
fx_status VARCHAR AS LOWER(col_status)
);
CREATE OR REPLACE FORCE VIEW VW1_DUMMY_XX
("ROWID", "COL_STATUS", "COL_1", "COL_2", "COL_3", "COL_4", "COL_5",
"COL_6"
, "FX_COL_2", "FX_COL_1", "FX_COL_3", "FX_COL_4", "FX_COL_5",
"FX_COL_6", "FX_STATUS")
AS (
SELECT _ROWID_, COL_STATUS, COL_1, COL_2, COL_3, COL_4, COL_5, COL_6
, FX_COL_2, FX_COL_1, FX_COL_3, FX_COL_4, FX_COL_5, FX_COL_6,
FX_STATUS
FROM TBL_DUMMY_XX
WHERE FX_STATUS = 'active'
);
CREATE OR REPLACE FORCE VIEW VW2_DUMMY_XX
("ROWID", "COL_STATUS", "COL_1", "COL_2", "COL_3", "COL_4", "COL_5",
"COL_6"
, "FX_COL_2", "FX_COL_1", "FX_COL_3", "FX_COL_4", "FX_COL_5",
"FX_COL_6", "FX_STATUS")
AS (
SELECT _ROWID_, COL_STATUS, COL_1, COL_2, COL_3, COL_4, COL_5, COL_6
, FX_COL_2, FX_COL_1, FX_COL_3, FX_COL_4, FX_COL_5, FX_COL_6,
FX_STATUS
FROM TBL_DUMMY_XX
WHERE FX_STATUS = 'active'
) ORDER BY col_1, col_2, col_3 DESC
;
CREATE INDEX IDX_DUMMY_XX_1 ON TBL_DUMMY_XX(fx_col_1, fx_col_2,
fx_col_3, fx_col_4, fx_col_5, fx_col_6);
The data can be loaded into the table using the insert scripts in file
SampleData.zip (I have shared it on Google Docs). Let me know if there
is a better way to attach a file.
---------------------------------------------------------------------------
Thanks,
Vinod
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
