You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/03/26 01:31:07 UTC
[GitHub] [incubator-shardingsphere] Akigaze opened a new issue #4947: Is
there any way to traverse tables in the specified order by Schema & Table
Route
Akigaze opened a new issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947
## 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 ...
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?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] terrymanu closed issue #4947: Is there
any way to traverse tables in the specified order by Schema & Table Route
Posted by GitBox <gi...@apache.org>.
terrymanu closed issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4947: Is
there any way to traverse tables in the specified order by Schema & Table
Route
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604282336
I think we should broadcast to all sharding tables and merge results.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4947: Is
there any way to traverse tables in the specified order by Schema & Table
Route
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604178039
`select * from t_msg where business_key=? and created_time = now() `
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking edited a comment on issue
#4947: Is there any way to traverse tables in the specified order by Schema
& Table Route
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604309108
@GolderGao
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. The 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`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4947: Is
there any way to traverse tables in the specified order by Schema & Table
Route
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604309108
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. The 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 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`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking edited a comment on issue
#4947: Is there any way to traverse tables in the specified order by Schema
& Table Route
Posted by GitBox <gi...@apache.org>.
kimmking edited a comment on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604309108
@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`.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] kimmking commented on issue #4947: Is
there any way to traverse tables in the specified order by Schema & Table
Route
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604215177
can you show your sharding rule
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] Akigaze commented on issue #4947: Is
there any way to traverse tables in the specified order by Schema & Table
Route
Posted by GitBox <gi...@apache.org>.
Akigaze commented on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604226757
Here is my sharding rule
```java
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();
}
}
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] GolderGao commented on issue #4947: Is
there any way to traverse tables in the specified order by Schema & Table
Route
Posted by GitBox <gi...@apache.org>.
GolderGao commented on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604289488
@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
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services
[GitHub] [incubator-shardingsphere] Akigaze commented on issue #4947: Is
there any way to traverse tables in the specified order by Schema & Table
Route
Posted by GitBox <gi...@apache.org>.
Akigaze commented on issue #4947: Is there any way to traverse tables in the specified order by Schema & Table Route
URL: https://github.com/apache/incubator-shardingsphere/issues/4947#issuecomment-604198433
@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.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
With regards,
Apache Git Services