Pantea's questions

我有一张具有以下结构的表格：

create table TEST_TAB
(
  activity_type CHAR(1),
  tracking_code NUMBER,
  eff_date      DATE
)

该表的示例数据：

insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('01-11-2020', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('02-01-2024', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('01-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('02-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('03-08-2023', 'dd-mm-yyyy'));

这只是示例数据，原始表中的实际数据量接近 4 亿条记录。我需要做的是，对于每组activity_type, tracking_code，我需要保留具有最高“eff_date”的记录并删除其余记录。因此，对于，activity_type=A and tracking_code = 1我需要保留具有的记录eff_date = 1/2/2024并删除另一个记录。我现在有以下查询：

delete from test_tab
 where rowid in (select rid
                   from (select rowid as rid,
                                row_number() over(partition by activity_type, tracking_code order by eff_date desc) as row_num
                           from test_tab
                           )
                  where row_num > 1
                  )

但是这似乎很慢。您能提出更好的解决方案吗？原始表按 eff_date 进行分区，并在其余两列上建立索引。另一点是，单个组中每条记录的 eff_date 之间可能相差一年以上。

提前致谢

我有一个具有以下结构的表：

create table student_info
(
  item_number    number,
  st_firstname   varchar2(50),
  st_lastname    varchar2(50),
  st_score       varchar2(50)
)

这是表的示例数据：

item_number  |  st_firstname   |         st_lastname      |  st_score
----------------------------------------------------------------------------
     1         Ali|Reza|Pantea    Hashemi|Nosrati|Yaghobi    10|20|20
     
     2          Maryam|Ahmad         Moghise|Majlesi          20|20    

我需要有以下输出：

item_number  |  st_firstname   |         st_lastname      |  st_score
----------------------------------------------------------------------------
     1             Ali                    Hashemi               10
     1             Reza                   Nosrati               20
     1             Pantea                 Yaghobi               20
     
     2             Maryam                 Moghise               20
     2             Ahmad                  Majlesi               20

我发现通过以下查询，我可以使用其中一列（即st_firstname）做我想做的事：

select distinct t.item_number,
                trim(regexp_substr(t.st_firstname, '[^|]+', 1, level)) str
  from student_info t
connect by instr(st_firstname, '|', 1, level - 1) > 0
 order by t.item_number

问题是我不知道如何将其他列（st_lastname，st_lastname）添加到上述查询中。我想知道你是否可以在这里帮助我。

提前致谢

下面，我试图在查询中使用array我声明的数据，where clause但我收到以下错误：

declare

  v_input varchar2(400) := '0,1,2,3,4';

  type t_dep2custrel_type is table of number;
  t_dep2cust_rel t_dep2custrel_type;

begin


  select regexp_substr(v_input, '[^,]+', 1, level) zz
    bulk collect
    into t_dep2cust_rel
    from dual
  connect by regexp_substr(v_input, '[^,]+', 1, level) is not null;


  insert into bb_tmp_natonalcode_accnumber
    (identificationnumber)
    select b.customer_num
      from [sample_table] b
     where b.dpst2cust_rel_cod in (select * from table(t_dep2cust_rel)); /* here I'm trying to use the array*/

  commit;

end;

1. “sql 语句中不允许使用本地集合类型”
2. “无法访问非嵌套表项中的行”(ORA-22905)

我想知道你是否可以帮我解决这个问题。

提前致谢

我有一个这种格式的输入参数'0,1,2,3,4'，我想做的是声明array一个可变大小的（我的意思是输入参数可能是这样'0,1'的，所以大小不固定），分开项目（0,1， 2,3,4) 并将它们存储在数字数组中。下面你可以看到我到目前为止所做的：

declare

  v_input varchar2(400) := '0,1,2,3,4';

  type t_dep2custrel_type is table of number;
  t_dep2cust_rel t_dep2custrel_type;

begin

  select REGEXP_SUBSTR(v_input, '[^,]+', 1, LEVEL)
    into t_dep2cust_rel
    from dual
  CONNECT BY REGEXP_SUBSTR(v_input, '[^,]+', 1, LEVEL) IS NOT NULL;

end;

我收到此错误消息：

"Local collection types not allowed in SQL statements"

我想知道你是否可以帮我找到问题并告诉我是否有更好的方法来做到这一点。

提前致谢

我有一个具有以下结构的表：

create table item_test
(item_id       varchar2(10),
 item_row      number)

示例数据如下，请注意Item_Row列中的数字从 1 开始，并以 1 递增为一个数字，没有任何间隙。我需要做的是([1-100] , [101-200] , [201-300] ,......)为每个Item_Id.

   Item_Id       Item_Row    
 ------------   ----------- 
    A               1           
    A               2            
    A              ...
    A              ...
    A              236  /* Item_Row starts from 1 and continues to 236 */

    B               1
    B              ...
    B              ...
    B              173  /* Item_Row starts from 1 and continues to 173 */
    
    C               1
    C               2
    C              ...
    C              ...
    C              300  /* Item_Row starts from 1 and continues to 300 */ 

结果将如下所示：

  Item_Id       RowNum_From     RowNum_From
 ------------   -----------     ----------- 
     A              1               100
     A             101              200
     A             201              236

     B              1               100
     B             101              173
     
     C              1               100 
     C             101              200
     C             201              300 

这有什么特定的功能吗？

我有一个具有以下结构和示例数据的表：

create table CUSTOMER_TEST
(
  customer_num Number,
  rel_one      Number,
  rel_two      Number,
  rel_three    Number,
  rel_four     Number
)

   Customer_num    Rel_one     Rel_two        Rel_three        Rel_four 
 --------------- ----------- -------------  --------------  --------------
    1               7           12             1000               5
    2               2            1               0                12
    3              12           99              13                0

期望的结果是这样的：

   Customer_num    Rel_one     Rel_two        Rel_three        Rel_four      Relation_Code
 --------------- ----------- -------------  --------------  --------------  ***************
    1               7           12             1000               5           L07R12C99S05
    2               2            1               0                12          L02R01C00S12
    3              12           99              13                0           L12R99C13S00

我写的是这样的：

select   customer_num,
         rel_one , 
         rel_two,
         rel_three,
         rel_four,
       'L' ||
       case 
         when   rel_one > 99 then '99'
           else lpad(  rel_one,2,0) end ||
       'R' ||
       case
         when   rel_two > 99 then '99'
           else lpad(  rel_two,2,0) end ||
       'C' ||
        case
         when   rel_three > 99 then '99'
           else lpad(  rel_three,2,0) end  || 
        'S' ||
                case
                 when   rel_four > 99 then '99'
                   else lpad(  rel_four,2,0) end as relation_code            
                  
from customer_test ;

有人有更好的主意吗？而不是使用这么多的案例陈述..

提前致谢

我有一张桌子，如下所示：

create table z_test_duration
( Days     date,
  Status   char(8)
);

样本数据如下：

想要的结果是这样的

到目前为止，我的解决方案是：

select min(days) on_date, 
       off_day off_date, 
       off_day - min(days) cnt
       
  from (select t1.off_day, 
               t1.prev_offday, 
               t2.days            
          from (                
                select t.days off_day,
                        nvl(lag(t.days, 1) over(order by t.days),convert(datetime, '1/1/2022') - 100) prev_offday
                  from z_test_duration t
                 where t.status = 'off'
                                 
                ) t1
         inner join z_test_duration t2
            on t2.days > t1.prev_offday
           and t2.days < t1.off_day)
 group by off_day;

我在想是否有更好的方法来解决这个问题，如果你能分享你解决这个问题的方法，我将不胜感激。

提前致谢。

我有一个具有以下结构的表：

create table Z_TEST_ONE
(
  col_id   NUMBER,
  bed_amnt NUMBER,
  bes_amnt NUMBER,
  bop      NUMBER
)

样本数据是这样的：

     col_id    bed_amnt    bes_amnt    bop
   ---------- ----------- ---------- -------
       1        1000         0         20
       1        5000         0         7
       1          0         3000       10
       1          0          6         14
       2        1000         0         1
       2        2000         0         2
       2          0         1000       3
       2          0         2000       4

现在我需要的是：对于每个col_id，最高的 bop 列bes_amnt必须等于最高的 bop bed_amnt。因此，执行查询后，结果必须如下所示：

     col_id    bed_amnt    bes_amnt    bop
   ---------- ----------- ---------- -------
       1        1000         0         20
       1        5000         0         7
       1          0         3000       7 ---> changed from 10 to 7
       1          0          6         14
       2        1000         0         1
       2        2000         0         2
       2          0         1000       3
       2          0         2000       2 ---> changed from 4 to 2

我为此写了一个查询，如下所示：

update z_test_one a
   set a.bop =
       (select t.bop /* bop for highest bed_amnt */
          from z_test_one t
         where t.bed_amnt = (select max(bed_amnt)
                               from z_test_one
                              where col_id = a.col_id
                              group by col_id))     
 where a.bes_amnt = (select max(bes_amnt)
                       from z_test_one
                      where col_id = a.col_id
                      group by col_id)

这个查询工作正常并给了我正确的结果，我想知道是否有更好的方法来编写所需的查询。

提前致谢

我有一个查询，如下所示：

select /*+gather_plan_statistics*/
 *
  from mi_dimcustomer t
 where t.CUSTOMER_NUM = 321937

表中有一个Unique Index (IDX1_DIMCUSTOMER)onCustomer_Num列。我使用了/*+gather_plan_statistics*/提示并Dbms_xplan.display_cursor获得了真正的执行计划，这就是我所拥有的：

SQL_ID  adj0b6drg6bjd, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from vmi_dimcustomer t where 
t.CUSTOMER_NUM = 321937
 
Plan hash value: 3784660444
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |        |     2 (100)|      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MI_DIMCUSTOMER   |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | IDX1_DIMCUSTOMER |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CUSTOMER_NUM"=321937)

我的问题是，Operation-2 , Index unique scan.据我所知，发生的Index scan只是扫描/搜索Index Page，检索所需的 Rowid，然后使用这些 Rowid 访问表中的正确位置（这是 op-1）。我不期望操作-2（它只是扫描索引页）以生成任何行！那么为什么我们在 op-2 的执行计划中看到 [A-rows]=1 呢？这个数字代表什么？

我的猜测是它将等于从 index page 返回的 ROWID 的数量。

提前致谢

我有一个查询，如下所示：

with cte as
 (select customer_num
    from vmi_segment_customer_relation
   where effective_date = to_date('12/30/2021', 'mm/dd/yyyy')
     and segment_id = 10000000592
     )
select 
 t.customer_num, 
 cust_first_name, 
 cust_last_name, 
 cust_type_desc
  from vmi_factcustomer t
  join cte f
    on t.customer_num = f.customer_num
   and t.effective_date = to_date('12/30/2021', 'mm/dd/yyyy')

  join vmi_dimcustomer d
    on t.customer_num = d.customer_num;

如您所见，此查询中有三个表

1) vmi_segment_customer_relation、索引："IDX1_SEGMENT"在"segment_id" 列上。

2) vmi_factcustomer、索引："IDX1_F"在"customer_num" 列上。

3) vmi_dimcustomer、索引："IDX_CUSTNUM"在"customer_num"列上。

所有表statistics都是最新的，并且没有过时的统计信息。我execution plan使用这个提示得到了这个查询的真实信息/*+gather_plan_statistics*/，你可以看到这里是计划：

我对计划有一些疑问：

1. 我希望在operation-10（operation-11op-10 是 op-11 的子项）下，因为'IDX_CUSTNUM'索引是针对'MI_DIMCUSTOMER'table 的！看看op-5 and op-6例子。或者op-8 and op-9，我希望 op-10 和 op-11 完全像这两个一样，但不知道为什么不是！

2. 另一个问题是，查询中有两个连接，为什么我们Nested loop joins在计划中看到三个？每个嵌套循环的作用是什么？

提前致谢

我刚刚熟悉了"qb_name" (query block name)Oracle 中的提示，下面您可以看到在查询中使用此提示的示例：

select
        /*+
                qb_name(main)
        */
        ord.id,
        ord.valuation,
        ord.status,
        (select /*+ qb_name(company) */ max(com.name)  from companies  com  where com.id  = ord.id_company) company,
        (select /*+ qb_name(product) */ max(prd1.name) from products   prd1 where prd1.id = orl.id_product) product,
        orl.quantity
from
        orders          ord,
        order_lines     orl
where
        ord.date_placed > trunc(sysdate) - 7
and     orl.id_ord = ord.id
and     orl.id_product in (
                select  /*+ qb_name(class) */
                        prd2.id
                from    products prd2
                where   prd2.class = 'Group25'
        )

我已经搜索过它，但这些文章并不是那么实用，所以我决定在这里询问这个提示。我的问题是：

1. 对 有什么显着影响performance吗？
2. 这里有人在他们的查询中使用过这个提示吗？为什么 ？
3. 在什么情况下我们必须使用这个提示？

提前致谢

我有一张包含大量数据（近 1500 万）及以下结构的表格。

create table test
(a int,--> /* There is a normal index on this column */
 b int,
<other columns>)

有一个从此表中选择的查询，where 子句中的条件之一是：

where a!=1 or (a=1 and b!=0) /* The original condition */

查询非常慢，我认为这种糟糕的性能大部分可能是因为逻辑运算符使用不当。我已经改变了条件，如下所示：

where not (a=1 and b=0) /* The edited version*/

并且性能发生了巨大变化！我需要确定的是这两个条件完全相同，所以我不会错过任何数据。我想知道您是否可以帮助我，并告诉我您是否有更好的替代方案。

如果您知道任何关于正确使用逻辑操作以及方式/顺序优化器处理它们的文章，请分享链接。

提前致谢

我有一个具有以下结构和数据的表：

create table PTEST
(
  col_name  VARCHAR(50),
  col_value VARCHAR(50)
)

    COL_NAME    COL_VALUE
   -----------------------
     first       apple
     first       banana
     second      apple
     second      banana
     second      orange
     third       apple
     third       banana

**) 我要做的是将col_value列中的每个值分为两类：[常见，不常见]

**)'Common'如果一个值出现在每个中，则考虑一个值col_name，因此apple很常见，因为它出现在 中col_name = first and col_name = second and col_name = third。对于banana. Orange并不常见，因为它只是出现在col_name = second.

所需的输出将是这样的：

    COL_NAME   COL_VALUE   STATUS
   ---------------------------------
    first       apple       Common
    first       banana      Common
    second      banana      Common
    second      apple       Common
    second      orange      Not common
    third       apple       Common
    third       banana      Common

我为此写的查询是：

select col_name,
       col_value,
       case
         when count_col = count_val then
          'Common'
         else
          'Not common'
       end STATUS
  from (select t.col_name,
               count(distinct t.col_name) over() count_col,
               t.col_value,
               count(t.col_value) over(partition by t.col_value) count_val
          from PTEST t)

我想知道是否有更好的方法来做到这一点。

提前致谢

我有一个具有以下结构的示例表：

create table mi_dimcustomer
(customer_num    number(10),
 <other columns> <data types>)

并且有一个unique indexon 列customer_num。我试图hint the optimizer不像这样使用这个索引（只是为了练习）：

select /*+gather_plan_statistics*/ /*+ no_index(t idx1_dimcustomer) */
 *
  from mi_dimcustomer t
 where t.customer_num = 12;

但是在执行计划中，我可以看到优化器还在使用索引！这是我捕获执行计划的方式：

Step-1)

    select sql_id, child_number, sql_text
      from v$sql
     where sql_text like '%where t.CUSTOMER_NUM = 12%';

Step-2)    

select *
  from table(dbms_xplan.display_cursor('2qataxp9mahpj',
                                       '0',
                                       'ALLSTATS LAST +COST +OUTLINE'))

您可以在下面看到执行计划：

SQL_ID  2qataxp9mahpj, child number 0
-------------------------------------
select /*+gather_plan_statistics*//*+ NO_INDEX(t idx1_dimcustomer) */ * 
from mi_dimcustomer t where t.CUSTOMER_NUM = 12
 
Plan hash value: 3784660444
 
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |      1 |        |     2 (100)|      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MI_DIMCUSTOMER   |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | IDX1_DIMCUSTOMER |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------------------

为什么会这样？

提前致谢

我正在尝试捕获execution plan我的查询，PL/SQL developer如下所示：

select  *
  from vmi_dimcustomer t1
 inner join vmi_factcustomer t2
    on t1.customer_num = t2.customer_num  ;
    
Select plan_table_output from table(dbms_xplan.display_cursor(null,null,'basic'));

但我收到了这张纸条：

  SQL_ID  9m7787camwh4m, child number 0
  begin :id := sys.dbms_transaction.local_transaction_id; end;
  NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
    Please verify value of SQL_ID and CHILD_NUMBER; 
    It could also be that the plan is no longer in cursor cache (check v$sql_plan)

我在这里做错了什么？我在这里搜索，我得到的答案是"set serveroutput off"。把我不能在 PL/SQL 开发人员上做到这一点。

提前致谢。

我正在Explain plan and Execution planOracle 中学习，根据我目前所读到的内容，解释的计划可能与语句执行期间使用的实际计划不同。所以我认为执行计划比解释计划更有用optimization purposes。

我的问题 ：

一个Sql语句，你通常如何生成执行计划？在阅读了不同的文章后，我现在有点困惑，因为在每篇文章中都引入了不同的方法！例如，我遇到了这个：

explain plan 
 set statement_id = 'ex_plan1' for

select phone_number
from employee
where phone_number like '650%';

select PLAN_TABLE_OUTPUT
 from table (DBMS_XPLAN.DISPLAY(STATEMENT_ID=>'ex_plan1'));

1. 这是correct way为了制定执行计划（不是解释计划）吗？

2. 拥有正确执行计划的其他方法是什么？（that optimizer选择执行语句的方法）。

提前致谢

我创建了一个这样的序列：

CREATE SEQUENCE seq_test2
  MINVALUE 0
  MAXVALUE 999999999999999999999999999
  START WITH 0
  INCREMENT BY 1;

这两个查询都向我显示数字 0：

 select SEQ_TEST2.nextval from DUAL;   
 select SEQ_TEST2.currval from DUAL;

我有一张桌子，你可以在这里看到：

create table STUDENT
(
  st_id      NUMBER,
  first_name VARCHAR2(150),
  last_name  VARCHAR2(150)
)

执行下面的查询后，

select t.st_id , nvl(t.st_id , seq_test2.nextval) as seq
from STUDENT t

当 column 中没有 null 时st_id，我得到的结果是：

    st_id    seq
   ---------------
      1       1
      2       2
      3       3
      4       4
      5       5

当我插入列为空的行时st_id，执行上面的查询后，结果如下：

      st_id    seq
   ---------------
       1       1
       2       2
       3       3
       4       4
       5       5
       null    10

如果我再次执行它，我会看到：

      st_id    seq
   ---------------
       1       1
       2       2
       3       3
       4       4
       5       5
       null    16

好像即使列st_id有数据，这部分NVL function(seq_test2.nextval) 也会被执行！！！为什么会这样？

提前致谢

维度建模中的一种事实表是Accumulating Snapshot fact Table. 如果你觉得需要复习一下这个类型的含义和定义，可以看看这篇文章： https ://www.holistics.io/blog/the-three-types-of-fact-tables/ 。

想象一下，我们有一个累积事实表，其中包含三个重要的日期列

'Order_Datekey , Manufacturing_Datekey , Ship_Datekey'

第一次插入表时，列没有数据Manufacturing_Datekey and Ship_Datekey'。我们只知道订购产品的日期（Order_date）。我需要知道的是我们如何处理 Date 列的空值？我在 Kimbal 书中读到的重点是在外键列中应避免 null。

'Referential
integrity is violated if you put a null in a fact table column declared as a foreign key
to a dimension table'

另一方面，我们起初对这两个日期列没有任何价值。你有什么建议？

提前致谢

我有一个具有以下结构和数据的表：

create table TEST_TABLE
(
  item_number NUMBER,
  item_name   VARCHAR2(50),
  item_col    VARCHAR2(50),
  item_pol    VARCHAR2(50)
)

样本数据：

item_number |  item_name|  item_col |  item_pol
------------------------------------------------
     1      |   blue    |     b     |   c
     2      |   red     |     d     |   a
     3      |   black   |     e     |   a
     4      |   yellow  |     d     |   b

这是我尝试使用的示例程序bind variables。

create or replace procedure test_bind_variable(res         out sys_refcursor,
                                               item_number varchar2,
                                               item_name   varchar2,
                                               item_col    varchar2,
                                               item_pol    varchar2) is

  qry varchar2(8000);

begin

  qry := 'select * from test_table where 1=1 ';

  if (item_number is not null) then
    qry := qry || ' and item_number = :1  ';
  end if;

  if (item_name is not null) then
    qry := qry || ' and item_name = :2 ';
  end if;

  if (item_col is not null) then
    qry := qry || ' and item_col= :3 ';
  end if;

  if (item_pol is not null) then
    qry := qry || ' and item_pol = :4 ';
  end if;

  dbms_output.put_line(qry);

  open res for  qry
    using item_number, item_name, item_col, item_pol;

end;

问题是当所有输入参数都有值时，程序正常工作，没有任何错误，但是当只有一个或两个参数有值时，我收到这个错误：ORA-01006: bind variable does not exist,。我怎么解决这个问题？有些参数可能有值，有些可能没有。

提前致谢

我有一个具有以下结构的表，它是数据：

create table test_table
(
Item_index   int,
Item_name    varchar(50)
)

insert into test_table (Item_index,Item_name) values (0,'A')
insert into test_table (Item_index,Item_name) values (1,'B')
insert into test_table (Item_index,Item_name) values (0,'C')
insert into test_table (Item_index,Item_name) values (1,'D')
insert into test_table (Item_index,Item_name) values (0,'E')

我想知道为什么更改order by查询部分中的列会更改结果？在QUERY-1, 我使用item_index和 在QUERY-2I useditem_name列中按部分顺序排列。我认为这两个查询必须生成相同的结果because I used item_index in both queries for partitioning!我现在完全糊涂了！为什么按列排序会影响最终结果？

QUERY-1：

select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_index) new_column
from test_table t;

结果：

Item_index  Item_name     new_column
----------- --------------------------
0           A                E
0           C                E
0           E                E
1           D                D
1           B                D

查询 2：

select t.*,
       max(t.Item_name)over(partition by t.item_index order by item_name) new_column
from test_table t;

结果：

Item_index  Item_name  new_column
----------- -----------------------
0           A             A
0           C             C
0           E             E
1           B             B
1           D             D

谁能解释这两个查询是如何执行的以及为什么每个查询都会产生不同的结果？

提前致谢