7 Replies Latest reply: Aug 31, 2006 11:32 PM by Kmensah-Oracle RSS

    JDBC Driver bug: ANALYZE TABLE when calling getIndexInfo()

    castorp
      Hello,

      why does the JDBC driver issue an ANALYZE TABLE statement before retrieving the index information when the DatabaseMetaData.getIndexInfo() is called?

      This prevents any read-only user to retrieve index information (because getIndexInfo() bombs out with an "permission denied" exception)

      Thomas
        • 1. Re: JDBC Driver bug: ANALYZE TABLE when calling getIndexInfo()
          82532
          It is supposed to update the index statistics unless the last parameter is TRUE. However, this flag seems to not matter and it always tries to update the stats. See enhancement #2593780.

          • 2. Re: JDBC Driver bug: ANALYZE TABLE when calling getIndexInfo()
            castorp
            So I guess I have to live with the fact that I cannot use this function, and have to write my own query for it. I still don't understand why it is necessary at all to do an ANALYZE. It's not only the fact that a read only user can't use the function, but it's dead slow as well!

            How do I see enhancement #2593780? I couldn't find a link on otn where I could browse for that number, a search for it, didn't return anything "valid" either.
            • 3. Re: JDBC Driver bug: ANALYZE TABLE when calling getIndexInfo()
              82532
              The JDBC spec. requires the stats updated unless the last parameter is false. This equates to an ANALYZE in Oracle. The bad part is not being able to stop it since setting the parameter flag doesn't help. I question this being an enhancement over a bug for that alone.

              The users groups (IOUG, ODTUG, etc.) have access to enhancements (and help vote on them for implementation). You should be able to see bugs/enhancements via MetaLink.

              You could also contact support and press them to bump this to a bug.
              • 4. Re: JDBC Driver bug: ANALYZE TABLE when calling getIndexInfo()
                149017
                The JDBC spec. requires the stats updated unless the
                last parameter is false.
                The problem is that the desired behaviour is unclear when the last parameter ("approximate") is true.

                (..)
                approximate - when true, result is allowed to reflect approximate or out of date values; when false, results are requested to be accurate
                (...)

                Oracle implementation doesn't show "out of date values" but tries to give "approximate results" via an "ANALYZE ... ESTIMATE".

                This leads again to the privilege problem...
                They could have coded it in the following way..


                if (!approximate) {
                ....analyze compute
                } else {
                ....try {
                ........analyze estimate
                ....} catch (SQLException) {* ignore */}
                }

                and that would have been a "good" compromise
                • 5. Re: JDBC Driver bug: ANALYZE TABLE when calling getIndexInfo()
                  428545
                  I had the same problem with Oracle 10g.
                  Replacing the getIndexInfo() by

                  SELECT * FROM USER_IND_COLUMNS

                  Didn't either help. Because my app is
                  using other JDBC meta info methods, I
                  am not sure whether the problem is that
                  the other JDBC meta info methods trigger
                  an analyze as well or because the above
                  select statement triggers an analyze.

                  Best Regards
                  • 6. Re: JDBC Driver bug: ANALYZE TABLE when calling getIndexInfo()
                    485912
                    I am also having this problem, please could we have some kind of fix. I found it was only a problem when the table name was created with quotes. This bug exists in at least drivers (10.2.0.1.0), (10.1.0.2.0) and (9.2.0.5)

                    Matt
                    • 7. Re: JDBC Driver bug: ANALYZE TABLE when calling getIndexInfo()
                      Kmensah-Oracle
                      This is bug# 4999817; we will furnish a fix as soon as posible.

                      Kuassi - blog http://db360.blogspot.com/