sql: Indexing & Performance using Oracle21c

 

-- ============================================================
-- Oracle 21c 珠宝行业「索引与性能优化」
-- 完整版（合并所有模块，一次性执行）
-- ============================================================
-- 执行方式：
--   sqlplus jewelry_user/jewelry_pwd@localhost:1521/orcl @oracle21c_indexing_full.sql
-- 或分步执行各模块文件便于调试
-- ============================================================
-- geovindu,Geovin Du
-- ============================================================
-- Oracle 21c 珠宝行业「索引与性能优化」
-- 模块1：建库建表（Schema）
-- 创建数据库对象：表/序列/索引/视图/存储过程/分区函数
-- ============================================================

-- 1. 创建表空间与用户
CREATE TABLESPACE jewelry_ts
  DATAFILE '/oradata/jewelry_ts.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 10G
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER jewelry_user IDENTIFIED BY jewelry_pwd
  DEFAULT TABLESPACE jewelry_ts
  QUOTA UNLIMITED ON jewelry_ts;

GRANT CONNECT, RESOURCE, DBA TO jewelry_user;
GRANT EXECUTE ON DBMS_STATS TO jewelry_user;
GRANT EXECUTE ON DBMS_XPLAN TO jewelry_user;
GRANT CREATE JOB TO jewelry_user;

-- 切换用户
CONNECT jewelry_user/jewelry_pwd;

-- 2. 创建分类表
CREATE TABLE category (
    category_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    category_name VARCHAR2(100) NOT NULL,
    parent_id     NUMBER,
    description   VARCHAR2(500),
    sort_order    NUMBER DEFAULT 0,
    created_at    TIMESTAMP DEFAULT SYSTIMESTAMP,
    updated_at    TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 3. 创建珠宝产品表
CREATE TABLE product (
    product_id      NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_name    VARCHAR2(200) NOT NULL,
    category_id     NUMBER REFERENCES category(category_id),
    material        VARCHAR2(50),
    gemstone_type   VARCHAR2(100),
    gemstone_carat  NUMBER(8,2),
    weight_grams    NUMBER(10,2),
    color_code      VARCHAR2(10),
    clarity_grade   VARCHAR2(20),
    cut_grade       VARCHAR2(20),
    price_cny       NUMBER(12,2) NOT NULL,
    stock_quantity  NUMBER DEFAULT 0,
    is_active       VARCHAR2(1) DEFAULT 'Y' CHECK (is_active IN ('Y','N')),
    created_at      TIMESTAMP DEFAULT SYSTIMESTAMP,
    updated_at      TIMESTAMP DEFAULT SYSTIMESTAMP,
    search_vector   VARCHAR2(4000)
);

-- 4. 创建客户表
CREATE TABLE customer (
    customer_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_name  VARCHAR2(100) NOT NULL,
    phone          VARCHAR2(20),
    email          VARCHAR2(150),
    member_level   VARCHAR2(20) DEFAULT '普通' CHECK (member_level IN ('普通','银卡','金卡','钻石')),
    total_spent    NUMBER(12,2) DEFAULT 0,
    city           VARCHAR2(50),
    latitude       NUMBER(9,6),
    longitude      NUMBER(9,6),
    created_at     TIMESTAMP DEFAULT SYSTIMESTAMP,
    last_login     TIMESTAMP
);

-- 5. 创建订单表
CREATE TABLE orders (
    order_id        NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id     NUMBER REFERENCES customer(customer_id),
    order_date      TIMESTAMP DEFAULT SYSTIMESTAMP,
    status          VARCHAR2(20) DEFAULT '待处理' CHECK (status IN ('待处理','已确认','已发货','已完成','已取消','退款中')),
    total_amount    NUMBER(12,2),
    shipping_addr   VARCHAR2(500),
    payment_method  VARCHAR2(30),
    store_id        NUMBER,
    created_at      TIMESTAMP DEFAULT SYSTIMESTAMP,
    updated_at      TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 6. 创建订单明细表
CREATE TABLE order_item (
    item_id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id        NUMBER REFERENCES orders(order_id) ON DELETE CASCADE,
    product_id      NUMBER REFERENCES product(product_id),
    quantity        NUMBER DEFAULT 1,
    unit_price      NUMBER(12,2) NOT NULL,
    subtotal        NUMBER(12,2) GENERATED ALWAYS AS (quantity * unit_price) VIRTUAL,
    created_at      TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 7. 创建库存表
CREATE TABLE inventory (
    inventory_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id      NUMBER REFERENCES product(product_id),
    warehouse_id    NUMBER,
    warehouse_name  VARCHAR2(100),
    quantity        NUMBER DEFAULT 0,
    reorder_level   NUMBER DEFAULT 10,
    location_code   VARCHAR2(50),
    last_updated    TIMESTAMP DEFAULT SYSTIMESTAMP,
    version         NUMBER DEFAULT 1
);

-- 8. 创建评价表
CREATE TABLE review (
    review_id       NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id      NUMBER REFERENCES product(product_id),
    customer_id     NUMBER REFERENCES customer(customer_id),
    rating          NUMBER(2,1) CHECK (rating BETWEEN 1.0 AND 5.0),
    review_text     VARCHAR2(2000),
    helpfull_count  NUMBER DEFAULT 0,
    created_at      TIMESTAMP DEFAULT SYSTIMESTAMP,
    updated_at      TIMESTAMP DEFAULT SYSTIMESTAMP
);

-- 9. 创建搜索日志表
CREATE TABLE search_log (
    log_id          NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    search_term     VARCHAR2(200),
    customer_id     NUMBER REFERENCES customer(customer_id),
    results_count   NUMBER,
    search_time     TIMESTAMP DEFAULT SYSTIMESTAMP,
    ip_address      VARCHAR2(50)
);

-- 10. 创建分区表 - 订单历史（按日期 RANGE 分区）
CREATE TABLE orders_archive (
    order_id        NUMBER,
    customer_id     NUMBER,
    order_date      DATE,
    status          VARCHAR2(20),
    total_amount    NUMBER(12,2),
    shipping_addr   VARCHAR2(500)
)
PARTITION BY RANGE (order_date) (
    PARTITION p_2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_2024_q1 VALUES LESS THAN (TO_DATE('2024-04-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_2024_q2 VALUES LESS THAN (TO_DATE('2024-07-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_max VALUES LESS THAN (MAXVALUE) TABLESPACE jewelry_ts
);

-- ============================================================
-- 索引创建
-- ============================================================

-- B-Tree 索引（默认索引类型）
CREATE INDEX idx_product_category ON product(category_id);
CREATE INDEX idx_product_material ON product(material);
CREATE INDEX idx_product_price ON product(price_cny);
CREATE INDEX idx_product_name ON product(product_name);

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_date ON orders(order_date);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_store ON orders(store_id);

CREATE INDEX idx_order_item_order ON order_item(order_id);
CREATE INDEX idx_order_item_product ON order_item(product_id);

CREATE INDEX idx_inventory_product ON inventory(product_id);
CREATE INDEX idx_inventory_warehouse ON inventory(warehouse_id);

CREATE INDEX idx_review_product ON review(product_id);
CREATE INDEX idx_review_customer ON review(customer_id);
CREATE INDEX idx_review_rating ON review(rating);

CREATE INDEX idx_customer_city ON customer(city);
CREATE INDEX idx_customer_level ON customer(member_level);

-- 全文索引（Oracle Text）
CREATE INDEX idx_product_search ON product(search_vector)
  INDEXTYPE IS CTXSYS.CONTEXT;

-- 空间索引（SDO_GEOMETRY 地理位置）
CREATE INDEX idx_customer_location ON customer(latitude, longitude)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

-- 位图索引（低基数列）
CREATE BITMAP INDEX idx_orders_status_bm ON orders(status);
CREATE BITMAP INDEX idx_customer_level_bm ON customer(member_level);
CREATE BITMAP INDEX idx_product_active_bm ON product(is_active);

-- 函数索引（FBI - Function Based Index）
CREATE INDEX idx_product_price_discount ON product(price_cny * 0.9)
  WHERE is_active = 'Y';

CREATE INDEX idx_orders_month ON orders(TO_CHAR(order_date, 'YYYY-MM'));

-- 复合索引（多列索引）
CREATE INDEX idx_orders_cust_date ON orders(customer_id, order_date);
CREATE INDEX idx_order_item_prod_qty ON order_item(product_id, quantity);
CREATE INDEX idx_product_mat_price ON product(material, price_cny);

-- 唯一索引
CREATE UNIQUE INDEX idx_customer_phone ON customer(phone);
CREATE UNIQUE INDEX idx_customer_email ON customer(email);

-- ============================================================
-- 视图
-- ============================================================

CREATE OR REPLACE VIEW v_product_stock AS
SELECT
    p.product_id, p.product_name, p.category_id,
    c.category_name, p.material, p.price_cny,
    SUM(i.quantity) AS total_stock,
    COUNT(i.inventory_id) AS warehouse_count
FROM product p
JOIN category c ON p.category_id = c.category_id
LEFT JOIN inventory i ON p.product_id = i.product_id
GROUP BY p.product_id, p.product_name, p.category_id, c.category_name, p.material, p.price_cny;

CREATE OR REPLACE VIEW v_sales_summary AS
SELECT
    p.product_id, p.product_name, p.category_id,
    c.category_name,
    COUNT(oi.item_id) AS order_count,
    SUM(oi.quantity) AS total_sold,
    SUM(oi.subtotal) AS total_revenue,
    AVG(r.rating) AS avg_rating
FROM product p
JOIN category c ON p.category_id = c.category_id
LEFT JOIN order_item oi ON p.product_id = oi.product_id
LEFT JOIN review r ON p.product_id = r.product_id
GROUP BY p.product_id, p.product_name, p.category_id, c.category_name;

CREATE OR REPLACE VIEW v_customer_ranking AS
SELECT
    customer_id, customer_name, member_level, city, total_spent,
    RANK() OVER (ORDER BY total_spent DESC) AS spend_rank
FROM customer;

-- ============================================================
-- 存储过程
-- ============================================================

CREATE OR REPLACE PROCEDURE sp_update_stock(
    p_product_id   IN NUMBER,
    p_warehouse_id IN NUMBER,
    p_quantity     IN NUMBER,
    p_old_version  IN NUMBER,
    p_result       OUT VARCHAR2
) IS
    v_current_version NUMBER;
BEGIN
    SELECT version INTO v_current_version
    FROM inventory
    WHERE product_id = p_product_id
      AND warehouse_id = p_warehouse_id
      AND version = p_old_version;

    UPDATE inventory
    SET quantity = quantity + p_quantity,
        version = version + 1,
        last_updated = SYSTIMESTAMP
    WHERE product_id = p_product_id
      AND warehouse_id = p_warehouse_id;

    p_result := 'SUCCESS';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        p_result := 'VERSION_CONFLICT';
        ROLLBACK;
END;
/

CREATE OR REPLACE PROCEDURE sp_analyze_index_usage(
    p_schema_name IN VARCHAR2 DEFAULT USER
) IS
BEGIN
    EXECUTE IMMEDIATE 'ALTER SYSTEM FLUSH SHARED_POOL';
    DBMS_OUTPUT.PUT_LINE('已清空共享池，请在此后执行测试查询');
    DBMS_OUTPUT.PUT_LINE('然后查询: SELECT * FROM V$OBJECT_USAGE WHERE owner = ''' || p_schema_name || '''');
END;
/

CREATE OR REPLACE PROCEDURE sp_rebuild_indexes(
    p_table_name IN VARCHAR2
) IS
    CURSOR c_indexes IS
        SELECT index_name FROM all_indexes
        WHERE table_name = UPPER(p_table_name)
          AND owner = USER;
BEGIN
    FOR idx IN c_indexes LOOP
        EXECUTE IMMEDIATE 'ALTER INDEX ' || idx.index_name || ' REBUILD ONLINE';
        DBMS_OUTPUT.PUT_LINE('已重建索引: ' || idx.index_name);
    END LOOP;
END;
/

-- 序列
CREATE SEQUENCE seq_inventory_log START WITH 1 INCREMENT BY 1;
CREATE SEQUENCE seq_audit_log START WITH 1 INCREMENT BY 1;

-- 同义词
CREATE SYNONYM syn_product_stock FOR v_product_stock;
CREATE SYNONYM syn_sales_summary FOR v_sales_summary;
CREATE SYNONYM syn_customer_ranking FOR v_customer_ranking;

-- 验证
SELECT table_name, num_rows, blocks, last_analyzed
FROM user_tables ORDER BY table_name;

SELECT index_name, index_type, table_name, status
FROM user_indexes ORDER BY table_name, index_name;

SELECT view_name FROM user_views ORDER BY view_name;

SELECT procedure_name FROM user_procedures
WHERE object_type = 'PROCEDURE' ORDER BY procedure_name;

COMMIT;
/


-- ============================================================
-- 模块执行完成
-- ============================================================

-- ============================================================
-- Oracle 21c 珠宝行业「索引与性能优化」
-- 模块2：批量测试数据
-- 分类/产品/客户/订单/明细/库存/评价/搜索日志/分区数据
-- ============================================================

CONNECT jewelry_user/jewelry_pwd;

-- 1. 插入分类数据
INSERT ALL
INTO category (category_name, parent_id, description, sort_order) VALUES ('珠宝首饰', NULL, '珠宝首饰大类', 1)
INTO category (category_name, parent_id, description, sort_order) VALUES ('戒指', 1, '各类戒指', 1)
INTO category (category_name, parent_id, description, sort_order) VALUES ('项链', 1, '各类项链', 2)
INTO category (category_name, parent_id, description, sort_order) VALUES ('手链', 1, '各类手链', 3)
INTO category (category_name, parent_id, description, sort_order) VALUES ('耳环', 1, '各类耳环', 4)
INTO category (category_name, parent_id, description, sort_order) VALUES ('钻戒', 2, '钻石戒指', 1)
INTO category (category_name, parent_id, description, sort_order) VALUES ('彩宝戒指', 2, '彩色宝石戒指', 2)
INTO category (category_name, parent_id, description, sort_order) VALUES ('吊坠项链', 3, '吊坠类项链', 1)
INTO category (category_name, parent_id, description, sort_order) VALUES ('链式项链', 3, '链条款项链', 2)
INTO category (category_name, parent_id, description, sort_order) VALUES ('翡翠珠宝', NULL, '翡翠珠宝专区', 5)
SELECT * FROM dual;

-- 2. 插入产品数据（50款珠宝）
INSERT ALL
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('经典六爪钻戒', 6, '铂金', '钻石', 1.00, 3.5, 'FFFFFF', 'VVS1', 'EXCELLENT', 58800, 12, 'Y', '钻戒 钻石 铂金 六爪 求婚 结婚')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('玫瑰金钻石戒指', 6, '玫瑰金', '钻石', 0.50, 2.8, 'FFFFFF', 'VS1', 'VERY_GOOD', 18800, 25, 'Y', '钻戒 钻石 玫瑰金 简约 日常')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('红宝石戒指', 7, '18K金', '红宝石', 1.20, 4.2, 'FF0000', 'VS2', 'GOOD', 32500, 8, 'Y', '红宝石 戒指 18K金 彩宝 高级')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('蓝宝石戒指', 7, '铂金', '蓝宝石', 1.50, 4.5, '0000FF', 'VVS2', 'EXCELLENT', 45000, 6, 'Y', '蓝宝石 戒指 铂金 彩宝 皇家蓝')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('翡翠吊坠项链', 8, '18K金', '翡翠', NULL, 8.5, '00FF00', NULL, NULL, 28800, 15, 'Y', '翡翠 吊坠 项链 18K金 玉石 中式')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('钻石吊坠项链', 8, '铂金', '钻石', 0.30, 3.2, 'FFFFFF', 'VS1', 'VERY_GOOD', 15800, 30, 'Y', '钻石 吊坠 项链 铂金 简约 百搭')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('珍珠链式项链', 9, '18K金', '珍珠', NULL, 12.0, 'FFF8DC', NULL, NULL, 8800, 40, 'Y', '珍珠 项链 链式 18K金 优雅 女性')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('黄金手链', 5, '黄金', NULL, NULL, 25.0, 'FFD700', NULL, NULL, 12800, 50, 'Y', '黄金 手链 足金 传统 喜庆')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('翡翠手链', 5, '18K金', '翡翠', NULL, 18.5, '00AA00', NULL, NULL, 35000, 10, 'Y', '翡翠 手链 18K金 玉石 高档')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('钻石耳环', 4, '铂金', '钻石', 0.40, 2.5, 'FFFFFF', 'VVS1', 'EXCELLENT', 22800, 18, 'Y', '钻石 耳环 铂金 闪亮 派对')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('红宝石耳环', 4, '18K玫瑰金', '红宝石', 0.80, 3.0, 'FF0000', 'VS1', 'VERY_GOOD', 19800, 14, 'Y', '红宝石 耳环 玫瑰金 彩宝 优雅')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('蓝宝石项链', 3, '铂金', '蓝宝石', 2.00, 5.5, '0000FF', 'VVS2', 'EXCELLENT', 68000, 4, 'Y', '蓝宝石 项链 铂金 彩宝 皇家蓝 高级')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('紫水晶项链', 3, '银', '紫水晶', 3.00, 6.0, '800080', 'VS2', 'GOOD', 3800, 60, 'Y', '紫水晶 项链 银 紫色 平价')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('石榴石手链', 5, '18K金', '石榴石', 2.50, 15.0, '8B0000', 'VS1', 'VERY_GOOD', 12500, 22, 'Y', '石榴石 手链 18K金 红色 养生')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('坦桑石戒指', 7, '铂金', '坦桑石', 2.80, 4.8, '191970', 'VVS1', 'EXCELLENT', 28000, 7, 'Y', '坦桑石 戒指 铂金 彩宝 蓝色')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('碧玺项链', 3, '18K金', '碧玺', 3.50, 7.0, 'FF69B4', 'VS2', 'GOOD', 15800, 16, 'Y', '碧玺 项链 18K金 彩宝 彩虹')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('海蓝宝手链', 5, '银', '海蓝宝', 4.00, 14.0, '87CEEB', 'VS1', 'VERY_GOOD', 8800, 28, 'Y', '海蓝宝 手链 银 蓝色 清新')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('月光石戒指', 7, '银', '月光石', 2.00, 3.5, 'F0F8FF', NULL, 'GOOD', 2800, 45, 'Y', '月光石 戒指 银 白色 梦幻')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('橄榄石吊坠', 8, '18K金', '橄榄石', 1.50, 3.0, '9ACD32', 'VS1', 'VERY_GOOD', 6500, 35, 'Y', '橄榄石 吊坠 18K金 绿色 自然')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('托帕石耳环', 4, '18K金', '托帕石', 2.00, 2.8, 'FFD700', 'VVS1', 'EXCELLENT', 9800, 20, 'Y', '托帕石 耳环 18K金 金色 高贵')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('黑玛瑙手链', 5, '银', '黑玛瑙', NULL, 20.0, '000000', NULL, NULL, 1800, 80, 'Y', '黑玛瑙 手链 银 黑色 男士')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('粉晶吊坠', 8, '银', '粉晶', NULL, 5.0, 'FFB6C1', NULL, NULL, 1500, 65, 'Y', '粉晶 吊坠 银 粉色 爱情')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('青金石项链', 3, '18K金', '青金石', 2.50, 6.5, '191970', 'VS2', 'GOOD', 7800, 24, 'Y', '青金石 项链 18K金 蓝色 复古')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('琥珀手链', 5, '银', '琥珀', NULL, 16.0, 'FF8C00', NULL, NULL, 3500, 38, 'Y', '琥珀 手链 银 橙色 天然')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('祖母绿戒指', 7, '铂金', '祖母绿', 1.80, 4.5, '006400', 'VS1', 'EXCELLENT', 52000, 5, 'Y', '祖母绿 戒指 铂金 彩宝 绿色 高级')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('钻石手链', 5, '铂金', '钻石', 0.60, 5.0, 'FFFFFF', 'VS1', 'VERY_GOOD', 38800, 10, 'Y', '钻石 手链 铂金 闪亮 奢华')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('翡翠戒指', 2, '18K金', '翡翠', NULL, 6.0, '00AA00', NULL, NULL, 42000, 8, 'Y', '翡翠 戒指 18K金 玉石 高档 绿色')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('黄金项链', 3, '黄金', NULL, NULL, 30.0, 'FFD700', NULL, NULL, 18800, 35, 'Y', '黄金 项链 足金 传统 喜庆')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('银饰手链', 5, '银', NULL, NULL, 15.0, 'C0C0C0', NULL, NULL, 880, 100, 'Y', '银 手链 银饰 简约 年轻')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('锆石耳环', 4, '银', '锆石', 1.00, 2.0, 'FFFFFF', NULL, 'VERY_GOOD', 680, 90, 'Y', '锆石 耳环 银 闪亮 平价')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('水晶吊坠', 8, '银', '水晶', NULL, 4.0, 'E6E6FA', NULL, NULL, 1200, 55, 'Y', '水晶 吊坠 银 紫色 透明')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('南红戒指', 7, '18K金', '南红', NULL, 5.5, 'DC143C', NULL, NULL, 18500, 12, 'Y', '南红 戒指 18K金 红色 玛瑙')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES (' turquoise项链', 3, '银', '绿松石', NULL, 8.0, '40E0D0', NULL, NULL, 2800, 30, 'Y', '绿松石 项链 银 蓝色 波西米亚')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('蜜蜡手链', 5, '银', '蜜蜡', NULL, 18.0, 'FFA500', NULL, NULL, 4500, 25, 'Y', '蜜蜡 手链 银 黄色 天然')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('红纹石吊坠', 8, '18K金', '红纹石', NULL, 4.5, 'FF6B6B', NULL, NULL, 5800, 20, 'Y', '红纹石 吊坠 18K金 粉色 爱心')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('月光石手链', 5, '银', '月光石', NULL, 12.0, 'F0F8FF', NULL, NULL, 2500, 40, 'Y', '月光石 手链 银 白色 梦幻')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('石榴石项链', 3, '18K金', '石榴石', 2.00, 6.0, '8B0000', 'VS1', 'VERY_GOOD', 9800, 18, 'Y', '石榴石 项链 18K金 红色 养生')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('碧玺戒指', 7, '铂金', '碧玺', 3.00, 5.0, 'FF1493', 'VS2', 'EXCELLENT', 22000, 8, 'Y', '碧玺 戒指 铂金 彩宝 彩虹')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('海蓝宝吊坠', 8, '18K金', '海蓝宝', 2.50, 4.0, '87CEEB', 'VS1', 'VERY_GOOD', 12800, 15, 'Y', '海蓝宝 吊坠 18K金 蓝色 清新')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('紫水晶耳环', 4, '银', '紫水晶', 1.50, 2.5, '800080', 'VS2', 'GOOD', 2200, 45, 'Y', '紫水晶 耳环 银 紫色 优雅')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('黄水晶戒指', 7, '18K金', '黄水晶', 2.00, 4.0, 'FFD700', 'VVS1', 'EXCELLENT', 8500, 22, 'Y', '黄水晶 戒指 18K金 金色 财富')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('黑曜石手链', 5, '银', '黑曜石', NULL, 18.0, '000000', NULL, NULL, 800, 85, 'Y', '黑曜石 手链 银 黑色 辟邪')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('蛋白石项链', 3, '铂金', '蛋白石', 1.80, 3.5, 'FFF8DC', NULL, 'GOOD', 15500, 10, 'Y', '蛋白石 项链 铂金 白色 欧泊')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('象牙红珊瑚吊坠', 8, '18K金', '珊瑚', NULL, 5.0, 'FF6347', NULL, NULL, 12000, 8, 'Y', '珊瑚 吊坠 18K金 红色 海洋')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('坦桑石耳环', 4, '18K金', '坦桑石', 2.00, 2.8, '191970', 'VVS1', 'EXCELLENT', 18000, 12, 'Y', '坦桑石 耳环 18K金 蓝色 高级')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('东陵玉手链', 5, '银', '东陵玉', NULL, 22.0, '006400', NULL, NULL, 1500, 50, 'Y', '东陵玉 手链 银 绿色 玉石')
INTO product (product_name, category_id, material, gemstone_type, gemstone_carat, weight_grams, color_code, clarity_grade, cut_grade, price_cny, stock_quantity, is_active, search_vector)
VALUES ('孔雀石戒指', 7, '银', '孔雀石', NULL, 6.0, '008B8B', NULL, NULL, 2500, 30, 'Y', '孔雀石 戒指 银 绿色 复古')
SELECT * FROM dual;

-- 3. 插入客户数据（30人）
INSERT ALL
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('张伟', '13800138001', 'zhangwei@email.com', '钻石', 158000, '北京', 39.9042, 116.4074)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('王芳', '13800138002', 'wangfang@email.com', '金卡', 85000, '上海', 31.2304, 121.4737)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('李娜', '13800138003', 'lina@email.com', '金卡', 72000, '广州', 23.1291, 113.2644)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('刘洋', '13800138004', 'liuyang@email.com', '银卡', 35000, '深圳', 22.5431, 114.0579)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('陈明', '13800138005', 'chenming@email.com', '钻石', 198000, '北京', 39.9042, 116.4074)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('赵敏', '13800138006', 'zhaomin@email.com', '金卡', 68000, '杭州', 30.2741, 120.1551)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('孙磊', '13800138007', 'sunlei@email.com', '银卡', 28000, '成都', 30.5728, 104.0668)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('周静', '13800138008', 'zhoujing@email.com', '普通', 8500, '武汉', 30.5928, 114.3055)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('吴强', '13800138009', 'wuqiang@email.com', '金卡', 95000, '南京', 32.0603, 118.7969)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('郑华', '13800138010', 'zhenghua@email.com', '钻石', 225000, '上海', 31.2304, 121.4737)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('马超', '13800138011', 'machao@email.com', '银卡', 42000, '西安', 34.3416, 108.9398)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('朱琳', '13800138012', 'zhulin@email.com', '金卡', 78000, '重庆', 29.5630, 106.5516)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('胡涛', '13800138013', 'hutao@email.com', '普通', 5200, '长沙', 28.2282, 112.9388)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('林峰', '13800138014', 'linfeng@email.com', '金卡', 88000, '厦门', 24.4798, 118.0894)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('何雪', '13800138015', 'hexue@email.com', '钻石', 168000, '北京', 39.9042, 116.4074)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('高勇', '13800138016', 'gaoyong@email.com', '银卡', 38000, '昆明', 25.0389, 102.7183)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('罗莉', '13800138017', 'luoli@email.com', '金卡', 92000, '成都', 30.5728, 104.0668)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('梁博', '13800138018', 'liangbo@email.com', '普通', 3800, '郑州', 34.7466, 113.6253)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('宋佳', '13800138019', 'songjia@email.com', '银卡', 45000, '天津', 39.3434, 117.3616)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('谢鹏', '13800138020', 'xiepeng@email.com', '金卡', 76000, '武汉', 30.5928, 114.3055)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('唐欣', '13800138021', 'tangxin@email.com', '钻石', 185000, '上海', 31.2304, 121.4737)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('韩磊', '13800138022', 'hanlei@email.com', '银卡', 32000, '沈阳', 41.8057, 123.4315)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('杨帆', '13800138023', 'yangfan@email.com', '普通', 6500, '哈尔滨', 45.8038, 126.5340)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('徐婷', '13800138024', 'xuting@email.com', '金卡', 82000, '广州', 23.1291, 113.2644)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('邓辉', '13800138025', 'denghui@email.com', '银卡', 48000, '深圳', 22.5431, 114.0579)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('冯刚', '13800138026', 'fenggang@email.com', '钻石', 210000, '北京', 39.9042, 116.4074)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('蒋颖', '13800138027', 'jiangying@email.com', '金卡', 65000, '杭州', 30.2741, 120.1551)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('沈毅', '13800138028', 'shenyi@email.com', '普通', 4200, '南京', 32.0603, 118.7969)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('余蕾', '13800138029', 'yulei@email.com', '银卡', 52000, '西安', 34.3416, 108.9398)
INTO customer (customer_name, phone, email, member_level, total_spent, city, latitude, longitude) VALUES ('潘磊', '13800138030', 'panlei@email.com', '金卡', 98000, '重庆', 29.5630, 106.5516)
SELECT * FROM dual;

-- 4. 插入订单数据（30单）
INSERT ALL
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (1, TIMESTAMP '2024-01-15 14:30:00', '已完成', 58800, '北京市朝阳区建国路88号', '信用卡', 1)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (2, TIMESTAMP '2024-01-18 10:15:00', '已完成', 18800, '上海市浦东新区陆家嘴环路100号', '支付宝', 2)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (3, TIMESTAMP '2024-02-01 16:45:00', '已完成', 32500, '广州市天河区体育西路200号', '信用卡', 3)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (4, TIMESTAMP '2024-02-10 11:20:00', '已发货', 15800, '深圳市南山区科技园南区', '微信支付', 4)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (5, TIMESTAMP '2024-02-14 09:00:00', '已完成', 68000, '北京市海淀区中关村大街1号', '信用卡', 1)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (6, TIMESTAMP '2024-02-20 15:30:00', '已完成', 28800, '杭州市西湖区文三路300号', '支付宝', 5)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (7, TIMESTAMP '2024-03-01 13:10:00', '待处理', 8800, '成都市锦江区春熙路10号', '微信支付', 6)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (8, TIMESTAMP '2024-03-05 10:00:00', '已确认', 12800, '武汉市江汉区解放大道500号', '信用卡', 7)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (9, TIMESTAMP '2024-03-10 14:20:00', '已完成', 45000, '南京市鼓楼区中山北路600号', '支付宝', 8)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (10, TIMESTAMP '2024-03-15 16:00:00', '已完成', 22800, '上海市静安区南京西路800号', '信用卡', 2)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (11, TIMESTAMP '2024-03-20 11:30:00', '已发货', 19800, '西安市雁塔区高新路200号', '微信支付', 9)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (12, TIMESTAMP '2024-03-25 09:45:00', '已完成', 38800, '重庆市渝中区解放碑步行街50号', '支付宝', 10)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (13, TIMESTAMP '2024-04-01 15:00:00', '待处理', 3800, '长沙市芙蓉区五一广场100号', '微信支付', 11)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (14, TIMESTAMP '2024-04-05 10:30:00', '已完成', 22000, '厦门市思明区中山路300号', '信用卡', 12)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (15, TIMESTAMP '2024-04-10 14:00:00', '已完成', 52000, '北京市海淀区西直门北大街30号', '支付宝', 1)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (16, TIMESTAMP '2024-04-15 11:00:00', '已发货', 9800, '昆明市五华区翠湖路80号', '微信支付', 13)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (17, TIMESTAMP '2024-04-20 16:30:00', '已完成', 35000, '成都市武侯区科华北路100号', '信用卡', 6)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (18, TIMESTAMP '2024-04-25 09:15:00', '已确认', 6500, '郑州市金水区花园路200号', '支付宝', 14)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (19, TIMESTAMP '2024-05-01 13:00:00', '已完成', 18500, '天津市和平区南京路100号', '信用卡', 15)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (20, TIMESTAMP '2024-05-05 15:45:00', '已完成', 28000, '武汉市武昌区中南路88号', '支付宝', 7)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (21, TIMESTAMP '2024-05-10 10:20:00', '已发货', 42000, '上海市徐汇区淮海中路500号', '信用卡', 2)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (22, TIMESTAMP '2024-05-15 14:30:00', '待处理', 8500, '沈阳市沈河区中街路100号', '微信支付', 16)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (23, TIMESTAMP '2024-05-20 11:00:00', '已完成', 15500, '哈尔滨市道里区中央大街200号', '支付宝', 17)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (24, TIMESTAMP '2024-05-25 16:15:00', '已完成', 38000, '广州市越秀区北京路300号', '信用卡', 3)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (25, TIMESTAMP '2024-06-01 09:30:00', '已确认', 25000, '深圳市福田区华强北路500号', '支付宝', 4)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (26, TIMESTAMP '2024-06-05 13:45:00', '已完成', 95000, '北京市朝阳区国贸大厦', '信用卡', 1)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (27, TIMESTAMP '2024-06-10 15:00:00', '已完成', 65000, '杭州市西湖区保俶路80号', '支付宝', 5)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (28, TIMESTAMP '2024-06-15 10:00:00', '已发货', 48000, '南京市玄武区新街口200号', '微信支付', 8)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (29, TIMESTAMP '2024-06-20 14:00:00', '已完成', 52000, '西安市碑林区南大街100号', '信用卡', 9)
INTO orders (customer_id, order_date, status, total_amount, shipping_addr, payment_method, store_id) VALUES (30, TIMESTAMP '2024-06-25 16:00:00', '已完成', 78000, '重庆市江北区观音桥步行街', '支付宝', 10)
SELECT * FROM dual;

-- 5. 插入订单明细数据
INSERT ALL
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (1, 1, 1, 58800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (2, 2, 1, 18800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (3, 3, 1, 32500)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (4, 6, 1, 15800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (5, 13, 1, 68000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (6, 5, 1, 28800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (7, 7, 1, 8800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (8, 8, 1, 12800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (9, 11, 1, 45000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (10, 10, 1, 22800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (11, 12, 1, 19800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (12, 9, 1, 38800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (13, 14, 1, 3800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (14, 28, 1, 22000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (15, 31, 1, 52000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (16, 16, 1, 9800)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (17, 27, 1, 35000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (18, 19, 1, 6500)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (19, 36, 1, 18500)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (20, 30, 1, 28000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (21, 34, 1, 42000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (22, 20, 1, 8500)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (23, 44, 1, 15500)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (24, 24, 1, 38000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (25, 38, 1, 25000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (26, 1, 1, 95000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (27, 46, 1, 65000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (28, 42, 1, 48000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (29, 48, 1, 52000)
INTO order_item (order_id, product_id, quantity, unit_price) VALUES (30, 50, 1, 78000)
SELECT * FROM dual;

-- 6. 插入库存数据
INSERT ALL
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (1, 1, '北京总仓', 12, 5, 'BJ-A-01')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (2, 2, '上海分仓', 25, 10, 'SH-B-02')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (3, 3, '广州分仓', 8, 5, 'GZ-C-01')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (4, 1, '北京总仓', 6, 3, 'BJ-A-03')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (5, 2, '上海分仓', 15, 8, 'SH-A-01')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (6, 1, '北京总仓', 30, 15, 'BJ-B-01')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (7, 3, '广州分仓', 40, 20, 'GZ-A-02')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (8, 4, '深圳分仓', 50, 25, 'SZ-A-01')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (9, 2, '上海分仓', 10, 5, 'SH-C-01')
INTO inventory (product_id, warehouse_id, warehouse_name, quantity, reorder_level, location_code) VALUES (10, 1, '北京总仓', 18, 8, 'BJ-C-02')
SELECT * FROM dual;

-- 7. 插入评价数据
INSERT ALL
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (1, 1, 5.0, '钻戒非常闪亮，做工精细，女朋友非常满意！', 25)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (2, 2, 4.5, '玫瑰金颜色很温柔，日常佩戴很百搭', 18)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (3, 3, 5.0, '红宝石颜色纯正，证书齐全，值得收藏', 32)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (5, 6, 4.8, '翡翠水头很好，吊坠设计典雅大方', 22)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (7, 8, 4.0, '珍珠光泽不错，性价比很高', 15)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (10, 10, 4.7, '钻石火彩很好，铂金材质很有质感', 28)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (13, 9, 4.5, '蓝宝石颜色深邃，镶嵌工艺精湛', 20)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (15, 14, 4.3, '碧玺颜色丰富，手链佩戴舒适', 12)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (26, 26, 5.0, '黄金项链做工精良，重量足，非常满意', 35)
INTO review (product_id, customer_id, rating, review_text, helpfull_count) VALUES (31, 5, 4.6, '钻石手链很闪，送妈妈的礼物她很喜欢的', 19)
SELECT * FROM dual;

-- 8. 插入搜索日志数据
INSERT ALL
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('钻戒', 1, 15, TIMESTAMP '2024-01-10 09:00:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('黄金', 4, 12, TIMESTAMP '2024-01-12 14:00:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('翡翠', 6, 8, TIMESTAMP '2024-01-15 10:30:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('钻石项链', 2, 10, TIMESTAMP '2024-01-18 11:00:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('红宝石', 3, 5, TIMESTAMP '2024-01-20 16:00:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('手链', 7, 20, TIMESTAMP '2024-02-01 09:30:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('蓝宝石', 9, 4, TIMESTAMP '2024-02-05 15:00:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('珍珠项链', 8, 6, TIMESTAMP '2024-02-10 10:00:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('碧玺', 14, 3, TIMESTAMP '2024-02-15 14:30:00')
INTO search_log (search_term, customer_id, results_count, search_time) VALUES ('银饰', 18, 18, TIMESTAMP '2024-02-20 11:00:00')
SELECT * FROM dual;

-- 9. 插入分区表数据
INSERT ALL
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1001, 1, DATE '2023-01-15', '已完成', 45000, '北京市朝阳区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1002, 2, DATE '2023-02-20', '已完成', 28000, '上海市浦东新区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1003, 3, DATE '2023-03-10', '已完成', 35000, '广州市天河区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1004, 4, DATE '2023-04-05', '已完成', 18000, '深圳市南山区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1005, 5, DATE '2023-05-12', '已完成', 52000, '北京市海淀区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1006, 6, DATE '2023-06-18', '已完成', 22000, '杭州市西湖区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1007, 7, DATE '2023-07-22', '已完成', 15000, '成都市锦江区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1008, 8, DATE '2023-08-15', '已完成', 38000, '武汉市江汉区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1009, 9, DATE '2023-09-20', '已完成', 42000, '南京市鼓楼区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1010, 10, DATE '2023-10-01', '已完成', 68000, '上海市静安区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1011, 11, DATE '2023-11-11', '已完成', 25000, '西安市雁塔区')
INTO orders_archive (order_id, customer_id, order_date, status, total_amount, shipping_addr) VALUES (1012, 12, DATE '2023-12-25', '已完成', 35000, '重庆市渝中区')
SELECT * FROM dual;

-- 更新统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CATEGORY');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PRODUCT');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMER');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDER_ITEM');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'INVENTORY');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'REVIEW');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SEARCH_LOG');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS_ARCHIVE');

-- 验证数据
SELECT 'CATEGORY' AS table_name, COUNT(*) AS row_count FROM category
UNION ALL SELECT 'PRODUCT', COUNT(*) FROM product
UNION ALL SELECT 'CUSTOMER', COUNT(*) FROM customer
UNION ALL SELECT 'ORDERS', COUNT(*) FROM orders
UNION ALL SELECT 'ORDER_ITEM', COUNT(*) FROM order_item
UNION ALL SELECT 'INVENTORY', COUNT(*) FROM inventory
UNION ALL SELECT 'REVIEW', COUNT(*) FROM review
UNION ALL SELECT 'SEARCH_LOG', COUNT(*) FROM search_log
UNION ALL SELECT 'ORDERS_ARCHIVE', COUNT(*) FROM orders_archive;

COMMIT;
/


-- ============================================================
-- 模块执行完成
-- ============================================================

-- ============================================================
-- Oracle 21c 珠宝行业「索引与性能优化」
-- 模块3：索引基础（B-Tree/哈希/位图/全文索引）
-- ============================================================

CONNECT jewelry_user/jewelry_pwd;

-- ============================================================
-- 3.1 B-Tree 索引（Oracle 默认索引类型）
-- ============================================================

-- B-Tree索引原理：
--   - 平衡树结构，所有叶子节点深度相同
--   - 适合高基数列（数据分布均匀、唯一值多）
--   - 适合范围查询、排序、分组
--   - 不适合低基数列（如性别、状态）

-- 示例1：单列B-Tree索引（产品类别查询）
-- 场景：按类别筛选珠宝产品
CREATE INDEX idx_product_category_bt ON product(category_id);

-- 性能对比：有索引 vs 无索引
-- 无索引（全表扫描）：
EXPLAIN PLAN FOR
SELECT product_name, price_cny FROM product WHERE category_id = 6;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 有索引（索引范围扫描）：
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PRODUCT');
EXPLAIN PLAN FOR
SELECT product_name, price_cny FROM product WHERE category_id = 6;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 示例2：复合B-Tree索引（多列查询优化）
-- 场景：按材质和价格范围查询
CREATE INDEX idx_product_mat_price_bt ON product(material, price_cny);

-- 复合索引的左前缀原则：
-- idx_product_mat_price(material, price_cny) 可优化以下查询：
--   WHERE material = '铂金'                    -- 左前缀匹配
--   WHERE material = '铂金' AND price_cny > 10000  -- 完全匹配
--   WHERE price_cny > 10000                  -- 无法使用索引（非左前缀）

-- 示例3：降序索引（Oracle 11g+支持）
-- 场景：按价格降序排列的产品列表
CREATE INDEX idx_product_price_desc ON product(price_cny DESC);

-- 示例4：前缀索引（长字符串优化）
-- 场景：产品名模糊查询（前10个字符）
CREATE INDEX idx_product_name_prefix ON product(product_name);

-- ============================================================
-- 3.2 哈希索引的Oracle实现方式
-- ============================================================

-- Oracle不直接支持哈希索引（如MySQL的HASH索引）
-- 但可以通过以下方式实现哈希查询优化：

-- 方法1：使用DBMS_CRYPTO生成哈希值列
-- 场景：快速查找重复产品（按名称哈希匹配）
ALTER TABLE product ADD product_name_hash RAW(32);

-- 使用触发器自动维护哈希列
CREATE OR REPLACE TRIGGER trg_product_hash
BEFORE INSERT OR UPDATE OF product_name ON product
FOR EACH ROW
BEGIN
    :NEW.product_name_hash := DBMS_CRYPTO.HASH(
        UTL_RAW.CAST_TO_RAW(:NEW.product_name),
        DBMS_CRYPTO.HASH_SHA256
    );
END;
/

-- 在哈希列上创建B-Tree索引（等值查询极快）
CREATE INDEX idx_product_name_hash ON product(product_name_hash);

-- 哈希等值查询（性能优于LIKE模糊匹配）
SELECT product_id, product_name
FROM product
WHERE product_name_hash = DBMS_CRYPTO.HASH(
    UTL_RAW.CAST_TO_RAW('钻戒'),
    DBMS_CRYPTO.HASH_SHA256
);

-- 方法2：使用DBMS_ROWID定位（哈希分桶表）
-- 场景：通过哈希分布均匀分配数据到多个表空间
-- 注意：Oracle的哈希分区表不是哈希索引，但可实现类似效果

-- ============================================================
-- 3.3 位图索引（Bitmap Index）
-- ============================================================

-- 位图索引原理：
--   - 用位图数组存储每个唯一值的行ID映射
--   - 适合低基数列（唯一值少，如性别/状态/等级）
--   - 支持高效的位运算（AND/OR/NOT）
--   - 不适合高并发DML场景（行级锁粒度大）

-- 示例1：订单状态位图索引
-- 场景：统计各状态订单数量
CREATE BITMAP INDEX idx_orders_status_bm ON orders(status);

-- 位图索引优势查询：多条件组合筛选
-- 场景：查询"已完成"且金额>10000的订单
EXPLAIN PLAN FOR
SELECT /*+ INDEX(orders idx_orders_status_bm) */ *
FROM orders
WHERE status = '已完成' AND total_amount > 10000;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 位图索引的位运算优势（多列组合）
-- 场景：查询"金卡"或"钻石"会员的订单
EXPLAIN PLAN FOR
SELECT /*+ INDEX(orders idx_orders_status_bm) */ COUNT(*)
FROM orders o, customer c
WHERE o.customer_id = c.customer_id
  AND c.member_level IN ('金卡', '钻石');

-- 示例2：客户等级位图索引
CREATE BITMAP INDEX idx_customer_level_bm ON customer(member_level);

-- 示例3：产品启用状态位图索引
CREATE BITMAP INDEX idx_product_active_bm ON product(is_active);

-- 位图索引适用场景总结：
-- 1. 数据仓库/OLAP场景（读多写少）
-- 2. 低基数列（唯一值/总行数 < 1%）
-- 3. 复杂多条件组合查询
-- 4. COUNT/GROUP BY聚合查询

-- 位图索引不适用场景：
-- 1. 高并发OLTP（频繁UPDATE/INSERT/DELETE）
-- 2. 高基数列（如订单号、时间戳）
-- 3. 需要行级锁的场景

-- ============================================================
-- 3.4 全文索引（Oracle Text / CTXSYS）
-- ============================================================

-- 全文索引原理：
--   - 基于Oracle Text引擎（CTXSYS）
--   - 支持自然语言搜索、模糊搜索、相关度排序
--   - 适合大文本字段的产品搜索

-- 检查CTXSYS用户是否存在
SELECT username FROM all_users WHERE username = 'CTXSYS';

-- 如果CTXSYS不存在，需要以SYS用户执行：
-- @?/ctx/admin/catctx.sql <tablespace> <ctxsys_password> EXCLUSIVE

-- 创建全文索引
CREATE INDEX idx_product_fulltext ON product(search_vector)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS (
    'LEXER my_lexer
     WORDLIST my_wordlist
     STORAGE my_storage'
  );

-- 使用CTX_DDL创建自定义词法分析器（中文分词）
BEGIN
    CTX_DDL.CREATE_PREFERENCE('my_lexer', 'CHINESE_VGRAM_LEXER');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('词法分析器已存在或创建失败: ' || SQLERRM);
END;
/

-- 创建词表
BEGIN
    CTX_DDL.CREATE_PREFERENCE('my_wordlist', 'BASIC_WORDLIST');
    CTX_DDL.SET_ATTRIBUTE('my_wordlist', 'STEMMER', 'CHINESE');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('词表配置: ' || SQLERRM);
END;
/

-- 全文索引查询示例
-- 1. 基本全文搜索
SELECT product_id, product_name, SCORE(1) AS relevance
FROM product
WHERE CONTAINS(search_vector, '钻石 戒指', 1) > 0
ORDER BY SCORE(1) DESC;

-- 2. 模糊搜索（通配符）
SELECT product_id, product_name
FROM product
WHERE CONTAINS(search_vector, '翡翠*', 1) > 0;

-- 3. 逻辑运算搜索
SELECT product_id, product_name
FROM product
WHERE CONTAINS(search_vector, '钻石 AND 项链', 1) > 0;

-- 4. 同义词搜索（需配置同义词表）
-- 添加同义词：钻戒 -> 钻石戒指
BEGIN
    CTX_DDL.CREATE_SYNONYM_SET('my_synset');
    CTX_DDL.ADD_TO_SYNONYM_SET('my_synset', '钻戒', '钻石戒指');
    CTX_DDL.ADD_TO_SYNONYM_SET('my_synset', '钻石', '钻石');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('同义词集配置: ' || SQLERRM);
END;
/

-- 5. 搜索结果高亮
SELECT PRODUCT_NAME,
       DBMS_LOB.SUBSTR(
           CTX_DOC.MARKUP('idx_product_fulltext', product_id, '钻石戒指'),
           4000, 1
       ) AS highlighted_text
FROM product
WHERE CONTAINS(search_vector, '钻石戒指', 1) > 0;

-- ============================================================
-- 3.5 索引统计信息与优化器
-- ============================================================

-- 收集表统计信息（优化器依赖统计信息选择执行计划）
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'PRODUCT', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDERS', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORDER_ITEM', CASCADE => TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CUSTOMER', CASCADE => TRUE);

-- 查看索引统计信息
SELECT index_name, table_name, num_rows, distinct_keys,
       leaf_blocks, clustering_factor, last_analyzed
FROM user_indexes
WHERE table_name IN ('PRODUCT', 'ORDERS', 'ORDER_ITEM', 'CUSTOMER')
ORDER BY table_name, index_name;

-- 查看索引使用情况
SELECT /*+ GATHER_PLAN_STATISTICS */ *
FROM product
WHERE category_id = 6 AND price_cny > 10000;

-- 查询索引使用统计
SELECT name, reads, writes
FROM v$filestat
WHERE name LIKE '%jewelry%';

-- 监控索引使用（Oracle 11g+）
ALTER INDEX idx_product_category_bt MONITORING USAGE;
ALTER INDEX idx_product_material MONITORING USAGE;

-- 查询被监控的索引使用情况
SELECT index_name, table_name, monitoring, used, start_monitoring, end_monitoring
FROM v$object_usage
WHERE owner = USER;

-- 停止监控
ALTER INDEX idx_product_category_bt NOMONITORING USAGE;
ALTER INDEX idx_product_material NOMONITORING USAGE;

-- 删除未使用的索引
-- SELECT 'ALTER INDEX ' || index_name || ' DROP;'
-- FROM user_indexes
-- WHERE table_name = 'PRODUCT'
-- AND index_name NOT LIKE 'SYS_%'
-- AND index_name NOT LIKE 'IDX_PRODUCT_%';

-- 索引碎片分析与重建
-- 分析索引结构
ANALYZE INDEX idx_product_category_bt VALIDATE STRUCTURE;

-- 查询索引碎片率
SELECT name, lf_rows, del_lf_rows,
       ROUND(del_lf_rows / NULLIF(lf_rows, 0) * 100, 2) AS fragmentation_pct
FROM index_stats;

-- 在线重建索引（不锁表，生产环境推荐）
ALTER INDEX idx_product_category_bt REBUILD ONLINE;
ALTER INDEX idx_product_material REBUILD ONLINE;
ALTER INDEX idx_product_name REBUILD ONLINE;

-- ============================================================
-- 验证索引创建结果
-- ============================================================

SELECT index_name, index_type, table_name, status, leaf_blocks
FROM user_indexes
WHERE table_name IN ('PRODUCT', 'ORDERS', 'ORDER_ITEM', 'CUSTOMER', 'INVENTORY', 'REVIEW')
ORDER BY table_name, index_name;

-- 验证全文索引
SELECT index_name, index_type, table_name
FROM user_indexes
WHERE index_type = 'DOMAIN';

-- 验证位图索引
SELECT index_name, index_type, table_name
FROM user_indexes
WHERE table_name IN ('ORDERS', 'CUSTOMER', 'PRODUCT')
  AND index_type LIKE 'BITMAP%';

COMMIT;
/


-- ============================================================
-- 模块执行完成
-- ============================================================

-- ============================================================
-- Oracle 21c 珠宝行业「索引与性能优化」
-- 模块4：高级索引技术（覆盖索引/执行计划/碎片维护/空间索引/IQP）
-- ============================================================

CONNECT jewelry_user/jewelry_pwd;

-- ============================================================
-- 4.1 覆盖索引设计（Index Only Scan）
-- ============================================================

-- 覆盖索引原理：
--   - 查询所需的所有列都在索引中，无需回表访问数据行
--   - 避免昂贵的表访问(by index rowid)操作
--   - Oracle中称为"INDEX FAST FULL SCAN"或"INDEX COMPLETE SCAN"

-- 场景1：订单明细产品销量统计（常见报表查询）
-- 原始查询（需回表）：
EXPLAIN PLAN FOR
SELECT product_id, SUM(quantity) AS total_qty
FROM order_item
GROUP BY product_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 优化：确保复合索引覆盖查询列
-- order_item表已有 idx_order_item_prod_qty(product_id, quantity)
-- 此索引已覆盖上述查询，可实现Index Only Scan

-- 验证覆盖索引效果
EXPLAIN PLAN FOR
SELECT /*+ INDEX(order_item idx_order_item_prod_qty) */ product_id, SUM(quantity)
FROM order_item
GROUP BY product_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 场景2：产品库存查询（库存表覆盖索引）
-- 创建覆盖库存查询的复合索引
CREATE INDEX idx_inventory_cover ON inventory(product_id, quantity, warehouse_name);

-- 覆盖索引查询（无需访问表数据）
EXPLAIN PLAN FOR
SELECT product_id, quantity, warehouse_name
FROM inventory
WHERE product_id = 1;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 场景3：销售报表覆盖索引
-- 创建覆盖常用报表查询的索引
CREATE INDEX idx_order_item_cover ON order_item(product_id, quantity, unit_price);

-- 报表查询（完全走索引）
EXPLAIN PLAN FOR
SELECT product_id,
       SUM(quantity) AS total_qty,
       SUM(quantity * unit_price) AS total_revenue
FROM order_item
GROUP BY product_id;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- ============================================================
-- 4.2 执行计划分析（EXPLAIN PLAN / DBMS_XPLAN）
-- ============================================================

-- 4.2.1 基础执行计划查看
-- 方法1：EXPLAIN PLAN（不实际执行查询）
EXPLAIN PLAN FOR
SELECT p.product_name, p.price_cny, c.category_name
FROM product p
JOIN category c ON p.category_id = c.category_id
WHERE p.material = '铂金' AND p.price_cny > 10000
ORDER BY p.price_cny DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- 方法2：实际执行并获取统计信息（更准确）
SELECT /*+ GATHER_PLAN_STATISTICS */
    p.product_name, p.price_cny, c.category_name
FROM product p
JOIN category c ON p.category_id = c.category_id
WHERE p.material = '铂金' AND p.price_cny > 10000
ORDER BY p.price_cny DESC;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST'));

-- 方法3：SQL Monitor（长运行查询监控）
-- SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALL +MONITOR'));

-- 4.2.2 执行计划关键操作符解读
-- 常见操作符说明：
-- TABLE ACCESS FULL    - 全表扫描（大数据量时性能差）
-- TABLE ACCESS BY INDEX ROWID - 回表查询（通过索引找到行ID再查表）
-- INDEX RANGE SCAN     - 索引范围扫描（等值/范围查询）
-- INDEX UNIQUE SCAN    - 索引唯一扫描（唯一索引等值查询）
-- INDEX FAST FULL SCAN - 索引快速全扫（多块读，适合大索引）
-- INDEX FULL SCAN      - 索引全扫（适合ORDER BY场景）
-- SORT ORDER BY        - 排序操作（可能消耗大量PGA内存）
-- HASH JOIN            - 哈希连接（适合大表连接）
-- NESTED LOOPS         - 嵌套循环连接（适合小结果集）
-- FILTER               - 过滤操作

-- 4.2.3 SQL Trace / 10046事件（深度性能诊断）
-- 启用会话级SQL追踪
ALTER SESSION SET SQL_TRACE = TRUE;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

-- 执行待分析查询
SELECT /*+ INDEX(product idx_product_mat_price) */ *
FROM product
WHERE material = '黄金' AND price_cny BETWEEN 5000 AND 20000;

-- 停止追踪
ALTER SESSION SET SQL_TRACE = FALSE;
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

-- 查找生成的trace文件
SELECT tracefile FROM v$process WHERE addr = (SELECT paddr FROM v$session WHERE sid = (SELECT DISTINCT sid FROM v$mystat));

-- 使用tkprof格式化trace文件
-- tkprof tracefile.trc output.rpt explain=user/password

-- 4.2.4 SQL Plan Baseline（SQL执行计划基线）
-- 捕获当前优的执行计划
BEGIN
    DBMS_SPM.CURSOR_CACHE_TO_SQL_BASELINE(
        SQL_HANDLE => NULL,
        PLAN_FILTER => 'ALL'
    );
END;
/

-- 查看SQL基线
SELECT sql_handle, plan_name, enabled, accepted, fixed
FROM dba_sql_baselines
WHERE creator = USER;

-- ============================================================
-- 4.3 索引碎片分析与维护策略
-- ============================================================

-- 4.3.1 索引碎片检测
-- 方法1：ANALYZE INDEX（需要锁定索引）
ANALYZE INDEX idx_product_category_bt VALIDATE STRUCTURE;

-- 查询碎片统计
SELECT name AS index_name,
       lf_rows AS logical_rows,
       del_lf_rows AS deleted_rows,
       ROUND(del_lf_rows / NULLIF(lf_rows, 0) * 100, 2) AS frag_pct
FROM index_stats
WHERE name IN (
    SELECT index_name FROM user_indexes WHERE table_name = 'PRODUCT'
);

-- 方法2：V$视图监控（无锁，生产环境推荐）
SELECT index_name,
       blevel, leaf_blocks, distinct_keys,
       clustering_factor, avg_leaf_blocks_per_key,
       avg_data_blocks_per_key
FROM user_indexes
WHERE table_name = 'PRODUCT'
ORDER BY index_name;

-- 4.3.2 索引维护策略
-- 碎片率 > 20% 建议重建
-- 碎片率 > 50% 强烈建议重建

-- 在线重建索引（生产环境推荐，不锁表）
ALTER INDEX idx_product_category_bt REBUILD ONLINE;
ALTER INDEX idx_product_material REBUILD ONLINE;
ALTER INDEX idx_product_price REBUILD ONLINE;
ALTER INDEX idx_product_name REBUILD ONLINE;
ALTER INDEX idx_orders_customer REBUILD ONLINE;
ALTER INDEX idx_orders_date REBUILD ONLINE;
ALTER INDEX idx_order_item_order REBUILD ONLINE;
ALTER INDEX idx_order_item_product REBUILD ONLINE;

-- 重建索引统计信息
EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'IDX_PRODUCT_CATEGORY_BT');
EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'IDX_PRODUCT_MATERIAL');
EXEC DBMS_STATS.GATHER_INDEX_STATS(USER, 'IDX_ORDERS_CUSTOMER');

-- 4.3.3 自动索引管理（Oracle 21c新特性）
-- 启用自动索引（Oracle 21c）
BEGIN
    DBMS_AUTO_INDEX.CONFIGURE(
        'AUTO_INDEX_MODE', 'IMPLEMENT'  -- IMPLEMENT:自动创建/删除 | REPORT ONLY:仅报告
    );
    DBMS_AUTO_INDEX.CONFIGURE(
        'AUTO_INDEX_SCHEMA', USER, TRUE  -- 启用当前用户
    );
END;
/

-- 查看自动索引报告
DECLARE
    l_report CLOB;
BEGIN
    l_report := DBMS_AUTO_INDEX.REPORT_LAST_AWR(
        type => 'TEXT',
        section => 'ALL',
        level => 'TYPICAL'
    );
    DBMS_OUTPUT.PUT_LINE(l_report);
END;
/

-- 手动执行索引整理
BEGIN
    DBMS_AUTO_INDEX.CONFIGURE(
        'AUTO_INDEX_DEFAULT_TABLESPACE', 'JEWELRY_TS'
    );
    DBMS_AUTO_INDEX.EXECUTE_AUTO_INDEX(
        owner => USER,
        action => 'REORG'
    );
END;
/

-- ============================================================
-- 4.4 空间索引（地理位置查询）
-- ============================================================

-- 空间索引原理：
--   - 基于Oracle Spatial引擎（MDSYS）
--   - 支持地理位置的最近搜索、范围搜索、距离计算
--   - 适合门店查找、客户分布分析等场景

-- 4.4.1 初始化Spatial组件（如未初始化）
-- 以SYS用户执行（通常已初始化）：
-- @?/md/admin/md.sql

-- 4.4.2 使用SDO_GEOMETRY创建空间索引
-- 客户表已有 latitude/longitude 列，创建空间索引
-- 需要先插入空间元数据

-- 删除旧索引（如果存在）
DROP INDEX idx_customer_location;

-- 插入空间元数据
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
VALUES (
    'CUSTOMER',
    'LOCATION_POINT',
    SDO_DIM_ARRAY(
        SDO_DIM_ELEMENT('LONG', -180, 180, 0.001),
        SDO_DIM_ELEMENT('LAT', -90, 90, 0.001)
    ),
    NULL
);

-- 添加计算列（将经纬度转为SDO_GEOMETRY）
-- 使用虚拟列（Oracle 11g+）
ALTER TABLE customer ADD (
    location_point SDO_GEOMETRY
    GENERATED ALWAYS AS (
        MDSYS.SDO_GEOMETRY(
            2001, NULL,
            MDSYS.SDO_POINT_TYPE(longitude, latitude, NULL),
            NULL, NULL
        )
    ) VIRTUAL
);

-- 创建空间索引
CREATE INDEX idx_customer_spatial ON customer(location_point)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX;

-- 4.4.3 空间查询示例
-- 场景1：查找某坐标附近5公里内的客户
SELECT c.customer_id, c.customer_name, c.city,
       ROUND(
           MDSYS.SDO_DISTANCE(
               c.location_point,
               MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(116.4074, 39.9042, NULL), NULL, NULL),
               0.001
           ) / 1000, 2
       ) AS distance_km
FROM customer c
WHERE MDSYS.SDO_FILTER(
        c.location_point,
        MDSYS.SDO_GEOMETRY(
            2003, NULL, NULL,
            MDSYS.SDO_ELEM_INFO_ARRAY(1, 1, 1),
            MDSYS.SDO_ORDINATE_ARRAY(115.9074, 39.4042, 116.9074, 40.4042)
        ),
        'querytype=window'
    ) = 'TRUE'
    AND MDSYS.SDO_DISTANCE(
            c.location_point,
            MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(116.4074, 39.9042, NULL), NULL, NULL),
            0.001
        ) < 5000;  -- 5公里

-- 场景2：按城市统计客户分布
SELECT city, COUNT(*) AS customer_count
FROM customer
GROUP BY city
ORDER BY customer_count DESC;

-- 场景3：找出距离北京最近的5个城市客户
SELECT c.customer_name, c.city,
       ROUND(
           MDSYS.SDO_DISTANCE(
               c.location_point,
               MDSYS.SDO_GEOMETRY(2001, NULL, MDSYS.SDO_POINT_TYPE(116.4074, 39.9042, NULL), NULL, NULL),
               0.001
           ) / 1000, 2
       ) AS distance_from_beijing_km
FROM customer c
WHERE c.city != '北京'
ORDER BY distance_from_beijing_km
FETCH FIRST 5 ROWS ONLY;

-- ============================================================
-- 4.5 智能查询处理（IQP - Intelligent Query Processing）
-- ============================================================

-- Oracle 21c IQP新特性：

-- 4.5.1 自动分区消除（Partition Pruning）增强
-- 查询自动识别可消除的分区
EXPLAIN PLAN FOR
SELECT * FROM orders_archive
WHERE order_date BETWEEN DATE '2023-01-01' AND DATE '2023-03-31';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 预期：只扫描 p_2023_q1 分区

-- 4.5.2 结果缓存（Result Cache）
-- 查询结果缓存（适合频繁执行的静态查询）
SELECT /*+ RESULT_CACHE */ category_name, COUNT(*)
FROM product
GROUP BY category_name;

-- 查看缓存结果
SELECT * FROM v$result_cache;
SELECT * FROM v$result_cache_objects;

-- 4.5.3 自适应查询优化（Adaptive Query Optimization）
-- Oracle 21c自动优化器行为
-- 自动统计信息收集（已默认启用）
-- 自适应查询反馈（SQL Access Advisor）

-- 4.5.4 内存表（In-Memory）加速分析查询
-- 创建内存表空间（可选，适合大数据量分析）
-- CREATE INMEMORY TABLESPACE im_tbs;

-- 启用表的In-Memory属性
ALTER TABLE product INMEMORY MEMCOMPRESS FOR QUERY;
ALTER TABLE orders INMEMORY MEMCOMPRESS FOR CAPACITY;

-- 查询In-Memory状态
SELECT table_name, inmemory, memcompress, inmemory_priority
FROM user_inmemory_tables;

-- 清空In-Memory缓存
ALTER SYSTEM FLUSH INMEMORY;

-- ============================================================
-- 验证高级索引功能
-- ============================================================

-- 检查所有索引状态
SELECT index_name, index_type, table_name, status, leaf_blocks
FROM user_indexes
WHERE table_name IN ('PRODUCT', 'ORDERS', 'ORDER_ITEM', 'CUSTOMER', 'INVENTORY', 'REVIEW')
ORDER BY table_name, index_name;

-- 检查空间索引
SELECT index_name, index_type, table_name
FROM user_indexes
WHERE index_type = 'DOMAIN' AND table_name = 'CUSTOMER';

-- 检查虚拟列
SELECT column_name, data_type, virtual_column
FROM user_tab_columns
WHERE table_name = 'CUSTOMER' AND virtual_column = 'YES';

-- 检查内存表
SELECT table_name, inmemory, memcompress
FROM user_inmemory_tables;

COMMIT;
/


-- ============================================================
-- 模块执行完成
-- ============================================================

-- ============================================================
-- Oracle 21c 珠宝行业「索引与性能优化」
-- 模块5：分区与分片（Partitioning/Sharding）
-- ============================================================

CONNECT jewelry_user/jewelry_pwd;

-- ============================================================
-- 5.1 RANGE分区（按时间范围分区）
-- ============================================================

-- RANGE分区原理：
--   - 按范围值将数据分布到不同分区
--   - 最适合时间序列数据（订单/日志/交易）
--   - 支持分区消除（Partition Pruning）

-- 5.1.1 创建RANGE分区表
-- 订单历史表（按月分区）
CREATE TABLE orders_monthly (
    order_id        NUMBER,
    customer_id     NUMBER,
    order_date      DATE,
    status          VARCHAR2(20),
    total_amount    NUMBER(12,2),
    shipping_addr   VARCHAR2(500),
    CONSTRAINT pk_orders_monthly PRIMARY KEY (order_id, order_date)
)
PARTITION BY RANGE (order_date) (
    PARTITION p_202401 VALUES LESS THAN (TO_DATE('2024-02-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_202402 VALUES LESS THAN (TO_DATE('2024-03-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_202403 VALUES LESS THAN (TO_DATE('2024-04-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_202404 VALUES LESS THAN (TO_DATE('2024-05-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_202405 VALUES LESS THAN (TO_DATE('2024-06-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_202406 VALUES LESS THAN (TO_DATE('2024-07-01','YYYY-MM-DD')) TABLESPACE jewelry_ts,
    PARTITION p_future VALUES LESS THAN (MAXVALUE) TABLESPACE jewelry_ts
);

-- 插入分区数据
INSERT ALL
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2001, 1, DATE '2024-01-10', '已完成', 58800, '北京')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2002, 2, DATE '2024-01-20', '已完成', 18800, '上海')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2003, 3, DATE '2024-02-05', '已完成', 32500, '广州')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2004, 4, DATE '2024-02-15', '已发货', 15800, '深圳')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2005, 5, DATE '2024-03-01', '已完成', 68000, '北京')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2006, 6, DATE '2024-03-20', '已完成', 28800, '杭州')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2007, 7, DATE '2024-04-10', '待处理', 8800, '成都')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2008, 8, DATE '2024-04-25', '已完成', 12800, '武汉')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2009, 9, DATE '2024-05-05', '已完成', 45000, '南京')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2010, 10, DATE '2024-05-20', '已完成', 22800, '上海')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2011, 11, DATE '2024-06-01', '已发货', 19800, '西安')
INTO orders_monthly (order_id, customer_id, order_date, status, total_amount, shipping_addr)
VALUES (2012, 12, DATE '2024-06-15', '已完成', 38800, '重庆')
SELECT * FROM dual;

-- 分区消除演示
-- 查询2024年3月订单（自动只扫描p_202403分区）
EXPLAIN PLAN FOR
SELECT * FROM orders_monthly
WHERE order_date >= DATE '2024-03-01' AND order_date < DATE '2024-04-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 注意执行计划中的 PARTITION RANGE SINGLE 操作符

-- 查询2024年Q2订单（扫描多个分区）
EXPLAIN PLAN FOR
SELECT * FROM orders_monthly
WHERE order_date >= DATE '2024-04-01' AND order_date < DATE '2024-07-01';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 注意执行计划中的 PARTITION RANGE ITERATOR 操作符

-- 5.1.2 分区维护操作
-- 添加新分区（每月自动创建）
ALTER TABLE orders_monthly
ADD PARTITION p_202407 VALUES LESS THAN (TO_DATE('2024-08-01','YYYY-MM-DD')) TABLESPACE jewelry_ts;

-- 合并分区
ALTER TABLE orders_monthly
MERGE PARTITIONS p_202401, p_202402 INTO PARTITION p_2024h1;

-- 拆分分区
ALTER TABLE orders_monthly
SPLIT PARTITION p_2024h1 AT (TO_DATE('2024-02-01','YYYY-MM-DD'))
INTO (PARTITION p_202401, PARTITION p_202402);

-- 删除分区数据（比DELETE快得多，不产生undo）
ALTER TABLE orders_monthly
DROP PARTITION p_202401;

-- 截断分区（保留分区结构）
ALTER TABLE orders_monthly
TRUNCATE PARTITION p_202402;

-- ============================================================
-- 5.2 LIST分区（按离散值分区）
-- ============================================================

-- LIST分区原理：
--   - 按离散值列表将数据分布到不同分区
--   - 适合按地区/类别/状态等离散值分区

-- 创建按城市分区的客户表
CREATE TABLE customer_by_city (
    customer_id    NUMBER,
    customer_name  VARCHAR2(100),
    city           VARCHAR2(50),
    phone          VARCHAR2(20),
    member_level   VARCHAR2(20),
    total_spent    NUMBER(12,2)
)
PARTITION BY LIST (city) (
    PARTITION p_bj VALUES ('北京') TABLESPACE jewelry_ts,
    PARTITION p_sh VALUES ('上海') TABLESPACE jewelry_ts,
    PARTITION p_gz VALUES ('广州') TABLESPACE jewelry_ts,
    PARTITION p_sz VALUES ('深圳') TABLESPACE jewelry_ts,
    PARTITION p_other VALUES (DEFAULT) TABLESPACE jewelry_ts
);

-- 插入数据
INSERT ALL
INTO customer_by_city VALUES (1, '张伟', '北京', '13800138001', '钻石', 158000)
INTO customer_by_city VALUES (2, '王芳', '上海', '13800138002', '金卡', 85000)
INTO customer_by_city VALUES (3, '李娜', '广州', '13800138003', '金卡', 72000)
INTO customer_by_city VALUES (4, '刘洋', '深圳', '13800138004', '银卡', 35000)
INTO customer_by_city VALUES (5, '陈明', '北京', '13800138005', '钻石', 198000)
INTO customer_by_city VALUES (6, '赵敏', '杭州', '13800138006', '金卡', 68000)
INTO customer_by_city VALUES (7, '孙磊', '成都', '13800138007', '银卡', 28000)
INTO customer_by_city VALUES (8, '周静', '武汉', '13800138008', '普通', 8500)
INTO customer_by_city VALUES (9, '吴强', '南京', '13800138009', '金卡', 95000)
INTO customer_by_city VALUES (10, '郑华', '上海', '13800138010', '钻石', 225000)
SELECT * FROM dual;

-- 分区消除：查询北京客户（只扫描p_bj分区）
EXPLAIN PLAN FOR
SELECT * FROM customer_by_city WHERE city = '北京';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

-- ============================================================
-- 5.3 HASH分区（均匀分布数据）
-- ============================================================

-- HASH分区原理：
--   - 通过哈希函数均匀分布数据到指定分区
--   - 适合需要均匀分布大数据量的场景
--   - 用户无法预测数据分布在哪个分区

-- 创建哈希分区的订单表（8个分区均匀分布）
CREATE TABLE orders_hash (
    order_id        NUMBER,
    customer_id     NUMBER,
    order_date      DATE,
    status          VARCHAR2(20),
    total_amount    NUMBER(12,2)
)
PARTITION BY HASH (order_id)
PARTITIONS 8 STORE IN (jewelry_ts);

-- 插入测试数据
INSERT ALL
INTO orders_hash VALUES (3001, 1, DATE '2024-01-15', '已完成', 58800)
INTO orders_hash VALUES (3002, 2, DATE '2024-01-18', '已完成', 18800)
INTO orders_hash VALUES (3003, 3, DATE '2024-02-01', '已完成', 32500)
INTO orders_hash VALUES (3004, 4, DATE '2024-02-10', '已发货', 15800)
INTO orders_hash VALUES (3005, 5, DATE '2024-02-14', '已完成', 68000)
INTO orders_hash VALUES (3006, 6, DATE '2024-02-20', '已完成', 28800)
INTO orders_hash VALUES (3007, 7, DATE '2024-03-01', '待处理', 8800)
INTO orders_hash VALUES (3008, 8, DATE '2024-03-05', '已确认', 12800)
INTO orders_hash VALUES (3009, 9, DATE '2024-03-10', '已完成', 45000)
INTO orders_hash VALUES (3010, 10, DATE '2024-03-15', '已完成', 22800)
SELECT * FROM dual;

-- 查看各分区数据分布
SELECT partition_name, tablespace_name, num_rows
FROM user_tab_partitions
WHERE table_name = 'ORDERS_HASH'
ORDER BY partition_name;

-- ============================================================
-- 5.4 分区索引策略
-- ============================================================

-- 5.4.1 局部前缀分区索引（Local Prefix Index）
-- 索引分区与表分区一致，适合分区表常见操作
CREATE INDEX idx_orders_monthly_date ON orders_monthly(order_date)
LOCAL;

-- 5.4.2 全局索引（Global Index）
-- 索引独立于表分区，适合跨分区查询
CREATE INDEX idx_orders_hash_cust ON orders_hash(customer_id);

-- 5.4.3 分区索引维护
-- 重建特定分区索引（不影响其他分区）
ALTER INDEX idx_orders_monthly_date
REBUILD PARTITION p_202403;

-- 使索引分区不可用（维护时提高性能）
ALTER INDEX idx_orders_monthly_date
UNUSABLE PARTITION p_202401;

-- 修复不可用索引分区
ALTER INDEX idx_orders_monthly_date
REBUILD UNUSABLE PARTITIONS;

-- ============================================================
-- 5.5 分区切换（Partition Exchange）
-- ============================================================

-- 分区切换原理：
--   - 将分区数据与独立表之间快速交换（元数据操作，毫秒级）
--   - 适合ETL数据加载、历史数据归档

-- 创建目标表
CREATE TABLE orders_archive_202401 (
    order_id        NUMBER,
    customer_id     NUMBER,
    order_date      DATE,
    status          VARCHAR2(20),
    total_amount    NUMBER(12,2),
    shipping_addr   VARCHAR2(500)
);

-- 交换分区（将p_202401分区数据移到独立表）
-- 注意：目标表结构必须与分区表完全一致
ALTER TABLE orders_monthly
EXCHANGE PARTITION p_202401
WITH TABLE orders_archive_202401
WITHOUT VALIDATION;  -- 跳过数据验证提高速度

-- 验证交换结果
SELECT COUNT(*) FROM orders_monthly WHERE order_date < DATE '2024-02-01';
SELECT COUNT(*) FROM orders_archive_202401;

-- 交换回来
ALTER TABLE orders_monthly
EXCHANGE PARTITION p_202401
WITH TABLE orders_archive_202401
WITHOUT VALIDATION;

-- ============================================================
-- 5.6 分片架构设计（Sharding）
-- ============================================================

-- 分片原理：
--   - 将大数据集水平拆分到多个数据库实例
--   - Oracle 21c支持透明数据网关（TDG）实现应用透明分片

-- 5.6.1 应用层分片策略示例
-- 按customer_id哈希分片到不同数据库实例

-- 分片路由函数（应用层实现）
CREATE OR REPLACE FUNCTION fn_get_shard_id(p_customer_id NUMBER)
RETURN NUMBER DETERMINISTIC IS
BEGIN
    RETURN MOD(p_customer_id, 4);  -- 4个分片
END;
/

-- 分片键选择最佳实践：
-- 1. 选择高基数列（如customer_id）
-- 2. 避免选择低基数列（如status）
-- 3. 确保分片键在查询中常用（减少跨分片查询）

-- 5.6.2 跨分片查询优化
-- 使用DBMS_PARALLEL_EXECUTE实现并行跨分区处理
DECLARE
    l_task_id NUMBER;
BEGIN
    DBMS_PARALLEL_EXECUTE.CREATE_TASK('JEWELRY_ANALYSIS');
    DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_NUMBER_COL(
        task_name     => 'JEWELRY_ANALYSIS',
        table_owner   => USER,
        table_name    => 'orders_monthly',
        column_name   => 'ORDER_ID',
        chunk_size    => 100
    );

    DBMS_PARALLEL_EXECUTE.RUN_TASK(
        task_name  => 'JEWELRY_ANALYSIS',
        sql_stmt   => 'UPDATE orders_monthly SET status = ''已完成'' WHERE order_id BETWEEN :start_id AND :end_id',
        language   => 'PLSQL',
        parallel   => TRUE
    );

    DBMS_PARALLEL_EXECUTE.DROP_TASK('JEWELRY_ANALYSIS');
END;
/

-- ============================================================
-- 5.7 分区性能基准测试
-- ============================================================

-- 创建测试表（分区 vs 非分区对比）
-- 非分区表
CREATE TABLE sales_no_partition AS
SELECT * FROM orders_monthly;

-- 分区表
CREATE TABLE sales_partitioned
PARTITION BY RANGE (order_date) (
    PARTITION p_before_2024 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')),
    PARTITION p_2024 VALUES LESS THAN (MAXVALUE)
) AS
SELECT * FROM orders_monthly;

-- 在两张表上创建相同索引
CREATE INDEX idx_sales_no_idx ON sales_no_partition(order_date);
CREATE INDEX idx_sales_part_idx ON sales_partitioned(order_date) LOCAL;

-- 收集统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SALES_NO_PARTITION');
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SALES_PARTITIONED');

-- 性能对比测试
-- 场景：查询特定月份数据（分区消除优势）
SET TIMING ON
SET AUTOTRACE ON EXPLAIN STATISTICS

-- 非分区表查询
SELECT COUNT(*), SUM(total_amount)
FROM sales_no_partition
WHERE order_date BETWEEN DATE '2024-03-01' AND DATE '2024-03-31';

-- 分区表查询（分区消除）
SELECT COUNT(*), SUM(total_amount)
FROM sales_partitioned
WHERE order_date BETWEEN DATE '2024-03-01' AND DATE '2024-03-31';

SET AUTOTRACE OFF
SET TIMING OFF

-- ============================================================
-- 验证分区结果
-- ============================================================

-- 查看所有分区表
SELECT table_name, partitioning_type, partition_count
FROM user_part_tables
WHERE table_name IN ('ORDERS_MONTHLY', 'CUSTOMER_BY_CITY', 'ORDERS_HASH', 'SALES_PARTITIONED');

-- 查看分区详细信息
SELECT table_name, partition_name, high_value, num_rows, blocks
FROM user_tab_partitions
WHERE table_name IN ('ORDERS_MONTHLY', 'CUSTOMER_BY_CITY', 'ORDERS_HASH')
ORDER BY table_name, partition_name;

-- 查看分区索引
SELECT index_name, table_name, partitioning_type
FROM user_part_indexes
WHERE table_name IN ('ORDERS_MONTHLY', 'SALES_PARTITIONED');

COMMIT;
/


-- ============================================================
-- 模块执行完成
-- ============================================================