1

As seen below there is a simple join between my Tables A And B. In addition, there is a condition on each table which is combined with Or operator.

SELECT /*+ NO_EXPAND */
   * FROM  IIndustrialCaseHistory B ,
           IIndustrialCaseProduct A 
     where (
                                  A.ProductId  IN ('9_2') OR
                                  contains(B.KeyWords,'%some text goes here%' ) <=0

           )
       and ( B.Id = A.IIndustrialCaseHistoryId)

on ProductId defined a b-tree index and for KeyWords there is a function index. but I dont know why my execution plan dose not use these indexes and performs table access full? as I found in this URL NO_EXPAND optimization hint could couse using indexes in execution plan(The NO_EXPAND hint prevents the cost-based optimizer from considering OR-expansion for queries having OR conditions or IN-lists in the WHERE clause ). But I didn't see any use of defined indexes

whats is oracle problem with my query?!

Jon Heller
  • 34,999
  • 6
  • 74
  • 132
Hamed
  • 150
  • 2
  • 16
  • 1
    Without any data or schema it's kind of hard to tell. How are the indexes defined and how selective are they? (i.e. what proportion of the table rows would those indexes match?) Are your stats up to date? Are any indexes being used? Maybe showing the execution plan would be useful, with and without the hint. – Alex Poole Nov 06 '14 at 16:21
  • Are there indexes on `B.Id` and `A.IIndustrialCaseHistoryId` ? If yes, does the query use one of them in the execution plan ? OR-Expansion can be used in cases where OR combines two expressions that refer to columns **from the same table**, the OR in your question combines columns from different tables (join), so NO_EXPAND hint is useless in this case, because Oracle doesn't consider OR-expansion transformation. – krokodilko Nov 06 '14 at 19:08
  • yes, B.Id is Primary key of B and A.A.IIndustrialCaseHistoryId is foreign key which refers to B.Id and there is indexes on both of them. but in my plan there is just 2 table access full! thanks but can you please give a reference for your sentence about OR-Expansion? – Hamed Nov 06 '14 at 19:42
  • FOr Mr Alex pool: please explain more precise about a porsion which an index can match. unfortunately I can't understand. what is stats? any index is not used, i just get 2 table access full each for one of A and B – Hamed Nov 06 '14 at 19:48
  • 1
    What volume of data is in the tables? If the volume is below a certain threshold the optimizer may decide that full table scan is cheaper than first going to the index and subsequently to the table. – Jens Krogsboell Nov 06 '14 at 21:39
  • No Jens! for A there is more than 6 million records and for B there is about of 800,000 records and I thinks not using indexes and full scan tables has made my query execution time more than a minutes! – Hamed Nov 07 '14 at 06:41

1 Answers1

0

Unless there is something magical about the contains() function that I don't know about, Oracle cannot use an index to find a matching value that leads with a wildcard, i.e. a text string value within a varchar2 column but not starting in the first position with that value. [OR B.KeyWords LIKE'%some text goes here%' -- as opposed to -- OR B.KeyWords LIKE'Some text starts here%' -- optimizable via index.] The optimizer will default back to the full table scan in that case. Also, although it may not be material, why use IN() if there is only one value in the list? Why not A.ProductId = '9_2' ?

Howard007
  • 124
  • 7
  • As I know there is no relation with contains() and IN. I've replaced contains() and also IN with a simple equal condition. but there was no any change in my execution plan – Hamed Nov 07 '14 at 03:19
  • There *is* something magical about the contains() function that allows wildcards on the left. Oracle Text is complicated and changes everything. See [this answer](http://stackoverflow.com/a/6650496/409172) for an example. – Jon Heller Nov 07 '14 at 06:05