[20250103]distinct的函数实现.txt
--//前天使用递归代替类似select distinct rtype from routine2;
--//今天尝试使用函数是否可以实现,首先提一下，写pl/sql代码不是我擅长的工作，我的工作不需要写代码。
--//主要目的仅仅为了学习。
1.环境：
SCOTT@book01p> @ ver2
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 21.0.0.0.0
BANNER : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
BANNER_FULL : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
BANNER_LEGACY : Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
2.建立测试环境：
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;这步不需要。
--//分析略。
CREATE OR REPLACE FUNCTION distinct2varlist
(
p_table_name IN VARCHAR2
,p_column_name IN VARCHAR2
)
RETURN vartabletype
PIPELINED
AS
v_str VARCHAR2 (100);
BEGIN
EXECUTE IMMEDIATE
'select min(' || p_column_name || ')' || ' from ' || p_table_name
INTO v_str;
LOOP
EXIT WHEN (v_str IS NULL);
PIPE ROW (v_str);
EXECUTE IMMEDIATE 'select min('||p_column_name||')'||' from '||p_table_name||' where '||p_column_name||'> :j' into v_str using v_str;
END LOOP;
RETURN;
END;
/
--//传入2个参数,表以及字段。
--//小插曲，调试函数遇到1个问题，PIPE ROW (v_str);一定要加括号，不然过不去。
SCOTT@book01p> show error
Errors for FUNCTION DISTINCT2VARLIST:
LINE/COL ERROR
-------- ----------------------------------------------------------------------------------------------------
17/16 PLS-00103: Encountered the symbol "V_STR" when expecting one of the following: (
20/4 PLS-00103: Encountered the symbol "RETURN" when expecting one of the following:
end not pragma final instantiable persistable order
overriding static member constructor map
3.测试：
SCOTT@book01p> @ sl all
alter session set statistics_level = all;
Session altered.
SCOTT@book01p> select * from distinct2varlist('T','owner') ;
COLUMN_VALUE
---------------
APPQOSSYS
AUDSYS
BBB
CTXSYS
DBSFWUSER
DBSNMP
DVF
DVSYS
GSMADMIN_INTERNAL
LBACSYS
MDSYS
OJVMSYS
OLAPSYS
ORACLE_OCM
ORDDATA
ORDPLUGINS
ORDSYS
OUTLN
PUBLIC
REMOTE_SCHEDULER_AGENT
SCOTT
SI_INFORMTN_SCHEMA
SYS
SYSTEM
WMSYS
XDB
26 rows selected.
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9p7a9u1b3xp3r, child number 1
-------------------------------------
select * from distinct2varlist('T','owner')
Plan hash value: 2418813107
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 29 (100)| | 26 |00:00:00.01 | 54 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISTINCT2VARLIST | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 26 |00:00:00.01 | 54 |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1 / "KOKBF$0"@"SEL$2"
--//54个逻辑读，前面使用递归35个逻辑读。
--//测试返回数字的情况,按照上面的脚本修改如下：
CREATE OR REPLACE TYPE numtabletype AS TABLE OF NUMBER
/
CREATE OR REPLACE FUNCTION distinct2numlist
(
p_table_name IN VARCHAR2
,p_column_name IN VARCHAR2
)
RETURN numtabletype
PIPELINED
AS
v_str NUMBER;
BEGIN
EXECUTE IMMEDIATE
'select min(' || p_column_name || ')' || ' from ' || p_table_name
INTO v_str;
LOOP
EXIT WHEN (v_str IS NULL);
PIPE ROW (v_str);
EXECUTE IMMEDIATE
'select min('
|| p_column_name
|| ')'
|| ' from '
|| p_table_name
|| ' where '
|| p_column_name
|| '> :j'
INTO v_str
USING v_str;
END LOOP;
RETURN;
END;
/
SCOTT@book01p> select * from distinct2numlist('emp','deptno') ;
COLUMN_VALUE
------------
10
20
30
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7ysn70g61z2pu, child number 0
-------------------------------------
select * from distinct2numlist('emp','deptno')
Plan hash value: 1096181357
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 29 (100)| | 3 |00:00:00.01 | 24 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISTINCT2NUMLIST | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 3 |00:00:00.01 | 24 |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1 / "KOKBF$0"@"SEL$2"
--//没有建立索引，可以发现执行效率不高,这是第2次执行测试的逻辑读24.。
--//建立索引后，重复测试：
SCOTT@book01p> create index i_emp_deptno on emp(deptno);
Index created.
SCOTT@book01p> set feed on
SCOTT@book01p> select * from distinct2numlist('emp','deptno') ;
COLUMN_VALUE
------------
10
20
30
3 rows selected.
SCOTT@book01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7ysn70g61z2pu, child number 0
-------------------------------------
select * from distinct2numlist('emp','deptno')
Plan hash value: 1096181357
------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 29 (100)| | 3 |00:00:00.01 | 4 |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISTINCT2NUMLIST | 1 | 8168 | 16336 | 29 (0)| 00:00:01 | 3 |00:00:00.01 | 4 |
------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$F5BB74E1 / "KOKBF$0"@"SEL$2"
4.总结：
--//仅仅为了学习，没有实际的意义.在返回值很少并且相关字段索引存在的情况下也许执行效率高。
--//另外测试没有考虑NULL的情况。
--//理论还可以修改返回多个值，不在上面浪费时间。
--//再次提醒一些开发在写代码时想想，我开发的程序运行时间有多长，数据结构是否合理。
--//再贴一个生产系统看到的情况：
SYS@127.0.0.1:9105/xtdb/xtdb1> @ sql_id 43cm4x9swk2ga
-- SQL_ID = 43cm4x9swk2ga come from shared pool
select distinct MR_Class from MED_EMR_ARCHIVE_DETIAL;
SYS@127.0.0.1:9105/xtdb/xtdb1> @ seg2 %.MED_EMR_ARCHIVE_DETIAL
SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------
41 MEDCOMM MED_EMR_ARCHIVE_DETIAL TABLE TSP_MEDCOMM 5248 2 19346
SYS@127.0.0.1:9105/xtdb/xtdb1> @ desczz MEDCOMM.MED_EMR_ARCHIVE_DETIAL MR_Class
eXtended describe of MEDCOMM.MED_EMR_ARCHIVE_DETIAL
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,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
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- --------- -----------
MEDCOMM MED_EMR_ARCHIVE_DETI 5518 2024-12-05 22:01:54 3 MR_CLASS NOT NULL VARCHAR2(10) 1 .00000209385 0 FREQUENCY 1 麻醉 麻醉
AL
--//不同的值仅仅1个.无语.
--//自己想想随着表数据增加，如果程序代码经常这样调用有意义吗？