What is difference between a table and a materialised view, a view and a materialised view?

Pooja

• May 12th, 2016

Materialised views do not have the same rowid as that of the actual table wherease views have the same rowid.
Materialised views are faster because indexing can be applied since the result is stored in a db unlike view which is a logical query executed everytime.

sonali Kumari

• Jan 15th, 2016

Unlike views, however, materialized views also store the results of the query in the database. However the rowids for the table and the view are identical, indicating the view returns the exact same ...

Answer Question  Select Best Answer

I `ve 1 table with 3 colums A,B,C

A B C
1 3 5
4 null 6
8 2 null
null 1 null
null null 9
5 null 6
3 5 7
null null 3

Paurian

 Profile Answers by Paurian Questions by Paurian

• Feb 17th, 2016

A surprise to me, using CASE statements was slightly faster, and had the exact same execution plan. People would prefer this code over the SUM( ISNULL( ... ) ) answer because the intent is more clearl...

vinay

• Feb 16th, 2016

It should print out null 1 nll

Answer Question  Select Best Answer

I am new to Oracle. I have created one table in that one column name salary, while creating the table I have given the datatype for that column is varchar2 and also I have given not null constraint. Now I want to change the datatype varchar2 to number, Is it possible to alter the datatype when having constraint in table?

DevaNaidu

 Profile Answers by DevaNaidu Questions by DevaNaidu

• Mar 7th, 2018

If you want change only varchar2 to number you can change by using ALTER, but you also defined NOT NULL constraints..so if you need to change a datatype along with Not null..you must use MODIFY key word

Abhimanyu eati

• Nov 17th, 2017

Yes we can change datatype but make sure about size if you have created varchar(5) then alter to number(5)

Answer Question  Select Best Answer

I have a source table with one column of 15 characters and I want output like divide the 15 chars column into multiple of 3 columns like every column 5 chars

source empnoorderprdct target empno order prdct

Divya

• Jun 5th, 2017

You can use Substring function to split the column values to 3 columns

Lokesh M

 Profile Answers by Lokesh M Questions by Lokesh M

• Jul 12th, 2016

Please use MID() function to extract characters from a field.

Code
 

Answer Question  Select Best Answer

Source:
col1 col2
1 Will
2 John
3 Josh
4 Devin

Target:
col1 col2
1 Will
2 NULL
3 John
4 NULL
5 Josh
6 NULL
7 Devin
8 NULL

I want the SQL query for the...

SANTHOSH KUMAR GUJJA

• Nov 26th, 2017

SEL CASE WHEN COL1=1 THEN COL1 ELSE COL1+MIN(COL1) OVER(ORDER BY COL1 ASC ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) END AS
COL1,COL2 FROM TABLE
UNION
SEL COL1,NULL AS COL2 FROM TABLE WHERE COL11

Rakesh

• Mar 2nd, 2017

SELECT COL1, CASE
WHEN MOD(COL1,2) = 0 THEN NULL
ELSE
COL2
END CASE
FROM G2

Answer Question  Select Best Answer

DROP TABLE &TABLE_NAME;Variable names should start with an alphabet. Here the table name starts with an '&' symbol.

Poonam Sachdeva

• Nov 23rd, 2005

This does not give an error in Oracle 9iFollowing is the outputSQL> DROP TABLE &TABLE_NAME;Enter value for table_name: temp_special_payslip(this has been eneterd by me)old   1: DROP TABLE &TABLE_NAMEnew   1: DROP TABLE temp_special_payslipTable dropped.

naveen

• Sep 19th, 2005

this works in oracle9i and it will ask for table nameand the given table will be deleted

Answer Question  Select Best Answer

It is used to drop constraints specified on the table.

chitra

• Nov 14th, 2005

The rtrim function requires 1 arguments.

Anuradha

• Oct 13th, 2005

Ans:The given answer says the use of DROP command.but not the drop option in the ALTER TABLE command.       Drop option in the ALTER TABLE command is uesd to drop columns...

Answer Question  Select Best Answer

FLOOR

Tushar

• May 9th, 2007

TRUNC will give same output as FLOOR

sahu

 Profile Answers by sahu Questions by sahu

• Jan 27th, 2006

Ans:Yes ,we can find the largest integer less than or equal to a specific valueFLOOR

Answer Question  Select Best Answer

er.prateek.cs

 Profile Answers by er.prateek.cs Questions by er.prateek.cs

• May 28th, 2010

c>LIKE   LIKE%% with wildcards used for searching

kranti

• Jul 28th, 2005

LIKE

Answer Question  Select Best Answer

How do I write a cron which will run a SQL query and mail the results to a group?

Kwisatz_Haderach

 Profile Answers by Kwisatz_Haderach Questions by Kwisatz_Haderach

• Feb 1st, 2011

Create sql file name_of_file.sqlIn name_of_file.sql file output to pipe 'mailx -s "SUBJECT"  mail_recepient' select * from table;Create a unix script name  "whatever&q...

Kalyan

• Oct 18th, 2005

Use DBMS_JOB for scheduling a cron job and DBMS_MAIL to send the results throught email.

Answer Question  Select Best Answer

Tushar

• May 9th, 2007

Use Export and Import tools for backups and restore resp.you can use exp and imp as well

shuaib

• Nov 11th, 2005

U can backup the sql files through backup utilities or some backup command in sql . SAP is ERP software for the organization to integrate the software .

Answer Question  Select Best Answer

naveen_vangapalli1

• May 26th, 2007

The select statement goes to oracle server and the server responds

shashi

• Oct 25th, 2005

First it will look into the System Global Area (SGA) weather the query is been exectued earlier.If it exist, it would retrive the same output present in memory.If not the query we typed...

Answer Question  Select Best Answer

gunjan28

 Profile Answers by gunjan28 Questions by gunjan28

• Nov 11th, 2009

When using a count(distinct) it is better to use self-joinExp- in employees table to get manager name and total employees including manager querry will be-id first_name last_name m...

Sujatha

• Aug 1st, 2006

Hi All,Instead of this we can use GROUP BY Clause with HAVING condition.For ex,Select count(*),lastname from tblUsers group by lastname having count(*)>1This query return the duplicated lastnames values in the lastname column from tblUsers table.

Answer Question  Select Best Answer

Please give some sub-query examples based on correlated sub-query and nested

Pallavi g rao

 Profile Answers by Pallavi g rao Questions by Pallavi g rao

• Apr 21st, 2011

correlated:- outer query is executed first and result is sent to inner query for execution       select * from emp e1 where e1.basicsal=(selcte max(basicsal) from emp e2 ...

nandlalseth

 Profile Answers by nandlalseth Questions by nandlalseth

• Aug 14th, 2008

Correlated Sub Query:Select * from emp e where exists (select 1 from dept d where d.deptno?= e.deptno)Nested Sub Query:Select * from emp e where deptno in (select deptno from dept d)

Answer Question  Select Best Answer

Can any one guide me how can i import and export schema using SQL and OEM

Shaik Kareem

 Profile Answers by Shaik Kareem Questions by Shaik Kareem

• Jun 6th, 2011

Exp file=fullprd.dmp logfile=fullprd.log full=y

luzaolai

 Profile Answers by luzaolai Questions by luzaolai

• Apr 27th, 2009

In db2 three different format (ixf,del,asc) to export data:Export to tablename.ixf of ixf select * from tablename. When do import we can define if the original data should be kept or cleaned. Insert ...

Answer Question  Select Best Answer

meenakvasulochanan

 Profile Answers by meenakvasulochanan Questions by meenakvasulochanan

• Aug 17th, 2007

The column "duration" in all_tables will tell us whether the temporary table is defined with "on commit preserve row" or "on commit delete rows"The values :SYS$TRANSACTIO...

Answer Question  Select Best Answer

intelmental4

 Profile Answers by intelmental4 Questions by intelmental4

• Jul 23rd, 2007

BLOB is Binary Large ObjectWhen you declare any variable as image, text, ntext ... they would be BLOB data typesText = 2^31-1text = 2^30-1ntext = 2^31-1-Each text, ntext, image colum can in a table ca...

Answer Question  Select Best Answer

venkat82.k

 Profile Answers by venkat82.k Questions by venkat82.k

• Mar 19th, 2008

Hi guys  partitionings nothing but a huge table is divided into some meaningfull  parts to do manipulations ,for easy maintainwe divide partitions while creating tables.mainly 3 types of par...

Answer Question  Select Best Answer

Nikhil_4_Oracle

• Mar 26th, 2007

Both are same....-Nikhil

Answer Question  Select Best Answer

How to Create Sequence and insert data using Alter command to a new column?

callmesomu

• Apr 17th, 2007

ALTER SEQUENCE SEQUENCE_NAMEINCREMENT BY 10

Answer Question  Select Best Answer