Re: View questions...

From: Kees Verruyt <kverruyt_at_nl.oracle.com>
Date: 6 Nov 92 09:31:02 GMT
Message-ID: <3197_at_nlsun1.oracle.nl>


In article <UN.92Nov5120254_at_gdstech.GRUMMAN.COM>, un_at_gdstech.GRUMMAN.COM (Un Fu) writes:
|> How can I get the block usage of a view I created? Looked
|> into "user_extents" and it only displays block usages of tables.

As their name implies, VIEWs don't use space. They retrieve their data from the underlying table.

|>
|> Also how do I direct the creation of a view or several views to use
|> a temporary, TEMP_TS, tablespace. I see one can specify which
|> tablespace to create a table at, but not on the 'CREATE VIEW' command.

Again, selecting from a view is just the same as selecting from the underlying tables. The data is only stored in the tables.

The only thing that I can think of is that your view-definition joins tables which do not have indexes. In that case the server will have to do a sort-merge scan which can result in temporary tables. These are assigned to the TEMPORARY TABLESPACE for that user. If you haven't defined that for the user, that will be SYSTEM.

So it's very wise to create a separate tablespace (fi. TEMP) and then execute for every user in your system:

ALTER USER ... TEMPORARY TABLESPACE TEMP; (This will prevent repeated allocation & deallocation of small extents in  your 'data-tablespaces'. In the temporary tablespace that cannot do harm since  all temporary tables are deallocated when they are no longer needed, thus  ensuring that the free extents are contiguous and can be coalesced into larger  ones.)

|>
|> I have been asking lots of question lately and this newsgroup has been
|> tremendously responsive. THANK YOU ALL, FOLKS!
|> --
|> ****************************************************************************
|> Un Un Fu Internet: un_at_gdstech.grumman.com
|> Disclaimer: I am solely responsible for what I am saying here!
|> ****************************************************************************
|> --
|> ****************************************************************************
|> Un Un Fu Internet: un_at_gdstech.grumman.com
|> Disclaimer: I am solely responsible for what I am saying here!
|> ****************************************************************************

You're welcome.

-- 
EXEC SQL INCLUDE STD_DISCLAIMER; -- My views, not Oracle's.
------------------------------------------------------------------------
Kees Verruijt                           Rijnzathe 6
Software Engineer Gateway development   3454 PV  DE MEERN
Oracle Europe                           The Netherlands
Email: kverruyt_at_nl.oracle.com           Tel: +31 3406 94886
   or: kverruyt_at_oracle.nl               Fax: +31 3406 65603
Received on Fri Nov 06 1992 - 10:31:02 CET

Original text of this message