Forum Stats

  • 3,782,582 Users
  • 2,254,669 Discussions
  • 7,880,131 Comments

Discussions

index clarification

Rajkumar_29
Rajkumar_29 Member Posts: 44
edited Feb 25, 2016 8:36AM in SQL & PL/SQL

There 5 session each session are try to hit the one Table?there is bitmap index on gender column.now what will happen over performance side? it just an interviewer question

Tagged:
Paul  Horth

Answers

  • User_6XD9J
    User_6XD9J Member Posts: 618 Bronze Badge
    edited Feb 25, 2016 1:30AM

    You have not given enough information here, see other replies.

    It may be worth reading through  the oracle manuals and testing code in a test / development environment,

  • Jarkko Turpeinen
    Jarkko Turpeinen Member Posts: 1,792 Silver Trophy
    edited Feb 25, 2016 1:38AM

    Hi,

    with given information nothing happens. There's room for guessing and assuming but not enough facts to give exact answer.

    You need more information about the SQL that is "hitting" and description of requirement, table, index and data.

    What was your answer?

  • Nimish Garg
    Nimish Garg Member Posts: 3,185 Gold Trophy
    edited Feb 25, 2016 1:46AM

    if your query have predicate for gender and if CBO thinks it is good to use, it will use index.

    Select query from multiple sessions does not create locks, but every thing which run on db machine consume CPU, MEMORY and IO. how much all depends on your query and environment configuration..

  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Feb 25, 2016 8:08AM
    Rajkumar_29 wrote:
    
    There 5 session each session are try to hit the one Table?there is bitmap index on gender column.now what will happen over performance side? it just an interviewer question
    
    

    The mention of bitmap indexes and many sessions makes me believe that hitting the table means updating the table and bitmaps aren't very many session friendly when it comes to DML:

    https://docs.oracle.com/database/121/CNCPT/indexiot.htm#CNCPT88846

    If the indexed column in a single row is updated, then the database locks the index key entry (for example, M or F) and not the individual bit mapped to the updated row. Because a key points to many rows, DML on indexed data typically locks all of these rows. For this reason, bitmap indexes are not appropriate for many OLTP applications.
    

    However this is purely guessing what the interviewer could have been wanting you to say based on the key words used.

  • BluShadow
    BluShadow Member, Moderator Posts: 41,623 Red Diamond
    edited Feb 25, 2016 8:18AM

    I would ask the interview what they meant by "hit the table", as that is the crux of what is happening.  If it's querying data, then the performance difference will hardly be noticeable (in most cases - there can always be exceptions - depending on unknown factors)... if it's DML statements, then performance could be impacted due to the nature of bitmap indexes, but again it depends on other factors (how much data is in them, what is being updated/inserted, and how that relates to the bitmap index etc. etc.).  Essentially there is too little information to give a concise answer.

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    edited Feb 25, 2016 8:36AM

    It's doubtful that an index on a gender column would be of any use, assuming M and F are evenly distributed.

    Or maybe it's roughly 50% M, 50% F, and Bruce Jenner.

    EDIT:

    Sorry, Caitlin Jenner (or whatever it is this week)

  • John Stegeman
    John Stegeman Member Posts: 24,269 Blue Diamond
    edited Feb 25, 2016 8:34AM

    Generally, when a lot of people hit my table, the table will eventually break. I was going to do a test where I had 5 people hit our wooden coffee table with baseball/cricket bats whilst my laptop (which has an Oracle database containing some tables with bitmap indexes on its hard drive) sat on top of the table, but both my wife (for the coffee table's sake) and my boss (for my laptop's sake) rejected that test.

    As others have said - "hitting the table" isn't a precise technical term at all, so if I was asked that question in an interview, I'd say - "well, my experience tells me you might be trying to ask me about whether bitmap indexes are a good idea in an OLTP-like environment with multiple people doing simultaneous small transactions against the table, but could you please clarify more what you mean by 'hitting the table.'"

    Paul  Horth
This discussion has been closed.