1.
Virtual columns
“virtual
column" is an empty column that contains a function upon other table
columns (the function itself is stored in the data dictionary). You cannot
attempt to insert anything into the virtual column and only read that column
Ex: create table t1 (c1 number,
c2 number, c3 as (c1+c2) virtual)
Note:
o
Virtual columns may not
reference other virtual column values
Create table t(x number, x1
as (x+1), x2 as (x1 +1)); gives error
o
11g virtual columns only work
within the specified table, and you cannot reference columns within other
tables
2.
Read only Tables
Prior
to oracle 11g, one can’t make a single table as READ-ONLY; either makes the
whole tablespace to READ-ONLY OR NOT. But this is possible in 11g.
SQL
:\> alter table test read only;
The
DBA_TABLES and USER_TABLES and ALL_TABLES view have a new column READONLY which
can be used to see which tables are READONLY or not.
3.
Invisible indexes
You can remove an index from consideration by the optimizer by
making it invisible. One possible use of this feature is to test the removal of
an index before dropping it.
Prior to 11g, this was
typically achieved by making an index unusable during a set period of time.
During this observation period, the DBA would monitor the database performance
to make the determination of whether to drop the index. If performance was
negatively affected, the index would need to be rebuilt before it could
be used again.
Beginning with Oracle 11g,
the DBA has the option of making the index invisible, as opposed to unusable,
during this observation period. If performance degradation is observed, the
index can be made visible again without rebuilding the index. This can minimize
the period of performance degradation while also preventing an expensive
operation to rebuild or recreate an index
SQL :\> create index t_ind on t (column_name) invisible
SQL :\> alter index t_ind invisible;
SQL :\> alter index t_ind visible
4.
Sequences in PL/SQL
Oracle 11g allows the use of sequence in PL/SQL expressions.
Prior to 11g:
DECLARE
n NUMBER;
BEGIN
SELECT x_seq.NEXTVAL INTO n FROM dual;
DBMS_OUTPUT.PUT_LINE
('Nextval=' || TO_CHAR (n));
END;
In
11g:
DECLARE
n NUMBER: = x_seq.NEXTVAL;
BEGIN
DBMS_OUTPUT.PUT_LINE
('Nextval=' || TO_CHAR (n));
END;
5.
Compound Triggers
In
oracle11g allows to developer to combine triggers with different timing option into
a single compound trigger.
CREATE
OR REPLACE TRIGGER compound_trig
FOR INSERT ON test
COMPOUND TRIGGER
-------------------------------
BEFORE STATEMENT IS
BEGIN
dbms_output.put_line('BEFORE STATEMENT LEVEL');
END BEFORE STATEMENT;
-------------------------------
BEFORE EACH ROW IS
BEGIN
dbms_output.put_line('BEFORE ROW LEVEL');
END BEFORE EACH ROW;
-------------------------------
AFTER STATEMENT IS
BEGIN
dbms_output.put_line('AFTER STATEMENT LEVEL');
END AFTER STATEMENT;
-------------------------------
AFTER EACH ROW IS
BEGIN
dbms_output.put_line('AFTER ROW LEVEL');
END AFTER EACH ROW;
END compound_trig;
/
FOR INSERT ON test
COMPOUND TRIGGER
-------------------------------
BEFORE STATEMENT IS
BEGIN
dbms_output.put_line('BEFORE STATEMENT LEVEL');
END BEFORE STATEMENT;
-------------------------------
BEFORE EACH ROW IS
BEGIN
dbms_output.put_line('BEFORE ROW LEVEL');
END BEFORE EACH ROW;
-------------------------------
AFTER STATEMENT IS
BEGIN
dbms_output.put_line('AFTER STATEMENT LEVEL');
END AFTER STATEMENT;
-------------------------------
AFTER EACH ROW IS
BEGIN
dbms_output.put_line('AFTER ROW LEVEL');
END AFTER EACH ROW;
END compound_trig;
/
6.
Database Replay
Database
Replay provides DBAs and system administrators with the ability to faithfully,
accurately and realistically rerun actual production workloads, including online
user and batch workloads, in test environments. By capturing the full database
workload from production systems, including all concurrency, dependencies and
timing, Database Replay enables you to realistically test system changes by
essentially recreating production workloads on the test system –something that
a set of scripts can never duplicate.
7.
Query Result Cache
Improve
the performance of SQL across the whole database instance by caching query
results.
SQL :\>
SELECT * FROM EMP;
5
rows selected.
Elapsed:
00:00:05.15
SQL :\>
SELECT /*+ result_cache */ * FROM EMP;
5
rows selected.
Elapsed:
00:00:05.20
Adding the RESULT_CACHE hint to the query tells the server to
attempt to retrieve the information from the result cache. If the information
is not present, it will cache the results of the query provided there is enough
room in the result cache. Since we have no cached results, we would expect the
first run to take approximately five seconds, but subsequent runs to be much quicker.
SQL :\>
SELECT /*+ result_cache */ * FROM EMP;
5
rows selected.
Elapsed:
00:00:00.25
The
default action of the result cache is controlled by the RESULT_CACHE_MODE
parameter. When it is set to MANUAL, the RESULT_CACHE hint must be used for a
query to access the result cache.
SQL :\>
SHOW PARAMETER RESULT_CACHE_MODE
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
Result_cache_mode string MANUAL
If we
set the RESULT_CACHE_MODE parameter to FORCE, the result cache is used by
default, but we can bypass it using the NO_RESULT_CACHE hint.
SQL
:\> ALTER SESSION SET RESULT_CACHE_MODE=FORCE;
SQL :\>
select /*+ no_result_cache */ * from EMP;
No comments:
Post a Comment