ToB企服应用市场:ToB评测及商务社交产业平台

标题: [20250103]使用递归实现distinct功能.txt [打印本页]

作者: 饭宝 时间: 2025-1-5 23:28
标题: [20250103]使用递归实现distinct功能.txt
[20250103]使用递归实现distinct功能.txt

--//生产系统遇到实际上许多条类似语句，顺便拿此中几个出来,至心不知道开发如何学计算机的。

1.问题提出：
SYS@127.0.0.1:9106/xtdb/xtdb2> @ sql_id c29undaquszs6
-- SQL_ID = c29undaquszs6 come from shared pool
select distinct ritem from routine2;

SYS@127.0.0.1:9106/xtdb/xtdb2> @ sql_id fhz2xwcnx2uyh
-- SQL_ID = fhz2xwcnx2uyh come from shared pool
select distinct rtype from routine2;

SYS@127.0.0.1:9106/xtdb/xtdb2> @ seg2 nis5.routine2 ''
SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------ ------------ ------------------- ------ ------ ------
1088 NIS5 ROUTINE2     TABLE        NIS                 139264     21 843528

SYS@127.0.0.1:9106/xtdb/xtdb2> @ desczz nis5.routine2 ritem,rtype
eXtended describe of nis5.routine2

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT  ,USE "" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED       Col# Column Name Null? Type         NUM_DISTINCT        Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value            High_value
----- ---------- ----------- ------------------- ---- ----------- ----- ------------ ------------ -------------- ---------- --------- ----------- -------------------- ----------------------
NIS5 ROUTINE2       4861544 2025-01-01 22:01:25    7 RTYPE             VARCHAR2(40)           36   .00000010285          0 FREQUENCY          36 13种呼吸道病原体检测真菌三项病原体核酸检测
                     4861544 2025-01-01 22:01:25    8 RITEM             VARCHAR2(40)           32   .00000010285          0 FREQUENCY          32 13种呼吸道病原体检测中性粒细胞

--//表ROUTINE2 1088M，而RTYPE，RITEM的差别值分布为36，32。要优化上面的sql语句分布建立对应索引简直就是资源浪费。
--//而且类似的sql语句另有一大堆,不想贴出来了，这样的项目简直就是豆腐渣中的豆腐渣工程。
--//上该公司网站，我至心不知道这样的产品竟然到处在买，简直就是垃圾，站在优化的角度又是一个豆腐渣工程。

--//要优化它，只能建立索引，还好NUM_NULLS＝0,要给字段加not null约束，然后建立相应索引。
--//执行计划可以猜测出来走快速全索引扫描(需要另外排序)，或者走全索引扫描(不需要排序).
--//我在想实际上每个值仅仅返回1行，是否可以通过递归实现类似功能，这样可以减少逻辑读，规避快速全索引扫描或者全索引扫描带
--//来的逻辑读，这样表索引应该也不小，即使选择压缩模式。
--//通过例子验证我的想法。

2.情况：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

create table t as select * from all_objects;
create index i_t_owner on t(owner) COMPRESS 1;
--//alter table t modify owner not null;这步不需要。
--//分析略。

3.测试：

SCOTT@book> @ sl all
alter session set statistics_level = all;
Session altered.

SCOTT@book> select distinct owner from t;
OWNER
------------------------------
OWBSYS_AUDIT
MDSYS
CTXSYS
FLOWS_FILES
HR
OLAPSYS
OUTLN
OWBSYS
PUBLIC
APEX_030200
EXFSYS
ORACLE_OCM
SCOTT
SYSTEM
DBSNMP
OE
ORDPLUGINS
ORDSYS
PM
SH
SYSMAN
APPQOSSYS
BI
IX
ORDDATA
XDB
SI_INFORMTN_SCHEMA
SYS
WMSYS
29 rows selected.

SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g3ywa5u5raj7d, child number 0
-------------------------------------
select distinct owner from t
Plan hash value: 4043955095
--------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation             | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | OMem | 1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |       |    40 (100)|          |     29 |00:00:00.04 |     138 |       |       |          |
|   1 | HASH UNIQUE          |           |      1 |     29 |   174 |    40   (8)| 00:00:01 |     29 |00:00:00.04 |     138 | 5686K| 1858K| 1246K (0)|
|   2 |   INDEX FAST FULL SCAN| I_T_OWNER |      1 | 84801 |   496K|    37   (0)| 00:00:01 | 84801 |00:00:00.02 |     138 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
--//oracle选择快速全扫描,然后hash unique排序，逻辑读138.

4.使用递归方式：
WITH t_rec (owner) AS (
SELECT MIN (owner) FROM t
UNION ALL
SELECT (SELECT MIN (owner) FROM t WHERE owner > c.owner) FROM t_rec c WHERE c.owner IS NOT NULL)
SELECT * FROM t_rec;

OWNER
------------------------------
APEX_030200
APPQOSSYS
BI
CTXSYS
DBSNMP
EXFSYS
FLOWS_FILES
HR
IX
MDSYS
OE
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
OWBSYS
OWBSYS_AUDIT
PM
PUBLIC
SCOTT
SH
SI_INFORMTN_SCHEMA
SYS
SYSMAN
SYSTEM
WMSYS
XDB

30 rows selected.
--//留意这样查询返回一个空行.

SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 46c0waacwrkh6, child number 0
-------------------------------------
WITH t_rec (owner) AS ( SELECT MIN (owner) FROM t UNION ALL SELECT
(SELECT MIN (owner) FROM t WHERE owner > c.owner) FROM t_rec c WHERE
c.owner IS NOT NULL) SELECT * FROM t_rec
Plan hash value: 3372523748
-------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                 | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                          |           |      1 |        |       |     4 (100)|          |     30 |00:00:00.01 |      35 |
|   1 | VIEW                                     |           |      1 |      2 |    34 |     4   (0)| 00:00:01 |     30 |00:00:00.01 |      35 |
|   2 |   UNION ALL (RECURSIVE WITH) BREADTH FIRST|           |      1 |        |       |            |          |     30 |00:00:00.01 |      35 |
|   3 |    SORT AGGREGATE                         |           |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |       2 |
|   4 |     INDEX FULL SCAN (MIN/MAX)             | I_T_OWNER |      1 |      1 |     6 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|   5 |    SORT AGGREGATE                         |           |     29 |      1 |     6 |            |          |     29 |00:00:00.01 |      33 |
|   6 |     FIRST ROW                             |           |     29 |      1 |     6 |     2   (0)| 00:00:01 |     28 |00:00:00.01 |      33 |
|* 7 |      INDEX RANGE SCAN (MIN/MAX)           | I_T_OWNER |     29 |      1 |     6 |     2   (0)| 00:00:01 |     28 |00:00:00.01 |      33 |
|   8 |    RECURSIVE WITH PUMP                    |           |     30 |        |       |            |          |     29 |00:00:00.01 |       0 |
-------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$1 / T_REC@SEL$4
   2 - SET$1
   3 - SEL$1
   4 - SEL$1 / T@SEL$1
   5 - SEL$3
   7 - SEL$3 / T@SEL$3
   8 - SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   7 - access("OWNER">:B1)
--//充实使用取最大最小值,减少了逻辑读.我的表不大,逻辑读也就是35.如果返回记录许多的话效果也许没有这么好..
--//排除null应该修改如下:

WITH t_rec (ownerx) AS (
SELECT MIN (owner) ownerx FROM t
UNION ALL
SELECT (SELECT MIN (owner) FROM t WHERE owner > c.ownerx) ownerx FROM t_rec c WHERE c.ownerx IS NOT NULL)
SELECT * FROM t_rec where ownerx is not null;

--//如果存在空值呢?

SCOTT@book> alter table t modify owner null;
Table altered.

SCOTT@book> update t set owner=null where object_id=2;
1 row updated.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> select distinct owner from t;
...
--//返回30行.

SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g3ywa5u5raj7d, child number 0
-------------------------------------
select distinct owner from t
Plan hash value: 1793979440
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |       |   340 (100)|          |     30 |00:00:00.04 |    1215 |       |       |          |
|   1 | HASH UNIQUE       |      |      1 |     29 |   174 |   340   (1)| 00:00:05 |     30 |00:00:00.04 |    1215 | 5686K| 1858K| 1246K (0)|
|   2 |   TABLE ACCESS FULL| T    |      1 | 84801 |   496K|   338   (1)| 00:00:05 | 84801 |00:00:00.02 |    1215 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1
--//owner索引不再有效,因为存在null值.

WITH t_rec (ownerx) AS (
SELECT MIN (owner) ownerx FROM t
UNION ALL
SELECT (SELECT MIN (owner) FROM t WHERE owner > c.ownerx) ownerx FROM t_rec c WHERE c.ownerx IS NOT NULL)
SELECT * FROM t_rec where ownerx is not null;
--//执行计划同上.返回29条.

WITH t_rec (ownerx) AS (
SELECT MIN (owner) ownerx FROM t
UNION ALL
SELECT (SELECT MIN (owner) FROM t WHERE owner > c.ownerx) ownerx FROM t_rec c WHERE c.ownerx IS NOT NULL)
SELECT * FROM t_rec where ownerx is not null
union all
select owner from t where owner is null and rownum=1;

--//如果全表扫描很快满足owner is null and rownum=1的情况,逻辑读不会很高,不行只能建立函数索引,解决这个问题.

Plan hash value: 2493656999
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |           |      1 |        |       |   342 (100)|          |     30 |00:00:00.01 |      38 |
|   1 | UNION-ALL                                 |           |      1 |        |       |            |          |     30 |00:00:00.01 |      38 |
|* 2 |   VIEW                                     |           |      1 |      2 |    34 |     4   (0)| 00:00:01 |     29 |00:00:00.01 |      35 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|           |      1 |        |       |            |          |     30 |00:00:00.01 |      35 |
|   4 |     SORT AGGREGATE                         |           |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |       2 |
|   5 |      INDEX FULL SCAN (MIN/MAX)             | I_T_OWNER |      1 |      1 |     6 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|   6 |     SORT AGGREGATE                         |           |     29 |      1 |     6 |            |          |     29 |00:00:00.01 |      33 |
|   7 |      FIRST ROW                             |           |     29 |      1 |     6 |     2   (0)| 00:00:01 |     28 |00:00:00.01 |      33 |
|* 8 |       INDEX RANGE SCAN (MIN/MAX)           | I_T_OWNER |     29 |      1 |     6 |     2   (0)| 00:00:01 |     28 |00:00:00.01 |      33 |
|   9 |     RECURSIVE WITH PUMP                    |           |     30 |        |       |            |          |     29 |00:00:00.01 |       0 |
|* 10 |   COUNT STOPKEY                            |           |      1 |        |       |            |          |      1 |00:00:00.01 |       3 |
|* 11 |    TABLE ACCESS FULL                       | T         |      1 |      1 |     6 |   338   (1)| 00:00:05 |      1 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------------------------------------

--//最差扫描全部.
Plan hash value: 2493656999
--------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name      | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |           |      1 |        |       |   342 (100)|          |     30 |00:00:00.01 |    1240 |
|   1 | UNION-ALL                                 |           |      1 |        |       |            |          |     30 |00:00:00.01 |    1240 |
|* 2 |   VIEW                                     |           |      1 |      2 |    34 |     4   (0)| 00:00:01 |     29 |00:00:00.01 |      35 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|           |      1 |        |       |            |          |     30 |00:00:00.01 |      35 |
|   4 |     SORT AGGREGATE                         |           |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |       2 |
|   5 |      INDEX FULL SCAN (MIN/MAX)             | I_T_OWNER |      1 |      1 |     6 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|   6 |     SORT AGGREGATE                         |           |     29 |      1 |     6 |            |          |     29 |00:00:00.01 |      33 |
|   7 |      FIRST ROW                             |           |     29 |      1 |     6 |     2   (0)| 00:00:01 |     28 |00:00:00.01 |      33 |
|* 8 |       INDEX RANGE SCAN (MIN/MAX)           | I_T_OWNER |     29 |      1 |     6 |     2   (0)| 00:00:01 |     28 |00:00:00.01 |      33 |
|   9 |     RECURSIVE WITH PUMP                    |           |     30 |        |       |            |          |     29 |00:00:00.01 |       0 |
|* 10 |   COUNT STOPKEY                            |           |      1 |        |       |            |          |      1 |00:00:00.01 |    1205 |
|* 11 |    TABLE ACCESS FULL                       | T         |      1 |      1 |     6 |   338   (1)| 00:00:05 |      1 |00:00:00.01 |    1205 |
--------------------------------------------------------------------------------------------------------------------------------------------------

--//删除原来索引，建立函数索引再测试
SCOTT@book> create index if_t_owner on t(owner,0) COMPRESS 2;
Index created.

SCOTT@book> select distinct owner from t;
...
--//返回30行.

SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID g3ywa5u5raj7d, child number 0
-------------------------------------
select distinct owner from t
Plan hash value: 3764002236
------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation             | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers | Reads | OMem | 1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |       |    46 (100)|          |     30 |00:00:00.04 |     161 |    154 |       |       |          |
|   1 | HASH UNIQUE          |            |      1 |     29 |   174 |    46   (7)| 00:00:01 |     30 |00:00:00.04 |     161 |    154 | 5686K| 1858K| 1243K (0)|
|   2 |   INDEX FAST FULL SCAN| IF_T_OWNER |      1 | 84801 |   496K|    43   (0)| 00:00:01 | 84801 |00:00:00.02 |     161 |    154 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T@SEL$1

WITH t_rec (ownerx) AS (
SELECT MIN (owner) ownerx FROM t
UNION ALL
SELECT (SELECT MIN (owner) FROM t WHERE owner > c.ownerx) ownerx FROM t_rec c WHERE c.ownerx IS NOT NULL)
SELECT * FROM t_rec where ownerx is not null
union all
select owner from t where owner is null and rownum=1;

SCOTT@book> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID cu7sbwm03bu92, child number 0
-------------------------------------
WITH t_rec (ownerx) AS ( SELECT MIN (owner) ownerx FROM t UNION ALL
SELECT (SELECT MIN (owner) FROM t WHERE owner > c.ownerx) ownerx FROM
t_rec c WHERE c.ownerx IS NOT NULL) SELECT * FROM t_rec where ownerx is
not null union all select owner from t where owner is null and rownum=1
Plan hash value: 995977602
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation                                  | Name       | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time   | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |            |      1 |        |       |     5 (100)|          |     30 |00:00:00.01 |      37 |
|   1 | UNION-ALL                                 |            |      1 |        |       |            |          |     30 |00:00:00.01 |      37 |
|* 2 |   VIEW                                     |            |      1 |      2 |    34 |     4   (0)| 00:00:01 |     29 |00:00:00.01 |      35 |
|   3 |    UNION ALL (RECURSIVE WITH) BREADTH FIRST|            |      1 |        |       |            |          |     30 |00:00:00.01 |      35 |
|   4 |     SORT AGGREGATE                         |            |      1 |      1 |     6 |            |          |      1 |00:00:00.01 |       2 |
|   5 |      INDEX FULL SCAN (MIN/MAX)             | IF_T_OWNER |      1 |      1 |     6 |     2   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
|   6 |     SORT AGGREGATE                         |            |     29 |      1 |     6 |            |          |     29 |00:00:00.01 |      33 |
|   7 |      FIRST ROW                             |            |     29 |      1 |     6 |     2   (0)| 00:00:01 |     28 |00:00:00.01 |      33 |
|* 8 |       INDEX RANGE SCAN (MIN/MAX)           | IF_T_OWNER |     29 |      1 |     6 |     2   (0)| 00:00:01 |     28 |00:00:00.01 |      33 |
|   9 |     RECURSIVE WITH PUMP                    |            |     30 |        |       |            |          |     29 |00:00:00.01 |       0 |
|* 10 |   COUNT STOPKEY                            |            |      1 |        |       |            |          |      1 |00:00:00.01 |       2 |
|* 11 |    INDEX RANGE SCAN                        | IF_T_OWNER |      1 |      1 |     6 |     1   (0)| 00:00:01 |      1 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SET$2
   2 - SET$1 / T_REC@SEL$1
   3 - SET$1
   4 - SEL$2
   5 - SEL$2 / T@SEL$2
   6 - SEL$4
   8 - SEL$4 / T@SEL$4
   9 - SEL$3
10 - SEL$5
11 - SEL$5 / T@SEL$5
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OWNERX" IS NOT NULL)
   8 - access("OWNER">:B1 AND "OWNER" IS NOT NULL)
10 - filter(ROWNUM=1)
11 - access("OWNER" IS NULL)

3.思索：
--//另有怎么方法可以解决这个问题，物化视图可以吗？感觉这样无法优化，写成group by也许可以。
select owner from t group by owner;
--//有机会测试看看。
--//另外以上代码存在问题，也许数据模型不应该允许生产系统执行这类sql语句。应该有类似应用的数据字典生存这些信息。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。

欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/)