Does View occupy memory? If we delete the base table of existing view, What happens?

Read Best Answer

Editorial / Best Answer

Mad Hatter  

 Profile Answers by Mad Hatter Questions by Mad Hatter

• Member Since Nov-2008 | Nov 4th, 2008

---

View does not occupy memory, but Oracle keeps view code in its dictionary (you can check in user_views, for example)
If you delete the base table, the view becomes INVALID.
In order to make it valid again, you have to ether build the deleted table again, or create anotehr view with the same name and structure as deleted table, or create synonym with the same name as deleted table, which will point to the table with the same structure.

pkshve

 Profile Answers by pkshve Questions by pkshve

• Sep 12th, 2010

Views are database objects - they are just SQL code stored in database which will be executed every single time the view is called. Database will check the existance of columns and tables in the base ...

manimehra905

 Profile Answers by manimehra905 Questions by manimehra905

• May 18th, 2010

View does not occupy memory as it only provides a way for watching multiple views of an object: Table. Moreover, it is a virtual table which does not take space.It provides DATA HIDING.For e.g. i...

Improve Answer

I have table with col1 values , col2 1st row value should be same col1 1st row, then col 2 row onwards row each should added each row values eg: col1 values 10,20,30. Etc, in col2 values display like 10, 30,60 etc. how we can write sql query ?

roshan.gwr

 Profile Answers by roshan.gwr Questions by roshan.gwr

• Mar 13th, 2015

Using Lag we can get it.
Select col1,col1+lag(col1,1,0) over (order by col1) col2 from table;

Vinod Kumar G M

• Nov 7th, 2014

SELECT COL_NAME , SUM(COL_NAME) OVER ( ORDER BY COL_NAME) FROM TABLE_NAME

Answer Question  Select Best Answer

Query to select distinct values without using distinct

Ashok

• Mar 21st, 2016

There are four ways for this.
1. Using distinct keyword
2. Using group by on all columns
3. Using Union operator
4. Using Analytical functions

Akshay Kadam

• Mar 27th, 2015

SELECT DISTINCT LASTNAME FIRSTNAME
FROM EMP;

Answer Question  Select Best Answer

No.

Shantanu

• Aug 17th, 2012

You cant directly use commit in triggers. You have to use PRAGMA Autonomous_Transaction or you can call other procedure or function, but other procedure or function should also have PRAGMA Autonomous_Transaction in it.

Jagan

• Aug 21st, 2011

Yes, by using autonomous transaction

Answer Question  Select Best Answer

bhagyashree010

 Profile Answers by bhagyashree010 Questions by bhagyashree010

• Jul 20th, 2010

SELECT COUNT(e.ename), d.dname FROM emp e, dept d WHERE d.deptno=e.deptnoGROUP BY d.dname HAVING COUNT(e.ename) > 20It is the same...only diff is...use "dname" in group by clause , not "deptno"Thanks

isha123

 Profile Answers by isha123 Questions by isha123

• Aug 9th, 2006

Select e.empno "Subordinate",e.ename "Emp Name",e.mgrID "ManagerID",m.ename "Manager Name",m.empno "Employee Number" from emp e JOIN emp m where (e.mgrid=m.empno);

Answer Question  Select Best Answer

vidya

• Oct 30th, 2007

Where clause doesnt allow group functions.You need to use Having Clause.i.e Having count(*)>1

ROHIT_4444

 Profile Answers by ROHIT_4444 Questions by ROHIT_4444

• Mar 28th, 2007

IN THIS PREV NO EXPRESSION I PUT = IT WORK BUT ONLY SIMILAR EXACT QUERY IS DELETE FROM  WHERE ROWID NOT IN (SELECT MIN(ROWID) FROM  GROUP BY <DUPLICATE VALUE FIELD NAME);IT'S WORK SURE

Answer Question  Select Best Answer

aytasirs

 Profile Answers by aytasirs Questions by aytasirs

• Nov 15th, 2012

No data found is an exception where as %not found is a cursor attribute.

sampra

 Profile Answers by sampra Questions by sampra

• Mar 6th, 2012

No data found is exception where as %notfound is retrn by exception

Answer Question  Select Best Answer

If you have a three columns table with one index on 2nd Column. In the SELECT statement you retrived column 1, can index be used or not?

amitsrivastava115

 Profile Answers by amitsrivastava115 Questions by amitsrivastava115

• Aug 2nd, 2011

Use index in the where clause , this way you can use it other wise not ...

geetha

• Jul 30th, 2011

Index will be used if the retrieval is based on an indexed column .if the where clause uses a column with index then the query will be executed based on that index.

Answer Question  Select Best Answer

SELECT * FROM empWhere emp_no+' '=12345;i.e you have to concatenate the column name with space within codes in the where condition.SELECT /*+ FULL(a) */ ename, emp_no from empwhere emp_no=1234;i.e using HINTS

jyothi_rayala

 Profile Answers by jyothi_rayala Questions by jyothi_rayala

• Jun 11th, 2009

One way is to diable the index on the column using syntaxAlter index indexname on table_name disable.One way is to apply  /*+no_index */ hint to the query For eg: select ename /*+no_index */from empwhere ename ='smith'

Srini

• Oct 10th, 2007

Its so distressing that no body could provide a better answer than a novice like me.In the where clause change the order of conditions in such a way that:? 5 = value cond1 & value =5or value + 1 = 5

Answer Question  Select Best Answer

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))from emp;the output like,SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))--------- -----------------------------------------------------800 eight hundred1600 one thousand six hundred1250 one thousand two hundred fiftyIf you want to add some text like, Rs. Three Thousand only.SQL> select sal "Salary ",(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))"Sal...

Talal Haider

• Mar 16th, 2013

Can we convert a number in words without using direct function/method?

boobal

• Mar 21st, 2012

This answer is no different to
select to_char(to_date(1290,j),jsp) from dual;

Answer Question  Select Best Answer

saradajasti

 Profile Answers by saradajasti Questions by saradajasti

• Mar 27th, 2008

Here CNT is a column name SELECT CNT FROM table_nameWHERE MOD(CNT,CNT) = 0AND MOD(CNT,2) != 0AND MOD(CNT,3) != 0AND MOD(CNT,5) != 0AND MOD(CNT,7) != 0AND MOD(SQRT(CNT),ROUND(SQRT(CNT))) != 0ORDER BY CNT

saradajasti

 Profile Answers by saradajasti Questions by saradajasti

• Mar 26th, 2008

SELECT CNT FROM table_nameWHERE MOD(CNT,CNT) = 0AND MOD(CNT,2) != 0AND MOD(CNT,3) != 0AND MOD(CNT,5) != 0AND MOD(CNT,7) != 0ORDER BY CNT

Answer Question  Select Best Answer

Read Best Answer

Editorial / Best Answer

Answered by: Jayakumar M

• Nov 8th, 2005

---

Yes. We can call Functions from SQL statements.

To be callable from SQL statements, a stored function must obey the following

"purity" rules, which are meant to control side effects:

 When called from a SELECT statement or a parallelized INSERT, UPDATE, or

DELETE statement, the function cannot modify any database tables.

 When called from an INSERT, UPDATE, or DELETE statement, the function

cannot query or modify any database tables modified by that statement.

 When called from a SELECT, INSERT, UPDATE, or DELETE statement, the

function cannot execute SQL transaction control statements (such as COMMIT),

session control statements (such as SET ROLE), or system control statements

(such as ALTER SYSTEM). Also, it cannot execute DDL statements (such as

CREATE) because they are followed by an automatic commit.

planet100

 Profile Answers by planet100 Questions by planet100

• Apr 19th, 2011

I have a question... what if the function returns long??i am not able to use in the select query.It says " ORA-00997: illegal use of LONG datatype " .Is there any other way to address this, ...

nis1981

 Profile Answers by nis1981 Questions by nis1981

• Aug 3rd, 2008

Yes, you can call function in sql query, these functions should be in-built or predefined in the DBMS.

Improve Answer

jayshree13

 Profile Answers by jayshree13 Questions by jayshree13

• May 13th, 2011

Select e.ename,e.deptno,d.deptno from emp e,dept d where e.deptno=d.deptno(+);

srividhya_85

 Profile Answers by srividhya_85 Questions by srividhya_85

• Aug 5th, 2008

Select emp_name from emp awhere not exists(select 1                         ...

Answer Question  Select Best Answer

upendra

• Jun 11th, 2015

Sub Query: Inner query is executed first then it gives to outer query and outer query is executed.

Correlated Query: Outer query executed first row by row then it based on inner query is executed.

hindol_saha

 Profile Answers by hindol_saha Questions by hindol_saha

• Feb 24th, 2010

subquery : returns value to main query. it wont get values from main query. Example: SELECT id "Emp #" First_name "Name" salary "Salary" ROWNUM rank FROM (SELECT id first...

Answer Question  Select Best Answer

aytasirs

 Profile Answers by aytasirs Questions by aytasirs

• Nov 15th, 2012

Explain plan will tell you

sampra

 Profile Answers by sampra Questions by sampra

• Mar 6th, 2012

Use select may be this works

Answer Question  Select Best Answer

10 rows

jasvinderss

 Profile Answers by jasvinderss Questions by jasvinderss

• Jun 30th, 2008

There could be two cases(1) if you supply the query as Select * from A,Byou have not mentioned any join condition in this case 10 rows will be selected.(2)if you supply a join condition with the above queries with a common filed between them, then in that case no rows will be selcted.

nandk.sharma

 Profile Answers by nandk.sharma Questions by nandk.sharma

• Jun 27th, 2008

No rows will be retrieved.The Cartesian product will only yield records when there is atleast one record in both the tables i.e.   A and B.

Answer Question  Select Best Answer

krishsidd

 Profile Answers by krishsidd Questions by krishsidd

• Nov 26th, 2010

CREATE TABLE EMP ( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10), JOB VARCHAR2(9), MGR NUMBER(4), HIREDATE DATE, SAL...

yagneswarasankar

 Profile Answers by yagneswarasankar Questions by yagneswarasankar

• Nov 24th, 2010

  1  select manager_id,last_name,count(employee_id) over (partition by manager_id ) cnt from employe  2* order by cntSQL> /MANAGER_ID LAST_NAME      &nb...

Answer Question  Select Best Answer

aytasirs

• Apr 29th, 2013

Yes We can. If we use the combination "unique+not null" the referential integrity is not maintained and that is not the case with primary key.

EHSAN REHMANI

• Apr 27th, 2013

There is only one primary key per table but we can use unique and not null constraints more than one column in a table. Primary key having both functionality as it can not be null but unique.

Answer Question  Select Best Answer

We have two type of constraints - Table Level and Column Level. Which is better and why..?

Mohit

• Oct 3rd, 2015

I think Table level contraints are imposed when we have to compare values in multiple columns,suppose delivery_date cannot be less than order_date..

abhilashnanupillai

 Profile Answers by abhilashnanupillai Questions by abhilashnanupillai

• Feb 20th, 2009

Constrains are the key word along with create table command. Different type of table constraints are primary key not null unique etc.

Answer Question  Select Best Answer

srini

 Profile Answers by srini Questions by srini

• Dec 6th, 2013

Using PIVOT we can do this:

http://www.dba-oracle.com/t_pivot_examples.htm

VIJAY SHARMA

• Sep 24th, 2012

SELECT SYS_CONNECT_BY_PATH(DEPTNO, )I ,
SYS_CONNECT_BY_PATH(DNAME, ) II,
SYS_CONNECT_BY_PATH(LOC, ) III
FROM DEPT
WHERE LEVEL = &TOTALDEPARTMENTS
START WITH DEPTNO = 10
CONNECT BY PRIOR DEPTNO < DEPTNO;

Answer Question  Select Best Answer