You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamatha K.V (JIRA)" <ji...@apache.org> on 2010/04/01 08:37:27 UTC
[jira] Created: (DERBY-4605) Derby Cost Based Optimizer to force
the use of index
Derby Cost Based Optimizer to force the use of index
----------------------------------------------------
Key: DERBY-4605
URL: https://issues.apache.org/jira/browse/DERBY-4605
Project: Derby
Issue Type: Task
Components: Demos/Scripts
Affects Versions: 10.5.3.0
Reporter: Mamatha K.V
Priority: Minor
Fix For: 10.5.3.0
Hello,
Please help on the syntax of using Cost Based Optimizer that I am using.
SELECT count (REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';
Column name- REPOSITORY_KEY
Table - TIDLRREP
Index name - TIILRREP
Also tried using - DERBY-PROPERTIES
When I through a file using java org.apache.derby.tools.ij C:\mysql.sql > C:\GLS\myoutput.txt
Derby Database Version : db-derby-10.5.3.0-bin
mysql.sql file contains
connect 'jdbc:derby:D:\test\PerformanceTest;user=admin;password=password';
MaximumDisplayWidth 9999;
CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';
Error output in myoutput.txt
ij version 10.5
ij> connect 'jdbc:derby:D:\test\PerformanceTest;user=admin;password=password';
ij> MaximumDisplayWidth 9999;
ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
0 rows inserted/updated/deleted
ij> SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';;
ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'TIILRREP WHERE REPOSITORY_KEY LIKE 'S%';' on table 'TIDLRREP'.
ij>
Also I have added below entries in my derby.properties file
derby.language.logQueryPlan=true
derby.optimizer.noTimeout=true
When I directly the query on ij ... it just hangs!!
Any help is appreciated.
Thanks
Mamatha
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (DERBY-4605) Derby Cost Based Optimizer to force the
use of index
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4605?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Knut Anders Hatlen closed DERBY-4605.
-------------------------------------
Resolution: Invalid
> Derby Cost Based Optimizer to force the use of index
> ----------------------------------------------------
>
> Key: DERBY-4605
> URL: https://issues.apache.org/jira/browse/DERBY-4605
> Project: Derby
> Issue Type: Task
> Components: Demos/Scripts
> Affects Versions: 10.5.3.0
> Reporter: Mamatha K.V
> Priority: Minor
> Fix For: 10.5.3.0
>
>
> Hello,
> Please help on the syntax of using Cost Based Optimizer that I am using.
> SELECT count (REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';
> Column name- REPOSITORY_KEY
> Table - TIDLRREP
> Index name - TIILRREP
> Also tried using - DERBY-PROPERTIES
> When I through a file using java org.apache.derby.tools.ij C:\mysql.sql > C:\GLS\myoutput.txt
> Derby Database Version : db-derby-10.5.3.0-bin
> mysql.sql file contains
> connect 'jdbc:derby:D:\test\PerformanceTest;user=admin;password=password';
> MaximumDisplayWidth 9999;
> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
> SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';
> Error output in myoutput.txt
> ij version 10.5
> ij> connect 'jdbc:derby:D:\test\PerformanceTest;user=admin;password=password';
> ij> MaximumDisplayWidth 9999;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> 0 rows inserted/updated/deleted
> ij> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
> 0 rows inserted/updated/deleted
> ij> SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';;
> ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'TIILRREP WHERE REPOSITORY_KEY LIKE 'S%';' on table 'TIDLRREP'.
> ij>
> Also I have added below entries in my derby.properties file
>
> derby.language.logQueryPlan=true
> derby.optimizer.noTimeout=true
> When I directly the query on ij ... it just hangs!!
> Any help is appreciated.
> Thanks
> Mamatha
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (DERBY-4605) Derby Cost Based Optimizer to force
the use of index
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-4605?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12852282#action_12852282 ]
Knut Anders Hatlen commented on DERBY-4605:
-------------------------------------------
Hi Mamatha,
You need a newline after index=TIILRREP, see the second paragraph and the examples on this manual page: http://db.apache.org/derby/docs/10.5/tuning/ctunoptimzoverride.html
Please direct further questions to the derby-user mailing list (http://db.apache.org/derby/derby_mail.html), as JIRA is reserved for bug reports and requests for new features.
> Derby Cost Based Optimizer to force the use of index
> ----------------------------------------------------
>
> Key: DERBY-4605
> URL: https://issues.apache.org/jira/browse/DERBY-4605
> Project: Derby
> Issue Type: Task
> Components: Demos/Scripts
> Affects Versions: 10.5.3.0
> Reporter: Mamatha K.V
> Priority: Minor
> Fix For: 10.5.3.0
>
>
> Hello,
> Please help on the syntax of using Cost Based Optimizer that I am using.
> SELECT count (REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';
> Column name- REPOSITORY_KEY
> Table - TIDLRREP
> Index name - TIILRREP
> Also tried using - DERBY-PROPERTIES
> When I through a file using java org.apache.derby.tools.ij C:\mysql.sql > C:\GLS\myoutput.txt
> Derby Database Version : db-derby-10.5.3.0-bin
> mysql.sql file contains
> connect 'jdbc:derby:D:\test\PerformanceTest;user=admin;password=password';
> MaximumDisplayWidth 9999;
> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
> SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';
> Error output in myoutput.txt
> ij version 10.5
> ij> connect 'jdbc:derby:D:\test\PerformanceTest;user=admin;password=password';
> ij> MaximumDisplayWidth 9999;
> ij> CALL SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
> 0 rows inserted/updated/deleted
> ij> CALL SYSCS_UTIL.SYSCS_SET_STATISTICS_TIMING(1);
> 0 rows inserted/updated/deleted
> ij> SELECT count(REPOSITORY_KEY) from TIDLRREP --derby-properties index = TIILRREP where REPOSITORY_KEY like 'S%';;
> ERROR 42Y46: Invalid Properties list in FROM list. There is no index 'TIILRREP WHERE REPOSITORY_KEY LIKE 'S%';' on table 'TIDLRREP'.
> ij>
> Also I have added below entries in my derby.properties file
>
> derby.language.logQueryPlan=true
> derby.optimizer.noTimeout=true
> When I directly the query on ij ... it just hangs!!
> Any help is appreciated.
> Thanks
> Mamatha
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.