"On" and "Where" Clause - Difference

Not, entirely learnt newly today but revisited some good old stuffs. Its about the difference between "on" and  "when" clauses in queries.


Let's say we have two table's "T1" and "T2"


create table t1(x number,y number);
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(null,3);


create table t2(x number,y number);
insert into t2 values(1,1);
insert into t2 values(2,2);
insert into t2 values(null,3);
commit;


rajesh@ORA11GR2> select t1.*
  2  from t1 left outer join t2
  3  on t1.x = t2.x
  4  and t1.x is not null
  5  and t2.x is not null;
         X          Y
---------- ----------
         1          1
         2          2
                    3


3 rows selected.


Elapsed: 00:00:00.01
rajesh@ORA11GR2>


Now, rewriting this query using Oracle native sql approch. It return different results.


rajesh@ORA11GR2> select t1.*
  2  from t1 , t2
  3  where  t1.x = t2.x (+)
  4  and t1.x is not null
  5  and t2.x is not null;


         X          Y
---------- ----------
         1          1
         2          2


2 rows selected.


Elapsed: 00:00:00.00
rajesh@ORA11GR2>


The problem is both the queries don't make the logic "the same", there is a huge difference between "on" and "where" 


Query-1 is executed mostly like this.


rajesh@ORA11GR2> select t1.*
  2  from t1 left outer join t2
  3  on (
  4      t1.x = t2.x
  5      and t1.x is not null
  6      and t2.x is not null
  7  );
         X          Y
---------- ----------
         1          1
         2          2
                    3


3 rows selected.


Elapsed: 00:00:00.01
rajesh@ORA11GR2>


So, the correct way of rewriting this query using Oracle native sql approch is to make joins in "ON" clause and filter the results sets using "Where" clause.


rajesh@ORA11GR2> select t1.*
  2  from t1 left outer join t2
  3  on t1.x = t2.x
  4  where t1.x is not null
  5  and t2.x is not null;
         X          Y
---------- ----------
         1          1
         2          2


2 rows selected.


Elapsed: 00:00:00.01
rajesh@ORA11GR2>


"on"     - restrict the row to be selected from DATA SOURCE for Join to be performed.


"when" - restrict the row to be selected from  RESULT SETS to display at client terminal.

Pending Statistics - 11GR2 - Bug !?!?!

Its now possible in Oracle 11g to gather optimizer statistics but not have them published immediately. Instead the statistics gathered for these objects are displayed in USER_*_PENDING_STATS. These statistics will be used by Optimizer if  initialization parameter OPTIMIZER_USE_PENDING_STATS is set to TRUE and running the necessary queries. Once you are happy with the pending statistics you can publish them.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t(
  2     x ,
  3     y
  4  )
  5  as
  6  select rownum, mod(rownum,5)
  7  from dual
  8  connect by level <= 10000;

Table created.

Elapsed: 00:00:00.09
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     cascade => true,
  7     method_opt=>'for all columns size 1');
  8  end;
  9  /
 
PL/SQL procedure successfully completed.

Elapsed: 00:00:00.28
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:35:30 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 2
X                 2

Elapsed: 00:00:00.03
rajesh@ORA11GR2>

Now to setup this Pending Stats we need to call this SET_TABLE_PREFS method defined in DBMS_STATS Api.

rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs(
  3     ownname => user,
  4     tabname => 'T',
  5     pname => 'PUBLISH',
  6     pvalue => 'FALSE');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> 100,
  6     cascade => true,
  7     method_opt=>'for all columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.32
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,last_analyzed
  2  from user_tab_pending_stats
  3  where table_name ='T';
TABLE_NAME                     LAST_ANALYZED
------------------------------ -----------------------
T                              14-apr-2011 12:36:38 am

Elapsed: 00:00:00.03

rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histgrm_pending_stats
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 5
X               255

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

As you see this Pending statistics is available in USER_*_PENDING_STATS and doesn't get published.

rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:35:30 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 2
X                 2

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

But it looks like a BUG in 11GR2 (11.2.0.1.0) When Index comes in place along with Tables. Here is the Testcase to demonstrate that !

rajesh@ORA11GR2> select * from v$version;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t(
  2     x ,
  3     y
  4  )
  5  as
  6  select rownum, mod(rownum,5)
  7  from dual
  8  connect by level <= 10000;

Table created.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>
rajesh@ORA11GR2> create index t_ind on t(y);

Index created.

Elapsed: 00:00:00.10
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> dbms_stats.auto_sample_size,
  6     cascade => true,
  7     method_opt=>'for all indexed columns size 1');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:41:14 am

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 2

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs(
  3     ownname => user,
  4     tabname => 'T',
  5     pname => 'PUBLISH',
  6     pvalue => 'FALSE');
  7  end;
  8  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.gather_table_stats(
  3     ownname =>user,
  4     tabname => 'T',
  5     estimate_percent=> 100,
  6     cascade => true,
  7     method_opt=>'for all indexed columns size 254');
  8  end;
  9  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,last_analyzed
  2  from user_tab_pending_stats
  3  where table_name ='T';

TABLE_NAME                     LAST_ANALYZED
------------------------------ -----------------------
T                              14-apr-2011 12:41:42 am

Elapsed: 00:00:00.03
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name,num_rows,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T';

TABLE_NAME                       NUM_ROWS LAST_ANALYZED
------------------------------ ---------- -----------------------
T                                   10000 14-apr-2011 12:41:14 am

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

This looks fine now. Since Pending statistics doesn't distrubed the Existing statistics. Now see what happens to Histograms.

rajesh@ORA11GR2>
rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histgrm_pending_stats
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 5

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

Latest Histograms Buckets has got updated in USER_TAB_HISTGRM_PENDING_STATS which is fine, but going back to Orginal Histogram data dictionary it got reflected. ( Even if PUBLISH = FALSE)  but it **SHOULD NOT**.

rajesh@ORA11GR2> select column_name,count(*)
  2  from user_tab_histograms
  3  where table_name ='T'
  4  group by column_name;

COLUMN_NAM COUNT(*)
---------- --------
Y                 5

Elapsed: 00:00:00.01
rajesh@ORA11GR2>

This looks like a BUG in 11GR2 and Tom kyte confirms the same in asktom.

Incremental Statistics in 11G

Gathering statistics on partitioned tables consists of gathering statistics at both the table level and partition level. Prior to Oracle Database 11g, adding a new partition or modifying data in a few partitions required scanning the entire table to refresh table-level statistics. Scanning the entire table can be very expensive as partitioned tables are generally very large. However, in Oracle Database 11g this issue has been addressed with the introduction of incremental global statistics, Where Oracle will update the global table statistics by scanning only the partitions that have been changed instead of the entire table if the following conditions hold:

1) the INCREMENTAL value for the partitioned table is set to TRUE;
2) the PUBLISH value for the partitioned table is set to TRUE;
3) the user specifies AUTO_SAMPLE_SIZE for ESTIMATE_PERCENT and AUTO for GRANULARITY when gathering statistics on the table.


rajesh@ORA11GR2>
rajesh@ORA11GR2> create table t(
  2     x ,
  3     y ,
  4     z
  5  )
  6  partition by range(x)
  7  (
  8     partition p1 values less than(2),
  9     partition p2 values less than(3),
 10     partition p3 values less than(4),
 11     partition pmax values less than(maxvalue)
 12  )
 13  as
 14  select level, rpad('*',100,'*'),sysdate
 15  from dual
 16  connect by level <= 5;

Table created.

Elapsed: 00:00:00.07
rajesh@ORA11GR2>
rajesh@ORA11GR2> begin
  2     dbms_stats.set_table_prefs(
  3             ownname =>user,
  4             tabname=>'T',
  5             pname=>'INCREMENTAL',
  6             pvalue=>'TRUE');
  7     dbms_stats.set_table_prefs(
  8             ownname =>user,
  9             tabname=>'T',
 10             pname=>'GRANULARITY',
 11             pvalue=>'AUTO');
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.32
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name, partition_name,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name nulls first;

TABLE_NAME                     PARTITION_NAME                 LAST_ANALYZED
------------------------------ ------------------------------ -----------------------
T                                                             13-apr-2011 01:14:16 pm
T                              P1                             13-apr-2011 01:14:16 pm
T                              P2                             13-apr-2011 01:14:16 pm
T                              P3                             13-apr-2011 01:14:16 pm
T                              PMAX                           13-apr-2011 01:14:16 pm

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> select dbms_stats.get_prefs('INCREMENTAL',user,'T') as incremental,
  2        dbms_stats.get_prefs('GRANULARITY',user,'T') as granularity
  3  from dual;

INCREMENTAL          GRANULARITY
-------------------- --------------------
TRUE                 AUTO

Elapsed: 00:00:00.01
rajesh@ORA11GR2>
rajesh@ORA11GR2> alter table t split partition pmax at (100) into (partition p_100, partition pmax);

Table altered.
Elapsed: 00:00:00.06
rajesh@ORA11GR2>
rajesh@ORA11GR2> insert into t(x,y,z)
  2  select 50,rpad('*',100,'*'),sysdate
  3  from all_objects;

71478 rows created.

Elapsed: 00:00:04.45
rajesh@ORA11GR2> commit;

Commit complete.

Elapsed: 00:00:00.00
rajesh@ORA11GR2>
rajesh@ORA11GR2> exec dbms_stats.gather_table_stats(user,'T');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.14
rajesh@ORA11GR2>
rajesh@ORA11GR2>
rajesh@ORA11GR2> select table_name, partition_name,last_analyzed
  2  from user_tab_statistics
  3  where table_name ='T'
  4  order by partition_name nulls first;

TABLE_NAME                     PARTITION_NAME                 LAST_ANALYZED
------------------------------ ------------------------------ -----------------------
T                                                             13-apr-2011 01:14:40 pm
T                              P1                             13-apr-2011 01:14:16 pm
T                              P2                             13-apr-2011 01:14:16 pm
T                              P3                             13-apr-2011 01:14:16 pm
T                              PMAX                           13-apr-2011 01:14:40 pm
T                              P_100                          13-apr-2011 01:14:40 pm

6 rows selected.

Elapsed: 00:00:00.04
rajesh@ORA11GR2>

ORA-30482: DISTINCT option not allowed for this function

Learnt something newly yesterday, thought of sharing it here.

I want to concatenate a column from multiple rows but i want only the DISTINCT values. so I did wm_concat(column_name) it works great.

rajesh@11GR2> select deptno, wm_concat(distinct ename) as enames
  2  from scott.emp
  3  group by deptno;

 DEPTNO ENAMES
------- --------------------------------------------------
     10 CLARK,KING,MILLER
     20 ADAMS,FORD,JONES,SCOTT,SMITH
     30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Elapsed: 00:00:00.03
rajesh@11GR2>

When i put this SQL inside a Pl/SQL block it ended up with error.

rajesh@11GR2>
rajesh@11GR2> begin
  2     insert into t(x,y)
  3     select deptno, wm_concat(distinct ename) as enames
  4     from scott.emp
  5     group by deptno;
  6  end;
  7  /
        select deptno, wm_concat(distinct ename) as enames
                       *
ERROR at line 3:
ORA-06550: line 3, column 17:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 2, column 2:
PL/SQL: SQL Statement ignored

Elapsed: 00:00:00.06
rajesh@11GR2>

The Solution to this problem is to use Dynamic SQL

rajesh@11GR2> declare
  2     v_sql varchar2(500);
  3  begin
  4     v_sql := 'insert into t(x,y)
  5     select deptno, wm_concat(distinct ename) as enames
  6     from scott.emp
  7     group by deptno ' ;
  8
  9     execute immediate v_sql;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.06
rajesh@11GR2>
rajesh@11GR2> select * from t;
         X Y
---------- --------------------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Elapsed: 00:00:00.03
rajesh@11GR2>
rajesh@11GR2>

Another solution is to use VIEW and hide the construct from Pl/SQL

rajesh@11GR2> truncate table t;

Table truncated.

Elapsed: 00:00:00.15
rajesh@11GR2>
rajesh@11GR2> select * from t;

no rows selected

Elapsed: 00:00:00.01
rajesh@11GR2> create or replace view v
  2  as
  3  select deptno, wm_concat(distinct ename) as enames
  4  from emp
  5  group by deptno;

View created.

Elapsed: 00:00:00.12
rajesh@11GR2>
rajesh@11GR2> begin
  2     insert into t(x,y)
  3     select deptno,enames
  4     from v;
  5  end;
  6  /

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04
rajesh@11GR2>
rajesh@11GR2> select * from t;

         X Y
---------- -----------------------------------------
        10 CLARK,KING,MILLER
        20 ADAMS,FORD,JONES,SCOTT,SMITH
        30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Elapsed: 00:00:00.03
rajesh@11GR2>
rajesh@11GR2>