How to install Oracle Database 10g?

There are many step-by-step documents available on the net which guide you through the installation process, but I have come across a video on YouTube.com which demonstrates how to install Oracle Database 10g on Windows Operating System.

The installation procedure is divided into four clips.

Here are the links:

Part 1

Part 2

Part 3

Part 4

This link shows how to create a table.

:-)

How much expensive are Indexes?

Indexes are used to enhance expensive queries to run more quickly. They provide faster access path to table data. But, there is a trade-off using indexes, DML statements against the table would consume more time as all indexes on the table have to be updated during the DML.

Here is a small test I performed to see how index existence would affect the DML statements. For the test case, I create a small table without indexes, inserted 100,000 records and measure the time taken. I repeat the same test but this time with indexes on all columns. The difference in timing will show us, how much expensive are our indexes.

Test 1: Without Indexes

SQL> set serveroutput on 
SQL> 
SQL> drop table t purge;

Table dropped.

SQL> create table t(a number, b varchar2(30), c date);

Table created.

SQL> declare 
  2   x number;
  3 begin
  4   x := dbms_utility.get_time;
  5   for i in 1..100000 loop 
  6     insert into t values(i, 'value = ' || i, sysdate + mod(i,365));
  7   end loop;
  8   dbms_output.put_line('Time taken WITHOUT indexes : ' ||to_char(dbms_utility.get_time - x));
  9 end; 
 10 /

Time taken WITHOUT indexes : 475

PL/SQL procedure successfully completed.

Now, repeat the same test by creating indexes on all the three columns.

Test 2: With Indexes:

SQL> drop table t purge; 

Table dropped.

SQL> 
SQL> create table t(a number, b varchar2(30), c date); 

Table created. 

SQL> 
SQL> 
SQL> create index t_idx1 on t(a); 

Index created. 

SQL> 
SQL> create index t_idx2 on t(b); Index created. 

SQL> 
SQL> create index t_idx3 on t(c); 

Index created. 

SQL> declare
  2   x number;
  3 begin
  4   x := dbms_utility.get_time;
  5   for i in 1..100000 loop
  6     insert into t values(i, 'value = ' || i, sysdate + mod(i,365));
  7   end loop;
  8   dbms_output.put_line('Time taken *WITH* indexes : ' ||to_char(dbms_utility.get_time - x));
  9 end;
 10 /

Time taken *WITH* indexes : 1006 

PL/SQL procedure successfully completed.

Well, it’s evident from the above tests that having too many indexes surely affects performance of DML statements. When I had three indexes, time taken to process 100,000 records was more than double compared to process the same number of records without indexes.

Moral of the test is to create indexes when required and avoid over-creating them. Oracle documentation discusses some guidelines of using and managing indexes.

Happy reading !!!

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 !!!