What is the difference between a view and a materialized view?

What is the difference between a view and a materialized view?
by

1 Answer

Answer :

A view is a select that is executed each time an user accesses to it. A materialized view stores the result of this  query in memory for faster access purposes.
by

Related questions

What background process refreshes materialized views?

Description : What background process refreshes materialized views?

Last Answer : The Job Queue Processes.

Explain materialized views and how they are used.

Description : Explain materialized views and how they are used.

Last Answer : Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

I was just listening to the famous Dr. Martin Luther King speech. Would you say his dream has been completely materialized since the day he made that wonderful speech?

Description : I was just listening to the famous Dr. Martin Luther King speech. Would you say his dream has been completely materialized since the day he made that wonderful speech?

Last Answer : I believe Dr. King would be pleased with the progress but disappointed that after so long, there is still so much bigotry and racism in America.

Why do people constantly return to the refrigerator with hopes that something new to eat will have materialized?

Description : Why do people constantly return to the refrigerator with hopes that something new to eat will have materialized?

Last Answer : It’s possible…

You have just compiled a PL/SQL package but got errors, how would you view the errors?

Description : You have just compiled a PL/SQL package but got errors, how would you view the errors?

Last Answer : SHOW ERRORS

What view would you use to determine free space in a tablespace?

Description : What view would you use to determine free space in a tablespae?

Last Answer : DBA_FREE_SPACE

What view would you use to look at the size of a data file?

Description : What view would you use to look at the size of a data file?

Last Answer : DBA_DATA_FILES

Can a view be updated/inserted/deleted? If Yes under what conditions ?

Description : Can a view be updated/inserted/deleted? If Yes under what conditions ?

Last Answer : A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.

If a View on a single base table is manipulated will the changes be reflected on the base table ?

Description : If a View on a single base table is manipulated will the changes be reflected on the base table ?

Last Answer : If changes are made to the tables which are base tables of a view will the changes be reference on the view.

What are the advantages of VIEW ?

Description : What are the advantages of VIEW ?

Last Answer : To protect some of the columns of a table from other users.To hide complexity of a query.To hide complexity of calculations.

What is a view ?

Description : What is a view ?

Last Answer : A view is stored procedure based on one or more tables, it’s a virtual table.

What is a canvas-view?

Description : What is a canvas-view?

Last Answer : A canvas-view is the background object on which you layout the interface items (text-items, check boxes, radio groups, and so on.) and boilerplate objects that operators see and interact with as they ... assigned to a specific canvas. Each canvas, in term, must be displayed in a specific window.

What is the content view and stacked view?

Description : What is the content view and stacked view?

Last Answer : A content view is the "Base" view that occupies the entire content pane of the window in which it is displayed. A stacked view differs from a content canvas view in that it is not the base view for the window to which it is assigned

Do a view contain data?

Description : Do a view contain data?

Last Answer : Views do not contain or store data.

Can you have more than one content canvas view attached with a window?

Description : Can you have more than one content canvas view attached with a window?

Last Answer : Yes. Each window you create must have atleast one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.

How does one get the view definition of fixed views/tables?

Description : How does one get the view definition of fixed views/tables?

Last Answer : Query v$fixed_view_definition. Example: SELECT * FROM v$fixed_view_definition WHERE view_name='V$SESSION';

Can a view based on another view?

Description : Can a view based on another view?

Last Answer : Yes.

What is a View ?

Description : What is a View ?

Last Answer : A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

Do View contain Data ?

Description : Do View contain Data ?

Last Answer : Views do not contain or store data.

Can a View based on another View ?

Description : Can a View based on another View ?

Last Answer : Yes.

What is an Oracle view?

Description : What is an Oracle view?

Last Answer : A view is a virtual table. Every view has a query attached to it. (The query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

What is VDL (View Definition Language)?

Description : What is VDL (View Definition Language)?

Last Answer : It specifies user views and their mappings to the conceptual schema.

What is a view? How it is related to data independence?

Description : What is a view? How it is related to data independence?

Last Answer : A view may be thought of as a virtual table, that is, a table that does not really exist in its own right but is instead derived from one or more underlying base table. In other ... users from the effects of restructuring and growth in the database. Hence accounts for logical data independence.

How can you view all the current users connected in your database in this moment?

Description : How can you view all the current users connected in your database in this moment?

Last Answer : SELECT COUNT(*),USERNAME FROM V$SESSION GROUP BY USERNAME;

How can you view all the users account in the database?

Description : How can you view all the users account in the database?

Last Answer : SELECT USERNAME FROM DBA_USERS

In which view can you find information about every view and table of oracle dictionary

Description : In which view can you find information about every view and table of oracle dictionary

Last Answer : DICT or DICTIONARY

Difference between SUBSTR and INSTR ?

Description : Difference between SUBSTR and INSTR ?

Last Answer : INSTR (String1,String2(n,(m)),INSTR returns the position of the mth occurrence of the string 2 instring1. The search begins from nth position of string1.SUBSTR (String1 n,m)SUBSTR returns a character string of size m in string1, starting from nth position of string1.

What is the difference between DBFile Sequential and Scattered Reads?(for DBA

Description : What is the difference between DBFile Sequential and Scattered Reads?(for DBA

Last Answer : Both "db file sequential read" and "db file scattered read" events signify time waited for I/O read requests to complete. Time is reported in 100's of a second for Oracle 8i releases and below, ... v_$system_event b where a.event = 'db file sequential read' and b.event = 'db file scattered read';

What is the difference between static and dynamic lov

Description : What is the difference between static and dynamic lov

Last Answer : The static lov contains the predetermined values while the dynamic lov contains values that come at run time

What is the difference between NAME_IN and COPY ?

Description : What is the difference between NAME_IN and COPY ?

Last Answer : Copy is package procedure and writes values into a field. Name in is a package function and returns the contents of the variable to which you apply.

What is the difference between restoring and recovering? (for DBA

Description : What is the difference between restoring and recovering? (for DBA

Last Answer : Restoring involves copying backup files from secondary storage (backup media) to disk. This can be done to replace damaged files or to copy/move a database to a new location. Recovery is the process ... as SYSDBA Sql> RECOVER DATABASE UNTIL TIME '2001-03-06:16:00:00' USING BACKUP CONTROLFILE;

What is the difference between online and offline backups? (for DBA

Description : What is the difference between online and offline backups? (for DBA

Last Answer : A hot backup is a backup performed while the database is online and available for read/write. Except for Oracle exports, one can only do on- line backups when running in ARCHIVELOG mode. A cold backup is a backup performed while the database is off-line and unavailable to its users.

What is the difference between the SQL*Loader and IMPORT utilities?

Description : What is the difference between the SQL*Loader and IMPORT utilities?

Last Answer : These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL* ... from different data sources just so long as it conforms to ASCII formatted or delimited files.

What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

Description : What is the difference between a TEMPORARY tablespace and a PERMANENT tablespace?

Last Answer : A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Description : Explain the difference between a FUNCTION, PROCEDURE and PACKAGE.

Last Answer : A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any ... functions and procedures that are grouped together based on their commonality to a business function or application.

Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

Description : Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

Last Answer : ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

Description : Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

Last Answer : ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. ... having to write transactions to an archive log and thus increases the performance of the database slightly.

Explain the difference between a data block, an extent and a segment.

Description : Explain the difference between a data block, an extent and a segment.

Last Answer : A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings ... that an object takes when grouped together are considered the segment of the database object.

Explain the difference between a hot backup and a cold backup and the benefits associated with each.

Description : Explain the difference between a hot backup and a cold backup and the benefits associated with each.

Last Answer : A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is ... and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

What is the Difference between a post query and a pre query

Description : What is the Difference between a post query and a pre query

Last Answer : A post query will fire for every row that is fetched but the pre query will fire only once.

What is the difference between deleting and truncating of tables

Description : What is the difference between deleting and truncating of tables

Last Answer : Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.

What is the difference between candidate key, unique key and primary key

Description : What is the difference between candidate key, unique key and primary key

Last Answer : Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.)

What is difference between CHAR and VARCHAR2 ? What is the maximum SIZE allowed for each type ?

Description : What is difference between CHAR and VARCHAR2 ? What is the maximum SIZE allowed for each type ?

Last Answer : CHAR pads blank spaces to the maximum length. VARCHAR2 does not pad blank spaces. For CHAR it is 255 and 2000 for VARCHAR2.

What is difference between TRUNCATE & DELETE ?

Description : What is difference between TRUNCATE & DELETE ?

Last Answer : TRUNCATE commits after deleting entire table i.e., can not be rolled back. Database triggers do not fire on TRUNCATEDELETE allows the filtered deletion. Deleted records can be rolled back or committed.Database triggers fire on DELETE.

What is difference between UNIQUE and PRIMARY KEY constraints?

Description : What is difference between UNIQUE and PRIMARY KEY constraints?

Last Answer : A table can have only one PRIMARY KEY whereas there can be any number of UNIQUE keys. The columns that compose PK are automatically define NOT NULL, whereas a column that compose a UNIQUE is not automatically defined to be mandatory must also specify the column is NOT NULL.

Difference between database triggers and form triggers?

Description : Difference between database triggers and form triggers?

Last Answer : Data base trigger(DBT) fires when a DML operation is performed on a data base table. Form trigger(FT) Fires when user presses a key or navigates between fields on the screen -Can be row ... other database triggers to fire.Can cause other database triggers to fire, but not other form triggers.

Difference between procedure and function.?

Description : Difference between procedure and function.?

Last Answer : Functions are named PL/SQL blocks that return a value and can be called with arguments procedure a named block that can be called with parameter. A procedure all is a PL/SQL statement by itself, while a Function call is called as part of an expression.

Difference between NO DATA FOUND and %NOTFOUND?

Description : Difference between NO DATA FOUND and %NOTFOUND?

Last Answer : NO DATA FOUND is an exception raised only for the SELECT....INTO statements when the where clause of the querydoes not match any rows. When the where clause of the explicit cursor does not match any rows the %NOTFOUND attribute is set to TRUE instead.

Difference between an implicit & an explicit cursor.?

Description : Difference between an implicit & an explicit cursor.?

Last Answer : only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop. Explicit cursor is a cursor in which the cursor name is explicitly ... statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO

What is difference between Rename and Alias?

Description : What is difference between Rename and Alias?

Last Answer : Rename is a permanent name given to a table or column whereas Alias is a temporary name given to a table or column which do not exist once the SQL statement is executed.