Forum Stats

  • 3,781,122 Users
  • 2,254,483 Discussions
  • 7,879,578 Comments

Discussions

2 identical indexes but with different names - index load balancing?

694243
694243 Member Posts: 155
edited Jul 9, 2009 9:26AM in General Database Discussions
Hello,

is it theoretically possible to have 2 indexes which differ only in their names? And make something like load balancing?

The real situation that brought me to that question is that one of my indexes is sometimes unavailable and the queries that use it are during this time too slow. So my idea is to have 2 indexes for the same purpose and when one of them is unavailable for some reason, then the other one will serve all queries.

So what do you think, is it possible, and if yes, do I have to do something more except defining the 2 indexes?

And please, don't try to advise me to check why my index is not available and so on. I really want to hear an answer to that particular question - it is interesting for me also just theoretically. Thanks in advance for any replies!

Regards
Tagged:
«1

Answers

  • SomeoneElse
    SomeoneElse Member Posts: 14,866 Silver Crown
    Not sure what you're trying to accomplish, but you can't create two indexes with the same column list:
    SQL> create table t(n number);
    
    Table created.
    
    SQL> create index tidx1 on t(n);
    
    Index created.
    
    SQL> create index tidx2 on t(n);
    create index tidx2 on t(n)
                            *
    ERROR at line 1:
    ORA-01408: such column list already indexed
  • sb92075
    sb92075 Member Posts: 42,196 Blue Diamond
    So what do you think, is it possible
    No, not a chance
  • EdStevens
    EdStevens Member Posts: 28,599 Gold Crown
    user10962462 wrote:
    Hello,

    is it theoretically possible to have 2 indexes which differ only in their names? And make something like load balancing?

    The real situation that brought me to that question is that one of my indexes is sometimes unavailable and the queries that use it are during this time too slow. So my idea is to have 2 indexes for the same purpose and when one of them is unavailable for some reason, then the other one will serve all queries.

    So what do you think, is it possible, and if yes, do I have to do something more except defining the 2 indexes?

    And please, don't try to advise me to check why my index is not available and so on. I really want to hear an answer to that particular question - it is interesting for me also just theoretically. Thanks in advance for any replies!

    Regards
    "And please, don't try to advise me ..."

    Then why ask? You did say you had a situation that brought you to that point. That situation needs to be addressed, and that would imply finding the root cause and solving it, not implementing a half-baked band-aid solution.

    And if you are interested in just a theoretical situation, why not do a little experimenting yourself? Why not try to create such a duplicate index? And if do create it, how do you suppose Oracle will treat it? How do you suppose it will use it in a query? What overhead would you expect to have to maintain two indexes that are identical?
  • 694243
    694243 Member Posts: 155
    Hi

    now I have something for you:
    CREATE INDEX employees_idx1 ON employees (last_name, job_id); 
    CREATE INDEX employees_idx2 ON employees (job_id, last_name); 
    so, how does oracle decide which one to use, if one of these is not available, will oracle automatically use the other one?
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    Assuming a moderately recent version of Oracle and assuming you are using the cost-based optimizer, the CBO will use whichever index it believes would be more efficient for your particular query. That will depend on a variety of factors-- the SQL statement, object statistics, index statistics, optimizer parameters, and system statistics being the primary factors.

    If we assume that one of the indexes was unavailable, and we assume that whatever caused the one index to be unavailable didn't also cause the other index to be unavailable, which seems unlikely, depending on what, exactly, you mean by "unavailable", and depending on initialization parameters (i.e. is skip_unusable_indexes set to true?), Oracle may use the other index.

    Note that since you haven't provided a whole lot of information about your database or about the underlying problem, it's rather difficult to do much more than say "it depends" and list a host of factors upon which it might depend.

    Justin
  • 694243
    694243 Member Posts: 155
    I am using 10.2.0.4

    The problem appears when I start a reorg job: shrink space, rebuild index online. The job is started weekly.
    For some reason one query runs very very slow only during that job. The query is part of our application and has nothing to do with the reorg job's code.

    The problem with the slow query has been investigated by oracle support but without success. They said, that we have to optimize the query. I posted another thread about that optimization:
    918756
    The query itself cannot be optimized and runs very quick during the week! So the actual problem is not the query, but the indexes which are being organized. That's why I want to duplicate this index. There are no problems with other queries, by the way.
  • EdStevens
    EdStevens Member Posts: 28,599 Gold Crown
    user10962462 wrote:
    Hi

    now I have something for you:
    CREATE INDEX employees_idx1 ON employees (last_name, job_id); 
    CREATE INDEX employees_idx2 ON employees (job_id, last_name); 
    so, how does oracle decide which one to use, if one of these is not available, will oracle automatically use the other one?
    In addition to what Justin has pointed out, I'd point out a couple of things:

    First, you initially talked about two identical indexes. The example above are not identical. They reference the same columns but not in the same order. So index 1 would be preferred (by the CBO) with a selection predicate on last_name, while index 2 would be preferred with a predicate on job_id.

    Coming back to your original, where you asked about identical indexes and load balancing - the indexes will NOT be used for load balancing .. Oracle will not use one index for one query, then when the next query comes in say "I'm already using index 1 for the previous query, so I'll use index 2 for this one." In fact, such a scenario (if Oracle would do that) would actually have a negative impact on performance, because it would negate the ability to use what was already in the buffer from the first query.

    And on top of that, every index you create means decreased performance for INSERT and UPDATE operations because there will be more recursive sql necessary to maintain the indexes. Creating indexes is always a balancing act between SELECT performance and INSERT/UPDATE performance. There's no free lunch here.
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    Why do you have a reorg job? And why does that job run weekly and during a time when other users are accessing the system?

    Indexes, as a general rule, do not need to be rebuilt ever. A small percentage of indexes may benefit from a rebuild in certain very specific situations. Most of those would be one-time operations. It would be exceptionally rare to have an index that needed to be rebuilt on a weekly basis.

    As for the query you posted in the other thread, I assume you posted the plan from the point where the query was running quickly. Do you have a plan from the point where the query was running slowly? That is, presumably, the bit you're trying to tune.

    Justin
  • EdStevens
    EdStevens Member Posts: 28,599 Gold Crown
    user10962462 wrote:
    I am using 10.2.0.4

    The problem appears when I start a reorg job: shrink space, rebuild index online. The job is started weekly.
    For some reason one query runs very very slow only during that job. The query is part of our application and has nothing to do with the reorg job's code.

    The problem with the slow query has been investigated by oracle support but without success. They said, that we have to optimize the query. I posted another thread about that optimization:
    918756
    The query itself cannot be optimized and runs very quick during the week! So the actual problem is not the query, but the indexes which are being organized. That's why I want to duplicate this index. There are no problems with other queries, by the way.
    This is why it is so important to state all of the facts of the problem you are trying to solve, and not just inquire about the feasability of some half-baked technical solution you have in mind.

    Have you done any reading on the necessity/advisability of index reorganization in Oracle?

    Or is the index rebuild the result of reorging the underlying table?

    If, after researching that question, you determine that the reorg really is of benefit, can you look at scheduling it at a time when the problem query is unlikely to run?
  • 694243
    694243 Member Posts: 155
    Thanks, your post helped me a little.

    Now I'll try to explain my situation better.

    Only one query could come at a time. So what happens during the weekly job is (maybe) this: the jobs reorganizes one of the indexes. The query is started and runs very slow (20-30 minutes during the job and 10 ms otherwise). No other queries that use that index are started until this query is done.

    This slow-running query uses all of the index's fields! What happens in this case? Actually which one is being preferred during normal load is not important for me. Important is whether oracle can switch between them when it sees that one of the indexes is being reorganized or is somehow being unavailable.
This discussion has been closed.