1、sql
SELECT
id,
NAME,
time_liness AS xxsx,
effect_level AS jb,
effect_level_id AS jbdm,
publish_org AS fbjg,
publish_date AS fbrq,
impl_date AS ssrq,
details
FROM (
SELECT
l1.id,
l1.NAME,
l1.time_liness,
l1.effect_level,
l1.effect_level_id,
l1.publish_org,
l1.publish_date,
l1.impl_date,
l1.details,
l1.impl_date AS sort_order -- 用于外层排序
FROM test_ywxt.laws l1
WHERE (l1.xzqhdm LIKE '37%' OR l1.xzqhdm = '000000000000')
AND l1.name LIKE CONCAT('%', '宪法', '%')

UNION ALL

SELECT
l2.id,
l2.NAME,
l2.time_liness,
l2.effect_level,
l2.effect_level_id,
l2.publish_org,
l2.publish_date,
l2.impl_date,
l2.details,
l2.impl_date AS sort_order
FROM test_ywxt.laws l2
INNER JOIN test_ywxt.laws_detail d ON d.law_id = l2.id
WHERE (l2.xzqhdm LIKE '37%' OR l2.xzqhdm = '000000000000')
AND d.node_detail LIKE CONCAT('%', '宪法', '%')
) AS combined
ORDER BY sort_order DESC
LIMIT 1, 10;
2、执行计划
1 #NSET2: [25, 10, 422]
2 #PRJT2: [25, 10, 422]; exp_num(9), is_atom(FALSE)
3 #PRJT2: [25, 10, 422]; exp_num(9), is_atom(FALSE)
4 #TOPN2: [25, 10, 422]; top_num(10), top_off(1)
5 #UNION ALL(MERGE): [25, 1557, 422]; merge_type(D), n_merge_keys(1)
6 #PRJT2: [2, 357, 422]; exp_num(10), is_atom(FALSE)
7 #UNION FOR OR(MERGE): [2, 357, 422]; merge_type(A), n_merge_keys(1), n_dist_keys(0)
8 #SLCT2: [1, 300, 422]; exp11 > 0
9 #BLKUP2: [1, 300, 422]; idx_laws_xzqhdm_impl(l1)
10 #SSEK2: [1, 300, 422]; scan_type(ASC), idx_laws_xzqhdm_impl(LAWS as l1), scan_range[('000000000000',min),('000000000000',max)), is_global(0)
11 #SLCT2: [1, 57, 422]; (l1.XZQHDM >= '37' AND l1.XZQHDM < '38' AND exp11 > 0 AND exp11)
12 #BLKUP2: [1, 57, 422]; idx_laws_impldate_wxd(l1)
13 #SSCN: [1, 57, 422]; idx_laws_impldate_wxd(LAWS as l1); btr_scan(1); is_global(0)
14 #PRJT2: [22, 1200, 530]; exp_num(10), is_atom(FALSE)
15 #UNION FOR OR(MERGE): [22, 1200, 530]; merge_type(A), n_merge_keys(1), n_dist_keys(0)
16 #SLCT2: [10, 600, 530]; exp_cast(d.NODE_DETAIL) LIKE '%宪法%'
17 #NEST LOOP INDEX JOIN2: [10, 600, 530]
18 #BLKUP2: [1, 300, 422]; idx_laws_xzqhdm_impl(l2)
19 #SSEK2: [1, 300, 422]; scan_type(ASC), idx_laws_xzqhdm_impl(LAWS as l2), scan_range[('000000000000',min),('000000000000',max)), is_global(0)
20 #BLKUP2: [1, 2, 48]; IDX_LAW_ID(d)
21 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LAW_ID(LAWS_DETAIL as d), scan_range[l2.ID,l2.ID], is_global(0)
22 #SLCT2: [10, 600, 530]; exp_cast(d.NODE_DETAIL) LIKE '%宪法%'
23 #NEST LOOP INDEX JOIN2: [10, 600, 530]
24 #SLCT2: [1, 300, 422]; (l2.XZQHDM >= '37' AND l2.XZQHDM < '38' AND exp11)
25 #BLKUP2: [1, 300, 422]; idx_laws_impldate_wxd(l2)
26 #SSCN: [1, 300, 422]; idx_laws_impldate_wxd(LAWS as l2); btr_scan(1); is_global(0)
27 #BLKUP2: [1, 2, 48]; IDX_LAW_ID(d)
28 #SSEK2: [1, 2, 48]; scan_type(ASC), IDX_LAW_ID(LAWS_DETAIL as d), scan_range[l2.ID,l2.ID], is_global(0)

3、已经添加索引
CREATE INDEX idx_laws_xzqhdm_impl_wxd ON test_ywxt.laws(xzqhdm, impl_date DESC);

CREATE INDEX idx_laws_impldate_wxd ON test_ywxt.laws (impl_date DESC);

4、测试添加hint，未发现有明显效果
主要集中再排序ORDER BY sort_order DESC这里，使用hint测试，未发现有明显效果
添加的hint测试如下：
添加hint or查询优化参数（默认0）
/+ OPTIMIZER_OR_NBEXP(1)/
添加hint or和排序的优化参数（默认69）
/+ TOP_ORDER_OPT_FLAG(127)/

5、依据执行计划 看看还有优化空间吗，不添加排序ORDER BY sort_order DESC（排序字段所在表800万行数据）执行1s内完成，添加排序后6s执行完，或者怎么改写下sql，我后续测试下