Melihat ROW-CHAINNING
SQL> CREATE TABLE test_chain(A VARCHAR2(100));
Table created.
SQL> BEGIN
2 FOR i IN 1..1000 LOOP
3 INSERT INTO test_chain VALUES('ABC');
4 END LOOP;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> ANALYZE TABLE test_chain COMPUTE STATISTICS;
Table analyzed.
Melihat jumlah row-chainning ... (chain_cnt)
SQL> SELECT table_name, num_rows, chain_cnt FROM user_tables WHERE
table_name='TEST_CHAIN';
TABLE_NAME NUM_ROWS
CHAIN_CNT
------------------------------ ---------- ----------
TEST_CHAIN 1000
0
SQL> UPDATE test_chain SET a = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
1000 rows updated.
SQL> commit;
Commit complete.
SQL> ANALYZE TABLE test_chain COMPUTE STATISTICS;
Table analyzed.
SQL> SELECT table_name, num_rows, chain_cnt FROM user_tables WHERE
table_name='TEST_CHAIN';
TABLE_NAME NUM_ROWS
CHAIN_CNT
------------------------------ ---------- ----------
TEST_CHAIN 1000
921
SQL>
SQL> ALTER TABLE test_chain MOVE TABLESPACE example;
Table altered.
SQL> ANALYZE TABLE test_chain COMPUTE STATISTICS;
Table analyzed.
SQL> SELECT table_name, num_rows, chain_cnt FROM user_tables WHERE
table_name='TEST_CHAIN';
TABLE_NAME NUM_ROWS
CHAIN_CNT
------------------------------ ---------- ----------
TEST_CHAIN 1000
0
Bowo
--- In [email protected], "Ujang Jaenudin"
<[EMAIL PROTECTED]> wrote:
>
> ----- Original Message -----
> From: "Yulius Wibowo" <[EMAIL PROTECTED]>
> > AUTOALLOCATE vs UNIFORM SIZE
> >
> > 1. AUTOALLOCATE
> > - Default extent size = 64KB
> > - Utk setiap segment, size dari extent pertama s.d extent ke 16
> > besarnya 64KB, after that 1MB
> > (jadi size dari extentnya yg menentukan system, dan besarnya
tidak
> > sama (extent no.1 s/d 16 , dengan yg 17,18 ...))
> > - Cocok utk segment2 yg sizenya bervariasi dan disimpan dalam
sebuah
> > tablespace yg sama.
> >
> > 2. UNIFORM SIZE
> > - extent size yg menentukan DBA, bebas
> > - size dari extentnya selalu sama untuk seluruh segment yg ada di
> > dalam tablespace ybs.
> > - menurut saya, hanya cocok utk menyimpan segment dengan
> > characteristic yg sama, segment yg extentnya kecil: kecillll
semua.
> > segment yg besar: besaaarrrr semua.
> >
>
> SQL> show parameter db_block_size;
>
> NAME TYPE VALUE
> ------------------------------------ ----------- ------------------
------------
> db_block_size integer 8192
>
>
> select num_rows,avg_row_len,num_rows * avg_row_len
> from user_tables
> where table_name = 'XXX_DETAIL';
>
> result :
> num_rows avg_row_len num_rows * avg_row_len
> 45496 114 5186544
>
> select bytes, extents
> from user_segments
> where segment_name = 'XXX_DETAIL';
>
> result :
> bytes extents
> 9437184 24
>
> disini mungkin saya mengartikannya, bahwa per row , tabel
xxx_detail
> membutuhkan space sekitar 114 bytes,
> maka per block nya akan terdapat sekitar 8192/114 = 71 rows.
> (71.859649122807017543859649122807)
>
> nah... persoalannya, ini pasti ada row chaining, dan fragmentasi
di block
> level.....tapi gimana cari tau info ini ?
>
> karena saya pake autoallocate extent, maka extent 1-16 (as per pak
bowo
> explain), akan menjadi
> 64*1024 * 16 = 1048576
> sisanya 1024*1024 * 8 = 8388608 (8 = sisa extent dari 24-16).
>
> sementara 1048576/8192 = 128 block
> 8388608 /8192 = 1024 block
>
> kalo dilihat dari itung-itungan block >< extents, kelihatannya gak
ada
> fragmentasi...apa ini sudah bakalan fix dari sononya?
> nah kenapa sampai terjadi fragmentasi extent? apa karena block di
atas yg
> kelihatan bakal terjadi fragmentasi ikut mempengaruhi fragmentasi
extent?
>
> regards
>
> J
> http://blog.faaza.info
>
>
>
> Send instant messages to your online friends
http://asia.messenger.yahoo.com
>
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.lizt.org (NEW)
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.vze.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
