[20250103]distinct的函数实现.txt

[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个.无语.
--//自己想想随着表数据增加,如果程序代码经常这样调用有意义吗?
posted @   lfree  阅读(9)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· [翻译] 为什么 Tracebit 用 C# 开发
· Deepseek官网太卡,教你白嫖阿里云的Deepseek-R1满血版
· 2分钟学会 DeepSeek API,竟然比官方更好用!
· .NET 使用 DeepSeek R1 开发智能 AI 客户端
· 刚刚!百度搜索“换脑”引爆AI圈,正式接入DeepSeek R1满血版
历史上的今天:
2022-01-05 [20220104]in list 几种写法性能测试.txt
2021-01-05 [20201231]RAC buffer states: XCUR, SCUR, PI,CR.txt
点击右上角即可分享
微信分享提示