20-Oracle 23 ai free Database Sharding-特性驗證

對于Oracle 23ai Sharding 新特性的驗證腳本，目標是涵蓋其核心改進和新增功能。基于 Oracle 23ai 的 Sharding 特性總結（Raft 協議、True Cache、Vector等），結合常見場景驗證。

通過SQL腳本驗證這些特性。例如：

1.基于Raft協議的分布式Sharding：需要創建分片表，并模擬跨節點的數據分布和故障切換。
2.True Cache集成：需要啟用緩存并查詢緩存命中情況。
3.自動化分片管理：需要動態添加分片并觀察數據重新分布。
4.JSON與關系模型的統一分片：創建包含JSON類型的分片表，并插入數據驗證分片策略。
5.AI Vector Search：可能需要使用向量存儲并執行相似性搜索。

也許Oracle在MAA 架構領域太過強大的緣故，導致它的分布式raft實現沒有其他分布式產品宣傳的那么多，那么強。

大多數的企業是否真得需要一個分布式數據庫，那就是另外一個話題了。

由于Oracle 23 ai free目前的版本，能測的Sharding特性不是很多，這個坑需要在OP版本發布后再填上。

1. 驗證基于 Raft 協議的分布式 Sharding?

驗證：創建分片表并模擬跨節點故障切換。

-- 1. 創建分片表（假設使用 Raft 協議）CREATE TABLE employees_shard (employee_id NUMBER PRIMARY KEY,name VARCHAR2(100),department_id NUMBER
)
PARTITION BY HASH (employee_id)
PARTITIONS 4;-- 2. 插入測試數據INSERT INTO employees_shard VALUES (1, 'Alice', 10);
INSERT INTO employees_shard VALUES (2, 'Bob', 20);
INSERT INTO employees_shard VALUES (3, 'Charlie', 30);-- 3. 查詢分片分布（需啟用 Raft 協議相關視圖），free不存在視圖
--SELECT * FROM v$sharding_nodes;
--SELECT * FROM v$sharding_partition_map WHERE table_name = 'EMPLOYEES_SHARD';-- 4. 模擬故障切換（需關閉一個分片節點）
-- 假設分片節點 1 故障，Oracle 23ai 會自動切換到其他節點
-- 驗證數據一致性SELECT * FROM employees_shard;
SYS@CDB$ROOT> SELECT * FROM employees_shard;EMPLOYEE_ID NAME          DEPARTMENT_ID
______________ __________ ________________2 Bob                      201 Alice                    103 Charlie                  30SYS@CDB$ROOT>

2. 驗證 True Cache 與 Sharding 集成

驗證：啟用 True Cache 并觀察緩存命中率，free不存在視圖

-- 1. 啟用 True Cache（需先安裝并配置多個HOST）
ALTER SYSTEM SET TRUE_CACHE_ENABLED = TRUE SCOPE=BOTH;-- 2. 創建分片表并插入數據CREATE TABLE sales_shard (sale_id NUMBER PRIMARY KEY,product_id NUMBER,amount NUMBER
)
PARTITION BY RANGE (sale_id) (PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (200)
);
Table SALES_SHARD created.
INSERT INTO sales_shard VALUES (1, 101, 1000);
INSERT INTO sales_shard VALUES (101, 102, 2000);
1 row inserted.
1 row inserted.
-- 3. 查詢數據并觸發緩存
SYS@CDB$ROOT> SELECT * FROM sales_shard WHERE sale_id = 101;SALE_ID    PRODUCT_ID    AMOUNT
__________ _____________ _________101           102      2000
-- 4. 查看 True Cache 命中率，free不存在視圖
--SELECT * FROM v$true_cache_hit_ratio;

3. 驗證自動化分片管理--整體報錯，無法驗證

驗證：動態擴縮容并觀察數據自動平衡。

-- 1. 創建分片表（假設支持自動化管理）--LIST分區報錯CREATE TABLE customer_data (customer_id NUMBER PRIMARY KEY,name VARCHAR2(100),region VARCHAR2(50)
)
PARTITION BY LIST (region) (PARTITION p_east VALUES IN ('East'),PARTITION p_west VALUES IN ('West')
);-- 2. 插入數據INSERT INTO customer_data VALUES (1, 'John', 'East');
INSERT INTO customer_data VALUES (2, 'Jane', 'West');-- 3. 動態添加分片（AI 自動平衡數據）ALTER TABLE customer_data ADD PARTITION p_north VALUES IN ('North');-- 4. 驗證數據分布（AI 自動遷移數據）SELECT * FROM dba_part_partitions WHERE table_name = 'CUSTOMER_DATA';

4. 驗證 JSON 與關系模型的統一分片

驗證：創建 JSON 分片表并查詢數據。

-- 1. 創建 JSON 分片表CREATE TABLE user_logs (user_id NUMBER PRIMARY KEY,log_data JSON
)
PARTITION BY HASH (user_id)
PARTITIONS 2;
Table USER_LOGS created.
-- 2. 插入 JSON 數據INSERT INTO user_logs VALUES (1,JSON_OBJECT('event' VALUE 'login', 'timestamp' VALUE SYSTIMESTAMP)
);
1 row inserted.
-- 3. 查詢 JSON 數據（分片內查詢）SELECT user_id, log_data FROM user_logs WHERE user_id = 1;
SYS@FREE> SELECT user_id, log_data FROM user_logs WHERE user_id = 1;USER_ID LOG_DATA
---------- --------------------------------------------------------------------------------1 {"event":"login","timestamp":"2025-06-08T19:34:47.636391+08:00"}-- 4. 驗證分片分布
SELECT * FROM dba_part_tables WHERE table_name = 'USER_LOGS';
SYS@CDB$ROOT> SELECT * FROM dba_part_tables WHERE table_name = 'USER_LOGS';OWNER    TABLE_NAME    PARTITIONING_TYPE    SUBPARTITIONING_TYPE       PARTITION_COUNT    DEF_SUBPARTITION_COUNT    PARTITIONING_KEY_COUNT    SUBPARTITIONING_KEY_COUNT STATUS    DEF_TABLESPACE_NAME       DEF_PCT_FREE    DEF_PCT_USED    DEF_INI_TRANS    DEF_MAX_TRANS DEF_INITIAL_EXTENT    DEF_NEXT_EXTENT    DEF_MIN_EXTENTS    DEF_MAX_EXTENTS    DEF_MAX_SIZE    DEF_PCT_INCREASE       DEF_FREELISTS    DEF_FREELIST_GROUPS DEF_LOGGING    DEF_COMPRESSION    DEF_COMPRESS_FOR    DEF_BUFFER_POOL    DEF_FLASH_CACHE    DEF_CELL_FLASH_CACHE    REF_PTN_CONSTRAINT_NAME    INTERVAL    AUTOLIST    INTERVAL_SUBPARTITION    AUTOLIST_SUBPARTITION    IS_NESTED    DEF_SEGMENT_CREATION    DEF_INDEXING    DEF_INMEMORY    DEF_INMEMORY_PRIORITY    DEF_INMEMORY_DISTRIBUTE    DEF_INMEMORY_COMPRESSION    DEF_INMEMORY_DUPLICATE    DEF_READ_ONLY    DEF_CELLMEMORY    DEF_INMEMORY_SERVICE    DEF_INMEMORY_SERVICE_NAME    AUTO
________ _____________ ____________________ _______________________ __________________ _________________________ _________________________ ____________________________ _________ ______________________ _______________ _______________ ________________ ________________ _____________________ __________________ __________________ __________________ _______________ ___________________ ________________ ______________________ ______________ __________________ ___________________ __________________ __________________ _______________________ __________________________ ___________ ___________ ________________________ ________________________ ____________ _______________________ _______________ _______________ ________________________ __________________________ ___________________________ _________________________ ________________ _________________ _______________________ ____________________________ _______
SYS      USER_LOGS     HASH                 NONE                                     2                         0                         1                            0 VALID     SYSTEM                              10              40                1              255 DEFAULT               DEFAULT            DEFAULT            DEFAULT            DEFAULT         DEFAULT                            0                      0 NONE           NONE                                   DEFAULT            DEFAULT            DEFAULT                                                        NO                                   NO                       NO           NONE                    ON              NONE                                                                                                                      NO                                                                                      NOSYS@CDB$ROOT>

5. 驗證 AI Vector Search 與分片集成

驗證：存儲向量數據并執行相似性搜索。

-- 1. 創建向量分片表（假設支持 AI Vector Search）CREATE TABLE image_vectors (image_id NUMBER PRIMARY KEY,features VECTOR(128) -- 假設向量維度為 128
)
PARTITION BY HASH (image_id)
PARTITIONS 3;
--Table IMAGE_VECTORS created.
-- 2. 插入向量數據（示例）--INSERT INTO image_vectors VALUES (1, VECTOR(1, 2, 3,128));
--INSERT INTO image_vectors VALUES (2, VECTOR(4, 5, 6, ..., 128));-- 3. 執行相似性搜索（跨分片）--錯誤
--SELECT * FROM image_vectors WHERE SIMILARITY(features, VECTOR(1, 2, 3, 128)) > 0.8;-- 4. 驗證分片內的向量索引
SELECT * FROM dba_part_indexes WHERE table_name = 'IMAGE_VECTORS';
SYS@CDB$ROOT> SELECT * FROM dba_part_indexes WHERE table_name = 'IMAGE_VECTORS';OWNER    INDEX_NAME                  TABLE_NAME       PARTITIONING_TYPE    SUBPARTITIONING_TYPE       PARTITION_COUNT    DEF_SUBPARTITION_COUNT    PARTITIONING_KEY_COUNT    SUBPARTITIONING_KEY_COUNT LOCALITY    ALIGNMENT       DEF_TABLESPACE_NAME       DEF_PCT_FREE    DEF_INI_TRANS    DEF_MAX_TRANS DEF_INITIAL_EXTENT    DEF_NEXT_EXTENT    DEF_MIN_EXTENTS    DEF_MAX_EXTENTS    DEF_MAX_SIZE    DEF_PCT_INCREASE       DEF_FREELISTS    DEF_FREELIST_GROUPS DEF_LOGGING    DEF_BUFFER_POOL    DEF_FLASH_CACHE    DEF_CELL_FLASH_CACHE    DEF_PARAMETERS    INTERVAL    AUTOLIST    INTERVAL_SUBPARTITION    AUTOLIST_SUBPARTITION
________ ___________________________ ________________ ____________________ _______________________ __________________ _________________________ _________________________ ____________________________ ___________ _______________ ______________________ _______________ ________________ ________________ _____________________ __________________ __________________ __________________ _______________ ___________________ ________________ ______________________ ______________ __________________ __________________ _______________________ _________________ ___________ ___________ ________________________ ________________________
SYS      SYS_IL0000076364C00002$$    IMAGE_VECTORS    HASH                 NONE                                     3                         0                         1                            0 LOCAL       NON_PREFIXED                                        10                1              255 DEFAULT               DEFAULT            DEFAULT            DEFAULT            DEFAULT         DEFAULT                            0                      0 NONE           DEFAULT            DEFAULT            DEFAULT                                               NO                                   NOSYS@CDB$ROOT>

6. 驗證 Shrink Tablespace 與分片存儲優化

驗證：收縮分片表的表空間。ORA-14809

-- 1. 創建分片表并插入數據CREATE TABLE logs_shard (log_id NUMBER PRIMARY KEY,message VARCHAR2(4000)
)
PARTITION BY RANGE (log_id) (PARTITION p1 VALUES LESS THAN (100),PARTITION p2 VALUES LESS THAN (200)
);
Table LOGS_SHARD created.
INSERT INTO logs_shard SELECT ROWNUM, 'Test log' FROM dual CONNECT BY ROWNUM <= 150;
150 rows inserted.
-- 2. 收縮表空間（回收未使用空間）ALTER TABLE logs_shard MOVE PARTITION p1 ONLINE;
--ORA-14809: schema does not support ONLINE MOVE PARTITION
ALTER TABLESPACE users COALESCE;
TABLESPACE USERS altered.
-- 3. 驗證表空間大小SELECT segment_name, bytes/1024/1024 AS size_mb FROM dba_segments WHERE tablespace_name = 'USERS';
--no rows selected

7. 驗證 Schema Annotations 與分片元數據

驗證：為分片表添加注釋并查詢。報錯

-- 1. 創建分片表CREATE TABLE products_shard (product_id NUMBER PRIMARY KEY,name VARCHAR2(100)
)
PARTITION BY HASH (product_id)
PARTITIONS 2;
Table PRODUCTS_SHARD created.
-- 2. 添加表級注釋ALTER TABLE products_shard ANNOTATIONS (ADD Title 'Product Data');
SYS@CDB$ROOT> ALTER TABLE products_shard ANNOTATIONS (ADD Title 'Product Data');Table PRODUCTS_SHARD altered.-- 3. 查詢注釋
SELECT * FROM user_annotations_usage WHERE object_type = 'TABLE' AND column_name IS NULL;
SYS@CDB$ROOT> SELECT * FROM user_annotations_usage WHERE object_type = 'TABLE' AND column_name IS NULL;OBJECT_NAME       OBJECT_TYPE    COLUMN_NAME    DOMAIN_NAME    DOMAIN_OWNER    ANNOTATION_NAME    ANNOTATION_VALUE
_________________ ______________ ______________ ______________ _______________ __________________ ___________________
PRODUCTS_SHARD    TABLE                                                        TITLE              Product Data
-- 4. 添加列級注釋，語法錯誤
ALTER TABLE products_shard ANNOTATIONS (ADD COLUMN (name) Description 'Product Name');
ORA-11548: missing or invalid annotation name 'COLUMN' in the ANNOTATIONS sequence
-- 5. 查詢列級注釋
SELECT * FROM user_annotations_usage WHERE object_type = 'TABLE' AND column_name IS NOT NULL;
SYS@CDB$ROOT> SELECT * FROM user_annotations_usage WHERE object_type = 'TABLE' AND column_name IS NOT NULL;no rows selected

?8. 驗證 IF [NOT] EXISTS 語法在 Sharding 中的兼容性

驗證：IF [NOT] EXISTS

-- 1. 創建分片表（驗證if exists忽略已存在錯誤）CREATE TABLE IF NOT EXISTS employees_shard (employee_id NUMBER PRIMARY KEY,name VARCHAR2(100)
)
PARTITION BY HASH (employee_id)
PARTITIONS 4;
Table EMPLOYEES_SHARD created.
-- 2. 重復執行上述語句（無錯誤）
SYS@CDB$ROOT> CREATE TABLE IF NOT EXISTS employees_shard (2      employee_id NUMBER PRIMARY KEY,3      name VARCHAR2(100)4  )5  PARTITION BY HASH (employee_id)6* PARTITIONS 4;Table EMPLOYEES_SHARD created.-- 3. 刪除表并驗證 IF EXISTS 子句
DROP TABLE IF EXISTS employees_shard;
SYS@CDB$ROOT> DROP TABLE IF EXISTS employees_shard;Table EMPLOYEES_SHARD dropped.

9. 向量索引加速

驗證：向量索引，分片表查詢優化報錯

-- 向量索引加速
CREATE VECTOR INDEX doc_vec_idx ON document_vectors (doc_vector)ORGANIZATION INMEMORY NEIGHBOR GRAPH;
SYS@CDB$ROOT> CREATE VECTOR INDEX doc_vec_idx ON document_vectors (doc_vector)2*   ORGANIZATION INMEMORY NEIGHBOR GRAPH;Vector INDEX created.-- 分片表查詢優化
SELECT /*+ SHARD_MIN_MAX */ * 
FROM document_vectors 
WHERE doc_id BETWEEN 100 AND 1000;
Error report -
ORA-17001: Internal error: Unknown or unimplemented accessor type: 127
https://docs.oracle.com/error-help/db/ora-17001/