Re: Table and its view

In article <D1vEy3.7I4_at_mail.auburn.edu>, moorthy_at_eng.auburn.edu (Moorthy N. Rekapalli) writes:
|> Hello Everyone,
|> I have a table named "quarter". I also have another table named "quarter_view". When I describe them,
|>
|> SQL> desc quarter;
|> SQL> desc quarter_view;
|>
|> both of them are showing the same attributes (I mean columns). These two tables were created by my predecessor and I am doing that job now. Initially, I thought quarter is the table and quarter_view is view of quarter. But, they are not showing the sam|> e number of records. But, when I add a record into quarter_view, the count is automatically increasing in quarter also. Please see the following for an example.
|>
|> Initially, I did this.
|>
|> SQL> select count(*) from quarter_view;
|>
|> COUNT(*)
|> ----------
|> 2835
|>
|> SQL> select count(*) from quarter;
|>
|> COUNT(*)
|> ----------
|> 3972
|>
|> After adding a record in quarter_view, both rows in quarter_view and quarter are increased by one as shown below:
|>
|> SQL> select count(*) from quarter_view;
|>
|> COUNT(*)
|> ----------
|> 2836 -- initially it was 2835
|>
|> SQL> select count(*) from quarter;
|>
|> COUNT(*)
|> ----------
|> 3973 -- initially it was 3972
|>
|> 1) If quarter_view is a view of quarter, then it should show the same number of rows always.
|>
|> 2) If quarter_view is NOT a view of the quarter, it should not increase the count in quarter automatically.
|>
|> Please let me know what could be the problem.
|>
|> THANKS IN ADVANCE.
|>
|> Moorthy
|> (moorthy_at_eng.auburn.edu)
|>
|> ---
|> :=) Do unto others as you would wish to be done by others. :=)
|>

You were probably correct in that quarter_view is probably a view of quarter. But the counts are different due to a where clause on the view.

Take a look at the dictionary view dba_views

set long 5000
select text from dba_views where view_name = 'QUARTER_VIEW';

This will probably explain all.

  __________ /    __      __/______  |Jack C. Carter,  Systems Architect
    / _  _  /_   / ' _  _  /  _  _   |Cap Gemini America - Consulting
 \_/ (/ (_ /|   /_, (/ /' /, (' /'   |EMAIL jack.carter_at_nt.com
 ----------------------------------- |PHONE: (214) 933-4313