Re: Why a query take a long time on a relative small table?

SET linesize 2000
then query the view text...
Try EXPLAIN PLAN to really see what you're doing. my bet is that the view in the middle of your statement is goofing things. Try to write the whole thing out the long way ( without the nested view ).

Randy :)

Paul Cao (paulc) wrote:
: Hi,
: In our software, we have a query that seems to take forever
: to response. Basically, the query is
: SELECT A, B, C, D, ...
: FROM VIEW (a view)
: WHERE A IN ('HELLO'),
: B IN ('THE'),
: C IN ('WORLD'),
: AND D IN ('OK');
: This query takes about 7 minutes or more to response (TKPROF). If
: I remove one condition in the WHERE then it responses immmediately.
: I tried to look into how the view is define in DBA_VIEWS, but only able to
: see partially how the view is defined.
 

: First, how do I export a complete table of DBA_VIEWS (I was able to export but
: the lines is toolong so it comes out missing important stuff) or find out
: how a view is defined?
: Second, any insight into above problem is greatly appreciated.
 

: Paul

--
    ..uu.                                     ----------------------
  .?$" '?i     .                              I  Randy DeWoolfson  I
 .T^M  ._at_"    d9    .     f   ,.un.  b,    i  I--------------------I
 "  Z :#"    M `8   U    <  .dP"``"# `M   _at_"  I  randyd_at_cais.com   I
    &H?`    Xl _R   $5.  $  ?*    _at_   'P,#"   I--------------------I
  ,d#^*L   :RP'~$b  f`$L:M  Xf  .f'    dH`    I        ,\//.       I
    &  'M ,P    `E  M   "$  Mux~      n!`     I        |o o|       I
   dk   `h"       ' j     " y"       *~       I====oOO==(_)==Ooo===I