how to use Query hint

[ddaeyongee]

hi. I'm Taeyong.
I have one question. I would like to add a query hint to improve performance when processing large volumes.

path :
eventuate-local-java-cdc-connector-polling/src/main/java/io/eventuate/local/polling/PollingDao.java
existing query
String markEventsAsReadQuery = String.format("UPDATE %s SET %s = 1 WHERE %s in (:ids)",
handler.getQualifiedTable(), PUBLISHED_FIELD, pk);

Query hint to add :
String markEventsAsReadQuery = String.format("UPDATE ### /*+ index_rs(handler.getQualifiedTable()) */ %s SET %s = 1 WHERE %s in (:ids)",
handler.getQualifiedTable(), PUBLISHED_FIELD, pk);

Or is there another way without directly modifying the query in 'String.format'

thank you !

[cer]

These SQL statements are hardwired into PollingDao:

eventuate-cdc/eventuate-local-java-cdc-connector-polling/src/main/java/io/eventuate/local/polling/PollingDao.java

Lines 119 to 120 in eba1d54

	String findEventsQuery = eventuateSqlDialect.addLimitToSql(String.format("SELECT * FROM %s WHERE %s = 0 ORDER BY %s ASC",
	handler.getQualifiedTable(), PUBLISHED_FIELD, pk), ":limit");

and

eventuate-cdc/eventuate-local-java-cdc-connector-polling/src/main/java/io/eventuate/local/polling/PollingDao.java

Lines 150 to 151 in eba1d54

	String markEventsAsReadQuery = String.format("UPDATE %s SET %s = 1 WHERE %s in (:ids)",
	handler.getQualifiedTable(), PUBLISHED_FIELD, pk);

One simple solution would be to make these SQL statements configurable via properties:

• PollingPipelineReaderProperties
• CdcPipelineReaderProperties

@ddaeyongee Thoughts?

[ddaeyongee]

@cer
thanks your comment. I have accepted your comment. And I changed the oracle query hint to a String string as shown below and reflected it to the CDC server through gradle service.build .

[file path]
eventuate-local-java-cdc-connector-polling/src/main/java/io/eventuate/local/polling/PollingDao.java

[as-is]
150line
...
String markEventsAsReadQuery = String.format("UPDATE %s SET %s = 1 WHERE %s in (:ids)",
handler.getQualifiedTable(), PUBLISHED_FIELD, pk);
...

[fixed]
...
String markEventsAsReadQuery = String.format("UPDATE %s /*+ index_rs(%s) */ SET %s = 1 WHERE %s in (:ids)",
handler.getQualifiedTable(), PUBLISHED_FIELD, pk);
...

and One more question, please check if it is possible to add OracleDialect to "package io.eventuate.sql.dialect". For microservices currently using oracle RDBMS, I create and use it myself.

I always appreciate your active support.