ORDERED Hint

Learned something new from Oracle product documentation. Its about ORDERED Hint.causes Oracle to join tables in the order in which they appear in the FROM clause. If you omit the ORDERED hint from a SQL statement performing a join, then the optimizer chooses the order in which to join the tables. A quick demonstration about that.

test@9iR2> set autotrace traceonly explain;
test@9iR2>
test@9iR2>
test@9iR2> SELECT e.FIRST_NAME, e.last_name, d.department_name, j.job_title
  2  from hr.employees e,
  3       hr.departments d,
  4       hr.jobs j
  5  WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
  6  and   e.job_id = j.job_id
  7  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=106 Bytes=7420)
   1    0   HASH JOIN (Cost=8 Card=106 Bytes=7420)
   2    1     HASH JOIN (Cost=5 Card=107 Bytes=5778)
   3    2       TABLE ACCESS (FULL) OF 'JOBS' (Cost=2 Card=19 Bytes=513)
   4    2       TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Bytes=2889)
   5    1     TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=2 Card=27 Bytes=432)

Now using ORDERED hint causes Oracle to join tables in the order in which they appear in the FROM clause

test@9iR2> SELECT /*+ ordered */ e.FIRST_NAME, e.last_name, d.department_name, j.job_title
  2  from hr.employees e,
  3       hr.departments d,
  4       hr.jobs j
  5  WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID
  6  and   e.job_id = j.job_id
  7  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=106 Bytes=7420)
   1    0   HASH JOIN (Cost=8 Card=106 Bytes=7420)
   2    1     HASH JOIN (Cost=5 Card=106 Bytes=4558)
   3    2       TABLE ACCESS (FULL) OF 'EMPLOYEES' (Cost=2 Card=107 Bytes=2889)
   4    2       TABLE ACCESS (FULL) OF 'DEPARTMENTS' (Cost=2 Card=27 Bytes=432)
   5    1     TABLE ACCESS (FULL) OF 'JOBS' (Cost=2 Card=19 Bytes=513)

Skipping Access path using SAMPLE Options

Not something entirely learned new today - but rather a revisited something from Oracle product documentation Manuals. Its about Access Path Hints.


For access path hints, Oracle ignores the hint if you specify the SAMPLE option in the FROM clause of a SELECT statement.


test@9iR2> create index emp_dept_idx on emp(deptno);

Index created.

test@9iR2> begin
  2     dbms_stats.gather_table_stats(ownname=>USER,tabname=>'EMP',
estimate_percent=>100,cascade=>true);
  3     dbms_stats.gather_table_stats(ownname=>USER,tabname=>'DEPT',
estimate_percent=>100,cascade=>true);
  4  end;
  5  /

PL/SQL procedure successfully completed.

test@9iR2> set autotrace traceonly explain;
test@9iR2> select /*+ index(emp emp_dept_idx) */ empno,ename,sal
  2  from emp
  3  where deptno = 10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=5 Bytes=85)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=5 Bytes=85)
   2    1     INDEX (RANGE SCAN) OF 'EMP_DEPT_IDX' (NON-UNIQUE) (Cost=1 Card=5)


Now introducting SAMPLE clause in SELECT statement skipped by Index Range Scan access path, even Index Hint specified.

test@9iR2> select /*+ index(emp emp_dept_idx) */ empno,ename,sal
  2  from emp sample(95)
  3  where deptno = 10;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=4 Bytes=68)
   1    0   TABLE ACCESS (SAMPLE) OF 'EMP' (Cost=2 Card=4 Bytes=68)

Optimizer Plan Stability - does not resolve object reference

Optimizer Plan Stability does not, by design, resolve object references in the SQL text. It just stores a string, and when it gets another string that matches in the category you have enabled, it will utilize the stored hints

test@9iR2> select *
  2  from scott.emp e,scott.dept d
  3  where e.deptno =d.deptno
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   NESTED LOOPS
   2    1     TABLE ACCESS (FULL) OF 'EMP'
   3    1     TABLE ACCESS (BY INDEX ROWID) OF 'DEPT'
   4    3       INDEX (UNIQUE SCAN) OF 'PK_DEPT' (UNIQUE)
 
This Query could have better execution if it uses HASH join rather than Nested loop join.

test@9iR2> select /*+  use_hash(e) use_hash(d) */ *
  2  from scott.emp e,scott.dept d
  3  where e.deptno =d.deptno
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9594)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=9594)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2460)

We would like the applications that issue the first query, to get the hash join plan rather than the nested loop plan, but we need this to happen without changing the application code.

Well, since query plan outlines are based on character string comparisons, we can accomplish this using a different schema and some hinted views

test@9iR2> grant select on scott.emp to outline_test;

Grant succeeded.

test@9iR2> grant select on scott.dept to outline_test;

Grant succeeded.

test@9iR2> connect outline_test/outline_test
Connected.
outline_test@9iR2> create or replace view emp
  2  as
  3  select /*+ use_hash(emp) */ *
  4  from scott.emp emp;

View created.

outline_test@9iR2> create or replace view dept
  2  as
  3  select /*+ use_hash(dept) */ *
  4  from scott.dept dept;

View created.

outline_test@9iR2> set autotrace traceonly explain;
outline_test@9iR2> select *
  2  from emp e,dept d
  3  where e.deptno =d.deptno
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9594)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=9594)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2460)

Now we generate a stored outline for our application query.

outline_test@9iR2> alter session set create_stored_outlines=emp_test_outline;

Session altered.

outline_test@9iR2> select *
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4  /
...........................
...........................
...........................
14 rows selected.

outline_test@9iR2> alter session set create_stored_outlines=false;

Session altered.

outline_test@9iR2> SELECT name, sql_text
  2  FROM user_outlines
  3  /  

NAME                                                   SQL_TEXT
------------------------------                 ----------------------------------------------
SYS_OUTLINE_100604172908133     select *
                                                               from emp e,dept d
                                                               where e.deptno = d.deptno


So, in the OUTLINE_TEST schema, we have our hinted views of the base objects and weʹve created
a stored outline of the query we wanted in that Schema. We could drop the views at this point if we wanted to – we have what we want, the stored outline using hash joins. Now, when we log in as TEST again, we see the following:

outline_test@9iR2> connect test/test
Connected.
test@9iR2> alter session set use_stored_outlines=TEST_OUTLINE;

Session altered.

test@9iR2> set autotrace traceonly explain;
test@9iR2> select * from emp e,dept d where e.deptno = d.deptno;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=82 Bytes=9594)
   1    0   HASH JOIN (Cost=5 Card=82 Bytes=9594)
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=82 Bytes=7134)
   3    1     TABLE ACCESS (FULL) OF 'DEPT' (Cost=2 Card=82 Bytes=2460)

test@9iR2> drop outline SYS_OUTLINE_100604173512322;

Outline dropped.

test@9iR2> set autotrace traceonly explain;
test@9iR2> select *
  2  from emp e,dept d
  3  where e.deptno = d.deptno
  4  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     SORT (JOIN)
   3    2       TABLE ACCESS (FULL) OF 'DEPT'
   4    1     SORT (JOIN)
   5    4       TABLE ACCESS (FULL) OF 'EMP'
                              

SQL Test Case Builder - 11g New Features

Oracle Database 11g provides a new diagnostic tool called SQL Test Case Builder.

For most SQL problems, the single most important factor for a speedy bug resolution is to obtain a reproducible test case. However, this is normally the longest and most painful step for customers. The goal of the SQL Test Case Builder (TCB) is to automatically gather as much information as possible related to a SQL incident (problem) and package it in a way that allows a developer or a support engineer to reproduce the problem on his or her own machine quickly. At a very high-level, SQL Test Case Builder can be seen as a way to export a SQL. Currently, Oracle export (expdp) takes a schema or a set of tables and exports all the dependents objects. SQL Test Case Builder provides the same service but takes a SQL statement as input.


The main input of SQL Test Case Builder is a SQL object. A SQL object is defined as the SQL text plus all the information required to compile it on a particular database instance. Logically, a SQL test case appears as a script containing all the necessary commands to recreate the objects, the user, the statistics, and the environment.

How do I use the SQL Test Case Builder?

• Procedure dbms_sqldiag.export_sql_testcase exports a SQL test case for a given SQL statement to a given directory.
• Procedure dbms_sqldiag.import_sql_testcase imports a test case from a given directory

 sh@11G> SELECT sql_text
  2  FROM V$SQL
  3  WHERE SQL_ID ='8khuzqdww5thy'
  4  /

SQL_TEXT
-----------------------------------------------------------------------------------------------------------------------------------
SELECT pr.prod_id, sa.promo_id,count(*) as total_customers from sh.products pr,        sh.sales  sa where pr.prod_id = sa.prod_id g
roup by pr.prod_id, sa.promo_id

sh@11G> DECLARE
  2    l_test_case CLOB;
  3  begin
  4    dbms_sqldiag.export_sql_testcase(
  5        directory =>'DATA_PUMP_DIR',
  6        sql_id => '8khuzqdww5thy',
  7        plan_hash_value => null,
  8        exportEnvironment => true,
  9        exportMetadata => true,
 10        exportData => true,
 11        timeLimit => 3600,
 12        testcase_name =>'QUERY_HR_SCHEMA',
 13        testcase =>l_test_case);
 14  end;
 15  /

PL/SQL procedure successfully completed.   
You have the option to set exportData to TRUE to include the data. For security reason, the user data are not exported by default. Once the test case has been built, you can copy all the files under the export directory to your test environment. Note there is a file called xxxxxxxxmain.xml, which contains the metadata of the test case.

Now importing the Test case can be done as

scott@ORCL> create user test_user identified by test_user default tablespace EXAMPLE quota unlimited on EXAMPLE;

User created.

scott@ORCL> grant connect,resource,dba to test_user;

Grant succeeded.

scott@ORCL> grant read on directory DATA_PUMP_DIR to test_user;

Grant succeeded.

scott@ORCL> grant write on directory DATA_PUMP_DIR to test_user;

Grant succeeded.

scott@ORCL> connect test_user/test_user
Connected.
test_user@ORCL> begin
  2    dbms_sqldiag.import_sql_testcase(
  3      directory=>'DATA_PUMP_DIR',
  4      filename =>'QUERY_HR_SCHEMAmain.xml',
  5      importEnvironment=>true,
  6      importMetadata=>true,
  7      importData=>true,
  8      importDiagnosis=>true);
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:02:13.24
test_user@ORCL> SELECT table_name, status
  2  FROM user_tables
  3  /

TABLE_NAME                     STATUS
------------------------------ --------
PRODUCTS                       VALID
SALES                                VALID 

test_user@ORCL> SELECT pr.prod_id,
  2    sa.promo_id,
  3    COUNT(*) AS total_customers
  4  FROM products pr,
  5    sales sa
  6  WHERE pr.prod_id = sa.prod_id
  7  GROUP BY pr.prod_id,sa.promo_id
  8  /

   PROD_ID   PROMO_ID TOTAL_CUSTOMERS
---------- ---------- ---------------
        17        999            5962
        18        999            9206
        23        999           19022
        25        999           18943
       ............................................... 
       ...............................................
       ...............................................   

212 rows selected

Elapsed: 00:00:01.06
test_user@ORCL>


To verify that the test case is successfully rebuilt, you can just issue an explain command for the problem query. However, if you want to actully run the query, then you need to have the data available.
 

ORA-14652 in Reference Partitioning in Oracle 11g

What I learned today was - a new feature it is Reference Partitioning in Oracle 11g.

Reference partitioning is a new partitioning option in Oracle 11g that allows the partitioning of two related tables to be based on a referential constraint.  In other words, when there is a parent-child relationship between two tables, the parent table can be defined with its partitions.  Subsequently, the child table can be equipartitioned by defining the child table to inherit the partitioning key from the parent table, without the need to duplicate the partition key columns.

scott@11G> create table t(
  2     x number,
  3     y number,
  4     z date ,
  5     constraint t_pk primary key (x)
  6  )
  7  partition by range(z)
  8  (
  9     partition p1 values less than (to_date('01/01/2006','mm/dd/yyyy')),
 10     partition p2 values less than (to_date('02/01/2006','mm/dd/yyyy')),
 11     partition p3 values less than (to_date('03/01/2006','mm/dd/yyyy')),
 12     partition p4 values less than (to_date('04/01/2006','mm/dd/yyyy'))
 13  );

Table created.

scott@11G> create table t1
  2  (
  3     a_c number,
  4     b number,
  5     constraint t1_fk foreign key(a_c) references t(x)
  6  )partition by reference(t1_fk);
)partition by reference(t1_fk)
                        *
ERROR at line 6:
ORA-14652: reference partitioning foreign key is not supported


Referential constraint on child table must be defined on a NOT NULL parent column and a virtual column cannot be part of the partitioning foreign key. Violating any of these limitations will cause an error such as ORA-14652

scott@11G> create table t1
  2  (
  3     a_c number  NOT NULL,
  4     b number,
  5     constraint t1_fk foreign key(a_c) references t(x)
  6  )partition by reference(t1_fk);

Table created. 

scott@11G> SELECT table_name, partition_name, partition_position
  2  FROM user_tab_partitions
  3  WHERE table_name IN('T','T1')
  4  ORDER BY table_name, partition_name
  5  /

TABLE_NAME     PARTITION_NAME        PARTITION_POSITION
-------------------- ------------------------      ------------------
T                              P1                                     1
T                              P2                                     2
T                              P3                                     3
T                              P4                                     4
T1                            P1                                     1
T1                            P2                                     2
T1                            P3                                     3
T1                            P4                                     4

8 rows selected.
 

Shrinking Database Segments Online

Prior to Oracle 10g we have MOVE command to shrink the segments, but that make the Index to UNUSABLE state, but in Oracle 10g we have SHRINK SPACE command to shrink segments without making index to UNUSABLE state.

scott@10GR2> create table t (x number,y char(2000) default '*',z char(2000) default '*');

Table created.

scott@10GR2> insert /*+ append */ into t(x)
  2  select level
  3  from dual
  4  connect by level <= 10000;

10000 rows created.

scott@10GR2> commit;

Commit complete.

scott@10GR2> create index t_ind on t(x) nologging;

Index created.

scott@10GR2> delete from t where x <= 5000;

5000 rows deleted.

scott@10GR2> commit;

Commit complete.

scott@10GR2> alter table t enable row movement;

Table altered.

scott@10GR2> SELECT INDEX_NAME,STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T'
  4  /

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID

scott@10GR2> alter table t move;

Table altered.

scott@10GR2> SELECT INDEX_NAME,STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T'
  4  /

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          UNUSABLE

With  Oracle 10g we can use SHRINK segment rather than MOVE command.

scott@10GR2> alter table t shrink space;

Table altered.
scott@10GR2> SELECT INDEX_NAME,STATUS
  2  FROM USER_INDEXES
  3  WHERE TABLE_NAME ='T'
  4  /

INDEX_NAME                     STATUS
------------------------------ --------
T_IND                          VALID