alter session set optimizer_max_permutations=80000 : OPTIMIZER_MODE « SQL PLUS Session Environment « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. SQL PLUS Session Environment
3. OPTIMIZER_MODE

SQL> create table t as select mod(object_id,10) id, a.* from all_objects a;
SQL>
SQL> analyze table t compute statistics
  2  for table
  3  for columns id;
SQL>
SQL>
SQL>
SQL> alter session set optimizer_max_permutations=80000;
SQL>
SQL> explain plan for
  2  select count(*)
  3    from t t1, t t2, t t3, t t4, t t5, t t6
  4   where t1.id = t2.id
  5     and t1.id = t3.id
  6     and t1.id = t4.id
  7     and t1.id = t5.id
  8     and t1.id = t6.id;
SQL>
SQL> set autotrace off
SQL>
SQL>
SQL> drop table t;