Whoever Resembles a People is One of Them. He is not one of us who resembles other than us,
nor who resembles the Jews nor the Christians.(jami-at-tirmidhi-vol-5-ahadith-2695)
O you who believe (who wish to reach ALLAH C.C. before death)! Do not take my and
your enemies for friends!
And although they have denied what has come to you
from the Truth, you still love them (treating them as your friends).
If you go forth to struggle hard in My Way, seeking My Pleasure, (then why) you manifest
love to them and share with them your secrets.
And I know what you conceal and what you manifest. And whoever of you does that, then he
indeed has gone astray from the right Way. 60/Al-Mumtahanah-1
They are driving out the Messenger and yourselves from your land because you
believe in ALLAH C.C., your Lord.
Recite (read and explain to others) that which has been revealed to you of the Book
and keep up the Prayer.
Surely the Prayer keeps away from Al-Fahsh (to do what ALLAH C.C. prohibits) and Al-Munkar
(to deny what ALLAH C.C. commands).
Whoever obeys me, obeys ALLAH C.C., and whoever disobeys me, disobeys ALLAH C.C.
Whoever obeys the ruler, obeys me, and whoever disobeys the ruler, disobeys me. " (Sahih)

Index

Index

Checking the validity of the index

SQL > ANALYZE INDEX orders_region_id_idx VALIDATE STRUCTURE; ===> index_stats
SQL > SELECT blocks, pct_used, distinct_keys
lf_rows, del_lf_rows
FROM index_stats;

BLOCKS PCT_USED LF_ROWS DEL_LF_ROWS
—— ——— ——– ————
25 11 14 0
1 row selected.

Note: If the percentage of LF_ROWS within the DEL_LF_ROWS passes 30% then the index should be analyzed and recreated after being checked.

SQL > DROP INDEX hr.deptartments_name_idx;

To view the index usage:

Start with

ALTER INDEX summit.orders_id_idx MONITORING USAGE;

Followed by retrieving the following column information from V$OBJECT_USAGE:
INDEX_NAME
TABLE_NAME
MONITORING: Monitoring status (ON/OFF)
USED: YES / NO Whether the index has been used whilst monitoring was active
START_MONITORING: The start monitoring time
END_MONITORING: The end monitoring time

To end the monitoring

ALTER INDEX summit.orders_id_idx NOMONITORING USAGE;

Freeing some space by compressing the indexes

create index emp_last_name_idx on employees (last_name, first_name) compress;
alter index emp_last_name_idx rebuild compress;

Getting detailed information about indexes by reviewing the views/tables below

DBA_INDEXES
DBA_IND_COLUMNS
DBA_IND_EXPRESSIONS
V$OBJECT_USAGE
select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘table_name’ ;

It is important to collect statistical information for Cost Based Optimizers (CBO). There are several ways to accomplish this:

The first is to retrieve information about a particular time:

DBMS_STATS.GATHER_INDEX_STATS (‘SCHEMA_NAME’,’INDEX_NAME’);

Another way is by performing the following while creating the index

create index INDEX_NAME ………. compute statistics;

It may be necessary to rebuild the indexes from time to time. Index statistics can be gathered during this rebuilding process.

alter index INDEKX_NAME rebuild compute statistics;

However with 10g, the DBMS gathers statistics while the index is built.

To see an example of this:

drop table ttest;
create table ttest as select object_id, object_name, object_type from all_objects;
create index ttest_id_idx on ttest (object_id);
select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;
index_name last_analyzed num_rows sample_size
TTEST_ID_IDX 2/12/2007 3:59:01 PM 12332 12332

Statistics aren’t updated as transactions are carried out on the tables. To check:

insert into ttest select object_id, object_name, object_type from all_objects;
select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;
index_name last_analyzed num_rows sample_size
TTEST_ID_IDX 2/12/2007 3:59:01 PM 12332 12332

An insert operation (or any operation) performed on a table doesn’t automatically update the “last_analyzed” field.

To compute statistics run: “analyze index ttest_id_idx compute statistics”.

Viewing the results after running the command:

analyze index ttest_id_idx compute statistics;
select index_name, last_analyzed, num_rows, sample_size from user_indexes where table_name = ‘TTEST’ ;
index_name last_analyzed num_rows sample_size
TTEST_ID_IDX 2/12/2007 3:59:53 PM 36998 36998

By running ”GATHER_STATS_JOB” the DBMS can gather statistics automatically whenever necessary. To view the status of the this setting, run this command: (TRUE indicates that it’s active)

SELECT owner, job_name, enabled
FROM dba_scheduler_jobs
WHERE job_name = ‘GATHER_STATS_JOB’ => if inactive => “dbms_scheduler.enable(’GATHER_STATS_JOB’)

Deleting an index necessitates creatiing a new one. Creating a new and updated index increases a database’s performance.

Rebuilding indexes would normally lock the tables, this method shows how to set the lock to a minimum:

ALTER INDEX orders_id_idx DEALLOCATE UNUSED
ALTER INDEX orders_region_id_idx REBUILD TABLESPACE indx02;
ALTER INDEX orders_id_idx REBUILD ONLINE;
ALTER INDEX orders_id_idx COALESCE;

To view the amount of index fragmentation

validate index index_name ; => By committing this statement information gets written to index_stats

Reviewed using the following query

select name “index name”,
del_LF_rows “rows that have not be refilled after being deleted” ,
LF_rows “total index rows” ,
round ((del_LF_rows / (LF_rows + 0.0000000001)) * 100) “fragmentation percentage”
from index_stats;

If the fragmentation percentage is high

alter index index_name rebuild ; to rebuild the index.

To find the index tables belonging to this user

select a.index_name ,
b.index_type ,
a.column_name
from user_ind_columns a , user_indexes b
where a.table_name = ‘hr.employees’
and a.index_name = b.index_name
order by a.index_name ;

Copyright © 2015 Sysdba All Rights Reserved Web Designer