INDEX and PERFORMANCE
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: INDEX and PERFORMANCE

  1. #1
    Join Date
    Aug 2000
    Posts
    52

    Angry

    I have large tables having more than 600000 to 1000000 rows.
    We have a select query....
    In where clause we are using columns which have very few
    distinct values .
    I mean to say from 600000 rows ,only 15 or 20 rows have distinct values.
    Performance is a issue.
    What is the best way to improve performance.

    oracle version is 8i.
    Any help will be appreciated.

    Thanks.

    Sanjay

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Use parallel query.
    Index something else.
    Use hash partitioning on multiple 0+1 devices.
    Use bitmap indexes.
    Redesign your table.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Aug 2000
    Posts
    52
    Thanks for support.
    I know hash partition but could not understand term "on multiple 0+1 devices".
    Can you please explain ?
    Justt now ,I tried to create Bitmap index but gives error
    error "Table too fragmented to ............

    Any idea on this ?

    Thanks

    Sanjay


  4. #4
    Join Date
    Jul 2002
    Posts
    10

    Thumbs up to your query - performance

    Friend,

    Do one thing in your initorcl.ora file in the database directory insert one parameter

    optimizer_mode=FIRST_ROWS


    This will definately improve the performance greatly.

    also

    Try increasing the
    shared_pool_size, db_buffers parameter value in the same file

    This will give you good uplifted queries


    Do let me know your comments ok - sure

    Sack

  5. #5
    Join Date
    Aug 2000
    Posts
    52
    Thanks for support.
    In spite of changing INIT.ora ,if i use first_rows as hint
    then will not be work ?

    We use optimizer_mode =Choose in init.ora

    Sanjay

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by sanjay kan
    I know hash partition but could not understand term "on multiple 0+1 devices".
    A 0+1 device is a RAID device where multiple physical disks are grouped together as one logical volume.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  7. #7
    Join Date
    Jul 2002
    Posts
    10
    U could user bitmap indexes then. This is the situation they are ment for.

    I have never used it but you could try it .



    Do you know anything bout recovery i have just posted a question may you could help with it

    sack

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by sack

    Do you know anything bout recovery i have just posted a question may you could help with it

    sack
    Whoa, whoa, whoa. Considering you're a newbie I'll cut you some slack this time. If you have something to contribute to the thread, do it. Don't post something to try to get somebody to look at your question. There are many qualified people on this forum that can answer your questions. Taking the current thread off topic is not an acceptable way to get your question answered. (Neither is continuously posting "Can anybody help me" as a reply to your own post to keep "bumping" it up).
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    are your queries predicate use only that column or combined with other columns?
    you can probably try composite indexing if several columns are always used , bitmap index are good for low cardinality columns but you have to do some reaserach before using it because it's performance is poor in OLTP database (when DML is going on)

    FIRST_ROWS doesnt mean good performance, it only decrease the response time for the first rows

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