Introduction –

Partitioning is available in Oracle since 8i (few feature or type) version but in MySQL it is available after 5.1 version. Partitioning allows you to distribute portions of table across a file system (physically at different location). User can define the partitioning of table so that some portion of table can stored at different physical location….

Table is structure which has columns and rows. Partitioning can be applied to rows or volumes.

* Horizontal partitioning ~ Storing different rows of table to different physical location is called as horizontal partitioning of table.
* Vertical partitioning ~ Storing different columns of table to different physical location is called as vertical partitioning of table.

MySQL supports only support “Horizontal partitioning” !!

** Advantages of using partitions ~

a) Smaller and more manageable pieces of data
b) Faster access of data
c) Import / export at the partition Level
d) Each partitions is independent of other
e) Easy to use
f) Reduced recovery time
g) Failure impact is less

1] How to check whether MySQL installed on your system supports ‘partitioning’ ?

* Check MySQL version as…

root@laptop:~# mysqladmin version -u root -p

* Check ‘partition’ plugin is installed or not… ?

mysql> SHOW PLUGINS;
+————+———-+—————-+———+———+
| Name | Status | Type | Library | License |
+————+———-+—————-+———+———+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| partition | ACTIVE | STORAGE ENGINE | NULL | GPL | **
…
…..

+————+———-+—————-+———+———+
10 rows in set (0.00 sec)

mysql>

* Check above output. If you don’t see ‘partition’ entry in above query. Then MySQL version does not support partitioning. If it entry exist then check “have_partitioning” system variable is set to ‘YES’ or not….

mysql> SHOW VARIABLES LIKE ‘%partition%’;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| have_partitioning | YES |
+——————-+——-+
1 row in set (0.00 sec)
Partitioning
mysql>

2] Which storage engines (table type) supports partitioning?

MySQL partitioning engine runs in a separate layer and can interact with any of storage engine. So we can use most of the storage engine as backend/table type to store data. Note we must use same storage engine for all partitions of table.

3] Types of Partitions ~

There are 4 types of partitioning in MySQL or Oracle

a) Range Partitioning
b) List Partitioning
c) Hash Partitioning
d) Key Partitioning

NOTE ~ In any type of partitioning – partitions are always numbered automatically and in sequence starting with 0. When a new row is inserted into a partitioned table, these partition numbers that are used in identifying the correct partition. Also partition names are not case-sensitive. Number of partitions for the table, this must be expressed as a positive, non-zero integer literal with no leading zeroes, and may not be an expression.

MAXVALUE ~ It is an integer value that is always greater than the largest possible integer value (least upper limit).

** Let’s create database ‘mydata’ and create table with each partition type -

mysql> create database mydata;
Query OK, 1 row affected (0.33 sec)

mysql> show databases;
+——————–+
| Database |
+——————–+
| information_schema |
| mydata |
| mysql |
+——————–+
3 rows in set (0.00 sec)

mysql>

A] Range Partitioning ~ table is partitioned by range of values. Each partition contains row which lies within a given range. The ranges should be
contiguous but not overlapping…

mysql> use mydata;
Database changed

mysql> CREATE TABLE cricket (
-> player_name VARCHAR(30),
-> country_name VARCHAR(30),
-> match_count INT,
-> run_scored INT NOT NULL
-> )
-> PARTITION BY RANGE (run_scored)
-> (
-> PARTITION p0 VALUES LESS THAN (26),
-> PARTITION p1 VALUES LESS THAN (51),
-> PARTITION p2 VALUES LESS THAN (76),
-> PARTITION p3 VALUES LESS THAN (101),
-> PARTITION p4 VALUES LESS THAN MAXVALUE
->
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> show tables from mydata;
+——————+
| Tables_in_mydata |
+——————+
| cricket |
+——————+
1 row in set (0.00 sec)

mysql> desc cricket;
+————–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————–+————-+——+—–+———+——-+
| player_name | varchar(30) | YES | | NULL | |
| country_name | varchar(30) | YES | | NULL | |
| match_count | int(11) | YES | | NULL | |
| run_scored | int(11) | NO | | NULL | |
+————–+————-+——+—–+———+——-+
4 rows in set (0.00 sec)

mysql>

*** If cricketer’s score is…
<= 25 then row will go to p0 partition
<= 50 then row will go to p1 partition and so on ..
If score is more than 100 then row will go to p4 partition

* inserting few records to test behavior…

mysql> SELECT * FROM cricket;
+————-+————–+————-+————+
| player_name | country_name | match_count | run_scored |
+————-+————–+————-+————+
| Rahul | India | 2 | 16 |
| Sachin | India | 2 | 56 |
| Saurav | India | 2 | 80 |
| Sachin | India | 1 | 122 |
| Rahul | India | 1 | 153 |
| Saurav | India | 1 | 183 |
+————-+————–+————-+————+
6 rows in set (0.00 sec)

mysql>

** Let’s us check the physical location of database….

root@laptop:~# ls /var/lib/mysql/mydata/
cricket.frm cricket#P#p0.MYD cricket#P#p1.MYD cricket#P#p2.MYD cricket#P#p3.MYD cricket#P#p4.MYD db.opt
cricket.par cricket#P#p0.MYI cricket#P#p1.MYI cricket#P#p2.MYI cricket#P#p3.MYI cricket#P#p4.MYI
root@laptop:~#

B] LIST Partitioning ~ It is similar to range partitioning. But each partition is defined based on the columns value which is one of value in lists,
rather than in one of a set of contiguous ranges of values…

mysql> CREATE TABLE world (
-> country_name VARCHAR(25) NOT NULL,
-> country_id INT NOT NULL
-> )
-> PARTITION BY LIST (country_id)
-> (
-> PARTITION asia VALUES IN (91,95,35,20),
-> PARTITION europe VALUES IN (10,16,82,84),
-> PARTITION america VALUES IN (12,25),
-> PARTITION australia VALUES IN (60,65)
-> );
Query OK, 0 rows affected (0.02 sec)

mysql> show tables;
+——————+
| Tables_in_mydata |
+——————+
| cricket |
| world |
+——————+
2 rows in set (0.00 sec)

mysql>

* inserting few records to test behavior…

mysql> SELECT * FROM world;
+————–+————+
| country_name | country_id |
+————–+————+
| India | 91 |
| Sri Lanka | 95 |
| France | 10 |
| U.K. | 16 |
| Canada | 12 |
| Brazil | 60 |
+————–+————+
6 rows in set (0.00 sec)

mysql>

** What if ‘country_id’ value NOT matching with lists values ?

=> use IGNORE keyword while inserting records… let’s try

mysql> INSERT IGNORE INTO world VALUES (‘South Africa’,50);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM world;
+————–+————+
| country_name | country_id |
+————–+————+
| India | 91 |
| Sri Lanka | 95 |
| France | 10 |
| U.K. | 16 |
| Canada | 12 |
| Brazil | 60 |
+————–+————+
6 rows in set (0.00 sec)

mysql>

C] HASH Partitioning ~ HASH is used primarily to ensure an even distribution of data among a all number of partitions. If you do not include a
PARTITIONS clause while creating table with HASH partitioning, the number of partitions by defaults is one(1). The partition in
which the record is stored is determined by which_partition = MOD(expression, total_no_partition)

mysql> CREATE TABLE africa (
-> country_name VARCHAR(50),
-> population INT,
-> census_date DATE NOT NULL DEFAULT ‘2000-01-01′
-> )
-> PARTITION BY HASH (YEAR(census_date))
-> PARTITIONS 5;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
+——————+
| Tables_in_mydata |
+——————+
| africa |
| cricket |
| world |
+——————+
3 rows in set (0.01 sec)

mysql>

mysql> SELECT * FROM africa;
+————–+————+————-+
| country_name | population | census_date |
+————–+————+————-+
| Nigeria | 2278032 | 2008-09-18 |
| Egypt | 21278032 | 2008-05-28 |
| South Africa | 1678032 | 2009-04-20 |
+————–+————+————-+
3 rows in set (0.00 sec)

mysql>

D] KEY Partitioning ~ It is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression.

4] Subpartitioning ~

It is also known as composite partitioning in which partition is divided in to sub partition. Use SUBPARTITION clauses while creating table.

**Few useful command to know more about tables -

mysql> SHOW CREATE TABLE cricket \G
*************************** 1. row ***************************
Table: cricket
Create Table: CREATE TABLE `cricket` (
`player_name` varchar(30) DEFAULT NULL,
`country_name` varchar(30) DEFAULT NULL,
`match_count` int(11) DEFAULT NULL,
`run_scored` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (run_scored)
(PARTITION p0 VALUES LESS THAN (26) ENGINE = MyISAM,
PARTITION p1 VALUES LESS THAN (51) ENGINE = MyISAM,
PARTITION p2 VALUES LESS THAN (76) ENGINE = MyISAM,
PARTITION p3 VALUES LESS THAN (101) ENGINE = MyISAM,
PARTITION p4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
1 row in set (0.00 sec)

mysql>

** Below statement will give information about all tables in given DB…

mysql> SHOW TABLE STATUS FROM mydata;

Thank you,
Arun Bagul

Introduction -

Oracle Database is widely used commercial Database on Unix/Linux and Windows platform….
This article explain the step by step installation of Oracle 10g Release 2.0 on Ubuntu 8.04111!!
Please refer the Oracle installation guide for System requirement…

1] Hardware requirement -

RAM – minimum 1024 MB of physical memory
Disk space -1.5 GB  to 3.5 GB of disk space, depends on the installation…
Minimum 400 MB of disk space in the /tmp directory

arunsb@arun:~$ free  -m
arunsb@arun:~$ df -h
arunsb@arun:~$ df -h /tmp

2] Software dependency -

Make sure that following packages are installed on your system!!

make-3.8
gcc-4.2
libdb4.2
libdb4.2++-dev
libstdc++6
libstdc++6-4.2-dev
libmotif-dev
libmotif3

root@arun:~# apt-get install  build-essential libc6-dev  libmotif-dev  libmotif3
root@arun:~# apt-get install  xauth libxp6 libxt6 libxtst6 libaio1  glibc-source
root@arun:~# apt-get install  rpm

root@arun:~# ln -s /usr/bin/awk   /bin/awk
root@arun:~# ln -s /usr/bin/basename     /bin/basename
root@arun:~# ln -s /usr/bin/rpm    /bin/rpm

root@arun:~# dpkg -l

3] Operating System Groups and Users for Oracle -

* oinstall – Oracle Inventory group
* dba       – The OS DBA group
* oracle   – Oracle software owner  (in mycase arunsb is oracle user)

root@arun:~# id oracle
id: oracle: No such user
root@arun:~#
root@arun:~# grep oinstall /etc/group
root@arun:~# grep dba  /etc/group

NOTE – “oinstall” should be primary group of oracle user. It is not necessary to use “oracle” as username, you can name but make sure that the “oinstall” is primary group of that user!!

root@arun:~# addgroup oinstall
Adding group ‘oinstall’ (GID 1001) …
Done.
root@arun:~#

root@arun:~# addgroup  dba
Adding group `dba’ (GID 1002) …
Done.
root@arun:~#

root@arun:~# addgroup nobody
Adding group `nobody’ (GID 1000) …
Done.
root@arun:~#

root@arun:~# useradd -g oinstall -G dba   arunsb
root@arun:~#

root@arun:~# id arunsb
uid=1000(arunsb) gid=1001(oinstall) groups=1001(oinstall),1002(dba)
root@arun:~#

*** other setting -

root@arun:~# echo “Red Hat Linux release 3.0 (drupal)” > /etc/redhat-release

root@arun:~# cat /etc/redhat-release
Red Hat Linux release 3.0 (drupal)
root@arun:~#

PART 2 – please

ORACLE Application Express -

Application Express is the open source product by ORACLE. Every one knows HTMLDB, which is used for web representation of oracle database data. Now HTMLDB renamed as Application Express. We can use Oracle forms and reports for data representation. But application express got many good features, which makes web interface very user friendly. Now Apex 3.1 is released.

Features of Apex 3.1 -
1) Web 2.0 functionalities are used.
2) Ajax, Javascript, CSS, DOM used extensily when showing the reports in effective manner. Now the reports are user friendly.
3) Improved PDF document reports downloads and printing.
4) Mailing with Attachments features made effectivly.
5) Customization is possible in reports.

Thank you,
Santhosh Tirunahari