Efficient Data Archiving and Techniques with PHP and Oracle Database

Introduction

In large-scale applications, data archiving and storage are essential for maintaining the performance of an Oracle database. By moving older data to a dedicated archive table, the volume of data in the main table is reduced, improving query response times. This article demonstrates practical methods to implement data archiving using PHP and Oracle Database with full code examples.

Preparation for Data Archiving

Creating an Archive Table

In Oracle, you can use the CREATE TABLE statement to create an archive table to store archived data. Example:

CREATE TABLE archive_data (
    id NUMBER,
    name VARCHAR2(50),
    created_date DATE
);

Creating an Archive Trigger

To enable automatic archiving, create a trigger on the main table that transfers data to the archive table when rows are deleted or updated:

CREATE OR REPLACE TRIGGER archive_trigger
AFTER DELETE OR UPDATE OF created_date ON main_table
FOR EACH ROW
BEGIN
    INSERT INTO archive_data (id, name, created_date)
    VALUES (:old.id, :old.name, :old.created_date);
END;

Using PHP for Data Archiving

Connecting to Oracle Database

First, connect to the Oracle database using PHP:

<?php
$conn = oci_connect('username', 'password', 'database');
if (!$conn) {
    $e = oci_error();
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
?>

Executing Archiving Operations

Use PHP to execute SQL statements that move or delete outdated data:

<?php
$sql = 'DELETE FROM main_table WHERE created_date < SYSDATE - 365';
$stmt = oci_parse($conn, $sql);
if (!$stmt) {
    $e = oci_error($conn);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$result = oci_execute($stmt);
if (!$result) {
    $e = oci_error($stmt);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
ici_free_statement($stmt);
?>

Querying Archived Data

You can query the archive table using PHP:

<?php
$sql = 'SELECT * FROM archive_data';
$stmt = oci_parse($conn, $sql);
if (!$stmt) {
    $e = oci_error($conn);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
$result = oci_execute($stmt);
if (!$result) {
    $e = oci_error($stmt);
    trigger_error(htmlentities($e['message'], ENT_QUOTES), E_USER_ERROR);
}
while ($row = oci_fetch_array($stmt, OCI_ASSOC)) {
    echo $row['ID'] . ", " . $row['NAME'] . ", " . $row['CREATED_DATE'] . "<br>";
}
oci_free_statement($stmt);
?>

Conclusion

By combining PHP with Oracle Database, you can efficiently implement data archiving and storage to optimize database performance and speed up queries. In practice, archiving strategies can be based on timestamps, data importance, or other criteria. The provided code examples serve as a reference to quickly set up an archiving workflow and help developers implement efficient data management.