5 Replies Latest reply: Feb 16, 2012 6:34 AM by Rydman RSS

    Logging what data inserted from external table

    Rydman
      Hello,

      We load a lot of data using external tables and inserts them in tables partitioned by day or hour.

      Other parts of the system want to know which partitions were updated and when, so before we load any data we do a
      insert into new_data(date_of_event) values (select distinct(trunc(date_of_event) from external_table)
      This is when inserting into day partitions, ie getting all distinct days from that specific file.

      These files, however, can be quite large and after this first scan, we do a full insert into to the proper data-table, ie reading the entire file again.

      So my question is, is there any other (better) way for retrieving the different days/hours? Some way of doing the actual insert into the data-table and getting the information on which partitions Oracle inserted the data in from any dictionary etc?

      Haven't tried creating a "proper" temp-table, inserting the data into this one, extracting the dates and then inserting into to the real data-table. But then, what is best, creating a table and dropping it, or reading the file twice?

      Thanks in advance
        • 1. Re: Logging what data inserted from external table
          LKBrwn_DBA
          Rydman wrote:

          We load a lot of data using external tables and inserts them in tables partitioned by day or hour.

          ... Etc ...

          So my question is, is there any other (better) way for retrieving the different days/hours? Some way of doing the actual insert into the data-table and getting the information on which partitions Oracle inserted the data in from any dictionary etc?
          Perhaps you could pre-create all the possible partitions with minimum initial extent and larger next extent so that missing days/hours do not consume space.
          Then read the data only once.
          ;)
          • 2. Re: Logging what data inserted from external table
            Rydman
            LKBrwn_DBA wrote:
            Rydman wrote:

            We load a lot of data using external tables and inserts them in tables partitioned by day or hour.

            ... Etc ...

            So my question is, is there any other (better) way for retrieving the different days/hours? Some way of doing the actual insert into the data-table and getting the information on which partitions Oracle inserted the data in from any dictionary etc?
            Perhaps you could pre-create all the possible partitions with minimum initial extent and larger next extent so that missing days/hours do not consume space.
            Then read the data only once.
            ;)
            Well, that is really not an option, since there are already data in all of the different partitions...
            • 3. Re: Logging what data inserted from external table
              AndyKlock
              >
              Some way of doing the actual insert into the data-table and getting the information on which partitions Oracle inserted the data in from any dictionary etc?
              >

              I'm still not sure I understand what you are trying to accomplish, however, if you want to see what sort of inserts have been happening on your partitions between your external table load jobs you could peek at ALL_TAB_MODIFICATIONS. You didn't list which version you are running, so there are some caveats. For example, in 11g you need to run DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO to flush this data from memory so it is accessible to your query.
              • 4. Re: Logging what data inserted from external table
                rp0428
                You don't say how you are doing the querying and inserting but if you have some control over it a couple of possibilities come to mind.

                1. Use INSERT ALL to do a multi-table insert. Insert the 'date_of_event' into a new 'log' table and insert the other data to the table you insert into now.
                Then you can do the 'select distinct(trunc(date_of_event)' on the new log table. Just truncate the new log table before each query or add a column that includes the file that was imported.

                2. Create a VIEW that includes all columns of your current query and another copy of 'date_of_event' and then peel off the event date into another table.

                Option #1 is the simplest.

                See INSERT ALL in the SQL Reference: http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9014.htm
                • 5. Re: Logging what data inserted from external table
                  Rydman
                  Thanks!

                  I do have control of the statements etc, and doing a multiple table insert when reading the data worked like a charm!

                  Thanks!

                  Cheers,

                  Richard