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]'
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]
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;