12 Replies Latest reply: Jan 11, 2013 2:29 PM by 616342 RSS

    Need Help with Index

    616342
      hi Experts,

      I need a help with Index here..
      SELECT COUNT(MED_ID)
      FROM ABC WHERE (ORDER_ID=9777785 OR ORDER_NUM=319898462);

      i have a indexes on both order id and order_num.But still it is not using the index .. I tried with Hints it didnt worked..

      Can anyone help me out here...

      Thanks in Advance..
        • 1. Re: Need Help with Index
          Frank Kulash
          Hi,
          user123456 wrote:
          hi Experts,

          I need a help with Index here..
          SELECT COUNT(MED_ID)
          FROM ABC WHERE (ORDER_ID=9777785 OR ORDER_NUM=319898462);

          i have a indexes on both order id and order_num.But still it is not using the index .. I tried with Hints it didnt worked..
          Maybe a UNION would be more efficient:
          WITH  got_med_id  AS
          (
               SELECT     med_id
               FROM     abc
               WHERE     order_id     = 97777785
              UNION
                   SELECT     med_id
               FROM     abc
               WHERE     order_num     = 319898462
          )
          SELECT  COUNT (med_id)     AS cnt
          FROM     got_med_id
          ;
          This assumes that abc.med_id is unique. If not, include the primary key of abc in both branches of the UNION.

          Sometimes the optimizer will actually do a UNION when you coded an OR. Apparantly, it won't in this case, but you can explicitly code the UNION.
          • 2. Re: Need Help with Index
            EdStevens
            user123456 wrote:
            hi Experts,

            I need a help with Index here..
            SELECT COUNT(MED_ID)
            FROM ABC WHERE (ORDER_ID=9777785 OR ORDER_NUM=319898462);

            i have a indexes on both order id and order_num.But still it is not using the index .. I tried with Hints it didnt worked..

            Can anyone help me out here...

            Thanks in Advance..
            How many total rows in the table?
            How many with ORDER_ID=9777785?
            How many with ORDER_NUM=319898462?
            • 3. Re: Need Help with Index
              Peter vd Zwan
              Hi,
              To be able to use the an index when you use two columns and a "and" you should make a compound index like:
              create index abc_ind on abc ( ORDER_ID, ORDER_NUM);
              But when you use a "or" it depends on the size of tables and many things if oracle thinks it is faster to read two indexes or do one full table scan.

              Regards,

              Peter
              • 4. Re: Need Help with Index
                rp0428
                >
                I need a help with Index here..
                SELECT COUNT(MED_ID)
                FROM ABC WHERE (ORDER_ID=9777785 OR ORDER_NUM=319898462);

                i have a indexes on both order id and order_num.But still it is not using the index .. I tried with Hints it didnt worked..
                >
                Why do you care if it doesn't use an index?

                If there are only ten rows in the table why would an index help?

                If there are billions of rows in the table and all of them have (ORDER_ID=9777785 OR ORDER_NUM=319898462) why would an index help?

                Oracle chooses the best plan based on the information you have made available to it, often by collecting the appropriate statistics.

                Have you collected stats or histograms on the table and indexes?

                How many rows have ORDER_ID=9777785?

                How many rows have ORDER_NUM=319898462?

                Post the execution plan that shows how Oracle is doing the work.
                • 5. Re: Need Help with Index
                  616342
                  Thanks Guys..

                  it has 4 million rows... with the combination i get only one record... RIght now performance is very low without index.. Im trying to create and index ,but after creating an index also it didnt worked..
                  • 6. Re: Need Help with Index
                    616342
                    I tried to create a composite index on it , but still it is using full table scan... The problem here is this query runs whole day with different order_id and order_num . And right now it is taking 15 sec..
                    • 7. Re: Need Help with Index
                      onedbguru
                      If you execute the query the first time and it takes all day and then immediately execute it again, the odds are that the results are still in the cache. Make sure your statistics are up to date on the table and index. In 11g you can also create EXTENDED statistics on those 2 columns. search the docs for this.

                      Also you can try:
                      create index on order_id
                      create index on order_num
                      then use the UNION statment before - then show us the explain plan.

                      Edited by: onedbguru on Jan 11, 2013 10:18 AM
                      • 8. Re: Need Help with Index
                        jihuyao
                        With both indexes on ORDER_ID and ORDER_NUM,,

                        select count(rowid) from
                        (
                        select rowid from t where order_id=?
                        union
                        select rowid from t where order_num=?
                        )
                        • 9. Re: Need Help with Index
                          AdamMartin
                          You said you tried hints. But did you try this?
                          SELECT /*+ index_combine(t) */ COUNT(MED_ID)
                          FROM ABC t 
                          WHERE (ORDER_ID=9777785 OR ORDER_NUM=319898462)
                          The idea here is to get the optimizer to use the two indexes independently to narrow the result set to a few rowids and then combine the results with bitmap logic before accessing the table. We are hoping for a plan like this:
                           SORT AGGREGATE
                            TABLE ACCESS BY INDEX ROWID (to get the MED_ID for these rowids)
                             BITMAP CONVERSION TO ROWIDS
                              BITMAP OR
                               BITMAP CONVERSION FROM ROWIDS
                                INDEX RANGE SCAN (using the ORDER_ID index)
                               BITMAP CONVERSION FROM ROWIDS
                                INDEX RANGE SCAN (using the ORDER_NUM index)
                          It is even better if you can list the index names in the index_combine hint like this (note that the table alias is still there in the hint):
                          SELECT /*+ index_combine(t my_order_num_indx my_order_id_indx) */ COUNT(MED_ID)
                          Finally, did you gather statistics on this table? Are the stats fresh?

                          If this doesn't help, in order to further assist you, please describe your table and indexes (post the DDL for each). Also post the explain plan that you see. Also tell us how many distinct med_ids, how many distinct order_ids, and how many distinct order_nums are in the table.
                          • 10. Re: Need Help with Index
                            Martin Preiss
                            if there are few changes on the table then you could try to add two bitmap indexes on ORDER_ID and ORDER_NUM: bitmap indexes can be combined very efficiently (and that's not true for the standard b*tree indexes) - but they lead to big locking trouble if there is concurrent DML happening. But even with the bitmap indexes in place it's the decision of the optimizer if he uses the BITMAP OR - or not.

                            Looking for a good example I found Maria Colgan's (or Mingxi Wu's) interesting article on OR expansion: https://blogs.oracle.com/optimizer/entry/or_expansion_transformation.

                            Regards

                            Martin

                            I wrote "and that's not true for the standard b*tree indexes" - but it is absolutely possible as Adam Martin's plan shows with the BITMAP CONVERSION FROM ROWIDS

                            Edited by: mpreiss on Jan 11, 2013 8:13 PM
                            • 11. Re: Need Help with Index
                              Etbin
                              Maybe this might make the optimizer use both existing indexes one by one
                              with
                              from_order_id as
                              (select med_id
                                 from abc
                                where order_id = 97777785
                              ),
                              from_order_num as
                              (select med_id
                                 from abc
                                where order_num = 319898462
                              )
                              select count(*) as cnt
                                from (select med_id
                                        from from_order_id
                                      union   
                                      select med_id
                                        from from_order_num
                                     )
                              Regards

                              Etbin
                              • 12. Re: Need Help with Index
                                616342
                                Thanks Guyz.. Let me see,as im trying to use the with clause.. Hope that should work fine ..

                                Thanks for all the help from you all...