Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: query "select * from table_name where rownum = 1" very slow

Re: query "select * from table_name where rownum = 1" very slow

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 13 Jul 2005 07:11:39 -0700
Message-ID: <1121263899.903628.247790@g14g2000cwa.googlegroups.com>


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



  GL_JE_ID GL_JE_TRX_ID ITEM_NO
---------- ------------

JE_NUM JE_ORG_UNIT_ID JE_SRC JE LINE_NO ORDER_NO
------------ -------------------- ------------ -- ---------- ----------
ORDER_SUFFIX ORG_UNIT_ID          PL   POST_AMT POST_DATE  RECV_LINE
RECV_NO
------------ -------------------- -- ---------- --------- ----------


REFERENCE1

TRACKING_SEQ TRANS_NO TRX_DATE VENDOR_ID WAREHO SERIAL_NO ------------ ---------- --------- ---------- ------ --------------- ACTIVITY_BATCH B
-------------- -
ACOR N
200412200000000000
    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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US