seberapa cepat akses read partition table dengan non partition table ?, berikut demonya
————————————————————–
– 1. table non partition table
————————————————————–
SQL> desc tr_penjualan;
Name Null? Type
—————————————– ——– —————————-
TGL_TRANSAKSI DATE
KODE_CABANG VARCHAR2(10)
KODE_KASIR VARCHAR2(10)
KODE_ITEM VARCHAR2(7)
KODE_PRODUK VARCHAR2(12)
JUMLAH_PEMBELIAN NUMBER(38)
————————————————————–
– 2. total rows non partition table
————————————————————–
SQL> SELECT COUNT(*) FROM tr_penjualan;
COUNT(*)
———-
437724
————————————————————–
– 3. partition table demo
– create partition table
————————————————————–
SQL> CREATE TABLE tr_penjualan_part
( TGL_TRANSAKSI DATE,
KODE_CABANG VARCHAR2(10),
KODE_KASIR VARCHAR2(10),
KODE_ITEM VARCHAR2(7),
KODE_PRODUK VARCHAR2(12),
JUMLAH_PEMBELIAN NUMBER(38)
)
PARTITION BY RANGE (TGL_TRANSAKSI)
INTERVAL(NUMTOYMINTERVAL(1, ‘MONTH’))
( PARTITION p0 VALUES LESS THAN (TO_DATE(’1-1-2008′, ‘DD-MM-YYYY’)),
PARTITION p1 VALUES LESS THAN (TO_DATE(’1-2-2008′, ‘DD-MM-YYYY’)),
PARTITION p2 VALUES LESS THAN (TO_DATE(’1-3-2008′, ‘DD-MM-YYYY’)),
PARTITION p3 VALUES LESS THAN (TO_DATE(’1-4-2008′, ‘DD-MM-YYYY’)) );
————————————————————–
– 4. copy from non partition table to partition table
————————————————————–
SQL> insert into tr_penjualan_part(TGL_TRANSAKSI,KODE_CABANG,KODE_KASIR,KODE_ITEM,KODE_PRODUK,JUMLAH_PEMBELIAN)
SQL> select * from tr_penjualan;
SQL> commit;
————————————————————–
– 5. total rows partition table
————————————————————–
SQL> SELECT COUNT(*) FROM tr_penjualan_part;
COUNT(*)
———-
437724
————————————————————–
– 6. ELAPSED TIME non partition table
————————————————————–
–Obtain Starting Time
SQL> COLUMN start_time NEW_VALUE start
SQL> SELECT systimestamp(9) start_time FROM dual;
SQL> select * from tr_penjualan where to_char(TGL_TRANSAKSI, ‘yyyy/mm/dd’) = ’2008/12/15′;
–Obtain Ending Time
SQL> COLUMN end_time NEW_VALUE end
SQL> SELECT systimestamp(9) end_time FROM dual;
–Compute Elapse Time
SQL> Column elapsed_time NEW_VALUE elapsed
SQL> SELECT TO_TIMESTAMP_TZ(‘&end’)
- TO_TIMESTAMP_TZ(‘&start’) elapsed FROM dual;
ELAPSED
—————————————————————————
+000000000 00:00:02.488616000
————————————————————–
– 7. ELAPSED TIME partition table
————————————————————–
–Obtain Starting Time
SQL> COLUMN start_time NEW_VALUE start
SQL> SELECT systimestamp(9) start_time FROM dual;
SQL> select * from tr_penjualan_part where to_char(TGL_TRANSAKSI, ‘yyyy/mm/dd’) = ’2008/12/15′;
–Obtain Ending Time
SQL> COLUMN end_time NEW_VALUE end
SQL> SELECT systimestamp(9) end_time FROM dual;
–Compute Elapse Time
SQL> Column elapsed_time NEW_VALUE elapsed
SQL> SELECT TO_TIMESTAMP_TZ(‘&end’)
- TO_TIMESTAMP_TZ(‘&start’) elapsed FROM dual;
ELAPSED
—————————————————————————
+000000000 00:00:02.477965000
kesimpulan ELAPSED TIME non partition table lebih lambat dibandingkan ELAPSED TIME partition table
—————————————————————————
– ELAPSED TIME non partition table
—————————————————————————
(TGL_TRANSAKSI, ‘yyyy/mm/dd’) = ’2008/08′ = +000000000 00:00:02.488616000
(TGL_TRANSAKSI, ‘yyyy/mm/dd’) = ’2008/08/15′ = +000000000 00:00:00.360715000
(TGL_TRANSAKSI, ‘yyyy/mm/dd’) = ’2008/12/15′ = +000000000 00:00:00.332106000
—————————————————————————
– ELAPSED TIME partition table
—————————————————————————
(TGL_TRANSAKSI, ‘yyyy/mm/dd’) = ’2008/08′ = +000000000 00:00:02.477965000
(TGL_TRANSAKSI, ‘yyyy/mm/dd’) = ’2008/08/15′ = +000000000 00:00:00.273750000
(TGL_TRANSAKSI, ‘yyyy/mm/dd’) = ’2008/12/15′ = +000000000 00:00:00.298537000
setijoagus
