demo partition table

4 Apr

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: