4 Replies Latest reply on Dec 12, 2007 3:53 PM by Mannamal-Oracle

    query performance

    609674
      Hi,

      I want to search a triple in oracle 11g

      So, I am trying to use the following query

      select a.triple.get_triple()
      from TABLE_NAME a
      WHERE
      a.triple.get_subject() = '<http://www.aaaa.bbbb.com.ee/TTTT/TEST_GG_PP_STUDENT/StudentID:s-666666>'
      and a.triple.get_property() = '<http://www.aaaa.bbbb.com.ee/TTTT#classOf>'
      and to_char(a.triple.get_object()) = '<http://www.aaaa.bbbb.com.ee/TTTT/TEST_GG_PP_CLASS/ClassID:c-555555>'

      I ran the query, it was very slow. Before I ran it, I have created the three indexes on subject,property and object. I don't know, why like this, why so slow?

      Perhaps is because the character string too was long, I think.

      Have any good way to solve the problem?


      Waiting for your good suggestion, thank you very much.

      Message was edited by:
      user606671
        • 1. Re: query performance
          Mannamal-Oracle
          Hi,

          With the indexes on get_subject(), get_property(), and to_char(get_object()) the query should work efficiently. Did you use to_char while creating the index on get_object() as well?

          What is the size of your data, and what is the time taken that you are observing?

          Melli
          • 2. Re: query performance
            609674
            Hi Melli,

            Thank you for your quick response.

            For now, in our database I have 3217995 triples.

            I have used to_char creating the index on get_objcet().


            Every object like "http://www.aaaa.bbbb.com.ee/TTTT#....", maybe this is the reason for filting, it took 10 mins to get a result.
            • 3. Re: query performance
              609674
              Hi Melli,

              I have solved this problem just now.

              The reason for filting is I have not statistics for this table,the three indexes have not been used,the SQL statement was running on full table scan.

              So,I created CBO statistics for the table. Then it only took 0.01 seconds to get a result.


              Steven
              • 4. Re: query performance
                Mannamal-Oracle
                Hi Steven,

                Excellent!

                That was the next thing I was going to ask - whether you had generated statistics :)

                Melli