tablespace fragmentation and index creation

hi,

Happy New Year to all of you guys!
---------------------------------------------------------------------
1)I am giving below the statistics of my datafiles. This includes the tablespace specifications and also the fragmentation details for the given tablespaces.

All I would request you to mention is how to interpret these figures (specially the fragmentation data) and what to conclude from them and how to rectify the errors. What I can conclude from this data is that the tablespaces are fragmented but dont know how much of the fragmentation should fall under acceptable zone and how much of it should be treated as alarming ones.

Pls suggest me a way out of this situation so that I could reduce the fragmentation issues. I am unable to create bitmap indices on the objects of these tablespaces because of excessive fragmentation and as such my queries are running slower than expected.



DATAFILES:
Tablespace UsedMb FreeMb InitialExt NextExt MaxExtent PctIncr
---------- ------ ------ ---------- ------- --------- -------
USERS 996.01 75.24 131072 131072 4096 50
USERS 108.51 915.49 131072 131072 4096 50


FRAGMENTATION:
Tablespace TotalBlocks EmptyBlocks #Pieces Largest Smallest
----------- ----------- ----------- ------- ------- --------
USERS 131072 117183 87 45903 16
USERS 137120 9631 23 4800 15

--------------------------------------------------------------------

2)My next question is regarding the use of indices inorder to understand how oracle uses the indices on a given table.
The structure of the tables that i would be using in my query is given below:
(pk--primary key)
(if datatype is not mentioned then read them as numbers)

tb_transation_objects
---------------------
COMPANYID, --pk
OBJECTTYPEID, --pk
OBJECTID, --pk
FOROBJECTTYPEIDLEVEL1,
FOROBJECTIDLEVEL1,
FOROBJECTTYPEIDLEVEL2,
FOROBJECTIDLEVEL2,
TRANSACTIONDATE,
SUBTYPEOBJECTTYPEID,
SUBTYPEOBJECTID,
VALUE,
UNITSOBJECTTYPEID,
UNITSOBJECTID,
FORPERIODBEGINDATE, --date
FORPERIODENDDATE, --date
STATUSOBJECTTYPEID1,
STATUSOBJECTID1,
STATUSOBJECTTYPEID2,
STATUSOBJECTID2,
CREATIONDATE, --date
UPDATEDATE, --date
UPDATESEQUENCE

tb_names_objects
-----------------
COMPANYID, --pk
OBJECTTYPEID, --pk
OBJECTID, --pk
ORDINAL,
OBJECTNAME, ---varchar2(50)
EQUIVALENCEOBJECTTYPEID,
EQUIVALENCEOBJECTID,
CREATIONDATE, --date
UPDATEDATE, --date
UPDATESEQUENCE

The query:
----------
select a.objectid,
b.objectname billingaccountsref ,
a.creationdate,
a.updatedate,
a.updatesequence from transaction_objects a ,
names_objects b
WHERE
a.objecttypeid = 20083 and
a.forobjecttypeidlevel1 = b.objecttypeid and
a.forobjectidlevel1 = b.objectid and
(( b.objectname >= '0005 DTCC' and a.objectid >= 0 ))

Note:
-----
It should be noted that the above mentioned query is not accessing the tables directly(tb_transation_objects,tb_names_objects), instead they are accessing views(transation_objects,names_objects) which have their names similar to the tables except the prefix 'tb_'. In our schema all the tables have a view sitting on top of them and these views have all the fields from the corrsponding table in it except the companyid.When the view is created, we provide the companyid information in the views where clause so that for that views lifetime it holds data for a given company only.

Now since the primary key is (companyid,objecttypeid,objectid) so there is an unique index which gets automatically created on these 3 fields. keeping in view the nature of the queries done on these two tables are similar to the one that i've mentioned above, What should be the other indices that I should create?

Should I create indices for every pair of objecttypeid and objectid eg:(forobjecttypeidlevel1,forobjectidlevel1 ) or the indices should be eg:(objecttypeid,forobjecttypeidlevel1,forobjectidlevel1) or (objecttypeid,forobjecttypeidlevel2,forobjectidlevel2) since objecttypeid will always be the first field in the where statement of any query and then the (forobjecttypeidlevel1,forobjectidlevel1 etc) might come. Even there could be a third option like including the companyid also in the index viz(companyid,objecttypeid,forobjecttypeidlevel1,forobjectidlevel1)
In the where clause of any query, objecttypeid is always the first field and since companyid is not there in the view so the where clause doesn't have companyid anywhere mentioned in it but since the view's where clause has the companyid in it so i guess the companyid gets appended automatically to the query's where clause during the time of query execution.

What happens, If I create all these 3 indices on the table ? how does oracle decide which one will be used during execution of the query?

pls mention any good reading material/book to understand everything about indices and how oracle decides which index to pick if there are multiple indices for a given field/combination.

thanks a lot for your patience..

Parijat Paul

For index issue, the CBO will normally picks the one that is less cost to execute. By running table analyze regularily and use TKPROF or explain plans, these will help you to understand your sql's better.