Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

    Unanswered: 10 minutes to return a count on an empty table?

    This query:
    SELECT center,
    officer,
    as_of_date,
    COUNT(*)
    FROM hbi_rpt_lead_report_temp
    GROUP BY center,
    officer,
    as_of_date
    HAVING COUNT(*) > 1;
    ...just took more than 10 minutes to execute on an EMPTY TABLE!

    Zero rows returned!

    I'm looking for absolutely any explanation of what Oracle has been doing for the past 10 minutes...

    Anybody?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    No useful facts provided & still expect a definitive answer. Unbelievable.
    Run SQL_TRACE on the query to collect objective facts so you can answer the question yourself.
    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.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No useful facts?
    I gave the query.
    I gave the content of the table.
    I gave the time.
    I gave the results.
    The next time I ran the query it ran quickly, so I can't replicate it.

    No useful help is what you gave.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Why do you think the table is empty? It can contain a lot of rows as well, just differing in (center, officer, as_of_date) as the HAVING clause restricts the resultset to groups of (center, officer, as_of_date) with more than one occurance in the table.

    Oracle just does what you told it to do.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by blindman
    ...just took more than 10 minutes to execute on an EMPTY TABLE!
    What does SELECT COUNT(*) FROM hbi_rpt_lead_report_temp give you?

    Do you have proper indexes on the columns you are grouping by?
    What is the execution plan of the query?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >No useful facts?
    >I gave the query.
    >I gave the content of the table.
    >I gave the time.
    >I gave the results.
    I contend that if these were meaninful to answering the question, you would NOT have made the post because the answer was intuitively obvious!

    As others have indicated having "zero rows returned" does NOT mean there are ZERO rows in the table.

    For all we know the table may be 4Petabytes in size & a full table scan was required. Oracle will have to scan to MWH before returning the results.
    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
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Empty means I had previously deleted all the records from the table. Duh.
    When I said the table was empty, what I meant was "THE TABLE IS EMPTY".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by blindman
    Empty means I had previously deleted all the records from the table.
    A new piece of information everybody here missed. It took some minutes, I suppose.
    First you should read about High Water Mark eg. here or here
    Then, (when you want to get rid of ALL data), TRUNCATE the table.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, that at least is some useful information. Thanks flyboy.
    So without truncating a table, Oracles thinks there MIGHT still be 18000 records in the table, and so it scans all those pages just in case? OK, I guess I can accept that. But even had the table still contained 18000 records I'm surprised that the query would have taken 10 minutes. 18000 is not a lot of data, even if it had not been indexed.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    flyboy,

    The Oracle books I have don't mention squat about Truncate, but I have read the link you provided. The link does not state this, but apparently I cannot issue the Truncate statement from within a stored procedure? I get a compile error when I try. Does that mean that I have to put up with the effects of the HWM, or is there a way around this?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Mar 2007
    Posts
    623
    When you delete a row, Oracle just marks it as unused and does not free the space. It may be reused later when inserting a row. However when using APPEND hint, the new space is taken and the table 'grows'.

    You can use it in PL/SQL dynamically using EXECUTE IMMEDIATE.
    However be aware, TRUNCATE is a DDL, so it commits the current transaction and starts a new one.

    If you permanently insert and delete rows in one transaction, you should consider using GLOBAL TEMPORARY one.

  12. #12
    Join Date
    Apr 2007
    Posts
    2
    I aggree with people here. You give not enough information. There may a be plenty of reasons why simple select takes 10min.

    - Did you make massive delete?
    - Did you commit after delete? Because if you not - for another session your table is not empty yet.

    I will add another 5 cents here. It seems obvious but again I don't have specific details about your db location. Make sure there is no network issue. If DB is behind some VPN or remote even select * from dual may take forever. PING the server first.

    - Roman

  13. #13
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by blindman
    The link does not state this, but apparently I cannot issue the Truncate statement from within a stored procedure? I get a compile error when I try. Does that mean that I have to put up with the effects of the HWM, or is there a way around this?
    This is because a stored procedure is PL/SQL whereas TRUNCATE is "only" SQL. Read the manual about data manipulation in PL/SQL.

    In a nutshell: to use TRUNCATE (amongst others) you will need to use dynamic SQL:
    Code:
    EXECUTE IMMEDIATE 'TRUNCATE TABLE mytable';

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dbaid
    I will add another 5 cents here. It seems obvious but again I don't have specific details about your db location. Make sure there is no network issue. If DB is behind some VPN or remote even select * from dual may take forever. PING the server first.
    Dude, no WAY a network issue is going to make "select count(*) from table" take that long. The network is sending a simple string and returning a simple string. The processing takes place on the server and has nothing to do with the network. But thanks for the nickel...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    (scratching my old Oracle 8 knowledge ... been a while) Doesn't Oracle work through complex cursor oriented rollback logs?

    If you do something big, like a huge delete, and don't commit it (or it's not done executing INTERNALLY even though a commit has been issued), then subsequent reads must serially read the rollback log to get the "as of" state of the data for each row requested. Of course; it's far more complex, but that's how I understood the basic concept.

    One of these Oracle GURUs here should have a link, sorry, I do not. But the concept may help to explain this symptom.

    Therefore, one critical piece of information is, how long before the SELECT did the DELETE take place, and how complex is this table in relation to the rest of the database (ie: dependent links) that may cause a DELETE to be very expensive in terms of time to ACTUAL completion.

Posting Permissions

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