-
What is difference between TRUNCATE & DELETE
Answer posted by Scott on 2005-05-25 18:30:04: TRUNCATE is a DDL command and cannot be rolled back. All of the memory space is released back to the server. DELETE is a DML command and can be rolled back. Both commands accomplish identical tasks (removing all data from a table), but TRUNCATE is much faster.
Read Best Answer
Editorial / Best Answer
Answered by: Prangya Sahu
- Nov 21st, 2005
1>TRUNCATE is a DDL command whereas DELETE is a DML command.
2>TRUNCATE is much faster than DELETE.
Reason:When you type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thatswhy when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.
3>You cann't rollback in TRUNCATE but in DELETE you can rollback.TRUNCATE removes the record permanently.
4>In case of TRUNCATE ,Trigger doesn't get fired.But in DML commands like DELETE .Trigger get fired.
5>You cann't use conditions(WHERE clause) in TRUNCATE.But in DELETE you can write conditions using WHERE clause.
Shahida Sultana
- Mar 6th, 2014
TRUNCATE removes all rows from a table. The operation cannot be rolled back and no triggers will be fired. As such, TRUCATE is faster and doesnt use as much undo space as a DELETE.
saravanan
- Sep 18th, 2012
Delete & truncate command will delete entries from a table.. where as we can delete a single entry using delete but incase of truncate it wont be possible..
using truncate will delete the log files,buffer storage in a table permanently but incase of delete it wont -
HOw to get /select the nth row from the table ?how to select first n rows ,last n rows from a table
sandip.dhopat
- Jun 9th, 2011
Select nth Row:select * from(select t1.c1, t1.c2, .... , rank(col_name) over (order by col_name) rnkfrom table_name t1)where rnk = n;To select top n rows in a order.select * from(select t1.c1, t1.c2, ...
p.mamta
- Aug 30th, 2010
How to get the nth row from the table ?select distinct(Salary),* from Employee A where n=(select count(distinct(Salary)) from Employee b where b.Salary <= a.Salary);how to select first ...
-
-
How can we order the column of day (mon to sun)such that monday comes first then tues and so no.Suppose we have retrieved day out of hiredate column
katgeektalk
- Aug 26th, 2010
Select hiredate, to_char(hiredate, 'Day') "DAY OF THE WEEK"from scott.emporder by to_char(hiredate-1, 'D')
dj_dj_dj
- Mar 22nd, 2010
Select to_char(hire_date, 'day')from table_nameorder by to_char(hire_date -1 ,'day');RegardsDharmendra Jaiswal
-
What is the basic difference between primary key and (unique key along with Not NULL).
Balakrishna12207
- Feb 7th, 2011
Primarykey is a combination of Unique Key and NotNull constraints.The major difference is dependence upon the query performance.One table can have only one Primarykey, But One table can have meny comb...
-
How do I eliminate the duplicate rows
Naveen Kumar39
- May 3rd, 2011
Delete from emp where rowid not in(select max(rowid) from emp group by empno);
SujitKr
- Feb 24th, 2010
DELETE FROM WHERE rowid NOT IN (SELECT max(rowid) FROM GROUP BY <column name containing repeated datas>);AStable name: tcolumn name: ename,branchSQL: DELETE FROM t WHERE rowid NOT IN (SELECT MAX (rowid) FROM t GROUP BY ename,branch)Sujit
-
-
-
-
Can a view be updated/inserted/deleted? If Yes under what conditions ?
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.
-
How many ways you can delete the table records ?
In how many ways can I delete all the contents of a table and the entire table. Explain with syntax ?
ranadhee_85
- Feb 9th, 2011
we delete record in a table 2 ways 1.by using delete command syntax:delet from table name where condition. example:delete from emp where en...
mukesh chand yadav
- Dec 7th, 2010
There are two(2) ways to delete recordes from a table are as follows: 1. Delete a specific record from table. ...
-
Find out nth highest salary from emp table
abhi_only12000
- Jan 2nd, 2010
SELECT ename, sal FROM (select ename,sal,dense_rank() OVER (order by sal desc) dr FROM emp) WHERE dr =n;
chirla_pavan
- Sep 12th, 2008
Select * from employee where 1=(select count(*) from employee as e where employee.sal<e.sal)
-
What is a transaction
csbhaskar
- Mar 25th, 2011
A transaction is a logical unit of database related work that contains one or more valid and executable SQL statements. A transaction begins with the first executable SQL statement and ends when it is...
-
Explain the different types of joins
Read Best Answer
Editorial / Best Answer
krishna420- Member Since Jul-2008 | Jul 23rd, 2008
There are 5 types of joins available in sql server
1) inner join
2) outer join( left outer join,Right outer join)
3) Full Join
4) Cross Join
5) Self Join
1) inner Join : Two tables having the common column values go with inner join.
Example:
select * from emp inner join empsalary
on
emp.empid=empsalary.empid
2) outer join:
Outer join has two sub types left outer join,Right outer join
Left outer join: Taking Left side values of the table checking into right side
select * from emp left outer join empsalary
on emp.empid=empsalary.empid
Right Outer join:Taking Right side values of the table checking left side
select * from emp right outer join empsalary
on emp.empid=empsalary.empid
3) Full join :
Full join is combined with left outer join ,Right outer join.
select * from emp full join employee
on emp.empid=employee.empid
4) cross Join: cross join is an carteasion product ,its like matrix format here it wont come on condidtion
select * from emp cross join employee
Self join :
Self join can check within the table called self join.zunaid
- Oct 12th, 2012
Different types of joins
Natural join
Inner join
outer join
cross join
equi join
non equi join
self joinvamsi krishna devineni
- Nov 27th, 2011
Mainly we uses some joins in the real time environment.. that is 1) Inner join 2) left outer join 3) right outer join 4) full outer join 5) cross join i will explain in easy way so that u can underst...
-
What is difference between TRUNCATE & DELETE ?
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.
nandk.sharma
- Jun 27th, 2008
Truncate is faster than delete as it does not generate rollback segment.
nandk.sharma
- Jun 27th, 2008
TRUNCATE - Truncate is a ddl command.DELETE- Delete is a dml command.TRUNCATE - We can not use 'where' clause with truncate.DELETE- We can use 'where ' clause with delete .
-
-
How many types of joins are there in oracle and what are the difference between them
Biswadeepak Swain
- Jun 3rd, 2015
This is not Cartesian products that one is Cartesian products. Thanks
abhi_only12000
- Jan 2nd, 2010
In Oracle we can catogrise joins in following ways -(1) Equi Join (Query having equal sign for condition)select empno, ename, dnamefrom emp join depton emp.deptno = dept.deptno;(2) Natural Join : both...
-
To display the all employee name with end of ‘S’ without using like operator ?
goutamgeek
- Dec 23rd, 2009
select * from table_name where substr(first_name,length(first_name),1)='S'
-
How can we use of select query to get the current date and time?
-
What is a join ? Explain the different types of joins ?
Join is a query which retrieves related columns or rows from multiple tables.Self Join - Joining the table with itself.Equi Join - Joining two tables by equating two common columns.Non-Equi Join - Joining two tables by equating two common columns.Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.
SQL*Plus interview Questions
Read Best Answer
Ans