How to delete multiple records of a key?

If have come across various projects where especially staging tables had no Oracle enabled business key and not data cleansing process in place. It sorrowfully occurred that we were delivered data with multiple records of the same key characteristics and had to cleanse it manually - after those were loaded further. Cleansing can be done using following SQL skeleton.Be aware that we rely on none of the business key columns contain NULL!

To inspect

with BASE
     as (  select *
             from [TABLE NAME]
            where [FILTER])
   , TUPS
     as (  select [BUSINESS KEY COLUMNS]
             from BASE
         group by [BUSINESS KEY COLUMNS]
           having count(*) > 1)
  select /*+ parallel(4) */
         B.*
    from BASE B
         inner join TUPS on (B.[BUSINESS KEY COLUMNS]) in ((TUPS.[BUSINESS KEY COLUMNS]))
order by B.[BUSINESS KEY COLUMNS];

To delete

delete /*+ parallel(4) */
       from [TABLE NAME]
      where rowid in
               (
                  with NUM_RID
                       as (select rowid as RI
                                , [BUSINESS KEY COLUMNS]
                                , row_number()
                                  over(
                                       partition by [BUSINESS KEY COLUMNS]
                                       order by [SORTER COLUMN] [SORT ORDER]
                                      )
                                     as RN
                             from [TABLE NAME]
                            where [FILTER])
                  select RI
                    from NUM_RID
                   where RN > 1
               );

To terminate

merge into [TABLE NAME] DEST
using (
   select T.rowid
        , row_number()
          over(
                partition by [BUSINESS KEY COLUMNS]
                order by [SORTER COLUMN] [SORT ORDER]
              )
             as RN
     from [TABLE NAME] T
    where 1 = 1
      and [FILTER]
) SRC
on (dest.rowid = src.rowid and src.RN > 1)
when matched then
   update set dest.[VALIDITY TO COLUMN] = TO_DATE('-4701-01-01', 'SYYYY-MM-DD')
            , [MARKER CLAUSE];

How to find the shortest distance to a location? (Nearest neighbour problem)

Setup

We have data containing coordinates of points lets say dwellings (about 1.6 millions) and like wise of post offices (1300). To each dwelling we want to know the nearest post office.

Problem

Brute force would be to cross join the sets getting a Cartesian product to calculate the distance of each dwelling with each post office. Thus, the data set comprised of 2.08 * 10^9 records. This probably would be either very slow or the tablespaces would blow to a ORA-01652.

Proposed solution

This solution is a quite able thing. The problem I had with ORA-04036 was due to a counter erroneously not being increased making the square not increase such that recursion never exited... dooo. :-)

Repartitioning an exising table

Cool article on that.

Wrapper over the dict tables to make the content lower case

create or replace view DICT_MINUSCULE
as
   select lower(TABLE_NAME) as TAB, lower(COMMENTS) as COMS from DICTIONARY;

grant select on DICT_MINUSCULE to public;


create or replace view DICT_COLS_MINUSCULE
as
   select lower(TABLE_NAME) as TAB
        , lower(COLUMN_NAME) as COL
        , lower(COMMENTS) as COMS
     from DICT_COLUMNS;

grant select on DICT_COLS_MINUSCULE to public;

ORA-12704: Character Set Mismatch

If you try to compare or union varchar2 with nvarchar2 directly with each other you will get mentioned ORA-12704. Astonishingly enough I did get it under 12c also with following query!

with V
     as (  select 'VARCHAR2_TABLE' as SRC
                , VARCHAR2_ATTRIBUTE as ATTRIBUTE_VALUE
                , count(*) as NUM
                , count(distinct VARCHAR2_ATTRIBUTE) as NUM_DIST_TRACKINGNUMBER
             from VARCHAR2_TABLE
         group by VARCHAR2_ATTRIBUTE)
   , NV
     as (  select 'NVARCHAR2_TABLE' as SRC
                , cast(NVARCHAR2_ATTRIBUTE as varchar2(4000))
                     as ATTRIBUTE_VALUE
                , count(*) as NUM
                , count(distinct VKOR_BESTELLNUMMER) as NUM_DIST_TRACKINGNUMBER
             from NVARCHAR2_TABLE
         group by cast(NVARCHAR2_ATTRIBUTE as varchar2(4000)))
select * from V
union all
select * from NV;

I have no work around so far.