CODEWITHSUNDEEP
sqlperformanceoracle-database
Roman
Roman

Reputation: 66156

Why SQL query can take so long time to return results?

I have an SQL query as simple as:

select * from recent_cases where user_id=1000000 and case_id=10095;

It takes up to 0.4 seconds to execute it in Oracle. And when I do 20 requests in a row, it takes > 10s.

The table 'recent_cases' has 4 columns: ID, USER_ID, CASE_ID and VISITED_DATE. Currently there are only 38 records in this table.

Also, there are 3 indexes on this table: on ID column, on USER_ID column, and on (USER_ID, CASE_ID) columns pair.

Any ideas?

Upvotes: 1

Views: 2097

Answers (2)

DennisH
DennisH

Reputation: 383

Oracle Databases have a function called "analyze table". This function can speed up select statements a lot, even if there are just a few rows in the table.

Here are some links which might help you:

http://www.dba-oracle.com/t_oracle_analyze_table.htm

http://docs.oracle.com/cd/B28359_01/server.111/b28310/general002.htm

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52336

One theory -- the table has a very large data segment and high water mark near the end, but the statistics are not prompting the optimiser to use an index. Therefore you're getting a slow full table scan. You could ALTER TABLE ... MOVE and rebuild the indexes to fix such a problem, or COALESCE it.

Upvotes: 3

Related Questions