Skip navigation.

SQL & PL/SQL

SQL and PL/SQL Articles

Impact on Packages - Assign permissions directly vs Assign permission via Roles

Document depicts how Roles and Privileges behave for Packages

DB Version: 10.2.0.4

Users: TESTUSR (Table Owner)
TESTUSR_PKG (Package Onwer)
TESTUSR_APP (Application Owner)

Roles: TESTUSR_APP_ROLE
TESTUSR_PKG_ROLE

Table: TEST_TBL
Package: TEST_PKG

Table Text:
create table test_tbl(a number);

Package Text:
CREATE OR REPLACE PACKAGE test_pkg AS -- spec
PROCEDURE insert_test_tbl (
a NUMBER);
PROCEDURE delete_test_tbl (
a NUMBER);
END test_pkg;

CREATE OR REPLACE PACKAGE BODY test_pkg AS -- body
PROCEDURE insert_test_tbl (
a

Oracle Pipelined Table Functions

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.
A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions.

virtual columns in 11g

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.

Finding islands – 4 methods in Oracle

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.

Finding gaps with analytic functions

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
(

The Bitmap Conspiracy

(with apologies to Robert Ludlum and Eric Van Lustbader)

The Bitmap Betrayal (Introduction)

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.

Part 1 - The Bitmap Identity

What is a Bitmap Index?

This is first post of the four-part epic - The Bitmap Conspiracy - detailing the structure and behaviour of Bitmap Indexes. Later in the series we will cover the internal structure of Bitmap Indexes, how Oracle uses them, and finally we will expose some of the myths surrounding them. But before we get there let’s just get a clear understanding of what a Bitmap Index actually is.

Part 2 - The Bitmap Supremacy

The Structure of a Bitmap Index

I’ve been tuning Oracle database applications for a long time now. I started out recognising some simple patterns and applying template fixes (Got a full table scan? Use an index!) but such a collection of “Do this; don’t do that” anecdotes will only take you so far. If you are curious (I was), you can uncover the reasons why one method is faster than another; i.e. what is the computer doing to make slow code so slow. I found that a good understanding of the internals meant that you didn’t always need to know how to tune a specific example because you could work it out for yourself.

In a database application, these investigations frequently lead to data structures; how does the database store its information and how does it retrieve it? Good information on the internals of Bitmap Indexes is hard to piece together, so in Part 2 of this Bitmap Indexing epic we will look more closely at the internals of Bitmap indexes.

Part 3 - The Bitmap Dominion

Bitmap Execution Plans


This is Part 3 of The Bitmap Conspiracy, a four part epic on Bitmap Indexes.

In Part 1 we touched briefly on how Oracle can use Bitmap Indexes to resolve queries by translating equality and range predicates into bitmap retrievals. Now that we know more about how they are stored (see Part 2), let’s look closer at some of the operations that Oracle uses to access Bitmap Indexes and manipulate bitmaps.

Part 4 - The Bitmap Legacy

  1. Myth #1: Bitmap indexes are good for low-cardinality columns

Myth #1: Bitmap indexes are good for low-cardinality columns

This is one of those myths that is probably best demonstrated by example. First, let’s create some test data: 10 million rows with the following columns:

  • COL1 – Alternating values of 1 and 0 – not indexed
  • COL2 – Identical to COL1 – bitmap indexed
  • PAYLOAD – 100 bytes of X’s to make the rows a realistic width – not indexed

COL1 and COL2 are identical low cardinality columns: there are only two valid values.