Oracle 验证约束递归运行带有 “有序” 提示的SELECT

我一直在研究最小化验证引用约束所花费的时间。我们希望对其进行验证，以便优化器在查询重写期间利用联接消除。但是，对于一张大表，需要花费大量时间来验证-而且一如既往-时间很紧。

同时跟踪ALTER表…。修改约束... 验证我注意到正在执行一个递归语句，它确保子表中的所有数据都很好，并且可以验证约束。

该语句的问题在于，它使用 “ordered” 提示以该顺序连接子表和父表。这会导致子表成为驱动表，而在我们的情况下，它是巨大的，从而导致巨大的临时段。我尝试反转加入顺序，它在性能方面产生了巨大的差异。

决定两个表之间的连接顺序的动机是什么-先为子，然后为父？我们有很多情况下，让优化器选择加入顺序 (首先是父级，然后是子级) 将是非常有益的。

这是正在执行的递归语句:

select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3 from "FOO"."CHILD_TABLE" A , "FOO"."PARENT_TABLE" B where( "A"."FK" is not null) and( "B"."PK" (+)= "A"."FK") and( "B"."PK" is null)
复制

我尝试制作一个LiveSQL测试用例，但该站点已关闭。所以你会得到 “旧” 的方式:

create user foo identified by BAR default tablespace users;
grant connect to foo;
grant create table to foo;
alter user foo quota unlimited on users;
grant select on dba_objects to foo;
grant alter session to foo;
--Connect as FOO
drop table child_table;
drop table parent_table;
create table parent_table (pk not null, constraint parent_table_pk primary key (pk)) as select ROWNUM pk FROM DBA_OBJECTS WHERE ROWNUM <= 100;
create table child_table (pk not null, fk not null, constraint child_table_pk primary key (pk)) as select ROWNUM pk, mod(rownum, 100)+1 fk FROM DBA_OBJECTS WHERE ROWNUM <= 1000;
create index child_table_fk on child_table(fk);
alter session set tracefile_identifier='ORDERED_HINT';
ALTER SESSION SET SQL_TRACE = TRUE;
alter table child_table add constraint child_table_fk_pk foreign key (fk) references parent_table(pk) enable novalidate;
alter table child_table modify constraint child_table_fk_pk validate;
ALTER SESSION SET SQL_TRACE = FALSE;
复制

您应该能够在跟踪文件中找到有问题的查询。

P.s.请忽略DBA_OBJECTS的使用。我重新使用了一个旧的测试用例。

当你说你颠倒了加入顺序，你到底做了什么？

请记住，要验证外键，数据库必须检查子表中的所有行。我不知道包含有序提示的确切原因，但这可能与此有关。

通过使用大纲或其他 “提示注入” 选项，您可能能够获得更好的计划。至少，如果您的验证查询对child_table进行了全面扫描，则将其切换到索引 (快速) 全面扫描应会减少临时使用。

但是，如果这仍然是一个问题，并且您无法提供足够的临时空间，您仍然可以在查询中获得表消除，从而使约束依赖。您需要在priamry和外键上执行此操作。一旦到位，您可以通过将query_rewrite_integrity设置为trusted来获得表消除。

Be certain there are no orphaned rows in the child before doing this!如果有，你会得到不正确的结果...

create table parent_table (
  pk not null,
  constraint parent_table_pk primary key ( pk )
)
  as
    select rownum pk
    from dba_objects
    where rownum <= 100;
    
create table child_table (
  pk not null,
  fk not null,
  constraint child_table_pk primary key ( pk )
)
  as
    select rownum pk,
           mod( rownum,100 ) + 1 fk
    from dba_objects
    where rownum <= 1000;
    
create index child_table_fk on child_table(fk);
 
alter table parent_table modify constraint parent_table_pk rely;
alter table child_table add constraint child_table_fk_pk 
 foreign key (fk) references parent_table(pk) rely novalidate ;

set serveroutput off
select a.*
from "CHILD_TABLE" a,
     "PARENT_TABLE" b
where "B"."PK" = "A"."FK" ;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT                                                          
EXPLAINED SQL STATEMENT:                                                   
------------------------                                                   
select a.* from "CHILD_TABLE" a,      "PARENT_TABLE" b where "B"."PK" =    
"A"."FK"                                                                   
                                                                           
Plan hash value: 2903178540                                                
                                                                           
----------------------------------------------                             
| Id  | Operation          | Name            |                             
----------------------------------------------                             
|   0 | SELECT STATEMENT   |                 |                             
|   1 |  NESTED LOOPS      |                 |                             
|   2 |   TABLE ACCESS FULL| CHILD_TABLE     |                             
|   3 |   INDEX UNIQUE SCAN| PARENT_TABLE_PK |                             
---------------------------------------------- 

alter session set query_rewrite_integrity = trusted;

select a.*
from CHILD_TABLE a,
     PARENT_TABLE b
where "B"."PK" = "A"."FK" ;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC LAST'));

PLAN_TABLE_OUTPUT                                                      
EXPLAINED SQL STATEMENT:                                               
------------------------                                               
select a.* from CHILD_TABLE a,      PARENT_TABLE b where "B"."PK" =    
"A"."FK"                                                               
                                                                       
Plan hash value: 362782935                                             
                                                                       
-----------------------------------------                              
| Id  | Operation         | Name        |                              
-----------------------------------------                              
|   0 | SELECT STATEMENT  |             |                              
|   1 |  TABLE ACCESS FULL| CHILD_TABLE |                              
----------------------------------------- 
复制

当然，如果您要进入这个领域，更好的选择是重写您的查询，以便它们排除冗余表!