kalo sepintas dari exercise dibawah, bisa menyimpulkan kah? saya melihat ada perubahan data menjadi lebih skewed :) dimana clustering factornya jadi lebih gede setelah shrink...
SQL> truncate table emp drop storage; Table truncated. SQL> insert into emp(empno,ename,job,mgr) select object_id,object_name,object_type,object_id-3 from sys.dba_objects where rownum<=200000; 50426 rows created. SQL> commit; Commit complete. SQL> analyze table emp compute statistics; Table analyzed. SQL> select index_name,status,clustering_factor from user_indexes where table_name='EMP'; INDEX_NAME STATUS CLUSTERING_FACTOR ------------------------------ -------- ----------------- PK_EMP VALID 555 SQL> select rowid,empno from emp where empno between 50000 and 50010; ROWID EMPNO ------------------ ---------- AAAM2JAAEAAAAMPAB9 50000 AAAM2JAAEAAAAMPAB+ 50001 AAAM2JAAEAAAAMPAB/ 50002 AAAM2JAAEAAAAMPACA 50003 AAAM2JAAEAAAAMPACB 50004 AAAM2JAAEAAAAMPACC 50005 AAAM2JAAEAAAAMPACD 50006 AAAM2JAAEAAAAMPACE 50007 AAAM2JAAEAAAAMPACF 50008 AAAM2JAAEAAAAMPACG 50009 AAAM2JAAEAAAAMPACH 50010 11 rows selected. SQL> delete from emp where empno between 20000 and 30000; 10001 rows deleted. SQL> alter table emp shrink space compact; Table altered. SQL> alter table emp shrink space; Table altered. SQL> select rowid,empno from emp where empno between 50000 and 50010; ROWID EMPNO ------------------ ---------- AAAM2JAAEAAAAMIABG 50000 AAAM2JAAEAAAAMIABI 50001 AAAM2JAAEAAAAMIABK 50002 AAAM2JAAEAAAAMIABM 50003 AAAM2JAAEAAAAMIABO 50004 AAAM2JAAEAAAAMIABQ 50005 AAAM2JAAEAAAAMIABS 50006 AAAM2JAAEAAAAMIABU 50007 AAAM2JAAEAAAAMIABW 50008 AAAM2JAAEAAAAMIABY 50009 AAAM2JAAEAAAAMIABa 50010 11 rows selected. SQL> analyze table emp compute statistics; Table analyzed. SQL> select index_name,status,clustering_factor from user_indexes where table_name='EMP'; INDEX_NAME STATUS CLUSTERING_FACTOR ------------------------------ -------- ----------------- PK_EMP VALID 565 2009/7/23 H Gunawan <[email protected]> > Pak Ujang, > masalah yang timbul begini, > setelah table di shrink dan enable row movement, kemudian kami lakukan > truncate table lalu melakukan import ulang 52 juta row. > nah abis itu kami test pake query > select * from table_A order by PK_A Desc; > > biasanya query ini kalau di explain plan jadinya index range scan, tapi > karena row movement tadi jadi FULL SCAN. > > kira2 ada diagnosis kenapa ya. apa karena rowidnya acak2an jadi indexnya > bingung? kalo di paksa pake hint index, indexnya mau jalan. > > thanks > > ----- Original Message ----- > From: Ujang Jaenudin > To: [email protected] > Sent: Wednesday, July 22, 2009 11:38 PM > Subject: Re: [indo-oracle] Enable Row Movement > > > enable row movement, pada dasarnya anda membolehkan perubahan rowid. > jadi bukan karena lupa disable, tapi karena rowid berubah sehingga index > perlu direbuilt. > > On Thu, Jul 23, 2009 at 10:19 AM, H Gunawan <[email protected]> wrote: > > > Hi semua, > > saya pengen sedikit informasi kira2 apa sich kerugian atau apa efeknya > bagi > > performance kalo kita melakukan enable row movement pada table yang > tidak > > dipartisi. > > Saya punya table yang tidak di partisi. Jumlah recordnya 52 juta an. > suatu > > saat saya lakukan shrink table dan enable row movement. abis itu saya > lupa > > disable row movement. > > abis itu indexnya terutama Primary Key jadi kacau. tapi saya tidak tahu > > kenapa bisa begitu. > > > > thanks > > Henry Gunawan > > > > [Non-text portions of this message have been removed] > > > > > > > > ------------------------------------ > > > > -- > > -----------I.N.D.O - O.R.A.C.L.E--------------- > > Keluar: [email protected] > > Website: http://indooracle.wordpress.com > > http://www.facebook.com/group.php?gid=51973053515 > > ----------------------------------------------- > > > > Bergabung dengan Indonesia Thin Client User Groups, > > Terminal Server, Citrix, New Moon Caneveral, di: > > http://indo-thin.blogspot.comYahoo! Groups Links > > > > > > > > > > -- > thanks and regards > ujang | oracle dba | mysql dba > jakarta - indonesia > > [Non-text portions of this message have been removed] > > > > > > > > ------------------------------------------------------------------------------ > > > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.375 / Virus Database: 270.13.23/2254 - Release Date: 07/22/09 > 05:59:00 > > > [Non-text portions of this message have been removed] > > > > ------------------------------------ > > -- > -----------I.N.D.O - O.R.A.C.L.E--------------- > Keluar: [email protected] > Website: http://indooracle.wordpress.com > http://www.facebook.com/group.php?gid=51973053515 > ----------------------------------------------- > > Bergabung dengan Indonesia Thin Client User Groups, > Terminal Server, Citrix, New Moon Caneveral, di: > http://indo-thin.blogspot.comYahoo! Groups Links > > > > -- thanks and regards ujang | oracle dba | mysql dba jakarta - indonesia [Non-text portions of this message have been removed]
