Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    May 2002
    Posts
    62

    Unanswered: Efficient query to check whether a table is empty

    Hi,

    Can someone suggest an efficient query to check whether a table is empty ?

    Thanx..
    -Bheemsen

  2. #2
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: Efficient query to check whether a table is empty

    Originally posted by bheemsen
    Hi,

    Can someone suggest an efficient query to check whether a table is empty ?

    Thanx..
    -Bheemsen
    try
    select 'NOT EMPTY' from dual where exists (select 'x' from mytable)

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Can someone suggest an efficient query to check whether a table is empty ?
    Quantify efficient.
    Efficient based upon which metric?
    What problem are you REALLY trying to solve?
    Does "empty" equal zero rows in the table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You could do:

    select count(*) from table where rownum=1;

    That will return either 0 (empty) or 1 (not empty).

  5. #5
    Join Date
    May 2002
    Posts
    62
    Originally posted by anacedent
    >Can someone suggest an efficient query to check whether a table is empty ?
    Quantify efficient.
    Efficient based upon which metric?
    What problem are you REALLY trying to solve?
    Does "empty" equal zero rows in the table?
    Efficient based on performance wise (should execute in less time).
    I need to know whether the table has any rows. I need to check whether the rows exist in 15 to 20 tables before I trigger another process in a perl script.

    Thanx..
    -Bheemsen

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Upon further review, I agree with Tony Andrews answer.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    this seems to work:
    PHP Code:
    SELECT 1 FROM tablename
    WHERE EXISTS 
    (SELECT anyPKcolumn FROM tablename)
    AND 
    ROWNUM 
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110

    Re: Efficient query to check whether a table is empty

    i tested with some big tables with and without index, it sounds to me
    the exists way is faster than rownum = 1 way. Any idea why?

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    The statistics below indicate ROWNUM=1 does less work.

    1 SELECT 1 FROM dba_objects
    2 WHERE EXISTS (SELECT object_name FROM dba_objects)
    3 AND ROWNUM < 2
    14:09:47 SYS: DBW.WORLD>/

    1
    ----------
    1


    Statistics
    ----------------------------------------------------------
    23 recursive calls
    0 db block gets
    23 consistent gets
    2 physical reads
    0 redo size
    376 bytes sent via SQL*Net to client
    498 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    14:09:51 SYS: DBW.WORLD>select count(*) from dba_objects where rownum = 1;

    COUNT(*)
    ----------
    1

    Statistics
    ----------------------------------------------------------
    7 recursive calls
    0 db block gets
    10 consistent gets
    0 physical reads
    0 redo size
    383 bytes sent via SQL*Net to client
    498 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    14:10:30 SYS: DBW.WORLD>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by anacedent
    The statistics below indicate ROWNUM=1 does less work.

    1 SELECT 1 FROM dba_objects
    2 WHERE EXISTS (SELECT object_name FROM dba_objects)
    3 AND ROWNUM < 2
    14:09:47 SYS: DBW.WORLD>/

    1
    ----------
    1


    Statistics
    ----------------------------------------------------------
    23 recursive calls
    0 db block gets
    23 consistent gets
    2 physical reads
    0 redo size
    376 bytes sent via SQL*Net to client
    498 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    14:09:51 SYS: DBW.WORLD>select count(*) from dba_objects where rownum = 1;

    COUNT(*)
    ----------
    1

    Statistics
    ----------------------------------------------------------
    7 recursive calls
    0 db block gets
    10 consistent gets
    0 physical reads
    0 redo size
    383 bytes sent via SQL*Net to client
    498 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    14:10:30 SYS: DBW.WORLD>
    can you try

    select 1 from dual where exists (select 'x' from my table)

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Results in a few more consistent gets plus 1 (more) physical read.

    How many angels can dance on the head of a pin?


    14:10:30 SYS: DBW.WORLD>select 1 from dual where exists (select 'x' from dba_objects);

    1
    ----------
    1

    Statistics
    ----------------------------------------------------------
    7 recursive calls
    0 db block gets
    13 consistent gets
    1 physical reads
    0 redo size
    376 bytes sent via SQL*Net to client
    498 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  12. #12
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    intrsch@ACPT> select count(*) from dba_objects where rownum = 1;

    COUNT(*)
    ----------
    1

    Elapsed: 00:00:04.69


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    172239 consistent gets
    87 physical reads
    0 redo size
    195 bytes sent via SQL*Net to client
    248 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    intrsch@ACPT> select 1 from dual where exists (select 'x' from dba_objects);

    1
    ----------
    1

    Elapsed: 00:00:01.71


    Statistics
    ----------------------------------------------------------
    0 recursive calls
    0 db block gets
    11 consistent gets
    0 physical reads
    0 redo size
    188 bytes sent via SQL*Net to client
    248 bytes received via SQL*Net from client
    2 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    1 rows processed

    Any idea why?

  13. #13
    Join Date
    Jul 2003
    Posts
    2,296
    the above post gave me an idea.

    why not select from user_tables where num_rows > 0?

    SELECT
    a.table_name,
    a.num_rows
    FROM user_tables a
    where num_rows > 0;


    you could also add a where clause for the tables you want to check.
    this requires the tables to have been analyzed in the recent past.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  14. #14
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    Originally posted by The_Duck
    the above post gave me an idea.

    why not select from user_tables where num_rows > 0?

    SELECT
    a.table_name,
    a.num_rows
    FROM user_tables a
    where num_rows > 0;


    you could also add a where clause for the tables you want to check.
    this requires the tables to have been analyzed in the recent past.
    I think that number gets refreshed only after every analyzing, is it?
    Otherwise it's necessary the true number of rows in the table.

  15. #15
    Join Date
    Jul 2003
    Posts
    2,296
    Originally posted by lynden.zhang
    I think that number gets refreshed only after every analyzing, is it?
    Otherwise it's necessary the true number of rows in the table.
    yes, but I mention that in my post.

    Besides, this person is not interested in how many rows are in a table,
    just whether the table has ANY at all.

    If the tables get emptied and filled constantly then this is obviously
    not the option to use.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •