| Query with less rows takes less time ? [message #21080] |
Wed, 10 July 2002 23:15  |
Deborrah
Messages: 8
Registered: July 2002
|
Junior Member |
|
|
I have a doubt here on optimization.
I am having Oracle 7.3. There is a table with about
300,000+ records. When I query on this table, it takes
a long time to retrieve it. The indexes are exactly
as the where condition are. It takes about 60 seconds
for a query.
However, I have another schema in the same database.
I created a replica of this table with a few rows in
it. (used create table as (select from ...) .
I took about 400 rows only which are rquired for my
calculation. I have no indexes at all in this table
in the new schema.
When I queried on this table, it takes hardly about
10 seconds for my query to run. My doubts is : Is it
natural that a similar table with less number of rows
would be faster to access. I raise this question
because, I heard somewhere that for selecting/
inserting rows, the size of the table should not
matter.
Note: There is no group by function in my query.
TIA
Debby
|
|
|
|
| Re: Query with less rows takes less time ? [message #21083 is a reply to message #21080] |
Thu, 11 July 2002 09:38  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
I'm a bit surprised that you're surprised that a query on a table with 400 rows takes less time than the same query on a table with 300K+ rows. The query on the small table is doing a full-table scan (since there are no indexes) and, in some cases, that can be more efficient than using indexes.
But you're comparing apples and oranges here. The same query will run faster on 400 vs. 300K rows - indexes or not. You should never assume similar performance with such wildly different row counts. I'm wondering why your query even took 10 seconds to run with only 400 rows - that seems way, way too long.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
