Skip to Main Content
  • Questions

  • Validate constraint recursively run a SELECT with an "ordered" hint

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Daniel Overby.

Asked: November 27, 2017 - 7:34 pm UTC

Last updated: December 01, 2017 - 4:27 pm UTC

Version: 12.2

Viewed 1000+ times

You Asked

I have been looking into minimizing the time it takes to validate a referential constraint. We want it to be validated for the optimizer to utilize join elimination during query rewrite. However, for a big table it takes a lot of time to validate – and as always – time is tight.

While tracing the ALTER TABLE …. MODIFY CONSTRAINT … VALIDATE I noticed that there is a recursive statement being executed which ensures that all the data in the child table is fine, and that the constraint can be validated.

The problem with the statement is that it joins the child and parent table in that order using the “ordered” hint. This causes the child table to be the driving table, and in our case, it is huge, resulting in a huge temp segment. I tried inverting the join order, and it made a huge difference performance-wise.

What is motivation for dictating the join order between the two tables – child first, then parent? We have many cases where it would be very beneficial to let the optimizer choose the join order (parent first, then child).

This is the recursive statement being executed:
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)


I tried making a LiveSQL test case, but the site was down. So you will get the "old" way:

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;


You should be able to find the query in question in the trace file.

P.S. Please disregard the use of DBA_OBJECTS. I re-used an old test case.

and Chris said...

When you say you inverted the join order, what exactly did you do?

Remember that to validate the foreign key the database has to inspect all the rows in the child table. I don't know the exact reasons for including the ordered hint, but it likely relates to this.

You may be able to get better plans by using outlines or other "hint injecting" options. At a minimum, if your validation query does a full scan of child_table, switching this to an index (fast) full scan should reduce your temp usage.

But if this is still a problem and you're not able to provision enough temp space, you can still get table elimination in your queries making the constraints rely. You need to do this on the priamry and foreign key. Once this is in place you can get table elimination by setting query_rewrite_integrity to trusted.

Be certain there are no orphaned rows in the child before doing this! If there are you can get incorrect results...

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 |                              
-----------------------------------------


Of course, if you're getting into this territory, the better option is to rewrite your queries so they exclude the redundant tables!

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Daniel Overby Hansen, November 29, 2017 - 12:48 pm UTC

Thank you for your answer.

"Inverting the join order" might not be the right term, but I constructed a query that achieves the same but allows the optimizer to join at the best possible away. In my test case there are NOT NULL constraints on FOO.PARENT_TABLE.PK and FOO.CHILD_TABLE.FK that are ENABLED and VALIDATED. So, there can’t by any NULLs, so I should be able to just make a:

select count(*) from foo.child_table where fk not in (select pk from foo.parent_table);


But the plan is basically the same that I end up when removing the “ordered” hint from the recursive statement.

If I put some more data into the tables and run the recursive SELECT with and without the ordered hint, the optimizer chooses two different plans, and in the latter case it is much faster. Please observe the following revised test case:

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 <= 10000;
create table child_table (pk not null, fk not null, constraint child_table_pk primary key (pk)) as select ROWNUM pk, mod(rownum, 10000)+1 fk FROM DBA_OBJECTS, DBA_OBJECTS WHERE ROWNUM <= 10000000;
create index child_table_fk on child_table(fk);
select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, 'A', 'B', 'C' 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);
select * from   table(dbms_xplan.display_cursor(null, null, 'TYPICAL LAST'));
select /*+ all_rows dynamic_sampling(2) */ A.rowid, 'A', 'B', 'C' 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);
select * from   table(dbms_xplan.display_cursor(null, null, 'TYPICAL LAST'));


I would really like to understand the exact reason for the "ordered" hint. Because if it is truly needed in this case, then I am afraid that my alternate approach above will be flawed (manually checking the data and setting RELY).

I am aware of the possibility of setting the parameter "query_rewrite_integrity". However, when I read the documentation I found out that setting the parameter to “trusted” not only allows join elimination on foreign key constraints in RELY, but also:

In this mode, the optimizer also uses prebuilt materialized views or materialized views based on views, and it uses relationships that are not enforced as well as those that are enforced. It also trusts declared but not ENABLED VALIDATED primary or unique key constraints and data relationships specified using dimensions.

Ref.: https://docs.oracle.com/database/122/DWHSG/basic-query-rewrite-materialized-views.htm#DWHSG8496

Relationships that are declared but not enforced I would translate into constraints that disabled, and that might be too many "side effects" for us to accept.

I recognize – and agree with - your statement that it is a better option to get the developers to write perfect SQLs rather than relying on the optimizer doing join elimination, but we might not be there yet (… or never will be).
Chris Saxon
December 01, 2017 - 11:09 am UTC

When the optimizer chooses a nested loop for an outer join, it has to access the row-preserved table first:

However, in a nested loop outer join, the join condition determines the order of tables. The database uses the outer, row-preserved table to drive to the inner table.

https://docs.oracle.com/database/122/TGSQL/joins.htm#GUID-1ACFF09D-C8E1-4272-97B9-900D2053B91E

This also used to be the case for hash joins. From the 9i docs:

Like an outer join, the order of tables is not determined by the cost, but by the join condition.

https://docs.oracle.com/cd/A91202_01/901_doc/server.901/a87503/optimops.htm#49916

But is no longer true:

The cost determines the order of tables. The outer table, including preserved rows, may be used to build the hash table, or it may be used to probe the hash table.

https://docs.oracle.com/database/122/TGSQL/joins.htm#GUID-278E01B5-9498-40EC-B0BD-CC415C18E078

So, I suspect the ordered hint is a hang-over from when accessing child_table first was a strict requirement.

Running your test case on 12.2.0.1, I get the same plan whether or not the ordered hint is there:

/* using ordered hint */
PLAN_TABLE_OUTPUT                                                                                
SQL_ID  2kqwtvutvyy12, child number 0                                                            
-------------------------------------                                                            
select /*+ all_rows ordered dynamic_sampling(2) */ a.rowid,        'A',                          
       'B',        'C' from "CHILD_TABLE" a,      "PARENT_TABLE" b                               
where ( "A"."FK" is not null )       and   ( "B"."PK" (+) = "A"."FK" )                           
     and   ( "B"."PK" is null )                                                                  
                                                                                                 
Plan hash value: 2416273426                                                                      
                                                                                                 
----------------------------------------------------------------------------------------------   
| Id  | Operation           | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |   
----------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT    |                |       |       |       | 22996 (100)|          |   
|   1 |  MERGE JOIN ANTI    |                |    10M|   384M|       | 22996   (8)| 00:00:01 |   
|   2 |   INDEX FULL SCAN   | CHILD_TABLE_FK |    10M|   253M|       | 22914   (8)| 00:00:01 |   
|*  3 |   SORT UNIQUE       |                | 10000 |   126K|   408K|    82  (10)| 00:00:01 |   
|   4 |    TABLE ACCESS FULL| PARENT_TABLE   | 10000 |   126K|       |     8  (13)| 00:00:01 |   
----------------------------------------------------------------------------------------------   
                                                                                                 
Predicate Information (identified by operation id):                                              
---------------------------------------------------                                              
                                                                                                 
   3 - access("B"."PK"="A"."FK")                                                                 
       filter("B"."PK"="A"."FK")                                                                 
                                                                                                 
Note                                                                                             
-----                                                                                            
   - dynamic statistics used: dynamic sampling (level=2)                                         
                                                                                                 


29 rows selected. 

/* no ordered hint */

PLAN_TABLE_OUTPUT                                                                                
SQL_ID  bcmz76whb5jjk, child number 0                                                            
-------------------------------------                                                            
select /*+ all_rows dynamic_sampling(2) */ a.rowid,        'A',                                  
'B',        'C' from "CHILD_TABLE" a,      "PARENT_TABLE" b where (                              
"A"."FK" is not null )       and   ( "B"."PK" (+) = "A"."FK" )                                   
and   ( "B"."PK" is null )                                                                       
                                                                                                 
Plan hash value: 2416273426                                                                      
                                                                                                 
----------------------------------------------------------------------------------------------   
| Id  | Operation           | Name           | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |   
----------------------------------------------------------------------------------------------   
|   0 | SELECT STATEMENT    |                |       |       |       | 22996 (100)|          |   
|   1 |  MERGE JOIN ANTI    |                |    10M|   384M|       | 22996   (8)| 00:00:01 |   
|   2 |   INDEX FULL SCAN   | CHILD_TABLE_FK |    10M|   253M|       | 22914   (8)| 00:00:01 |   
|*  3 |   SORT UNIQUE       |                | 10000 |   126K|   408K|    82  (10)| 00:00:01 |   
|   4 |    TABLE ACCESS FULL| PARENT_TABLE   | 10000 |   126K|       |     8  (13)| 00:00:01 |   
----------------------------------------------------------------------------------------------   
                                                                                                 
Predicate Information (identified by operation id):                                              
---------------------------------------------------                                              
                                                                                                 
   3 - access("B"."PK"="A"."FK")                                                                 
       filter("B"."PK"="A"."FK")                                                                 
                                                                                                 
Note                                                                                             
-----                                                                                            
   - dynamic statistics used: dynamic sampling (level=2)


Yes, you need to take care with trusted query rewrite. If going down this route, I'd only enable it in sessions where you're sure it's safe. Not globally.

To requester

Ghassan, November 30, 2017 - 4:52 am UTC

In your 3 statements above I cannot find the fk definition . You have two tables with 3 pk.
You added an index on the "child" one.
Well since the index is on the second table and this is an advantage vs the first one, Oracle decides to make it in the first place and since the hinsts used by Oracle during transformation between them often the ordered one - as Oracle knows and is self confident that he had choose the right order while rewritin8query - so for those reason you have such query and such hint.

Try to add a real fk and index it.
Chris Saxon
November 30, 2017 - 2:08 pm UTC

The FK definition is right there at the end...

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;

Not same post

Ghassan, November 30, 2017 - 6:23 pm UTC

Hi Chris
Not talking at your post by the last post of requester.

"
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 <= 10000; create table child_table (pk not null, fk not null, constraint child_table_pk primary key (pk)) as select ROWNUM pk, mod(rownum, 10000)+1 fk FROM DBA_OBJECTS, DBA_OBJECTS WHERE ROWNUM <= 10000000; create index child_table_fk on child_table(fk); select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, 'A', 'B', 'C' 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); select * from table(dbms_xplan.display_cursor(null, null, 'TYPICAL LAST')); select /*+ all_rows dynamic_sampling(2) */ A.rowid, 'A', 'B', 'C' 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); select * fro..
"

Daniel Overby Hansen, December 01, 2017 - 12:01 pm UTC

In our 12.2 databases we get different plans depending on the presence of the "ordered" hint.

Do you think it would be worth raising an enhancement request with support for this issue?

Thanks (again) for your time. AskTOM is really a valuable asset to the Oracle community.
Chris Saxon
December 01, 2017 - 4:27 pm UTC

Thanks, happy to help :)

And yes, if you want this behaviour to change then submitting an ER is a good place to start! Or, perhaps more accurately, the chances of this changing are much lower if you don't submit one ;)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.