Useful functions in Oracle 11g Part 03

No.of Views3220
Bookmarked0 times
Downloads 
Votes0
By  Nimish Garg   On  06 Jul 2010 10:07:16
Tag : Oracle , Database Administrator
In this article, i have given samples to three useful functions and one tips in oracle 11g
emailbookmarkadd commentsprint

Images in this article missing? We recently lost them in a site migration. We're working to restore these as you read this. Should you need an image in an emergency, please contact us at info@codegain.com

 

Introduction

In this article, i have give some more useful functions and tips for oracle database.

1.Total Size of The Database

http://nimishgarg.blogspot.com/2010/05/oracle-total-size-of-database.html

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 
( 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

http://nimishgarg.blogspot.com/2010/05/oracle-fast-refresh-materialized-view.html

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
where 
    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

http://nimishgarg.blogspot.com/2010/05/oracle-using-outer-join-in-place-of-not.html

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

4.DBMS_UTILITY.ANALYZE_SCHEMA

http://nimishgarg.blogspot.com/2010/05/oracle-dbmsutilityanalyzeschema.html

DBMS_UTILITY.ANALYZE_SCHEMA  is used to gather statistics for all the tables, clusters and indexes of a schema.

Examples:

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

http://nimishgarg.blogspot.com/2010/03/oracle-function-isnumeric-check-value.html

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;

 EXAMPLE:

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.

 
Sign Up to vote for this article
 
About Author
 
Nimish Garg
Occupation-Software Engineer
Company-Indiamart Intermesh Limited
Member Type-Junior
Location-India
Joined date-04 Jun 2010
Home Page-http://nimishgarg.blogspot.com/
Blog Page-http://nimishgarg.blogspot.com/
Oracle DBA (Performance Tuning and Query Optimization) IndiaMART InterMESH Limited, Noida
 
 
Other popularSectionarticles
Comments
There is no comments for this articles.
Leave a Reply
Title:
Display Name:
Email:
(not display in page for the security purphase)
Website:
Message:
Please refresh your screen using Ctrl+F5
If you can't read this number refresh your screen
Please input the anti-spam code that you can read in the image.
^ Scroll to Top
</