Subquery in values clause of INSERT command
Actual you have two options when you want to drop a column.
The second options is especially useful considering the fact that deletion of a column does take a lot of time and if it is done when the load on the system is high then it will severely effect performance of the system. So, you can mark a column for deletion and then delete the column when load on the system is low.
To drop column REMARKS from table STUDENTS, enter:
SET UNUSED option of ALTER TABLE command is used to mark a column for deletion. But the column is not physically removed. However, the column is treated as deleted. Once a column is marked as UNUSED then it cannot be accessed.
The following example marks column REMARKS as unused.
To drop the column REMARKS after marking as unused, give the following command.
The following example checks whether the name of the student is Praneeth by removing leading and trailing space of the student name.
For example, we want to create an index for due amount of STUDENTS table. Due amount is calculated as the difference between TOTALFEE and FEEPAID. When we query STUDENTS table on due amount the performance of the query is significantly enhanced. Because, due amount need not be calculated at the time of querying and instead it is already calculated at the time of creating the index. The improvement in the performance is even more for more complex expressions.
To create an index on due amount:
The above index will be used when you give a query like below:
Now, with Oracle8i, you can update a table when an index is being created or being rebuilt. However, during the period of building an index, no DDL commands can be executed on the table. But DML commands are permitted. It increases the availability of the table.
To creating an index without preventing DML operations on the table, you have to use ONLINE keyword at the time of creating index.
To create an online index on SNAME column of STUDENTS table, enter:
To rebuild the index online, enter :
To create an index where SNAME of STUDENTS table is sorted in descending order:
To create a temporary table to store the details of student no and date of joining, enter the following:
Insert a row into tempstud table as follows:
Commit the transaction by issuing commit command. And then if you query tempstud table then it should be empty.
ON COMMIT PRESERVE ROWS option is used to create a temporary table where data is not deleted at the end of transaction.
Each session that is using temporary table will have its own private data. Data stored by one session is not available to other sessions. DML locks are not obtained on temporary tables, as each session has its own data.
For data manipulations on temporary tables no redo data is generated. Generally for each change that is made to a non-temporary table a redo entry is generated and stored redo log file of the database. This redo entry is used to recover the data in the event of failure. But this is not the case with changes made to temporary table.
To get the details of students who have paid top 5 highest fee, enter:
The following query will display the first 2 highest marks in the subject Oracle.
To insert a new row into students table with studno one greater than the highest studno, enter the following:
The following example is using the average totalfee of students with course IP and store that as the totalfee of new student.
Rollup creates subtotals from most details to grand total according the to the order given in ROLLUP clause. In the above example, first subtotal for each products in a year and state is created, and then subtotal for each state is created and then for each year and then finally grand total.
But with CUBE, you get subtotal of a product within a state irrespective of year and in the same way subtotal of a product irrespective of state and year.
Syntax of CUBE:
This function can be used to find out whether a null value is created by ROLLUP or CUBE function, or is it the result of the aggregate function.
The following example will display 1 when a null value is created by ROLLUP function otherwise it will display 0.
A materialized view is similar to a SANPSHOT in concept. Materialized view contains the data of the base table. It stores a separate copy of the data of the base table. When user refers to materialized view then data is taken from materialized view and not from the base table.
This type of view is very useful when you deal with grouping and joining of large of amount of data. In data warehousing applications and other DSS applications, we have to get summary information from large amount of data of the table. Grouping the data and getting aggregates every time is going to take a lot of time. So we can store the aggregates in the form a materialized view so that we get the data straight from materialized view instead of a large.
Assume we have to get the total amount of units we sold in each year of the last 5 years. Of course, we assume the data related to sales of the last five years is available in a table called OLDSALES.
If you have to use the table then you would give a query as follows.
If you can create a materialized view as follows:
In distributed environments, materialized views (also called snapshots) are used to replicate data at distributed sites. The materialized views as replicas of remote data provide local access to data which otherwise would have to be accessed from remote sites.
To create a materialized view, you must have the following system privileges:
The feature in Oracle, which rewrites queries to enable them to access materialized view instead of base table, is called as query rewriting.
The following query is trying to access the base table, OLDSALES, to get total quantity sold for each product in each year:
However, materialized view can be either enabled or disabled for query rewriting. The following command creates a materialized view by enabling rewrite option.
By default query rewrite is disabled. The following command is used to create a materialized view and enable it for query rewrite.
At the time of creating materialized view, we can specify how frequently the view is to be refreshed.
Complete refresh
Truncates the data of the materialized view and re-inserts all the data from the base table.
Fast refresh
Applies only the changes that were made since the last refresh.
In this you have to maintain a log of all changes made to base table of materialized view. This log is created using CREATE MATERIALIZED VIEW LOG command.
Fast refresh can be used only in some cases. For example, a materialized view that contains average cannot be recomputed unless the entire data is taken.
Automatic refresh
Refreshing can take place automatically either at the specified time or whenever the data in the base table is modified and committed.
ON COMMIT option of refresh clause specifies that the refresh is to occur automatically when at the next COMMIT operation of the base table.
Manual refresh
It is also possible to manually refresh a materialized view using DBMS_MVIEW package and REFRESH procedure.
The following example creates a materialized view, which will be refreshed once for each week.
Dropping a column
For the first time Oracle8i has provided a command to drop a column from the table. Till Oracle8, dropping a column is very lengthy task as there was no direct way to drop a column. Now, Oracle8i has provided a new option with ALTER TABLE command to drop a column.
Or
Alter table students drop column remarks;
To drop column COURSE from STUDENTS table and remove all the constraints that depend on this column. For example, if column being dropped is a unique key and there are foreign keys referring to this column, then you must use CASCADE CONSTRAINT option while dropping the column.
Alter table students drop column course cascade constraints;
Note: When you drop a UNIQUE or PRIMARY KEY column then Oracle automatically drops the index that it creates to enforce uniqueness.
alter table students set unused column remarks;
Columns that are marked for deletion can be physically deleted using DROP UNUSED COLUMNS option of ALTER TABLE command.
alter table students drop unused columns;
Note: You can view all the columns that are marked for deletion using USER_UNUSED_COL_TABS data dictionary view.
Note: Until a column is physically dropped from the table it is counted as a column of the table and counted towards the absolute limit of 1000 columns per table.
TRIM Function
Oracle8i has introduced TRIM function. It combines the functionality of the existing LTRIM and RTRIM functions, allowing the user to trim leading and trailing characters from a character string.
Select * from students
Where trim(studname) = ‘Praneeth’;
Function based Index
Oracle8i allows you to create an index on an expression. The result of the given expression is computed and index is crated on the precomputed values.
create index dueindex on students (totalfee - feepaid);
The expression used in a function-based index can be an arithmetic expression or an expression that contains a PL/SQL function, package function, or SQL function.
Select * from students where totalfee – feepaid > 5000;
Restrictions on Function-Based Indexes
The following restrictions on function-based indexes.
On-Line Indexing
Prior to Oracle8i, creating an index on a table would lock the table thus preventing all DML operations on the table during the process of building an index.
CREATE INDEX students_sname ON students(sname) ONLINE;
The keyword ONLINE is used to specify that the index is to be created while keeping the table available for updates.
ALTER INDEX students_sname REBUILD ONLINE;
Descending Indexes
Until Oracle8i, the option DESC with CREATE INDEX command is ignored by Oracle. But from Oracle8i, it is used and index is created using descending values. Oracle sorts the columns with DESC keyword in descending order. Such indexes are treated as function based indexes.
Create Index students_sname_desc on students (sname desc);
Once an index is created with descending order you can get the list of student names in descending order whenever the index is used. For example, we can force index to be used by the following command:
Select sname from students where sname >= ‘S‘;
The above command will display SNAME of all rows where SNAME is starting with a character that is either S or after that. As this query uses the descending query, the result of the query will be in descending order or student name.
Temporary Table
Temporary table is a table where data is deleted either at the end of the session or transaction. Temporary table provides the space to store intermediate data. At the time of creating temporary table you have to specify whether the data is to be deleted at the end of the user session or at the end of current transaction – when commit or rollback command is issued.
Create global temporary table tempstud
( studno number(5),
sname varchar2(20)
);
Whatever data is stored in TEMPSTUD table, it will remain throughout the transaction. Once commit or rollback command is issued to end the transaction, the data in the temporary table is deleted. Let us take a few steps to understand how temporary table is used.
Insert into tempstud values(10,’xyz’);
Along with this some more updates will take place during the transaction. Just like a normal table even a temporary table can be queried.
Select * from tempstud;
Now it will display only one row – with studno 10 and sname “XYZ”.
Commit;
Select * from tempstud;
At the time of creating temporary table, you can specify whether data is to be deleted after the current transaction or at the end of the session. By the way, a session starts when user logs in and ends when user logs out. Session specific data of temporary table will be deleted when the session is terminated either normally or abnormally.
Finding Top N Values
Oracle8i allows you to get top n values much easily then previous versions. This is achieved by allowing ORDER BY clause in subquery. Prior to Oracle8i, ORDER BY clause is allowed only in outer query and not allowed in inner query.
Select * from
(select * from students order by feepaid desc)
where rownum <=5;
First, subquery sorts the data in descending order of FEEPAID and sends that data to outer query. Then outer query will display only first five rows of the data passed from inner query.
Select * from
(select * from studmarks where subcode= ‘ORA’ order by marks desc)
where rownum <= 2;
Subquery in VALUES clause of INSERT command
Oracle8i allows select command to be given in VALUES clause of INSERT command. Prior to Oracle8i it was possible to use a subquery to insert rows into a table as follows:
Insert into students
Select * from oldstudents;
But, it was not possible to mix values with the result of a query. Now, it is allowed to have a query as part of VALUES clause of INSERT command.
Insert into students
Values ( (select max(studno) +1 from emp), ‘Praneeth’, …);
In the above example, the result of the query is passed as the value for first column and remaining columns will get whatever values that are passed in values clause.
Insert into students ( studno,sname,course,totalfee)
Values ( (select max(studno) + 1 from emp), ‘Praneeth’, ‘IP’,
(select avg(totalfee) from students where course=’IP’));
Note: You must enclose subquery in parentheses.
Rollup and Cube
These extensions are added to GROUP BY clause of SELECT statement. They are used to process multidimensional queries, which means retrieving the information from all possible dimensions. For example, let us take a table called sales with the structure as follows:
Create table sales
(
year number(4),
state char(2),
product number(3),
amount number(10)
);
And the data of the table is as follows:
Select * from sales;
YEAR STATE PRODUCT AMOUNT
--------- ------ --------- ---------
1998 AP 100 2000
1998 AP 101 5000
1998 AP 102 5000
1998 AP 102 15000
1998 TN 102 15000
1998 TN 101 10000
1998 TN 102 10000
1998 AP 102 10000
1998 AP 103 11000
1999 TN 103 6000
1999 TN 101 6000
1999 TN 102 12000
1999 AP 103 10000
1999 AP 101 5000
1999 AP 101 1000
1999 AP 102 3000
1999 AP 103 5000
1999 AP 102 7000
The syntax for ROLLUP is as follows:
SELECT ... GROUP BY
ROLLUP(grouping_column_reference_list)
In order to get the total sales of each product, state and year, the following SELECT command with ROLLUP function can be used.
SQL> select year,state,product, sum(amount)
2 from sales2
3 group by Rollup(year,state,product);
YEAR STATE PRODUCT SUM(AMOUNT)
--------- ------ --------- -----------
1998 AP 100 2000
1998 AP 101 5000
1998 AP 102 30000
1998 AP 103 11000
1998 AP [NULL] 48000
1998 TN 101 10000
1998 TN 102 25000
1998 TN [NULL] 35000
1998 [NULL] [NULL] 83000
1999 AP 101 6000
1999 AP 102 10000
1999 AP 103 15000
1999 AP [NULL] 31000
1999 TN 101 6000
1999 TN 102 12000
1999 TN 103 6000
1999 TN [NULL] 24000
1999 [NULL] [NULL] 55000
[NULL] [NULL] [NULL] 138000
After the above commands if you execute SELECT command again the result will be as shown above.
Note: [NULL] is displayed in place of null value for the sake of clarity. But in the actual output null value is displayed as blank.
CUBE
This does same job as ROLLUP and in addition to that it displays subtotals of all possible combinations. In ROLLUP you get subtotals only for the level you mention in ROLLUP clause, whereas in CUBE, you get subtotals for all possible combinations of the given levels. For example, in the above example of ROLLUP, you got subtotal for product within a state and each state within a year.
SELECT ... GROUP BY
CUBE (grouping_column_reference_list)
The following output of CUBE will make things more clear.
select year,state,product, sum(amo
from sales2
group by cube(year,state,product)
YEAR STATE PRODUCT SUM(AMOUNT)
--------- ------ --------- -----------
1998 AP 100 2000
1998 AP 101 5000
1998 AP 102 30000
1998 AP 103 11000
1998 AP [NULL] 48000
1998 TN 101 10000
1998 TN 102 25000
1998 TN [NULL] 35000
1998 [NULL] 100 2000
1998 [NULL] 101 15000
1998 [NULL] 102 55000
1998 [NULL] 103 11000
1998 [NULL] [NULL] 83000
1999 AP 101 6000
1999 AP 102 10000
1999 AP 103 15000
1999 AP [NULL] 31000
1999 TN 101 6000
1999 TN 102 12000
1999 TN 103 6000
1999 TN [NULL] 24000
1999 [NULL] 101 12000
1999 [NULL] 102 22000
1999 [NULL] 103 21000
1999 [NULL] [NULL] 55000
[NULL] AP 100 2000
[NULL] AP 101 11000
[NULL] AP 102 40000
[NULL] AP 103 26000
[NULL] AP [NULL] 79000
[NULL] TN 101 16000
[NULL] TN 102 37000
[NULL] TN 103 6000
[NULL] TN [NULL] 59000
[NULL] [NULL] 100 2000
[NULL] [NULL] 101 27000
[NULL] [NULL] 102 77000
[NULL] [NULL] 103 32000
[NULL] [NULL] [NULL] 138000
If you compare output with the output of ROLLUP, you will notice the following extra subtotals.
Grouping function
This function is used to return 1 when it encounter a null value created by ROLLUP or CUBE function otherwise it returns 0.
select state,product, sum(amount),
grouping(state), grouping(product)
from sales group by rollup(state,product)
ST PRODUCT SUM(AMOUNT) GROUPING(STATE) GROUPING(PRODUCT)
-- --------- ----------- --------------- -----------------
AP 100 2000 0 0
AP 101 11000 0 0
AP 102 40000 0 0
AP 103 26000 0 0
AP 79000 0 1
TN 101 16000 0 0
TN 102 37000 0 0
TN 103 6000 0 0
TN 59000 0 1
138000 1 1
With the help of GROUPING function and DECODE function we can create a neat output for the result of ROLLUP and CUBE. The following example will display “All Products” whenever null value is returned by ROLLUP and for product column and “All States” whenever null value is returned by ROLLUP for state column.
select decode(grouping(state),1,'All States',state) state,
decode(grouping(product),1,'All Products', product)
product, sum(amount) "Total Amount"
from sales group by rollup(state,product);
STATE PRODUCT Total Amount
---------- ---------------------------------------- ------------
AP 100 2000
AP 101 11000
AP 102 40000
AP 103 26000
AP All Products 79000
TN 101 16000
TN 102 37000
TN 103 6000
TN All Products 59000
All States All Products 138000
Materialized Views
A materialized view is a view that contains data. A normal relational view doesn’t contain any data. It takes the data from the base table whenever it is referred. Neither an object view contains data. It takes the data from a relational table and projects rows in the form of objects.
Select prodid, to_char(ds,’yyyy’), sum(qty)
From oldsales
Group by prodid, to_char(ds,’yyyy’);
Nothing is wrong with query except the fact that it takes more time to execute. Moreover, if you want to execute it for multiple times then each time it takes a lot of time since it has to access the table, group the data and then calculate aggregates.
CREATE MATERIALIZED VIEW YEARSALES
AS
SELECT PRODID, TO_CHAR (DS,’YYYY’) YEAR, SUM (QTY) TOTALQTY
FROM OLDSALES
GROUP BY PRODID, TO_CHAR (DS,’YYYY’);
Now, we can get the details of sales of each product in last few years just by giving the following query.
Select * from yearsales;
Getting data from materialized data hardly takes any time. Materialized view is populated at the time of creating the view. Since then whenever you access the view, the data is taken only from view and the base table is not accessed. That means a lot of saving in time and usage of resources. The savings will be substantial if base table is large (generally the case with data warehousing applications).
Query Rewriting
It is possible for Oracle to use the data of the materialized view to obtain data when query is actually trying to access base table.
SELECT PRODID, TO_CHAR (DS,’YYYY’) YEAR, SUM (QTY) TOTALQTY
FROM OLDSALES
GROUP BY PRODID, TO_CHAR (DS,’YYYY’);
But, as Oracle knows that a materialized view is already contains the data that this query has to retrieve, it will rewrite this query to access materialized view as follows, if query rewrite on materialized view is enabled.
Select * from yearsales;
This will drastically improve the performance as we are not accessing the grouping and calculating aggregates using the data of the base table and instead take ready made data from materialized view.
CREATE MATERIALIZED VIEW TOTALSALES
Enable query rewrite
AS
SELECT PRODID, SUM (QTY) TOTALQTY
FROM OLDSALES
GROUP BY PRODID
To create a materialized view by enabling query rewrite facility, you must have QUERY REWRITE system privilege.
Refreshing materialized view
A materialized view is to be refreshed to synchronize the content of the materialized view with the data of the base table.
Refresh methods
There are two methods to refresh a materialized view.
Note: If no refresh option is given then Oracle assumes Force refresh. Force tries to use Fast first, if not possible then uses Complete.
CREATE MATERIALIZED VIEW SALESDET
REFRESH
START WITH 1-JUL-2001
NEXT SYSDATE + 7 AS
SELECT S.PRODID, PRODNAME, QS, RATE
FROM PRODUCTS P, SALES S
WHERE P.PRODID = S.PRODID;