PL-SQL Tips and Tricks

Standard

I, like every other coder, have had a series of times where we need to get creative to get something done. Here’s my documented list of Tips and Tricks. This is by no means complete, but I’ll try and update this as I come across new things.

Get the source code for Oracle materialized views:

I needed this when I lost the file that created the MV that I was working on in DEV. Using this, I was able to get the code that was committed to the DB back.

SELECT
QUERY
FROM
USER_MVIEWS
WHERE
MVIEW_NAME = '[Materialized View Name]'

Source

Oracle Row Generator using “CONNECT BY LEVEL”

This is useful when you need to generate a table with a single column of values that are incrementing. I use this when I am working through data in the ETL process.

SELECT
LEVEL just_a_column
FROM
dual
CONNECT BY LEVEL <= [Number of rows needed]

Source

 

Bitmap Join Indexes

“In a Bitmap Index, each distinct value for the specified column is associated with a bitmap where each bit represents a row in the table. A ‘1’ means that row contains that value, a ‘0’ means it doesn’t.”

“Bitmap Join Indexes extend this concept such that the index contains the data to support the join query, allowing the query to retrieve the data from the index rather than referencing the join tables. Since the information is compressed into a bitmap, the size of the resulting structure is significantly smaller than the corresponding materialized view.”

CREATE BITMAP JOIN INDEX <index_name>
ON <table_name> (<table_name.column_name>)
FROM <table_name, table_name>
WHERE <join_condition> 
 
-- for example:
CREATE BITMAP INDEX cust_sales_us
ON    sales(customers.state)
FROM  sales, customers
WHERE sales.cust_id = customers.cust_id;

Source