db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Mamta A. Satoor (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 Mon, 01 Oct 2012 17:17:07 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3367?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Mamta A. Satoor updated DERBY-3367:
-----------------------------------

    Urgency: Normal
     Labels: derby_triage10_10  (was: )
    
> 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
>              Labels: derby_triage10_10
>         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.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira

Mime
View raw message