db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Suresh Thalamati (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3367) Sort is not avoided even when the has an index on a the column being ordered, for a query with id != -1 predicate.
Date Wed, 30 Jan 2008 20:27:34 GMT

     [ 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.


Mime
View raw message