• Post Reply Bookmark Topic Watch Topic
  • New Topic

Derby Database -Cost Based Optimizer to force the use of index  RSS feed

 
Mamatha Kv
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:\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
ij> 'jdbc:derby:\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
ij> = 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


 
Devaka Cooray
Marshal
Posts: 5568
713
Chrome Eclipse IDE Google App Engine IntelliJ IDE jQuery Postgres Database Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Mamatha Kv", please check your private messages regarding an important administrative matter from JavaRanch.
 
Consider Paul's rocket mass heater.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!