On TopN Analysis: Rownum – Row_Number – Rank

Here is another reason why you might test before making a decision on which function to use by your previous experiences. In this scenerio there is large partitioned log table just having a primary jey on nlog_id column and we want to find the last N inserted rows.

Code Listing 20 : On TopN Analysis: Rownum – Row_Number – Rank

Testing Information : the scripts mentioned are tested on Oracle Database 10g Enterprise Edition Release

Refences Used : On Top-n and Pagination Queries By Tom Kyte, Oracle Magazine Article

The differences between a marketing mindset and a scientific mindset

Without science, the forces of marketing and superstition combine to create waste that you pay for.

Cultures governed by superstition and marketing

1. Seller provides information that’s in the seller’s best interest
2. Waste
3. Guesses, hopes

Cultures governed by science

1. Seller provides accurate information because you can tell the difference
2. Efficiency
3. Fully informed decisions

Marketing mindset

1. Motive – Selling
2. Worst thing that could happen – Not selling
3. Communication style – Subjective
4. Spin The whole truth, Self-credit

Scientific mindset

1. Motive – Learning
2. Worst thing that could happen – Not learning
3. Communication style – Objective
4. Nothing but the truth, it’s hard!

Science begins with doubt. The late Dr. Richard Feynman, one of the most important physicists and educators of the twentieth century, characterized a scientist as someone who actively doubts, who instead of believing everything he sees and hears, subjects his observations to formal scrutiny. It is an immensely valuable habit to get into. It is the habit of asking one’s self questions like Really?, How can I be certain?, and What if I’m wrong?

If you ever find yourself unable to think of how to construct a test, or unable to think of anything useful to test, visit Tom Kyte’s page http://asktom.oracle.com. There you’ll find an entire community engaging in the kind of behavior described above.

References used : TESTING ORACLE PERFORMANCE presentation by Cary Millsap

History of Oracle

1978 -> Oracle V1; first commercial SQL relational database management system (RDBMS), Main architect Bob Miner, ran on pdp-11 under rsx; 128Kb memory, written in assembly, separated oracle and user codesto overcome the memory limitations

1979 -> Oracle V2; written in pdp-11 assembly language, ran on vax/vms in compatibility mode

1980 -> Oracle V3; written in C, soptable source code, introduced Transactions

1984 -> Oracle V4; introduced read consistency, ported to many plathforms, first interopability between PC and server

1986 -> Oracle V5; true client-sever, vax cluster support, distributed queries

1989 -> Oracle V6; OLTP performance enhancements, online backup/recovery, row level locking, plsql language, parallel server

1993 -> Oracle V7; declarative referential integrity, stored procedures and triggers, shared SQL, parallel execution, Advanced replication

1997 -> Oracle V8; Object-relational database, three-tier architecture, partitioning

1999 -> Oracle V8i; Java in database and native java support, XML support, Oracle Internet Directory, Summary management interMedia, Data warehousing enhancements, ported to Linux, Business components for java(BC4J), WebDB introduced(eventually mature into Portal and ApEx)

2001 -> Oracle V9i; Automatic segment space management, Real Apllication Clusters, Internet security enhancements, Data Guard, Advanced globalization support, record-breaking TPC-C benchmark results, 1st to complete 3 terabyte TPC-H world record

2003 -> Oracle V10g; Enterprise Grid Computing, 64-bit Linux with IPF

2005 -> Oracle VXE; free Oracle 10gR2 database

2007 -> Oracle V11g; as announced at Openworld 2006

Version Date Release Name

2 June 1979
3 March 1983
4 October 1984
5.0 April 1985
6.0 July 1988
7.0 June 1992
7.1 May 1994
7.2 May 1995
7.3 February 1996
8.0 June 1997 Oracle 8
8.1.5 February 1999 Oracle 8i Release 1
8.1.6 November 1999 Oracle 8i Release 2
8.1.7 August 2000 Oracle 8i Release 3
9.0.1 June 2001 Oracle 9i Release 1
9.2 May 2002 Oracle 9i Release 2
10.1 January 2004 Oracle 10g Release 1
10.2 July 2005 Oracle 10g Release 2

References used :

The Storage Hierarchy Summary in an Oracle Database

In summary, the hierarchy of storage in Oracle is as follows:

* A database is made up of one or more tablespaces.
* A tablespace is made up of one or more data files. These files might be cooked files in a file system, raw partitions, ASM managed database files, or a file on a clustered file system. A tablespace contains segments.
* A segment (TABLE, INDEX, and so on) is made up of one or more extents. A segment exists in a tablespace, but may have data in many data files within that tablespace.
* An extent is a logically contiguous set of blocks on disk. An extent is in a single tablespace and, furthermore, is always in a single file within that tablespace.
* A block is the smallest unit of allocation in the database. A block is the smallest unit of I/O used by a database.

References used : Oracle® Database Concepts 10g Release 2 (10.2)

On Oracle Certification

I strongly believe that in Turkey Human Resource specialists and even the Information Technology managers who are dominant during the recruiting process are not aware of the Oracle Certification terms; OCA, OCP and OCM.

Also what an OCP is or is not is another discussion topic. The very known Tom Kyte is not an OCP :) check this post

For me having a two-three years of Oracle development or administration experience in a busy company is much more important than having an OCP degree. The reason is simple, because this exam is not effective to check the participant’s Oracle experience and easy conditions are there to pass for someone who studies sample questions for several weeks. Check this post for an example :)

But when it comes to having an OCM degree it is really different, OCM credential is for the most advanced Oracle database administrator. This individual has comprehensive and extensive experience on a wide variety of topics since this exam consists an intensive two-day hands-on practical examination.

Recommended minimum skills include:

1. Proficient with Oracle 10g SQL
2. Working knowledge of LINUX command language that includes:

    a. Formatting and executing basic OS commands
    b. Creating and navigating through directory structures
    c. File management using copy, move, and delete
    d. Linux environment text editors
    e. Setting environment variables

3. The ability to locate and launch Oracle executables that include:

    a. RMAN utility
    b. Oracle Net Manager
    c. Oracle Net Configuration Assistant
    d. OEM
    e. Listener Utility
    f. OMS
    g. Oracle Password Utility
    h. Database Creation Assistant

4. Proficient with Oracle Enterprise Manager
5. Proficient in using Oracle Net Manager and the Oracle Net Configuration Assistant to configure networking
6. Advanced knowledge and use of Oracle Enterprise Server technology and features
7. Familiarity navigating through online Oracle documentation
8. Proficient with using Mozilla 1.6 browser software

You may want to check these free Oracle Certificate Practice Tests, also these vendors demos are available(Cisco®,CompTIA,Lotus,Microsoft,Novell,PMI,Sun,ISC2,Check Point,IBM,ITIL)

But if you want to experience a real challenge on your PL/SQL knowledge I reccomend you to try this one :)

For more information on Oracle certification you may want to check these resources;





The Power of Attitude

Our lives are not determined by what happens to us, but by how we respond to what happens; not by what life brings us, but by attitude we bring to life. A positive attitude causes a chain reaction of positive thoughts, events, and outcomes. It is a catalyst… a spark that creates extraordinary results.

From a strictly mathematical viewpoint proof goes like this ;)

A B C D E F G H I J K L M N O P Q R S T U V W X Y Z is represented as:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26.

11+14+15+23+12+5+4+7+5 = 96%

8+1+18+4+23+15+18+11 = 98%

1+20+20+9+20+21+4+5 = 100%

So, one can then conclude with mathematical certainty that while Knowledge and Hard Work will get you close, Attitude will get you there :)

Refences Used : The PL/SQL Grid: Time to Expand to 10g R1 & 10g R2 presentation by Joe Trezzo

How to Answer Questions the Smart Way

#1. Don’t answer questions to which you don’t know the answer
#2. Explain yourself
#3. Give as little assistance as necessary
#4. Show your workings
#5. Use humour judiciously
#6. If you can’t say something nice don’t say anything at all
#7. Avoid jargon, baffling acronyms and idiolects
#8. Never never never just respond with RTFM. Not ever.
#9. Meditate on eternity
#10. Keep your newbie mind

Refences Used : How To Be A Good Guru post by APC

Some inspirational quotes

No great thing is created suddenly. —Epictetus (A.D.200)

Well done is better than well said. —Benjamin Franklin

Know thyself. —Socrates

No matter where you go, there you are. – Buckarro Banzai

Knowlege comes with experience and experimentation. – Tom Kyte

Always is never true, never is always false. – Tom Kyte

I believe strongly – and more strongly every day – that there are only two possible answers to a “first question”. They are: Why and It Depends. – Tom Kyte

Claims — don’t want em! Benchmark, metrics, statistics — love em — want em — need em.. – Thomas Kyte

Do you really want to be an Oracle-(wo)man? Follow Thomas KYTE – Anonymous

Oracle documentation is here – H.Tonguç Yılmaz ;)