Compare data in two tables

Using a GROUP BY and some arithmetic it is possible to improve the performance of SQL comparing data of two tables. Even better, in Oracle this can be achieved with the PARTITION BY analytic function.
Below is an example comparing two tables (TAB1, TAB2) with two columns (col1 number, col2 varchar2) using a PARTITION BY analytic function:

create table tab1 (col1 number, col2 varchar2(3));
create table tab2 (col1 number, col2 varchar2(3));
 
insert into tab1 (col1, col2) values (3, 'ccc');
insert into tab1 (col1, col2) values (1, 'aaa');
insert into tab2 (col1, col2) values (1, 'aaa');
insert into tab2 (col1, col2) values (2, 'aaa');
 
select * from 
 -- list all results
 -- change to "select count(*) from" for a count of differences
(
   select rid, -- ROWID
   col1, -- column 1 of table to compare
   col2, -- column 2 of table to compare
   sum(summ) over (partition by col1, col2) summ
 -- must list all columns to compare in the PARTITION BY
   from
   (select 'In TAB1, NOT in TAB2' tab,
     rowid rid, -- need to alias ROWID
 -- list all/some columns of table to compare
     col1, -- column 1 of table to compare
     col2, -- column 2 of table to compare
 ------
     1 summ 
 -- summ is used for sum() operation,
 -- a matched row from other table will sum to 0
   from tab1
     UNION ALL 
 -- change to UNION for unique rows only
   select 'In TAB2, NOT in TAB1' tab,
     rowid rid, -- need to alias ROWID
 -- list all/some columns of table to compare
     col1, -- column 1 of table to compare
     col2, -- column 2 of table to compare
 ------
     -1 summ 
 -- summ used for sum() operation,
 -- a matched row from other table will sum to 0
   from tab2)
)
-- all the rows that didn't have a match have a non-zero sum()
where summ != 0
/   

RID                      COL1 COL       SUMM
------------------ ---------- --- ----------
AAAWK9AAGAAADNHAAB          2 aaa         -1
AAAWK8AAGAAADM/AAA          3 ccc          1

------------------------------------------------------------------------------
| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |     4 |   164 |     7  (15)| 00:00:01 |
|*  1 |  VIEW                 |      |     4 |   164 |     7  (15)| 00:00:01 |
|   2 |   WINDOW SORT         |      |     4 |   124 |     7  (15)| 00:00:01 |
|   3 |    VIEW               |      |     4 |   124 |     6   (0)| 00:00:01 |
|   4 |     UNION-ALL         |      |       |       |            |          |
|   5 |      TABLE ACCESS FULL| TAB1 |     2 |    56 |     3   (0)| 00:00:01 |
|   6 |      TABLE ACCESS FULL| TAB2 |     2 |    56 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("SUMM"<>0) 

Two full table scans and a SORT. Compare this to what traditionally people have used:

(
 select 'IN TAB1, NOT TAB2', col1, col2
   from tab1
   MINUS
 select 'IN TAB1, NOT TAB2', col1, col2
   from tab2
 )
 UNION ALL
 (
 select 'IN TAB2, NOT TAB1', col1, col2
   from tab2
   MINUS
 select 'IN TAB2, NOT TAB1', col1, col2
   from tab1
 )
 /  

'INTAB1,NOTTAB2'        COL1 COL
----------------- ---------- ---
IN TAB1, NOT TAB2          3 ccc
IN TAB2, NOT TAB1          2 aaa

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     4 |   128 |    12  (75)| 00:00:01 |
|   1 |  UNION-ALL           |      |       |       |            |          |
|   2 |   MINUS              |      |       |       |            |          |
|   3 |    SORT UNIQUE       |      |     2 |    32 |            |          |
|   4 |     TABLE ACCESS FULL| TAB1 |     2 |    32 |     3   (0)| 00:00:01 |
|   5 |    SORT UNIQUE       |      |     2 |    32 |            |          |
|   6 |     TABLE ACCESS FULL| TAB2 |     2 |    32 |     3   (0)| 00:00:01 |
|   7 |   MINUS              |      |       |       |            |          |
|   8 |    SORT UNIQUE       |      |     2 |    32 |            |          |
|   9 |     TABLE ACCESS FULL| TAB2 |     2 |    32 |     3   (0)| 00:00:01 |
|  10 |    SORT UNIQUE       |      |     2 |    32 |            |          |
|  11 |     TABLE ACCESS FULL| TAB1 |     2 |    32 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Four full table scans, four sorts. For a large table the PARTITION BY wins.

11g Password Expiry

Since 11gR1 Oracle have implemented good security practice by enforcing regular updating of passwords through a default password expiry of 180 days.  

11gR1 Parameters Used to Secure User Accounts 

11gR2 Parameters Used to Secure User Accounts 


As good as this sounds may spell a time bomb (sleeper) for some applications with hard coded passwords. For example Application Servers with no procedures in place for regular password changes. Some may not even deal with the grace period message warning of an impending lock out.

When logging into an account that has entered a GRACE period the following message is displayed

SQL> connect username/password
ERROR:
ORA-28002: the password will expire within 3 days

When logging into an account that has passed the EXPIRED period the following message is displayed

SQL> connect username
Enter password:
ERROR:
ORA-28001: the password has expired


Changing password for username
New password:
Retype new password:
Password changed
Connected.
SQL>

Finding user accounts that have expired or grace passwords:

select username, account_status, expiry_date from dba_users

USERNAME        ACCOUNT_STATUS                   EXPIRY_DATE
------------------------------ --------------------------------
USER1           EXPIRED                          11-JAN-12
USER2           EXPIRED(GRACE)                   01-FEB-12
USER3           EXPIRED(GRACE)                   02-FEB-12

The defaults are defined in the user's PROFILE. See these default LIMIT column values:

select * from dba_profiles where resource_name in ('PASSWORD_LIFE_TIME','PASSWORD_GRACE_TIME');

PROFILE       RESOURCE_NAME                    RESOURCE LIMIT
------------- -------------------------------- --------------
DEFAULT       PASSWORD_LIFE_TIME               PASSWORD 180
DEFAULT       PASSWORD_GRACE_TIME              PASSWORD 7

To change the default profile settings (not recommended):

alter profile default limit password_life_time unlimited;
alter profile default limit password_grace_time unlimited;

To un-expire an already expired where the password is not known in 11g+ use the following:

alter user identified by values ‘FD8C3D14F6B60015’;

As of 11g the DBA_USERS table no longer contains encrypted passwords. Use the SYS.USER$ table and the NAME, PASSWORD columns. 

select name, password, ltime from user$ order by name, exptime;

Verify the account status in the DBA_USERS table:

select username, account_status, expiry_date from dba_users where expiry_date is null;

USERNAME            ACCOUNT_STATUS                   EXPIRY_DATE
------------------- -------------------------------- -----------
USER1               OPEN
USER2               OPEN
USER3               OPEN

Faster access to DUAL

Since Oracle 10g there is a quicker way to access the DUAL table. Many applications use this table such as a "ping" from an application server. Below demonstrates how to SELECT from it over the "old" DUAL table. The difference is 0 consistent gets over 3 consistent gets using the old method. This may not sound like much but adds up for an application executing thousands of these requests in a short span of time.

In order to use FAST DUAL avoid using "SELECT * FROM DUAL", if possible use "SELECT 1 FROM DUAL".

Example:

SQL> set autotrace traceonly timing on
SQL> select * from dual;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
------------------------------------------------------------

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select 1 from dual;

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

----------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
----------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
404 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select * from dual;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 272002086

------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| DUAL | 1 | 2 | 2 (0)| 00:00:01 |
------------------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
407 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> select 1 from dual;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 1388734953

----------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 |
| 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 |
----------------------------------------------------


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
404 bytes sent via SQL*Net to client
380 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

Some more information on DUAL:

http://www.orafaq.com/wiki/Dual