473,233 Members | 1,559 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,233 software developers and data experts.

8.1.7 query results yielding erratic sort order

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.
Jul 19 '05 #1
2 3185

"One's Too Many" <on*******@yahoo.com> wrote in message
news:80**************************@posting.google.c om...
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
Jul 19 '05 #2
Thanks for the advice, we were able to fix the problem by deleting and
re-computing statistics by running dbms_stats on the tables involved.
I still don't completely understand why this fixed this bizzare
problem... my brain is still hurting from it right now. An added
benefit is the queries are running a lot quicker now too. This
weekend, we'll do the same thing for the rest of the tables (except
SYS and SYSTEM, of course).
Jul 19 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: shank | last post by:
I have a search page with 7 different field options to search upon. The user can use any or all options. On the results page I'd like to put at the top of each column and ASC and DESC link to...
5
by: will eichert | last post by:
Greetings. I have a problem with a combo box incorrectly displaying blank items when returning to a form from a modal form. It's fine when the main form first comes up, but gets messed up when the...
21
by: CSN | last post by:
I have a pretty simple select query that joins a table (p) with 125K rows with another table (pc) with almost one million rows: select p.* from product_categories pc inner join products p on...
7
by: serge | last post by:
How can I run a single SP by asking multiple sales question either by using the logical operator AND for all the questions; or using the logical operator OR for all the questions. So it's always...
0
by: Kaur | last post by:
Hi, I have created a report based on a cross tab query and would like to Sort the column heading of the report based on the sort order of another field. I have three tables called survey...
0
by: Kaur | last post by:
Hi, I have created a report based on a cross tab query and would like to Sort the column heading of the report based on the sort order of another field. I have three tables called survey...
0
by: umangjaipuria | last post by:
I have two job queues doing complimentary work and writing their output once a minute into a file. The files for each minute have to processed in pairs - one from the first job queue and one from...
6
by: traineeirishprogrammer | last post by:
I am currently working on a project where I need to sort my MYSQL query results by different categories. How ever the code does not seem to be working properly and I spend too much time on it...
1
by: Vinod Sadanandan | last post by:
A Roadmap To Query Tuning ============================ For each SQL statement, there are different approaches that could be used to retrieve the required data. Optimization is the process of...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.