In this article, i have give some more useful functions and tips for oracle database.
1.Total Size of The Database
An oracle database consists of data files, redo log files, control files, temporary files. The size of the database actually means the total size of all these files.
( select sum(bytes)/1024/1024/1024 data_size from dba_data_files ) +( select nvl(sum(bytes),0)/1024/1024/1024 temp_size from dba_temp_files ) +( select sum(bytes)/1024/1024/1024 redo_size from sys.v_$log ) +( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024/1024 controlfile_size from v$controlfile) "Size in GB"fromdual
2. Fast Refresh Materialized View with Joins
Step 1: Create Materialized View Log with Rowid on Each Table
create materialized view log on emp with rowid;
Step 2: Create Materialized View with rowids for all the base tables
create materialized view empdept_mv
refresh fast on commit asselect
e.rowid e_rowid,d.rowid d_rowid,e.empno,e.ename,d.deptno,d.dname,from
emp e,dept d
e.deptno = d.deptno;
NOTE: Restrictions on Fast Refresh on Materialized Views with Joins Only
- All restrictions from "General Restrictions on Fast Refresh".
- They cannot have GROUP BY clauses or aggregates.
- Rowids of all the tables in the FROM list must appear in the SELECT list of the query.
- Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.
- You cannot create a fast refreshable materialized view from multiple tables with simple joins that include an object type column in the SELECT statement.
3.using OUTER JOIN in place of NOT IN
Using NOT IN in (Oracle) SQL is expensive and that a way to overcome this problem is to collect the non-matching rows with an OUTER JOIN.
Not In Example:
Select bookname from book
where publisherid not in(select publisherid from publisher)
Above query rewritten with Outer Join
Select bookname from book, publisher
where book.publisherid = publisher.publisherid (+)and publisher.publisherid is null
DBMS_UTILITY.ANALYZE_SCHEMA is used to gather statistics for all the tables, clusters and indexes of a schema.
exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');
Note: It's also possible to analyze the whole database with the DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); command.
5.Function ISNUMERIC Check Value is Number or Not
CREATE OR REPLACE FUNCTION ISNUMERIC (PARAM IN CHAR) RETURN NUMBER ASDUMMY VARCHAR2(100);BEGINDUMMY:=TO_CHAR(TO_NUMBER(PARAM));RETURN (1);EXCEPTIONWHEN OTHERS THENRETURN (0);END;
SELECT * FROM EMP WHERE ISNUMERIC(PHONE) = 1;
That's all on this article, i will add more function in the next part of this article serius.i hope these functions help to you.