tablespace fragmentation and index creation
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: tablespace fragmentation and index creation

  1. #1
    Join Date
    Dec 2001
    Posts
    120

    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

  2. #2
    Join Date
    Oct 2002
    Location
    CA
    Posts
    67
    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.
    --------------------------------------
    It's not what the world does to you that matters. It's how you respond...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width