1 2 3 Previous Next 40 Replies Latest reply: Feb 18, 2014 2:19 AM by David Berger Go to original post RSS
  • 30. Re: Reg: philosophy behind Index creation -
    ranit B Expert
    Currently Being Moderated

    Thanks RP and Ric. I will reply to your posts as soon as possible.

     

    Just a quick test-scenario:

    SELECT

         COUNT(*),  -->-- 264296

         COUNT(DISTINCT item_id),  -->-- 222168

         COUNT(DISTINCT region_code)  -->-- 5

    FROM

         schema_x.table_items;

    We have an existing index on region_code.

     

    Now, a new query is written where both item_id and region_code are being used, and the optimizer is not choosing the existing index.

    So, if suppose an index need to be created - shall the existing index on region_code be altered to composite (with item_id added) or a new composite index should be created afresh?

     

    Again, I'm just trying to understand the logic of creating indexes by a new test-case.

     

    Please advise.

  • 31. Re: Reg: philosophy behind Index creation -
    David Berger Pro
    Currently Being Moderated

    Hello ranit

     

    It depends. If you have for example a list partitioned table where the partition key is the region_code in this case you can profit from the partition pruning and I would put the region_code to the leading position in your index.

     

    But, if you do not have any special case and you use these columns -> item_id and region_code in your filter predicate and you want to keep the item_id then you do not need to take up the region_code into your index.

  • 32. Re: Reg: philosophy behind Index creation -
    ranit B Expert
    Currently Being Moderated

    Thanks for the reply, David.

    If you have for example a list partitioned table where the partition key is the region_code in this case you can profit from the partition pruning and I would put the region_code to the leading position in your index.

    We don't have license for Partitions, so as of now I will keep this aside.

    But, if you do not have any special case and you use these columns -> item_id and region_code in your filter predicate and you want to keep the item_id then you do not need to take up the region_code into your index

    1. What does this mean - "you want to keep the item_id" ?

     

    2. Why do you advise not to keep region_code in the index?

     

    I feel an index like (region_code, item_id) will be more beneficial satisfying both the scenarios. Moreever, like Jonathan suggested above, keeping region_code as leading (low-cardinality) and item_id as following - will help in index compression.

     

    Please rectify if I am wrong.

  • 33. Re: Reg: philosophy behind Index creation -
    David Berger Pro
    Currently Being Moderated

    1. What does this mean - "you want to keep the item_id" ?

     

    I meant that you want an index on the column item_id (because of joins or filter predicate).

     

    2. Why do you advise not to keep region_code in the index?

     

    Yes it can help in index compression but do you want your index to be compressed?  Index compression is acutally for non-unique indexes because of duplicate-values.

     

    I do not see any benefit if you take this region_code in the index if you use the item_id already in joins or in filter-predicate where your item_id is almost unique.

     

    But of course, if use the region_code in your filter predicate and there are joins where you do not have the item_id in the join condition (but an other ID or other columns) in this case your suggestion is right then you can profit from this index.

     

    I read the comments through again and I think you have got a lot of inputs so I think you can decide what the best solution is in your case!

  • 34. Re: Reg: philosophy behind Index creation -
    David Berger Pro
    Currently Being Moderated

    Maybe it can be useful for you:

     

    Case 1: You use only the item_id in your join condition but not the region_code.

    Right index can be: (item_id)

    SELECT

           <your_table>   TAB

      JOIN <other_table>  OTH

        ON

           TAB.item_id = OTH.item_id

    ;

     

    Case 2: You use both of item_id and region_code in your join condition.

    Right index can be:

    - (item_id) -> It depends how unique is your item_id.

        If it is not enough unique then:

          - (item_id, region_code) OR (region_code, item_id)

    SELECT

           <your_table>   TAB

      JOIN <other_table>  OTH

        ON

           TAB.item_id     = OTH.item_id

       AND TAB.region_code = OTH.region_code

    ;

     

    Case 3: You use an other ID than your item_id and you use the region_code in the where clause.

    Right index might be an index on region_code - might be because we have to check whether it is reasonable or not -> Maybe not it depends on many things.

    Here there are many factors you have to consider what is better.

    An example if you have a high value for your DB-parameter db_file_multiblock_read_count in this case a full table scan could be much cheaper then using an index with wich which you filter only the 20% of your data.

    SELECT

           <your_table>   TAB

      JOIN <other_table>  OTH

        ON

           TAB.other_id     = OTH.other_id

    WHERE

           TAB.region_code = 'CA' -- Canada

    ;

     

    You have to check what are the most used cases and your business requirements then you can not do it wrong.

  • 35. Re: Reg: philosophy behind Index creation -
    ranit B Expert
    Currently Being Moderated

    Thanks David.

     

    I think I actually got a lot of quality information on Indexes, just need to read them thoroughly and test all scenarios practially.

     

    @Others - Thanks for all your efforts. Much appreciated.

    Hope this thread will be helpful to other readers as well.

     

    -- Ranit

  • 36. Re: Reg: philosophy behind Index creation -
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    David Berger wrote:

     

     

    It depends. If you have for example a list partitioned table where the partition key is the region_code in this case you can profit from the partition pruning and I would put the region_code to the leading position in your index.

     

     

    The information given shows 5 values for region_code. If you were thinking of using list partitioning to get one region code per partition then you do NOT need the region_code anywhere in the index.

     

    The only argument you might have for putting the region_code at the start of any index is because you were worried about the foreign-key locking problem - and we might hope that that was unlikely to appear for a column with a name like region_code.

     

    Regards

    Jonathan Lewis

  • 37. Re: Reg: philosophy behind Index creation -
    David Berger Pro
    Currently Being Moderated

    Hello Jonathan

     

    My experience is that under version 10g (I do not remember the right release) we had problem often with the partition pruning if we have not had a local prefixed index on our list-partitioned tables. Sometimes worked it sometimes not and we could not explain why. This is the reason why I recommended it.

    After we had created local prefixed indexes it worked right. Maybe it works much stable on 11g.

     

    -> You said one value per partition... maybe this was the problem at us? -> We had this so:

    value    Partition

    5        part_1

    7,9      part_2

    41,58,74 part_3

     

  • 38. Re: Reg: philosophy behind Index creation -
    Mohamed Houri Pro
    Currently Being Moderated

    Hi David

     

    I suppose that you have had a local non prefixed index (an index that doesn’t include the partition key in it) which you have transformed to a local prefixed index to solve your performance issue.

    If this is the case then your query was not including the partition key in its where clause.

     

    The important thing to care about is how would react the database to a query that doesn’t eliminate partitions (because it doesn’t include the partition key in its predicate) and which will be honored via a locally non prefixed index.


    If you ensure partition elimination via the presence of the partition key in the where clause then you doesn’t absolutely need to have an index that include this partition key to have partition pruning


    I have summarized this behaviour in this blog article


    Global or Local | Search Results | Mohamed Houri’s Oracle Notes

     

    and there is also interesting details in the following otn disucssion


    Local index:prefixed or non-prefixed


    Best regards

    Mohamed Houri

  • 39. Re: Reg: philosophy behind Index creation -
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated

    David Berger wrote:

     

     

    My experience is that under version 10g (I do not remember the right release) we had problem often with the partition pruning if we have not had a local prefixed index on our list-partitioned tables. Sometimes worked it sometimes not and we could not explain why. This is the reason why I recommended it.

    After we had created local prefixed indexes it worked right. Maybe it works much stable on 11g.

     

     

     

    The fix that made local-prefixing a redundant concept came in at 8.1.6, or possibly 8.1.7.

     

    The fact that you "did X and things got better" is not a good reason for telling other people to do it, unless you've figured out what was wrong and why doing X fixed it - that way you can explain WHY your suggestion might help and what problem it's actually addressing.

     

    Multi-column indexes have an effect on cardinality estimates and it MAY have been this that helped you - the fact that the partitioning key happened to be at the start of an index when you created a multi-column index may have been irrelevant.

     

    Regards

    Jonathan Lewis

  • 40. Re: Reg: philosophy behind Index creation -
    David Berger Pro
    Currently Being Moderated

    Yes, you are right. I check this out if we have this problem under 11g.

1 2 3 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points