XPost: comp.databases.oracle.misc   
   From: dillon@SpamMinuSaccessdenied.darktech.org   
      
   I have several views with unions, outer joints and calculations that later   
   combine to find a "final"   
   report view.   
   This was to make things easier for the web-based report programmer to just   
   pull the values from this   
   view.   
      
   If I query the view by the id/index/primary_key, the view runs fine (about   
   3secs)   
      
   e.g: Select *   
        from final_view   
        where Id_Index in (123, 456, 789)   
      
   However, when the view is queried by dates, it is slow.   
   More like impossible actually 'cos the temp table space gets filled up (32GB!)   
   and oracle returns an   
   error about   
   being unable to extend.   
      
   E.g: Select *   
        from final_view   
        where Submission_Date=to_date('01-01-2005', 'dd-mm-yyyy')   
      
      
   I tried indexing the Sub_Date but it didn't help.   
      
   But if I run the query directly on the table in question (with the sub_date),   
   the query works fine &   
   fast,   
   with or without the indexing.   
      
   Anyone got any pointers?   
      
   I'm using oracle 9i.   
   And the 3 big tables which the view taps on have about 100,000 records each.   
      
      
   Thanks   
      
      
      
   To e-mail, remove the obvious   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)