From: kennedy-downwithspammersfamily@attbi.net   
      
   "One's Too Many"  wrote in message   
   news:809fd590.0404211520.7a972841@posting.google.com...   
   > Ran into a strange problem today:   
   >   
   > 8.1.7 on AIX 4.3.3   
   >   
   > Database and applications had been working fine for two years and all   
   > of a sudden a couple of regularly-run queries are now no longer coming   
   > out in correct sort order specified in the ORDER clauses of the select   
   > statements. Behavior is erratic, about half the time the sort order is   
   > correct, and the other half is not. All expected rows are being   
   > returned (no data is missing) and the "out-of-sort-order-ness" appears   
   > in contiguous blocks of rows returned. For a crude analogy of what   
   > we're seeing: the bad results would come back like first all the A's,   
   > then all the B's then all the C's then all the E's, then all the D's,   
   > then the F's, etc (the values sorted erroneously are actually mixed   
   > alphanumerics, not pure alphas). The tables being queried contain   
   > several hundreds of thousands to a few millions of rows each. The   
   > queries had worked fine for two years, and there have been no schema   
   > changes or application code changes at all. The only thing that has   
   > changed is that a very large quantity of new rows have been inserted   
   > into most of the tables very recently. The client is reluctant to let   
   > me take the database down long enough for a complete   
   > export/import/diskspace re-organization, which it really needs, so I'm   
   >  about to suggest that we at least drop and re-create all the indexes   
   > on the tables involved. All indexes are showing "valid" status in   
   > sys.dba_objects, however.   
      
   You don't need a reorg, that isn't going to fix the problem.  Here are a   
   couple of things you can try (non-destructive, db can stay up)(no particular   
   order):   
   1. Export those tables that are giving you a problem.  You can export to a   
   null device as you really don't need the export file(s).  Do use a parameter   
   file and specify where to put the log.  Look in the log and see if the   
   export failed.  I have seen instances where a disk error happened and Oracle   
   didn't catch it , but the export does a full table scan and thus reads every   
   block that the table occupies.  If there is some sort of block corruption   
   then the export will fail and it will show up in the log file.   
      
   2. You can do an   
   analyze table foo validate cascade;   
      
   See if you get an error there.  This will lock the table and its indexes as   
   it runs and and so you have to be careful as to how it will effect the other   
   users that are accessing the data.   
      
   3. Look at one of the queries that is demonstrating this unusual behavior   
   and see what the explain plan is.  If an index is not being used to do the   
   sort; it is unlikely that than index is the problem.  Check the nls_sort   
   parameter and see what it is set to on the client.  (this could happen and   
   give wierd results)  Are you explicitly setting it when the application   
   connects to the back end? (AFIK which would override what the client is set   
   to )   
      
   4. Do a trace and see if in fact there is an order by statement.  I've seen   
   people think group by should implicitly do an order by (and it shouldn't be   
   relied on to).   
      
   Jim   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)