From: mcstockX@Xenquery   
      
    wrote in message   
   news:a1d154f4.0404202352.2ebd281f@posting.google.com...   
   | ccote_msl@yahoo.com (Christian) wrote in message   
   news:<992a9b5a.0404201837.3689d0b5@posting.google.com>...   
   | > HI,   
   | > I have a function that is used to constrain a query:   
   | >   
   | > Select COl1, Col2   
   | > From MyTable   
   | > WHERE col1 = ...   
   | > AND col2 = ...   
   | > And MyFunction(col1) = ...   
   | >   
   | > My problem is that MyFunction is executed as many times that there are   
   | > rows in MyTable. I would like that it is being eecuted only when the   
   | > MyTable data has been filtered by two previous where conditions. This   
   | > way, MyFunction would be executed minimal times. So I did this:   
   | >   
   | > Select a.*   
   | > From (Select COl1, Col2   
   | >       From MyTable   
   | >       WHERE col1 = ...   
   | >       AND col2 = ...)   
   | > Where MyFunction(a.col1) =...   
   | > With no success. MyFunction is executed as many times as there are   
   | > rows into MyTable.   
   | >   
   | > Is There a way to ensure that a function is being executed at the end   
   | > of the where clause, when the data is filtered by previous conditions   
   | > as much as possible?   
   | >   
   | > Thank you for your help,   
   | > Christian   
   |   
   | There is not. In Oracle all predicates will always be evaluated.   
   | The only 'solution' is to use Function Based Indexes (Enterprise Edition   
   required).   
   | The best advice is to avoid functions with embedded selects in them like   
   hell.   
   |   
   | Sybrand Bakker,   
   |   
   | Senior Oracle DBA   
      
      
   not that simple   
      
   in 8.1.7.0.0, for this query:   
      
       select ename   
       from   emp   
       where  deptno = 20   
       and    fnc(ename,'test5') = 'TRUE'   
      
   the function generally got executed once per employee in department 20, not   
   once per row in the table -- but as long as i had and index on deptno, or   
   had statistics on the table   
      
   with no index on deptno  and no statistics, the function got evaluated for   
   each row -- unless i reversed the order of the predicates, then the   
   optimizer only executed the function for each deptno 20 row   
      
   it also appears that the ORDERED_PREDICATES hint works to force evaluation   
   of predicates in WHERE-clause order, thus preventing extra comparisons   
   (there are limitations -- see the docs)   
      
   btw: a function based index is only good advise if the index is going to be   
   selective enough (bad idea to create an index that is never used, just adds   
   DML overhead)   
      
   i would suggest posting version info and the explain plan. you've got some   
   work to do with indexes or statistics or hints   
      
   -{ mcs   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)