This discussion is archived
3 Replies Latest reply: Aug 1, 2011 2:26 PM by Frank Kulash RSS

CREATE TABLE AS SELECT PROBLEM

798294 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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).

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points