点击上方“IT那活儿”公众号--专注于企业全栈运维技术分享，不管IT什么活儿，干就完了！！！
Oracle租户下，timestamp类型的字段可用于存储时间戳数据，当表上使用timestamp字段作为分区键时，检索语句存在where条件使用分区键，格式如timestamp '2025-08-12 22:28:00' ，这种使用方法将导致obproxy无法解析分区字段的值，从而路由出现错误，需要使用to_timestamp函数替代，示例如下：
使用timestamp转换：
explain route select * from oratable1 where create_time=timestamp'2025-08-05 11:11:11.123'
Route Prompts
-----------------
> ROUTE_INFO
[INFO] Will dotablepartition location lookup to decide which OBServer to route to
> PARTITION_ID_CALC_DONE
[WARN] Fail tousepartitionkeyvalueto calculate first part idx
Route Plan
-----------------
> SQL_PARSE:{cmd:"COM_QUERY", table:"ORATABLE1"}
> ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
> LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
> TABLE_ENTRY_LOOKUP_DONE:{table:"ORATABLE1", table_id:"500070", table_type:"USER TABLE", partition_num:17, entry_from_remote:false}
> PARTITION_ID_CALC_START:{}
> EXPR_PARSE:{col_val:"create_time="}
> RESOLVE_EXPR:{error:-4002, part_range:"(MIN ; MAX)always true"}
> RESOLVE_TOKEN:{error:-4002, token_type:"TOKEN_COLUMN", token:"timestamp"}
> PARTITION_ID_CALC_DONE:{error:-4002, partition_id:-1, level:1}
> ROUTE_INFO:{route_info_type:"USE_CACHED_SESSION", svr_addr:"11.11.11.11:2881"}
> ROUTE_POLICY:{route_policy:"", chosen_server:"Invalid"}
> CONGESTION_CONTROL:{svr_addr:"11.11.11.11:2881"}
修改to_timestamp函数后：
explain route select * from oratable1 where create_time=to_timestamp('2025-08-05 11:11:11.123','YYYY-MM-DD HH24:MI:SS.FF')
Route Prompts
-----------------
> ROUTE_INFO
[INFO] Will do table partition location lookup to decide which OBServer to route to
> ROUTE_POLICY
[INFO] Will route to table's partition leader replica(11.11.11.12:2881) using route policy PRIMARY_ZONE_FIRST because query for STRONG read
Route Plan
-----------------
> SQL_PARSE:{cmd:"COM_QUERY", table:"ORATABLE1"}
> ROUTE_INFO:{route_info_type:"USE_PARTITION_LOCATION_LOOKUP"}
> LOCATION_CACHE_LOOKUP:{mode:"oceanbase"}
> TABLE_ENTRY_LOOKUP_DONE:{table:"ORATABLE1", table_id:"500070", table_type:"USER TABLE", partition_num:17, entry_from_remote:false}
> PARTITION_ID_CALC_START:{}
> EXPR_PARSE:{col_val:"create_time=to_timestamp"}
> RESOLVE_EXPR:{part_range:"[2025-08-05 11:11:11.123000000 ; 2025-08-05 11:11:11.123000000]"}
> RESOLVE_TOKEN:{token_type:"TOKEN_FUNC", resolve:"TIMESTAMP NANO:2025-08-05 11:11:11.123000000", token:"to_timestamp", expr_type:"OB_PROXY_EXPR_TYPE_FUNC_TO_TIMESTAMP"}
> CALC_PARTITION_ID:{part_description:"partition by range (P500072[TIMESTAMP NANO:2025-08-04 00:00:00.000000000], P500073[TIMESTAMP NANO:2025-08-05 00:00:00.000000000], P500074[TIMESTAMP NANO:2025-08-06 00:00:00.000000000], P500075[TIMESTAMP NANO:2025-08-07 00:00:00.000000000], P500076[TIMESTAMP NANO:2025-08-08 00:00:00.000000000], P500077[TIMESTAMP NANO:2025-08-09 00:00:00.000000000], P500078[TIMESTAMP NANO:2025-08-10 00:00:00.000000000], P500079[TIMESTAMP NANO:2025-08-11 00:00:00.000000000], P500080[TIMESTAMP NANO:2025-08-12 00:00:00.000000000], P500081[TIMESTAMP NANO:2025-08-13 00:00:00.000000000], P500082[TIMESTAMP NANO:2025-08-14 00:00:00.000000000], P500083[TIMESTAMP NANO:2025-08-15 00:00:00.000000000], P500084[TIMESTAMP NANO:2025-08-16 00:00:00.000000000], P500085[TIMESTAMP NANO:2025-08-17 00:00:00.000000000], P500086[TIMESTAMP NANO:2025-08-18 00:00:00.000000000], P500087[TIMESTAMP NANO:2025-08-19 00:00:00.000000000], P500088[TIMESTAMP NANO:2025-08-20 00:00:00.000000000])"}
> PARTITION_ID_CALC_DONE:{partition_id:200060, level:1, partitions:"(p2)"}
> PARTITION_ENTRY_LOOKUP_DONE:{leader:"11.11.11.12:2881", entry_from_remote:false}
> ROUTE_POLICY:{route_policy:"", chosen_route_type:"ROUTE_TYPE_LEADER"}
> CONGESTION_CONTROL:{svr_addr:"11.11.11.12:2881"}
本文作者：孙其成(上海新炬中北团队）
本文来源：“IT那活儿”公众号
文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
