[
https://issues.apache.org/jira/browse/DERBY-3367?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Suresh Thalamati updated DERBY-3367:
------------------------------------
Attachment: derby.log
Derby log with the query plans.
> Sort is not avoided even when the has an index on a the column being ordered,
> for a query with id != -1 predicate.
> -------------------------------------------------------------------------------------------------------------------
>
> Key: DERBY-3367
> URL: https://issues.apache.org/jira/browse/DERBY-3367
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Affects Versions: 10.3.2.1
> Reporter: Suresh Thalamati
> Attachments: derby.log
>
>
> Sort is not avoided even when the has an index on a the column being ordered,
> Repro:
> go.ddl:
> -------
> connect 'jdbc:derby:testdb;create=true';
> create table t1 (i int, j int, vc varchar(30));
> insert into t1 values (1, -1, 'minus one');
> insert into t1 values (2, 2, 'two'), (3, 3, 'trois'), (3, -3, 'minus three'),
> (4, 4, 'four');
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> insert into t1 select * from t1 where j > 0;
> create index ix on t1 (j);
> disconnect all;
> exit;
> go.sql:
> -------
> connect 'jdbc:derby:testdb';
> get cursor c1 as 'select j, vc from t1 order by j asc';
> next c1;
> close c1;
> get cursor c1 as 'select j, vc from t1 where j != -1 order by j asc';
> next c1;
> close c1;
> --
> After running "go.sql", if you look at the derby.log file you'll see that the
> query with no predicate does an index scan and only has to read 1 row from
> disk
> before the cursor is closed. But the query _with_ a predicate does a table
> scan an has to read 3074 rows from disk, and sort them, just to return
> the first one in the result set.
> In the repro, it looks fast. But If the data is large,
> which was the case in my application.
> The table was:
> create table t2 (i int, j int, vc varchar(15000));
> and loaded with 13000 rows. It takes almost minute to get the first row ,
> for the query "select j, vc from t1 where j != -1 order by j asc'"
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
