Shardingsphere: Is there any way to traverse tables in the specified order by Schema & Table Route

[Akigaze]

Question

logic table: t_msg (column: created_time (sharding key), version, business_key)
physical table: t_msg, t_msg_20_3, t_msg_20_4, t_msg_20_5 ...
The sharding key created_time is type of Timestamp (yyyy-MM-dd hh:mm:ss.SSS), and I just use year and month for sharding.

I use Oracle database and now I want to find the frist record of specific business_key:
SQL: select * from t_msg where business_key=? fetch first 1 rows only;

For this one I know the sharding-jdbc will use Schema & Table Route strategy, and the result SQL is:

select * from t_msg where business_key = ?;
select * from t_msg_20_3 where business_key = ?;
select * from t_msg_20_4 where business_key = ?;
select * from t_msg_20_5 where business_key = ?;
... ...

But now it's March so I want it to search start with t_msg_20_3. And once finding out a record, I hope it can return immediately, not need to search in t_msg anymore. If nothing can be found in t_msg_20_3, it will continue to look for t_msg.

Is there any strategy or setting of sharding-jdbc can support this requirement?

[kimmking]

select * from t_msg where business_key=? and created_time = now()

[Akigaze]

@kimmking
Thanks for you answer. I am sorry I had missed some information in my original question.
The sharding key created_time is type of Timestamp (yyyy-MM-dd hh:mm:ss.SSS), and I just use year and month for sharding.

Per you answer, if I add created_time = now() to the query criteria, I will get nothing because I don't the specific creation time of any records, I just know the month.

[kimmking]

can you show your sharding rule

[Akigaze]

Here is my sharding rule

public class CreatedTimeShardingAlgorithm implements PreciseShardingAlgorithm<Date> {

  public static final SimpleDateFormat FORMAT = new SimpleDateFormat("yy_M");

  public static final String SEPARATOR = "_";

  @Override
  public String doSharding(Collection<String> availableTables, PreciseShardingValue<Date> shardingValue) {
    Date createdTime = shardingValue.getValue();
    String suffix = FORMAT.format(createdTime);
    String expectedTable = shardingValue.getLogicTableName() + SEPARATOR + suffix;
    return availableTables.contains(expectedTable) ? expectedTable : shardingValue.getLogicTableName();
  }
}

[kimmking]

I think we should broadcast to all sharding tables and merge results.

[GolderGao]

@kimmking
I have two questions, would like to consult shardingjdbc how to deal with?

1. If I create a table every month in 2020, and each table stores the data for that month, I just want to find the table with the data, and the table without the data will not query.

2. It is now march, and I only have 3 tables to store data, namely table_1,table_2, and table_3. The other 9 tables do not store data (table_4...).Table_12, I want to query data, first from the current month, and then to the previous month query, there is no data table query

[kimmking]

@GolderGao @Akigaze

1. How to know which one with data, and which one without data? If you know the data with its month is 3, you can direct query with select * from t_msg_20_3 where ..., ShardingSphere support this sql.
2. As a middleware for generic usage&scenario, each one of 12 sharding tables is the same to ShardingSphere engine, no one is specially prior of others. If in your business semantics, they have an order to process one by one, I suggestion in native sql way. And Actually broadcasting 12 sharding tables will be parallelly query and merged, it's faster than one by one queries for several times.

In our further plan, query optimization is a big blueprint.
As your case above, I recommend optimization by rewriting union all with the same database rather than broadcast or one by one query.