Thứ Hai, 30 tháng 11, 2020

Quản lý Index trong Oracle Database

--1.CHECK
--Check Index unusable
select owner, index_name from dba_indexes where status='UNUSABLE';
    
select 'alter index ' || owner ||'.' || index_name ||' rebuild tablespace INDX nologging parallel 8 online;' from dba_indexes where status='UNUSABLE' 
and owner='TEST_OWNER';

select 'alter index ' || index_owner || '.' || index_name || ' rebuild tablespace INDX nologging parallel 8 online;' from dba_ind_partitions where status='UNUSABLE';
-- Check size index
select sum(bytes) from dba_segments where segment_type like '%INDEX%' and segment_name = 'TAB1_IND1';

-- 2.CREATE
-- CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
-- Compressing an Index: 
CREATE INDEX ord_customer_ix_demo 
   ON orders (customer_id, sales_rep_id)
   COMPRESS 1;
CREATE INDEX TEST_OWNER.TEST_I2 ON TEST_OWNER.TEST
(ISDN)
LOGGING
NOPARALLEL
online;

--3.ALTER
-- Thực hiện index online không ảnh hưởng đến hoạt động
Alter index index_name rebuild online;

Alter index index_name rebuild parallel 8 online nologging; 

select 'alter index ' || owner ||'.' || index_name || ' noparallel;' from dba_indexes where  index_name like '';

-- Rebuild index 2 bang forbidden_subscriber','forbidden_subscriber_detail
select 'alter index ' || owner ||'.' || index_name || ' rebuild tablespace INDX nologging parallel 4 online;' from dba_indexes 
where  lower(table_name) in  ('tab1');

select 'alter index ' || owner ||'.' || index_name || ' no parallel;' from dba_indexes 
where  lower(table_name) in  ('tab1');

-- Noparallel
select * from dba_indexes where    degree>1 order by 2;

select 'alter index ' || owner ||'.' || index_name || ' noparallel;' from dba_indexes where  degree>1;         

--4.DROP
DROP INDEX ENERGY_INDEX;

KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH: 📧 Mail: binhoracle@gmail.com ☎️ Mobile: 0902912888 ⚡️ Skype: tranbinh48ca 👨 Facebook: https://www.facebook.com/BinhOracleMaster 👨 Inbox Messenger: https://m.me/101036604657441 (profile) 👨 Fanpage: https://www.facebook.com/tranvanbinh.vn 👨 Inbox Fanpage: https://m.me/tranvanbinh.vn 👨👩 Group FB: https://www.facebook.com/groups/OracleDBAVietNam 👨 Website: http://www.tranvanbinh.vn 👨 Blogger: https://tranvanbinhmaster.blogspot.com 🎬 Youtube: http://bit.ly/ytb_binhoraclemaster 👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi 👨 Linkin: https://www.linkedin.com/in/binhoracle 👨 Twitter: https://twitter.com/binhoracle 👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội #OracleTutorial #OracleDBA #OracleDatabaseAdministration #học oracle database #oca #ocp #oce #ocm

ĐỌC NHIỀU

Trần Văn Bình - Oracle Database Master