FAQ
All,

I came across a rather strange situation a few days ago. One of our
developers wrote a simple sql script that inserted some rows into a
table. However, he was getting things that were not expected. He ran
just the select portion of the code and got a different number of rows.
Any thoughts on what it might be or what to search for?

We are running on AIX Version 5.2 with Oracle Database 10g Enterprise
Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options.

Thanks,
Michael

The information contained in this message and any attachment may be
proprietary, confidential, and privileged or subject to the work
product doctrine and thus protected from disclosure. If the reader
of this message is not the intended recipient, or an employee or
agent responsible for delivering this message to the intended
recipient, you are hereby notified that any dissemination,
distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please notify me
immediately by replying to this message and deleting it and all
copies and backups thereof. Thank you.

Search Discussions

  • Rob Dempsey at Oct 19, 2007 at 12:13 pm
    I have seen different counts when there is an index on the table and
    oracle has not maintained the index.

    Check what the execution plan is. If it is using a index then try the
    same SQL with a /*+ FULL(t)*/ hint

    Rob

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    Michael.Coll-Barth_at_VerizonWireless.com
    Sent: 18 October 2007 20:50
    To: oracle-l_at_freelists.org
    Subject: select counts versus insert/select counts

    All,

    I came across a rather strange situation a few days ago. One of our
    developers wrote a simple sql script that inserted some rows into a
    table. However, he was getting things that were not expected. He ran
    just the select portion of the code and got a different number of rows.
    Any thoughts on what it might be or what to search for?

    We are running on AIX Version 5.2 with Oracle Database 10g Enterprise
    Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options.

    Thanks,
    Michael

    The information contained in this message and any attachment may be
    proprietary, confidential, and privileged or subject to the work
    product doctrine and thus protected from disclosure. If the reader
    of this message is not the intended recipient, or an employee or
    agent responsible for delivering this message to the intended
    recipient, you are hereby notified that any dissemination,
    distribution or copying of this communication is strictly prohibited.
    If you have received this communication in error, please notify me
    immediately by replying to this message and deleting it and all
    copies and backups thereof. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l

    --
    http://www.freelists.org/webpage/oracle-l
  • Rumpi Gravenstein at Oct 19, 2007 at 1:10 pm
    That's an excellent idea - I've seen this as well. If the index is
    corrupted, the indexed search will not return the correct results. In my
    case rebuilding the index fixed the issue.
    On 10/19/07, Rob Dempsey wrote:

    I have seen different counts when there is an index on the table and
    oracle has not maintained the index.

    Check what the execution plan is. If it is using a index then try the
    same SQL with a /*+ FULL(t)*/ hint

    Rob
    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Poder at Oct 19, 2007 at 2:07 pm
    This is a kind of post where the actual SQL statements would be *really*
    handy.

    The issue you have is probabaly not about Oracle returning wrong rowcounts
    but the developer *expecting* wrong rowcounts.

    Like this case with NULLs for example:

    SQL> create table t(a int);

    Table created.

    SQL> insert into t values(null);

    1 row created.

    SQL> select count(*) from t;

    COUNT(*)

    1

    SQL> select count(a) from t;

    COUNT(A)



    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com
    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of
    Michael.Coll-Barth_at_VerizonWireless.com
    Sent: Friday, October 19, 2007 03:50
    To: oracle-l_at_freelists.org
    Subject: select counts versus insert/select counts


    All,

    I came across a rather strange situation a few days ago. One
    of our developers wrote a simple sql script that inserted
    some rows into a table. However, he was getting things that
    were not expected. He ran just the select portion of the
    code and got a different number of rows.
    Any thoughts on what it might be or what to search for?

    We are running on AIX Version 5.2 with Oracle Database 10g
    Enterprise Edition Release 10.2.0.3.0 - 64bit Production With
    the Partitioning, OLAP and Data Mining options.

    Thanks,
    Michael


    The information contained in this message and any attachment
    may be proprietary, confidential, and privileged or subject
    to the work product doctrine and thus protected from
    disclosure. If the reader of this message is not the
    intended recipient, or an employee or agent responsible for
    delivering this message to the intended recipient, you are
    hereby notified that any dissemination, distribution or
    copying of this communication is strictly prohibited.
    If you have received this communication in error, please
    notify me immediately by replying to this message and
    deleting it and all copies and backups thereof. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
    --
    http://www.freelists.org/webpage/oracle-l
  • Daniel Fink at Oct 19, 2007 at 2:35 pm
    There is a bug associated with the new HASH GROUP BY operation where
    incorrect rows are returned. I don't recall the exact bug details, so it
    may or may not apply.

    --
    Daniel Fink

    Oracle Performance, Diagnosis and Training

    OptimalDBA http://www.optimaldba.com
    Oracle Blog http://optimaldba.blogspot.com

    Michael.Coll-Barth_at_VerizonWireless.com wrote:
    All,

    I came across a rather strange situation a few days ago. One of our
    developers wrote a simple sql script that inserted some rows into a
    table. However, he was getting things that were not expected. He ran
    just the select portion of the code and got a different number of rows.
    Any thoughts on what it might be or what to search for?

    We are running on AIX Version 5.2 with Oracle Database 10g Enterprise
    Edition Release 10.2.0.3.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options.

    Thanks,
    Michael


    The information contained in this message and any attachment may be
    proprietary, confidential, and privileged or subject to the work
    product doctrine and thus protected from disclosure. If the reader
    of this message is not the intended recipient, or an employee or
    agent responsible for delivering this message to the intended
    recipient, you are hereby notified that any dissemination,
    distribution or copying of this communication is strictly prohibited.
    If you have received this communication in error, please notify me
    immediately by replying to this message and deleting it and all
    copies and backups thereof. Thank you.

    --
    http://www.freelists.org/webpage/oracle-l


    --
    http://www.freelists.org/webpage/oracle-l
  • Michael.Coll-Barth_at_VerizonWireless.com at Oct 19, 2007 at 2:45 pm
    I see several responses out there, which we will look into and I will
    get back.

    As to the SQL, without giving you the tables and the data, I am not sure
    it would really help. We are having difficulties getting the problem to
    consistently replicate, even when using same data and tables in
    different schemas. Obviously, we will need something that we can go to
    Oracle with, but I was looking for general ideas to pursure rather than
    trying everything.

    The SQL statement was basically of this form;

    insert into tablex
    (
    a, b, c, d
    )
    select a, b, c, d
    from tabley y,

    tablez z
    where y.key = z.key;

    The number of rows 'select'ed is different than the number of rows
    'insert'ed when the code is cut and pasted into a SQL prompt.


    -----Original Message-----
    From: Tanel Poder

    This is a kind of post where the actual SQL statements would
    be *really*
    handy.
    The information contained in this message and any attachment may be
    proprietary, confidential, and privileged or subject to the work
    product doctrine and thus protected from disclosure. If the reader
    of this message is not the intended recipient, or an employee or
    agent responsible for delivering this message to the intended
    recipient, you are hereby notified that any dissemination,
    distribution or copying of this communication is strictly prohibited.
    If you have received this communication in error, please notify me
    immediately by replying to this message and deleting it and all
    copies and backups thereof. Thank you.
  • Edgar Chupit at Oct 19, 2007 at 4:03 pm
    Recently we have hit similar bug with MobileAdmin.

    When MobileAdmin is syncing data, it issues similar "insert as
    select" statements. This bug was very hard to track, because it also
    did not occurred on regular basis, but only from time to time.

    This metalink note describes situation: Note:406966.1 - Table
    Prefetching causes intermittent Wrong Results in 9iR2,10gR1 and 10gR2
    and associated Bug No. 5129039: WRONG RESULTS WITH INSERT AS SELECT
    AND TABLE PREFETCH

    In our case it was decided to use hidden parameter
    _table_lookup_prefetch_size=0.

    So actually, as you can see, there are a lot of bugs in Oracle that
    could cause the issue you are observing. What I would suggest is to
    try to create a simple test case, it will allow you to find out why
    are you getting this issue and then test if the solution is working or
    not.

    Best regards,
    Edgar

    On 10/19/07, Michael.Coll-Barth_at_verizonwireless.com
    wrote:

    The SQL statement was basically of this form;

    insert into tablex
    (
    a, b, c, d
    )
    select a, b, c, d
    from tabley y,
    tablez z
    where y.key = z.key
    ;

    The number of rows 'select'ed is different than the number of rows
    'insert'ed when the code is cut and pasted into a SQL prompt.
    --
    Best regards,
    Edgar Chupit
    callto://edgar.chupit
    --
    http://www.freelists.org/webpage/oracle-l
  • Jonathan_at_jlcomp.demon.co.uk at Oct 23, 2007 at 2:34 pm

    Date: Mon, 22 Oct 2007 12:55:22 -0700 (PDT)
    From: dba1 mcc
    Subject: [Q] Online rebuild index danger or NOT???

    We have ORACLE 10gr2 on Redhat server. Our
    application vendor told me don't "online rebuild
    index". They suggest kick out all users then rebuild
    index.

    I have been online rebuild index on several databases
    since ORACLE 8.1.7 and NO problem.

    I don't know why application vendor suggest don't
    "online rebuild index". I like listen your
    experience.

    Thanks.
    Oneline / Offline - doesn't matter; accidents can happen
    intermittently to random processes.

    http://jonathanlewis.wordpress.com/2007/09/16/index-rebuild/

    Regards
    Jonathan Lewis
  • Jonathan_at_jlcomp.demon.co.uk at Oct 23, 2007 at 5:07 pm
    It might be worth looking at the execution plans.
    Because of an oddity in costing, the plan for
    select ...
    may differ from the plan for
    insert into ... select ...

    The problem, of course, is knowing which plan is giving the correct answer.
    If the plans are different, you could try hinting the "alternative" plan to see what happens.

    Regards
    Jonathan Lewis
    http://jonathanlewis.wordpress.com
    http://www.jlcomp.demon.co.uk

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 18, '07 at 7:49p
activeOct 23, '07 at 5:07p
posts9
users7
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase