20-Oracle 23 ai free Database Sharding-特性验证

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                      20
             1 Alice                    10
             3 Charlie                  30

SYS@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                                                                                      NO

SYS@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                                   NO

SYS@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/

TIPS：