1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Database Re-Org & index rebuliding

Discussion in 'SQL PL/SQL' started by Girish, Mar 14, 2015.

  1. Girish

    Girish Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Hi All,

    Would like to know best site to get theoretical knowledge on Database Re-Org & index rebuilding.

    Please suggest me.

    :)
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why? What are you planning and why are you planning this? Index rebuilds are seldom, if ever, necessary and a 'database' reorganization makes no sense; you re-organize a table, not a database.


    I'm wondering what perceived problem you're trying to solve.
     
  3. Girish

    Girish Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    Zargon,

    Yes, You are correct its table re-organize.

    Actually we are facing Too much slowness in some of the reports which are running on our database.

    So we are planning for table re-Org / Index rebuilding. To speed up report generating process.

    Can you shed some more light on this.

    Thank You.
     
  4. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Reogranization of a database is a difficult question.
    Area of these questions from program/architectural to the hardware questions on upgrade.

    You most likely need to carry out reorganization of data. For a start carry out the analysis of segments of tables, indexes. On the basis of data retrieveds perform necessary operations on segments.

    Also carry out the analysis of problem sql-query i.e. which are implemented long from your point of view



    Additional links :
    Space, Object, and Transaction Management in Oracle Database 10g

    Reclaiming Unused Space in Datafiles

    Online Table Redefinition
     
    Girish likes this.
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    As mentioned in a previous response you need to see what these queries are doing in their execution plans before you jump to the conclusion that index rebuilds are necessary. You also need to post the version of Oracle you are running, and that does NOT mean '11g', '11gR2', etc. There is a four to five digit version number displayed in the banner when you login through SQL*Plus; it's that number you need to post. Depending on the version there may be patches you need to apply to address known performance issues, there may be known problems with gathered statistics and query performance for that release as well. It may also be inefficient queries causing the 'problem'. There may be many possible causes that do NOT involve index rebuilds or table reorganizations; jumping to that singular conclusion without sufficient investigation will only lead to problems down the line and could cause you to implement regular index rebuilds that are completely unnecessary.


    Don't be like Chicken Little and scream 'The sky is falling!' every time you have a slower than expected query; fully investigate the issue before you decide on a course of action.
     
  6. Girish

    Girish Active Member

    Messages:
    4
    Likes Received:
    1
    Trophy Points:
    65
    PL/SQL Release 11.2.0.3.0.

    We are investigating at Query level, as well.

    Thanks.
     
  7. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Also consider a question of updating of a database till 11.2.0.4