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] Commented: (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 22:53:34 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3367?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12564205#action_12564205
] 

Suresh Thalamati commented on DERBY-3367:
-----------------------------------------

Thanks for your comments, Mike. I am not looking for Derby to optimize time to  return the
first row, The application needs all the rows ,  returned by the query.   But it process the
rows as it 
gets and  shows results iteratively to the user.  Because of sorting, it takes time to get
the 
first row also ,  which makes it look as of the application is hung.

My observation was, even in IJ ,   selecting all the rows  without the  != -1  qualifier was

faster than  with the  qualifier.

I was also surprised it used  index  without the qualifier., but not with the qualifier. 

It turns out to be  good decision  by the optimizer , if the sorting is external  
and spilling to  disk, when  data size is large. 





> 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