SQL & PL/SQL
Submitted by John Watson on Wed, 2014-06-04 19:56
Why use interpreted PL/SQL when native compiled PL/SQL is so much faster? No reason at all - except that interpreted is the default, and most DBAs never change this. They should.
Submitted by Lalit Kumar B on Sat, 2014-05-03 09:48
It is a frequently asked question in almost all the Oracle forums. There have had been numerous questions/posts regarding "But how to generate the trace file?" Well, it might seem a heck of a task, however, looking it step by step will make you understand that it is actually not that difficult.
Usually, database application developers do not have the required/necessary permissions/privileges to do all the steps that I will mention below. However, most of the steps could be done by application developer. A few steps will need a DBA privilege.
Submitted by Lalit Kumar B on Sat, 2014-05-03 09:20
An important thing regarding function, you would agree with me that, at least once a PL/SQL developer must have heard that "A function MUST ALWAYS RETURN a VALUE of proper datatype". Having been said that a million times on various platforms, still developers make this mistake.
Submitted by Lalit Kumar B on Sat, 2014-05-03 08:52
Submitted by Lalit Kumar B on Fri, 2014-05-02 09:39
One of the most important things that a developer does apart from just code development is, debugging. Isn’t it? Yes, debugging the code to fix the errors that are raised. But, in order to actually debug, we need to first capture them somewhere. As of now, any application has it’s own user defined error logging table(s).
Imagine, if the tool is rich enough to automatically capture the errors. It is very much possible now with the new SQL*PLus release 11.1
Submitted by prabodha.sahoo on Thu, 2014-01-30 03:50
Oracle Pipelined Table Functions
________________________________________
Overview
Basically, when you would like a PLSQL (or java or c) routine to be the «source»
of data -- instead of a table -- you would use a pipelined function.
PIPELINED functions will operate like a table.
Submitted by prabodha.sahoo on Thu, 2014-01-30 03:47
virtual columns in 11g
Oracle has supported stored expressions for many years, in views and function-based indexes. Most commonly, views enable us to store and modularise computations and expressions based on their underlying tables' columns. In more recent versions (since around the 8i timeframe), we have been able to index expressions using function-based indexes. Now, with the release of 11g, Oracle enables us to store expressions directly in the base tables themselves as virtual columns.
Submitted by learn-with-vide... on Sat, 2014-01-18 15:12
Finding islands are classic problems in PL/SQL. The basic concept is that you have some sort of numbers, like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26. The islands problem involves identifying ranges of existing values. For these numbers, the solution will be as follows:
first_island_element last_island_element
1 3
5 6
8 10
15 15
20 23
25 26
Submitted by learn-with-vide... on Sun, 2014-01-12 06:20
Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows:
START_GAP END_GAP
4 4
7 7
11 14
16 19
24 24
First, run the following code, to create tab1 table:
CREATE TABLE tab1
(
Submitted by rleishman on Thu, 2013-05-30 01:34
(with apologies to Robert Ludlum and Eric Van Lustbader)
Oracle performance tuning is an excellent source of myths. The very best ones have a group of adherents who continue to support the myth even when presented with counter-examples. Who’s heard of these?
- Joins are faster than sub-queries
- Sub-queries are faster than joins
- Full Table Scans are bad
Those ones have been around as long as I can remember. Probably the single greatest concentration of Oracle performance tuning myths centres on Bitmap Indexes. Are these familiar?
- Bitmap indexes are good for low-cardinality columns, whereas B-Tree indexes are good for high-cardinality columns.
- Bitmap indexes are slow to update.
- Bitmap indexes don't support concurrent updates.
Pages
|
