5 Replies Latest reply on Jun 30, 2017 5:33 PM by Matperry-Oracle

    recover model from rdf_data?

    3274437

      Dear all,

       

      I did something stupid and dropped my model by mistake with "sem_apis.drop_sem_model". However, I see that the triples are still in table RDF_DATA with query:

       

      select a.triple.get_subject(), a.triple.get_property(), a.triple.get_object()

      from rdf_data a ;

       

      If I try to recreate the same model I get an error like "Table RDF_DATA already contains data".

       

      So, is there a way to recover the dropped model using the information in table RDF_DATA?

       

      Thanks in advance,

      Elisa.

        • 1. Re: recover model from rdf_data?
          Matperry-Oracle

          Hi Elisa,

           

          The best way is to export the lexical values into a staging table as follows:

           

          create table rdf_data_export(rdf$stc_sub varchar2(4000), rdf$stc_pred varchar2(4000), rdf$stc_obj varchar2(4000))

          compress

          nologging;

           

          insert /*+ append */ into rdf_data_export(rdf$stc_sub, rdf$stc_pred, rdf$stc_obj)

          select v1.value_name, v2.value_name, v3.value_name

          from rdf_data r, mdsys.rdf_value$ v1, mdsys.rdf_value$ v2, mdsys.rdf_value$ v3

          where r.triple.rdf_s_id = v1.value_id and

                r.triple.rdf_p_id = v2.value_id and

                r.triple.rdf_c_id = v3.value_id;

           

          commit;

           

          After you verify the exported data. You can truncate the rdf_data application table, re-create your model and then bulk load from rdf_data_export.

           

          If you have CLOB literals, you will need to create two export staging tables (one with a CLOB rdf$stc_obj column and one with a VARCHAR) and use two insert as select statements. One that selects v3.value_name WHERE v3.long_value is null, and one that selects v3.long_value WHERE v3.long_value is not null. You can then call bulk_load on the VC table and use insert as select into the application table from the CLOB table to load the CLOB triples.

           

          Hope this helps,

          Matt

          • 2. Re: recover model from rdf_data?
            3274437

            Hi Matt,

             

            Yeah I tried that, but since I have a lot of data and it is a really big insert it didn't work. So I found that I can do incremental commit using bulk collect and for all, something like the following.

             

            You think this will work? I did a small sample test and it did work, but I don't know for all the data.

             

            And why you join with mdsys.rdf_value$ instead of selecting with triple.get_subject? Is it better this way? Doesn't take longer?

             

            Thanks,

            Elisa.

             

            DECLARE
            commit_interval pls_integer := 10 ;
            uncommitted pls_integer := 0;
            fetch_size pls_integer := 2 ;
            total pls_integer := 0 ;
            
            cursor gl is
                select a.triple.get_subject() sub, a.triple.get_property() pred, a.triple.get_object() obj
                from rdf_data_test a;
            TYPE GL_T is table of gl%rowtype ;
            gl_array GL_T;
            
            BEGIN
                open gl ;
               
                loop
                    fetch gl bulk collect into gl_array limit fetch_size ;
               
                    forall i in gl_array.first .. gl_array.last
                        insert into stage_table(RDF$STC_sub, RDF$STC_pred, RDF$STC_obj)
                        values (gl_array(i).sub, gl_array(i).pred, gl_array(i).obj) ;
               
                    uncommitted := uncommitted + sql%rowcount ;
               
                    exit when gl_array.last < fetch_size ;
               
                    if uncommitted >= commit_interval then
                        commit ;
                        total := total + commit_interval;
                        dbms_output.put_line(total);
                        uncommitted := 0;
                    end if ;
               
                end loop ;
                commit ;
                close gl;
            END ;
            
            • 3. Re: recover model from rdf_data?
              Matperry-Oracle

              When you say it didn't work, does that mean it ran out of space?

               

              The application table only stores ids, so each call to get_subject(), etc. does a lookup query against RDF_VALUE$. When getting many values, it's better to do a single join instead of tons of lookups.

               

              The PL/SQL loop may be worth a try, but it could be slow. Do you have enough space left to hold another copy of the data? The staging table could be quite a bit larger than the application table since it is storing varchar lexical values instead of ids.

              • 4. Re: recover model from rdf_data?
                3274437

                Hi Matt,

                 

                No no, I think that I lost the connection or something. I saw that table RDF_DATA had 13 GB and I let running the insert select all night, the next day I saw that STAGE_TABLE had 19 GB but it was not increasing anymore and neither did the commit...That's why I would like to do incremental commits.

                 

                I have 63GB left. I think is it okay, right?

                 

                Thanks,

                Elisa.

                • 5. Re: recover model from rdf_data?
                  Matperry-Oracle

                  That should be enough space. I would try the insert as select rather than a PL/SQL loop. You could do something like follows to do the insert in 4 chunks.

                   

                  insert /*+ append parallel(4) */ into rdf_data_export(rdf$stc_sub, rdf$stc_pred, rdf$stc_obj)

                  select v1.value_name, v2.value_name, v3.value_name

                  from rdf_data r, mdsys.rdf_value$ v1, mdsys.rdf_value$ v2, mdsys.rdf_value$ v3

                  where r.triple.rdf_s_id = v1.value_id and

                        r.triple.rdf_p_id = v2.value_id and

                        r.triple.rdf_c_id = v3.value_id and

                        mod(r.triple.rdf_sid, 4) = 0;

                   

                  commit;

                   

                  insert /*+ append parallel(4) */ into rdf_data_export(rdf$stc_sub, rdf$stc_pred, rdf$stc_obj)

                  select v1.value_name, v2.value_name, v3.value_name

                  from rdf_data r, mdsys.rdf_value$ v1, mdsys.rdf_value$ v2, mdsys.rdf_value$ v3

                  where r.triple.rdf_s_id = v1.value_id and

                        r.triple.rdf_p_id = v2.value_id and

                        r.triple.rdf_c_id = v3.value_id and

                        mod(r.triple.rdf_sid, 4) = 1;

                   

                  commit;

                   

                  insert /*+ append parallel(4) */ into rdf_data_export(rdf$stc_sub, rdf$stc_pred, rdf$stc_obj)

                  select v1.value_name, v2.value_name, v3.value_name

                  from rdf_data r, mdsys.rdf_value$ v1, mdsys.rdf_value$ v2, mdsys.rdf_value$ v3

                  where r.triple.rdf_s_id = v1.value_id and

                        r.triple.rdf_p_id = v2.value_id and

                        r.triple.rdf_c_id = v3.value_id and

                        mod(r.triple.rdf_sid, 4) = 2;

                   

                  commit;

                   

                  insert /*+ append parallel(4) */ into rdf_data_export(rdf$stc_sub, rdf$stc_pred, rdf$stc_obj)

                  select v1.value_name, v2.value_name, v3.value_name

                  from rdf_data r, mdsys.rdf_value$ v1, mdsys.rdf_value$ v2, mdsys.rdf_value$ v3

                  where r.triple.rdf_s_id = v1.value_id and

                        r.triple.rdf_p_id = v2.value_id and

                        r.triple.rdf_c_id = v3.value_id and

                        mod(r.triple.rdf_sid, 4) = 3;

                   

                  commit;