What is the smallest number divisible by each of the numbers 1 to 20?

This question appeared on Project Euler. I thought of giving it a try using PL/SQL. Before I started to code, I thought of simplifying so that I can reach to the solution in most efficient way.

Any number which is divisible 20 is also divisible by 10, 5 and 1. Similarly, any number divisible by 18 is also divisible by 9. Following is the list of numbers:

Number	Divisible by
20	10, 5, 1
18	9, 1
16	8, 4, 2, 1
14	7, 1
12	6, 3, 2, 1
10	5, 1
8	4, 2, 1
6	3, 2, 1
4	2, 1

Any number divisible by 11 through 20 is also divisible by 1 through 10. So, basically we need to find a number which is divisible by all the number starting from 11 to 20.

We can easily write a PL/SQL code for this, but by what number should the loop increment by. Logically, any number which is divisible by each of the numbers 11 to 20 should be a multiple of 20. So, we need to increment by loop by 20.

Now that the problem is simplified, we can start our PL/SQL code.

SQL> set serveroutput on

SQL>

SQL> set timing on

SQL>

SQL> Declare

2 l_Result Boolean := FALSE;

3 l_Incr Number := 20;

4 l_Solution Number := 0;

5 Begin

6 while NOT l_Result loop

7 l_Solution := l_Solution + l_Incr;

8 If Mod(l_Solution, 11) = 0 And

9 Mod(l_Solution, 12) = 0 And

10 Mod(l_Solution, 13) = 0 And

11 Mod(l_Solution, 14) = 0 And

12 Mod(l_Solution, 15) = 0 And

13 Mod(l_Solution, 16) = 0 And

14 Mod(l_Solution, 17) = 0 And

15 Mod(l_Solution, 18) = 0 And

16 Mod(l_Solution, 19) = 0 And

17 Mod(l_Solution, 20) = 0 Then

18 l_Result := TRUE;

19 End If;

20 end loop;

21 Dbms_output.put_line('Iterations : ' || l_Solution/l_Incr);

22 Dbms_output.put_line('Smallest Number is : ' || l_Solution);

23 End;

24 /

Iterations : 11639628

Smallest Number is : 232792560

PL/SQL procedure successfully completed.

Elapsed: 00:00:10.98

SQL>

“232792560” is the smallest number which is divisible by each of the numbers 1 to 20 and to reach to the solution we performed “11639628” iterations.

Please share your views, if you think we can further simply or optimize this problem and reach to an efficient solution.

Happy reading !!!

Practicing Block Recovery in Oracle Database

Dear all,

During backup and recovery practice sessions, we often struggle to perform block recovery scenario. This is because we find it difficult to corrupt an Oracle block.

I performed this test on Oracle 10g Release 2 (10.2.0.1) on Windows XP and for the purpose of this test we need to keep our database in archivelog mode. In this article, I will discuss how to corrupt an Oracle data block, but before beginning this discussion, I would like to answer:

Why to corrupt an Oracle Block?

We will be corrupting an Oracle block in order to practice recovery procedures involved when one encounters a Block Corruption in a production environment. If a block gets corrupted in any of our production databases we will be in a position to rectify and correct the error instead of wandering for help.

This is purely for educational purpose and please do not practice this on any of your production/development/testing databases, rather create a new database for this purpose and practice it there.

For the purpose of this test, I have created a separate tablespace and a new schema.

SQL> create tablespace corrupt_ts datafile 'c:\mydb\data\corrupt01.dbf' size 10m; 
Tablespace created. 
SQL>
SQL> create user test identified by test default tablespace corrupt_ts; 
User created. 
SQL>
SQL> grant connect, resource to test;
Grant succeeded. 
SQL>

Create and populate test table with dummy data as shown:

SQL> conn test/test
Connected.
SQL>
SQL> create table t1 as select rownum rno, object_name from all_objects
  2  where object_name like 'AQ%';

Table created.

SQL> select count(*) from t1;

  COUNT(*)
----------
        42

Insert a record into this table which we will be corrupting:

SQL> insert into t1 values (99, 'LET ME CORRUPT');

1 row created.

SQL> commit;

Commit complete.

Let us take RMAN full database backup before we corrupt the block.

RMAN> backup format 'c:\mydb\rman\fulldb_%U' database plus archivelog;

Starting backup at 01-FEB-08
current log archived
:
:
piece handle=C:\MYDB\RMAN\FULLDB_0LJ7LIML_1_1 tag=TAG20080201T234641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:28
Finished backup at 02-FEB-08

Starting backup at 02-FEB-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=105 recid=105 stamp=645581560
channel ORA_DISK_1: starting piece 1 at 02-FEB-08
channel ORA_DISK_1: finished piece 1 at 02-FEB-08
piece handle=C:\MYDB\RMAN\FULLDB_0MJ7LINS_1_1 tag=TAG20080202T001242 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04
Finished backup at 02-FEB-08

RMAN>

Take the tablespace offline so that we can make changes to the datafile. There are many freeware and shareware Hex Editors available in the market. I am using UltraEdit editor to make changes in our datafile.

SQL> alter tablespace corrupt_ts offline;

Tablespace altered.

Open datafile “'c:\mydb\data\corrupt01.dbf” using UltraEdit (press “Ctrl+h” to toggle between Hex Mode). Search for our record entry “LET ME CORRUPT” in the file and changed “CORRUPT” to “NORRUPT” and save the file and close UltraEdit. I just changed “C” to “N”.

Bring back the tablespace to online mode.

SQL> alter tablespace corrupt_ts online;

Tablespace altered.

You may notice that Oracle doesn’t complain when it brings the datafile online because the file header wasn’t modified. Oracle will complain only when it tries to access the corrupt blocks. Let’s see what happens when we try to query table “T1”.

SQL> conn test/test
Connected.
SQL> select * from t1;

       RNO OBJECT_NAME
---------- ------------------------------
         1 AQ$_AGENT
         2 AQ$_DEQUEUE_HISTORY
          :
          :

          30 AQ$_JMS_NAMEARRAY
ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 13)
ORA-01110: data file 6: 'C:\MYDB\DATA\CORRUPT01.DBF'


30 rows selected.

Query returns 30 records and then complains of block corruption in file 6. Block numbered 13 is being reported as corrupt. Let us see what all blocks are corrupt in “corruption01.dbf” datafile by running dbv utility.

C:\ora10g\BIN>dbv file=C:\MYDB\data\corrupt01.dbf blocksize=8192

DBVERIFY: Release 10.2.0.1.0 - Production on Mon Feb 4 00:00:11 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

DBVERIFY - Verification starting : FILE = C:\MYDB\data\corrupt01.dbf
Page 13 is marked corrupt
Corrupt block relative dba: 0x0180000d (file 6, block 13)
Bad check value found during dbv:
Data in bad block:
 type: 6 format: 2 rdba: 0x0180000d
 last change scn: 0x0000.0039aa9f seq: 0x3 flg: 0x06
 spare1: 0x0 spare2: 0x0 spare3: 0x0
 consistency value in tail: 0xaa9f0603
 check value in block header: 0x85b0
 computed block checksum: 0x1b00

DBVERIFY - Verification complete

Total Pages Examined         : 1280
Total Pages Processed (Data) : 4
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 1264
Total Pages Marked Corrupt   : 1
Total Pages Influx           : 0
Highest block SCN            : 3779231 (0.3779231)
C:\ora10g\BIN>

This utility scans all the blocks in a given datafile and outputs the corrupt ones. In my case, I have one block marked as corrupt. Make a note of all the corrupt blocks as we need to recover them to previous state.

Start RMAN session and recover all the corrupt blocks. The beauty of RMAN is that it leaves the entire datafile online except the corrupted blocks and we need to recover only those corrupt blocks instead of entire datafile.

 
RMAN> blockrecover datafile 6 block 13;

Starting blockrecover at 04-FEB-08
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=44 devtype=DISK

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece C:\MYDB\RMAN\FULLDB_0KJ7LH72_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=C:\MYDB\RMAN\FULLDB_0KJ7LH72_1_1 tag=TAG20080201T234641
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:36

starting media recovery

archive log thread 1 sequence 105 is already on disk as file C:\MYDB\FRA\MYDB\ARCHIVELOG\2008_02_02\O1_MF_1_10
5_3T72T48S_.ARC
archive log thread 1 sequence 106 is already on disk as file C:\MYDB\FRA\MYDB\ARCHIVELOG\2008_02_03\O1_MF_1_10
6_3TD97K0Z_.ARC
media recovery complete, elapsed time: 00:00:35
Finished blockrecover at 04-FEB-08

RMAN>

RMAN reports success of block recovery command. Let us query the table again by logging in to SQL*Plus:

 
SQL> select * from t1;

       RNO OBJECT_NAME
---------- ------------------------------
         1 AQ$_AGENT
         2 AQ$_DEQUEUE_HISTORY
          :
          :

        41 AQ$_JMS_ARRAY_ERROR_INFO
        42 AQ$_JMS_ARRAY_ERRORS
        99 LET ME CORRUPT

43 rows selected.

SQL>

Wow, the query runs successfully and our original record is restored. Similar article on block recovery in UNIX environment can be found here.

Happy recovery (block)!!!

Performance Comparison of Different Datatypes

Dear readers,

Oracle database has a rich collection of datatypes and it offers different datatypes for different needs. Basically, datatype can be either scalar or non-scalar. A scalar type contains an atomic value, whereas a non-scalar contains a set of values. Examples of scalar datatypes include number, varchar2, etc, while that of non-scalar could be a collection.

Apart from the rich collection of data types available in Oracle database, PL/SQL offers few more datatypes like BINARY_INTEGER and PLS_INTEGER. These datatypes can be used within a PL/SQL block.

Often, these datatypes provide better performance over other and one should use them where appropriate.

I picked up couple of datatypes and performed 100,000,000 iterations to compare processing time of these datatypes. Amazingly there was a huge difference of processing time between datatypes. For some of the datatypes the processing time was 10 times lower than their counterparts.

Below is the script and its output, which I ran against Oracle Database 10g Release 2 (10.2.0.3).

set serveroutput on

declare
  l_time number;

  l_bi   BINARY_INTEGER;
  l_pi   PLS_INTEGER;
  l_bf   BINARY_FLOAT;
  l_bd   BINARY_DOUBLE;
  l_ntn  NATURALN := 0;

  l_num  NUMBER;
  l_int1 INTEGER;
  l_int2 INT;
  l_sint SMALLINT;
  l_dec1 DECIMAL;
  l_dec2 DEC;
  l_real REAL;
  l_flt  FLOAT;
  l_nrc  NUMERIC;
  l_dpr  DOUBLE PRECISION;

begin
  l_time := dbms_utility.get_time;
  dbms_output.put_line(chr(10)  chr(10) 
      'Time taken for 100,000,000 iterations for : '  chr(10)  chr(10) );

  for i in 0..99999999 loop
    l_bi := i;
  end loop;
  dbms_output.put_line(rpad('BINARY_INTEGER', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_pi := i;
  end loop;
  dbms_output.put_line(rpad('PLS_INTEGER', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_ntn := i;
  end loop;
  dbms_output.put_line(rpad('NATURALN', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_num := i;
  end loop;
  dbms_output.put_line(rpad('NUMBER', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_int1 := i;
  end loop;
  dbms_output.put_line(rpad('INTEGER', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_int2 := i;
  end loop;
  dbms_output.put_line(rpad('INT', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_sint := i;
  end loop;
  dbms_output.put_line(rpad('SMALLINT', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_dec1 := i;
  end loop;
  dbms_output.put_line(rpad('DECIMAL', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_dec2 := i;
  end loop;
  dbms_output.put_line(rpad('DEC', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_real := i;
  end loop;
  dbms_output.put_line(rpad('REAL', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_flt := i;
  end loop;
  dbms_output.put_line(rpad('FLOAT', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_nrc := i;
  end loop;
  dbms_output.put_line(rpad('NUMERIC', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

  l_time := dbms_utility.get_time;
  for i in 0..99999999 loop
    l_dpr := i;
  end loop;
  dbms_output.put_line(rpad('DOUBLE PRECISION', 20, ' ')
                   ' = '  lpad(to_char(dbms_utility.get_time - l_time), 4, ' '));

end;

Time taken for 100,000,000 iterations for :


BINARY_INTEGER......  172
PLS_INTEGER.........  189
BINARY_FLOAT........  611
BINARY_DOUBLE.......  473
NATURALN............  650
NUMBER.............. 1047
INTEGER............. 1839
INT................. 1841
SMALLINT............ 1842
DECIMAL............. 1933
DEC................. 1900
REAL................ 1051
FLOAT............... 1047
NUMERIC............. 1847
DOUBLE PRECISION.... 1055

PL/SQL procedure successfully completed.

As you may notice, PLS_INTEGER and BINARY_INTEGER datatypes are nearly 7 times faster than popularly used NUMBER datatype. This is because, these datatypes require less storage and they use hardware arithmetic whereas, NUMBER and INTEGER variables require calls to library routines.

Moreover, Oracle Documentation requests PL/SQL developers to avoid using INTEGER and NATURALN datatypes where performance is critical. According to the documentation, variables of these types require extra checking at run time, each time they are used in a calculation.

So, use of right datatype will really pay off in terms of performance, thus, better choose the right datatype for your operation.

References:

For more information on these datatypes and their magnitude ranges please refer to Oracle Documentation:

Use PLS_INTEGER for Integer Arithmetic.

Use BINARY_FLOAT and BINARY_DOUBLE for Floating-Point Arithmetic.

Oracle Database 10g Release 2: SQL Reference.

Oracle Database 10g Release 2: PL/SQL User’s Guide and Reference.

Happy reading !!!