Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: query "select * from table_name where rownum = 1" very slow
G, there should be little to no difference in the time to find a single
row from a single row table or a million row table:
Ran on 9.2.0.6 AIX 5.3
> set timing on
> select * from marktest where rownum = 1;
no rows selected
Elapsed: 00:00:00.04
> select * from ic_je_hist where rownum = 1;
ACCT_ D
----- -
GL_ACCT_ID
------------ -------------------- ------------ -- ---------- ---------- ORDER_SUFFIX ORG_UNIT_ID PL POST_AMT POST_DATE RECV_LINERECV_NO
------------ -------------------- -- ---------- --------- ----------
454281 38211 23533924 SY316548 200 PORC 02 1097 61977 200 25 -5903.76 28-SEP-04 1 1632869 VE:29522 PO:61977 LN:109 RC:1632869 RL:1 IT:23533924 41575528 28-SEP-04 29522
Elapsed: 00:00:00.08
> select count(*) from ic_je_hist;
COUNT(*)
5886770
Elapsed: 00:00:07.04
A table with a row length of 6,000 bytes would take longer to transfer to and display on your terminal than a 30 byte row but the run time of the queries should be very close. Also if the query fetched a block that was already in the buffer it would be faster than a version of the query which had to read the block from disk. And finally depending on your disk layout it is possible for access to one object to be better or worse than access to another object because of thow the files are laid out.
HTH -- Mark D Powell -- Received on Wed Jul 13 2005 - 09:11:39 CDT