Skip to Main Content

Breadcrumb

Warning

Before you submit your comment...

Remember - this is about feedback to the original question. AskTOM is about building a strong Oracle community, not stealing time away from others needing assistance by sneaking in a brand new question under the guise of "Comment".

If your comment requires a response that might include a query, please supply very simple CREATE TABLE and INSERT statements. Nice simple scripts so we can replicate your issue simply.

Remember to take a look at Legal Notices and Terms of Use before supplying a comment...

Don't forget, your comments are public. If you want to send a comment to just the AskTOM team, please use the feedback option

Comment

Highlight any SQL, PL/SQL, or fixed-width text and click the <code> button
 (will never be published or used to spam you)

Question and Answer

Tom Kyte

Thanks for the question, Kumar.

Asked: September 24, 2011 - 4:18 am UTC

Last updated: September 29, 2011 - 7:49 am UTC

Version: 10.2.0.3.0

Viewed 1000+ times

You Asked

Hello Tom,

This is Kumar working in a MNC. In out daily routine we will get some issues relating to the application and Database as well.


My current problem is:

We use Indexed tables and well tuned queries in our application. But in some part of day or night, Some of the tables were not making use of the indexes which were created on the tables. So while selecting a row from a table we are getting an unexpected results from the tables as if the index is not created on it.

NOTE: This Problem is being faced only in some part of Day or Night. But in remaining timings, It is giving the Normal expected results and index is working fine.

Does any other processing in the Database effect the usage of indexes?

Please suggest a solution to our problem.

Thanks in Advance.



Thank you very much for the response.

We have three databases Day(With actual data),Backup(with backup) and Night(with no data and will be inserted only in night and will be copied into Day database later) with 2 servers which will be working on 2 instances of the single Database. We will be switching the regions from Day to night during which the my application will be in sink with Night Database and Night database pointing to the Day database for information. Later we will perform Database Switch during which the Night database will be pointing to the Backup database and Day database will be going under Processing. After the Completion of the Processing then again we will perform the Database switch. Now Night Database will be pointing to Day.Later region will be completely switched to day.

My problem in database is getting started between first region switch and first Database switch. It is working fine during remaining timings.

The table in the database is given as follows.

create table records(system_no char(3),cust_id char(10),rec_no char(4),start_date char(8),end_date char(9),--------);

create index recpk on records(system_no,cust_id,rec_no);

The field rec_no contains the values in descending order from 9999 to 0001 which is a part of the primary key as given above

"Unexpected result" in the sense , While I use the following query

select /*+ INDEX_ASC(records recpk) */ * from records where ((system_id='123' and cust_id='3456218791') and rec_no>'0000' ) and rownum<2 order by system_id asc,cust_id asc,rec_no asc;

The above query should retrieve the minimum rec_no from the "records" table basing on the Index.

But it is retrieving maximum rec_no from the table with out using index.


Note:- The query is working fine in one instance1 and giving wrong info in other instance2 and vice verse.

I am not able to figure the problem, why is the database not using index only in such particular situation. Should I alter any Parameters during that Time.

Please suggest a Solution.

Thanks in Advance.

and Tom said...

this query is wronger than wrong:

select /*+ INDEX_ASC(records recpk) */ * from records where ((system_id='123' and cust_id='3456218791') and rec_no>'0000' ) and rownum<2 order by system_id asc,cust_id asc,rec_no asc;

it is giving CORRECT information on all nodes because what is says to do is:

find one random records that match the predicate and then sort it.

You want to get the data matching your predicate, sort them and then get the first two.


IT HAS TO BE:

select * from (
select /*+ first_rows(2) */ * from records where ((system_id='123' and cust_id='3456218791') and rec_no>'0000' )
order by system_id asc,cust_id asc,rec_no asc)
where rownum < 2;


that is the ONLY way to write that query semantically correctly.


Use that and only that query. Find the developer that wrote the bad query and educate them on how rownum works. This is called a top-n query and can only be written using rownum with an inline view. There are no shortcuts, you have to use the inline view to do a top-n query - period.

http://docs.oracle.com/cd/E11882_01/server.112/e17118/pseudocolumns009.htm#SQLRF00255



You cannot rely on a hint being observed - they are called HINTS, they are not directives. If the index is unavailable for any reason (different name, unusable, whatever), then the query will just ignore it. If it were a directive, the query would fail, but it won't. You have to code it the way I've demonstrated and I would recommend not using the INDEX_ASC hint.


ugh, and you've used strings to store all kinds of numbers :(

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment