Link: http://momendba.blogspot.com/2009/07/who-says-full-table-scans-are-evil.html

Really good article that demonstrates how full table scans can be better than indexed scans... but as you read on, the real truth emerges as to why. Lesson to be learned... if your statistics are up to date, then the optimizer will do its job in almost all cases... if they are out of wack, thats when you find yourself using optimization hints. So think twice before you apply the optimization hint and promote that to production. Work with your DBAs to identify the root cause.

Link: http://www.myoracleportal.net/blog1.php/2009/02/01/plsqlftp

Author Comments : Important issue identified regarding the structure of your remote FTP servers banners. Although with a true FTP client tool it may not be required, this FTP process follows best practice and expects that the server codes precede each line being returned by the remote FTP Server as it authenticates to the remote server. Failure to include the required prefix could cause you to be unable to access the remote server or failure to authenticate. See for more info => TCP Banners and then contact the respective server administrator.

Additional information on server codes can also be found here.

It might be possible to enhance the PLSQL solution to work past the issue, however I would not encourage that and instead stay with best practice.

On the topic of analytical functions, I will be the first to say that they are not my strength. However that said, reading one article after another I am increasingly aware of what I am missing.

I stumbled upon an excellent article demonstrating significant performance gains across the board from timing to resource utilization, all due to the use of analytical functions.

I encourage you take some time and review the posting.

Analytic Functions: A Savior by ASIF MOMEN

Sourced from := oracle-sql-l@Groups.ITtoolbox.com

A question was posed on the listserver regarding the 'how' of identifying how many vowels are in a particular string of text. Not certain as to the purpose of same, however intresting nonetheless. For those on 10g+, use of the REGEXP% syntax comes into play.

DECLARE
v_str varchar2(200):='RAJEEV HERE from lucknow' ;
vn_vowel number;
BEGIN
SELECT length(v_str)- length(REGEXP_REPLACE(v_str,'[a,e,i,o,u,A,E,I,O,U]',''))
into vn_vowel FROM DUAL;
dbms_output.put_line(vn_vowel);
END;

For those on versions prior to 10g, this method was proposed that provided the same result.

select length(COLUMN_NAME) - length( translate(lower(COLUMN_NAME),'zaeiou','z')) from TABLE_NAME ;

Provided by Chaitanya Susarla <chaitanyasusarla at yahoo.com>

This article is just to show some quick yet simple examples of how to use analytic functions in ORACLE. Though It doesn't cover all the analytical functions available, I am just focussing on trivial functions we use in daily sql queries and to understand the way analytics work for them.

All the following examples are tested on ORACLE Version 10.2.0.3.0.

What are analytic functions?
Answer:

(From Oracle documentation of 10g Release2(10.2))

(url: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#sthref965)

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.

Analytic functions are commonly used to compute cumulative, moving, centered, and reporting aggregates.

Do not worry If you don't understand the defintion from Oracle documentation. For any first timers, it is tough to grasp it quickly. After going through the following examples, probably you will have idea of what they are, so that you can revisit the definition and/or URL once again to get complete understanding of how they work and what they are meant for.

Now let us start.....

Pages: 1 · 2 · 3 · 4 · 5 · 6 · 7 · 8 · 9