Monday, December 17, 2018

Find the Most Frequent Values

To find the most frequent values, we can use STATS_MODE function. The following query shows areacode in state Missouri.
SQL> select areacode from T_PHONE_AREA where state='Missouri' order by 1;
  AREACODE
----------
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       314
       417
       417
       573
       573
       573
       636
       636
       636
       636
       636
       636
       660
       816
       816
       816
       816
       816
       816
       816
       816
       816
       816

34 rows selected.
In the following query, stats_mode(areacode) returns the areacode 314 that is the most frequent value.
SQL> select stats_mode(areacode) from T_PHONE_AREA where state='Missouri';

STATS_MODE(AREACODE)
--------------------
                 314

Remove the Last Word From a String Using Oracle SQL

I use the following query to remove the last word of a sentence.
with tbl as (select 'Boston city' as name  from dual)
select  name, substr(name, 1, instr(name,' ',-1)-1 ) simple_name  from tbl;

NAME        SIMPLE_NAME
----------- -----------
Boston city Boston     

Find Out Table Columns That Are Indexed

select index_name, column_name from USER_IND_COLUMNS where table_name='MYTABLE'

Oracle Function Based Index is Handy

In table t_my_table, column name is in lower case. However, I want to join this table with another table where names are in upper cases. I create a function based index.
create index t_my_tablei on t_my_table(upper(name));
That way, I don't to create another column or table that contains upper(name) and create index on it. When I join the two tables based on upper(a.name) = b.name, function based index upper(a.name) is used and it is fast.
select a.*, b.* from t_my_table a, my_another_table b where upper(a.name) = b.name;

Sunday, December 16, 2018

Amazon RDS Oracle Instance Running Out of Disc Space

My Oracle database instance on Amazon RDS runs out of disc space. I add more of them by modifying the instance and add extra disc space. This is the link to instructions.

Saturday, December 15, 2018

Roulette Wheel Selection Using SQL

Roulette wheel selection is a very useful algorithm found in many applications such as Genetic Algorithm(GA). In GA solutions with higher fitness values are given larger probabilities of being selected to produce children, just like natural evolution. I implemented an Oracle SQL version of the Roulette wheel selection algorithm.
The first step is to calculate for each record the cumulative value for the variable that the selection will be based on, such as fitness function, probability or other. I used sum() over(order by) analytics function. Make sure the "order by" is using a unique key so that the cumulative value is also unique.
 create table tbl as select id, num, sum(num) over(order by id) as cum_count
  from t_mydata;

The following is the roulette wheel selection scripts.
create table t_rw(sel number);

declare
  mx number;
  rnd  number;
  x number;
begin
   select max(cum_count) into mx from tbl;
  for i in 1..10000 loop
     execute immediate 'select ora_hash(:1,:2) from dual '
          into rnd using i, mx;
     select min(cum_count) into x from tbl where cum_count >= rnd;
     insert into t_rw(sel) values(x);
     end loop;
end;

create view v_selected as select a.* from tbl a, t_rw b where a.cum_count=b.sel;

In the above scripts, ora_hash() generates a uniformly distributed random number between 0 and maximum cum_count. The selected cum_count is inserted into t_rw. The final result is the view v_selected which is based on the inner join of table tbl and t_rw.

Tuesday, December 04, 2018

Generate Serial Number for Existing Table

Table T_REVIEW has column id as the unique key and dt as the timestamp. I want to add a serial number to the table based on the order by dt and id. In the following scripts, I use window function row_number to generate the serial number and update the table.
alter table t_review add(seq number);

update t_review a set seq= (
with tbl as (select id, row_number() over(order by dt, id) rnk from t_review)
select rnk from tbl b 
where b.id=a.id
);

Incrementally Add New Records to Table

I have a table t_review that stores historical records including key sid and timestamp dt. Every day, more records come into table t_new. I use the following scripts to add those new records identified by sid in t_new to t_review.
begin
insert into t_review(sid, dt) select sid, sysdate from 
 (select sid from t_new minus select sid from t_review);
commit;
end;