6 Replies Latest reply on Mar 11, 2008 10:27 AM by 609674

    who invalidate the index?

    609674
      I made the following statement yesterday.because I have created the index for triple.get_subject(),so it is fast.
      But today, when I excuted it again I got a lower efficiency.And then,I used the tool PL/SQL Developer's Explain Plan Window to analyzing the SQL. I saw the "table access full" on rdf_link$ and stu_rdf.

      So,would you please let me know the reason? I don't want to use /*+index()*/.

      Thanks.

      select t.triple.get_triple(),decode(v.value_type,'UR','URI','TL','URI','PL','LIT','PL@','LIT','BN','BLN')
      from stu_rdf t,(
      select '<' || Student || '>' Student from
      (
      select Student from TABLE
      (SEM_MATCH( '(?Student rdf:type :Student)(?Student :hasName "Jone")',
      SEM_Models('stu_rdf'),null,
      SEM_Aliases(SDO_RDF_Alias('rdf','http://www.w3.org/1999/02/22-rdf-syntax-ns#'),
      SEM_Alias('', 'http://www.aaa.bbb/#')),
      null))

      ) instanceRow where rownum < 1001
      ) instanceID,mdsys.rdf_value$ v
      where t.triple.get_subject() = instanceID.Student
      and t.triple.rdf_o_id = v.value_id
        • 1. Re: who invalidate the index?
          609674
          And then, I used the following statement, It was still Table Access Full.

          select /*+index(stu_rdf_sub_idx)*/ t.triple.get_subject()
          from stu_rdf t
          where t.triple.get_subject() = '<http://www.aaa.bbb/#/StudyID:6185>'


          But when I change /*index(stu_rdf_sub_idx) to /*RULE*/ as below, I got Index(Range Scan), it is fast.

          select /*+RULE*/ t.triple.get_subject()
          from stu_rdf t
          where t.triple.get_subject() = '<http://www.aaa.bbb/#/StudyID:6185>'


          So , I tried to add /*+RULE*/  into the first statement that I posted previous message, but it was noneffective. it was still Table Access Full.


          Hope any suggestions and helps.

          steven, Mar 6 2008
          thanks.
          • 2. Re: who invalidate the index?
            Mannamal-Oracle
            Hi Steven,

            Have you gathered statistics? Both on the stu_rdf table and on the internal storage tables using sem_perf.gather_stats? (see chapter 4 in the documentation).

            Melli
            • 3. Re: who invalidate the index?
              609674
              Hi Melli,

              I only gathered statistics on stu_rdf. Ok,I see.

              Thanks,thank you very much.

              Steven.
              • 4. Re: who invalidate the index?
                609674
                Hi Melli,

                internal storage tables? you mean that I should login as mdsys to run "SEM_PERF.GATHER_STATS",right ?

                but, today, I ran it , it was still "Table Full Access".


                I don't know why? I think I need your help very much.


                Steven,
                Mar 10 2008
                • 5. Re: who invalidate the index?
                  Mannamal-Oracle
                  Can you re-gather statistics for the stu_rdf table, this time using cascade=true and no_invalidate=false options?

                  If it still does not help, can you write to me at melliyal <dot> annamalai <at> oracle <dot> com and we can discuss this offline? We can post a summary on this forum once the problem is resolved.

                  Melli
                  • 6. Re: who invalidate the index?
                    609674
                    I have done this, but it is still TABLE ACCESS FULL.

                    I just sended a mail to you. Expecting your help.


                    thanks a lot
                    Steven