3 Replies Latest reply: Aug 1, 2011 4:26 PM by Frank Kulash RSS

    CREATE TABLE AS SELECT PROBLEM

    798294
      Dear members,

      I created a table whose definition is a select query.

      for ex: I created a table xx_customer as :
      create table xx_customer as
      select customer_name,customer_number
      from xx_ar_customer
      where org_id = '87'
      when i run the query
      select  count(*)
      from xx_customer;
      The count was 120 records.

      This was done as part of performance tunning.

      Now few days after the table xx_customer was created few records were inserted into table xx_ar_customer for org_id = '87' .

      So ideally the count should be more than 120 but its not.

      when i run the same query again
      select  count(*)
      from xx_customer;
      I get count as 120 Records which is wrong.

      It looks like it not refreshing data. If i run the table definition query i am getting count as more than 120 but if i do a select on the table count is still 120.

      Any ideas?

      Thanks
      Sandeep
        • 1. Re: CREATE TABLE AS SELECT PROBLEM
          Solomon Yakobson
          795291 wrote:

          I created a table whose definition is a select query.
          I think you are confusing table and view. You created a table based on what AT THE TIME was in table xx_ar_customer. You need a view:
          create view xx_customer as
          select customer_name,customer_number
          from xx_ar_customer
          where org_id = '87'
          /
          Then it will always return you as many rows as there are CURRENTLY in table xx_ar_customer org_id = '87'.

          SY.
          • 2. Re: CREATE TABLE AS SELECT PROBLEM
            798294
            I cant create view. I tried that before. If i use view then my query runs for a very long time and if i create a table then the run time is reduced by half :)

            So if we create Table as a select statement, then will it give data at the point of time it was created? Will it not give latest data?
            • 3. Re: CREATE TABLE AS SELECT PROBLEM
              Frank Kulash
              Hi,
              795291 wrote:
              I cant create view. I tried that before. If i use view then my query runs for a very long time and if i create a table then the run time is reduced by half :)

              So if we create Table as a select statement, then will it give data at the point of time it was created?
              Exactly!
              Will it not give latest data?
              Not unless the latest data happens to be the same as the data at the time it was created.

              CREATE TABLE AS is kind of like putting a photograph of yourself on your web site. If you smile, that doesn't mean the picture smiles.
              CREATE VIEW is kind of like hanging a video camea from your hat, and streaming the output to your web page. As soon as you smile, the picture smiles. It's more expensive than a still picture.

              A compromise apprioach is a Materialized View , which is really a type of table. Like any other table, it occupies space, and is relatively fast to use. You can define a materialized view to be refreshed at regular intervals (once a day, once an hour, once a week, ...) or whenever there is a change in the base table(s).