欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

[20180928]如何能在11g下执行.txt

程序员文章站 2024-02-13 21:20:46
[20180928]如何能在11g下执行.txt--//链接问的问题: http://www.itpub.net/thread-2105467-1-1.htmlcreate table test(t_id int,t_name varchar2(50));create table test2(t_i ......

[20180928]如何能在11g下执行.txt

--//链接问的问题: http://www.itpub.net/thread-2105467-1-1.html

create table test(t_id int,t_name varchar2(50));
create table test2(t_id int,t_name varchar2(50));

insert into test values(1,'a');
insert into test values(2,'b');
insert into test values(3,'c');

insert into test2 values(1,'a');
insert into test2 values(2,'b');
insert into test2 values(3,'c');

select a.t_id, a.t_name
    from test a
         left join (  select t_id, t_name
                        from test2
                    group by t_name) b
            on a.t_id = b.t_id and a.t_name = b.t_name
   where a.t_id = 1
group by a.t_id, a.t_name;

--//同样的sql ,在10g下就能执行,在11g下就会报错 ora-00979 不是group by 表达式。红色部分看起来确实是错的,可是10g下整句
--//执行就有结果。如何能做到这个语句再11g下也能执行呢。

--//家里没有10g,使用12c测试看看,这条语句明显错误(  select t_id, t_name from test2 group by t_name),这里少写了 group by
--//t_id,t_name.

1.环境:
scott@test01p> @ ver1
port_string                    version        banner                                                                               con_id
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
ibmpc/win_nt64-9.1.0           12.1.0.1.0     oracle database 12c enterprise edition release 12.1.0.1.0 - 64bit production              0

2.测试:
scott@test01p> select a.t_id,a.t_name from test a left join
  2  (select t_id,t_name from test2 group by t_name) b
  3  on a.t_id=b.t_id and a.t_name=b.t_name where a.t_id=1
  4  group by a.t_id,a.t_name;
(select t_id,t_name from test2 group by t_name) b
        *
error at line 2:
ora-00979: not a group by expression
--//12c 一样报错.

scott@test01p> show parameter feature
name                                 type                 value
------------------------------------ -------------------- ---------------
optimizer_adaptive_features          boolean              true
optimizer_features_enable            string               12.1.0.1

scott@test01p> alter session set optimizer_features_enable='10.2.0.1';
session altered.

scott@test01p> select a.t_id, a.t_name
  2      from test a
  3           left join (  select t_id, t_name
  4                          from test2
  5                      group by t_name) b
  6              on a.t_id = b.t_id and a.t_name = b.t_name
  7     where a.t_id = 1
  8  group by a.t_id, a.t_name;
      t_id t_name
---------- --------------------------------------------------
         1 a

--//ok通过.不过这句话明显错误.看看执行计划:

scott@test01p> @ dpc '' advanced
plan_table_output
-------------------------------------
sql_id  0urvg3qakxxcx, child number 0
-------------------------------------
select a.t_id, a.t_name     from test a          left join (  select
t_id, t_name                         from test2
group by t_name) b             on a.t_id = b.t_id and a.t_name =
b.t_name    where a.t_id = 1 group by a.t_id, a.t_name

plan hash value: 1211648783

-----------------------------------------------------------------------------------------------------------
| id  | operation             | name  | e-rows |e-bytes| cost (%cpu)| e-time   |  omem |  1mem | used-mem |
-----------------------------------------------------------------------------------------------------------
|   0 | select statement      |       |        |       |     9 (100)|          |       |       |          |
|   1 |  hash group by        |       |      1 |    40 |     9  (34)| 00:00:01 |  2063k|  2063k|  438k (0)|
|   2 |   view                |       |      1 |    40 |     8  (25)| 00:00:01 |       |       |          |
|   3 |    hash group by      |       |      1 |    92 |     8  (25)| 00:00:01 |  1345k|  1345k|  486k (0)|
|*  4 |     hash join outer   |       |      1 |    92 |     7  (15)| 00:00:01 |  1421k|  1421k|  722k (0)|
|*  5 |      table access full| test  |      1 |    52 |     3   (0)| 00:00:01 |       |       |          |
|*  6 |      table access full| test2 |      1 |    40 |     3   (0)| 00:00:01 |       |       |          |
-----------------------------------------------------------------------------------------------------------

query block name / object alias (identified by operation id):
-------------------------------------------------------------

   1 - sel$439afb4f
   2 - sel$3dd9cb74 / $vm_view_0@sel$439afb4f
   3 - sel$3dd9cb74
   5 - sel$3dd9cb74 / a@sel$3
   6 - sel$3dd9cb74 / test2@sel$2

outline data
-------------

  /*+
      begin_outline_data
      ignore_optim_embedded_hints
      optimizer_features_enable('10.2.0.1')
      db_version('12.1.0.1')
      all_rows
      outline_leaf(@"sel$3dd9cb74")
      merge(@"sel$2")
      outline_leaf(@"sel$439afb4f")
      outline(@"sel$f2b2f603")
      outline(@"sel$2")
      outline(@"sel$cd8351fa")
      merge(@"sel$f1d6e378")
      outline(@"sel$4")
      outline(@"sel$f1d6e378")
      merge(@"sel$1")
      outline(@"sel$3")
      outline(@"sel$1")
      no_access(@"sel$439afb4f" "$vm_view_0"@"sel$439afb4f")
      use_hash_aggregation(@"sel$439afb4f")
      full(@"sel$3dd9cb74" "a"@"sel$3")
      full(@"sel$3dd9cb74" "test2"@"sel$2")
      leading(@"sel$3dd9cb74" "a"@"sel$3" "test2"@"sel$2")
      use_hash(@"sel$3dd9cb74" "test2"@"sel$2")
      use_hash_aggregation(@"sel$3dd9cb74")
      end_outline_data
  */


predicate information (identified by operation id):
---------------------------------------------------

   4 - access("a"."t_name"="t_name" and "a"."t_id"="t_id")
   5 - filter("a"."t_id"=1)
   6 - filter("t_id"=1)

--//抽取outline.

--//编辑整理如下:

begin
dbms_sqltune.import_sql_profile(
   name => 'profile_group_error',
   description => 'sql profile created manually test',
   sql_text => q'[select a.t_id, a.t_name
    from test a
         left join (  select t_id, t_name
                        from test2
                    group by t_name) b
            on a.t_id = b.t_id and a.t_name = b.t_name
   where a.t_id = 1
group by a.t_id, a.t_name]',
   profile => sqlprof_attr(
'ignore_optim_embedded_hints',
q'[optimizer_features_enable('10.2.0.1')]',
q'[db_version('12.1.0.1')]',
'all_rows',
'outline_leaf(@"sel$3dd9cb74")',
'merge(@"sel$2")',
'outline_leaf(@"sel$439afb4f")',
'outline(@"sel$f2b2f603")',
'outline(@"sel$2")',
'outline(@"sel$cd8351fa")',
'merge(@"sel$f1d6e378")',
'outline(@"sel$4")',
'outline(@"sel$f1d6e378")',
'merge(@"sel$1")',
'outline(@"sel$3")',
'outline(@"sel$1")',
'no_access(@"sel$439afb4f" "$vm_view_0"@"sel$439afb4f")',
'use_hash_aggregation(@"sel$439afb4f")',
'full(@"sel$3dd9cb74" "a"@"sel$3")',
'full(@"sel$3dd9cb74" "test2"@"sel$2")',
'leading(@"sel$3dd9cb74" "a"@"sel$3" "test2"@"sel$2")',
'use_hash(@"sel$3dd9cb74" "test2"@"sel$2")',
'use_hash_aggregation(@"sel$3dd9cb74")'
),
   replace => true,
   force_match => true
);
end;
/

scott@test01p> show parameter feature
name                                 type                 value
------------------------------------ -------------------- --------------
optimizer_adaptive_features          boolean              true
optimizer_features_enable            string               12.1.0.1

scott@test01p> select a.t_id, a.t_name
  2      from test a
  3           left join (  select t_id, t_name
  4                          from test2
  5                      group by t_name) b
  6              on a.t_id = b.t_id and a.t_name = b.t_name
  7     where a.t_id = 1
  8  group by a.t_id, a.t_name;
      t_id t_name
---------- --------------------------------------------------
         1 a

--//换1个数值,select变成select看看.

scott@test01p> select a.t_id, a.t_name
  2      from test a
  3           left join (  select t_id, t_name
  4                          from test2
  5                      group by t_name) b
  6              on a.t_id = b.t_id and a.t_name = b.t_name
  7     where a.t_id = 2
  8  group by a.t_id, a.t_name;
      t_id t_name
---------- --------------------------------------------------
         2 b

--//ok解决,最好还是叫开发改代码..