| Weird Index Problems [message #55536] |
Wed, 29 January 2003 16:23  |
David Griffiths
Messages: 2
Registered: January 2002
|
Junior Member |
|
|
We have two tables, boat_model (which stores boat models) and model_name (which stores a name, and the manufacturer).
Obviously, two manufacturers can have the same model name, a model name will be active over many years, and more than one boat model can have the same model name.
If I run the following query against the boat_model table, I get,
SQL> SELECT boat_model_id, manufacturer_id, model_name_id, year
FROM boat_model
WHERE model_name_id = 30134
AND year = 2002;
BOAT_MODEL_ID MANUFACTURER_ID MODEL_NAME_ID YEAR
------------- --------------- ------------- ----------
23177 2448 30134 2002
If I then run that same query, but use the boat_model_id returned from the first query (the boat_model_id is the primary key for the table), I get:
SQL> SELECT boat_model_id, manufacturer_id, model_name_id, year
FROM boat_model
WHERE boat_model_id = 23117;
BOAT_MODEL_ID MANUFACTURER_ID MODEL_NAME_ID YEAR
------------- --------------- ------------- ----------
23117 1718 36234 2002
The manufacturer_id is different, the model_name_id is different, but both records claim to have a primary-key of 23117.
I've rebuilt all indexes associated with these two tables. I've also dropped and recreated them. I've rebuilt the indexes associated with the primary keys.
Sometimes I can get the correct results after an index rebuild. And after a query or two, this problem starts appearing again.
Anyone have any suggestions? It's kind of critical.
David.
|
|
|
|
| Re: Weird Index Problems [message #55538 is a reply to message #55536] |
Wed, 29 January 2003 16:44  |
Mike
Messages: 417
Registered: September 1998
|
Senior Member |
|
|
Which DB version are you using?
> Sometimes I can get the correct results after an index rebuild. And
> after a query or two, this problem starts appearing again.
Is the explain plan the same when you get the corret result then when you got the wrong results?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
