Bolte IT Consulting
Oracle PL SQL
A category holding articles for Oracles programming language PL SQL.
Recently I had to rebuild some indexes, since they met two major conditions under which an index rebuild most likely will improve performance.
According to Burleson, an index rebuild can help under the following conditions.
http://www.dba-oracle.com/art_dbazine_idx_rebuild.htm
-
High index fragmentation: The SQL workload has lots of table DML causing lots of deleted leaf blocks.
-
High index scan access plans: The SQL workload is rich with index scans (index fast-full scans and index range scans)
The second condition definetely holds valid for my clients database. The application, RSA IGL is used for over 10 years without any archiving or regular house keeping and up to 100 Million entries in several important tables.
- Hits: 5062
Unfortunately you will not always have all rights on the database you would want or need to fulfill your tasks.
Especially when you have to do performance tuning on Queries and you do not have the right to use the Oracle Advisor you can get frustrated because the DBA is not available as you only do stuff on a UAT or even SIT. "Sorry, the DBA is busy with PRD." Nice!
When in addition your company decided to switch off automated update of statistics for the Optimizer, you want to pull out your hair.
- Hits: 2711
Recently I had to check over and over again, if tables, which I needed for testing are existing in an Oracle database.
Since checking for existance and completion repeatedly involved the same tasks I decided to implement little PL SQL script, which does the task for me.
SQL
- Hits: 5691
Stored Procedures and Functions are sometimes referred to as Stored Procedures. The difference between Stored Procedures and Functions is simple. Functions have to return values, Stored Procedures do not return anything. They just do stuff.
Stored Procedures do not execute DDL Statements. Creating database objects like tables at runtime is a bad idea since this should be defined by a datamodel. If you try to create a Procedure with DDL Statements it will not compile.
- Hits: 5804
In order to select a value / a result from a query into a variable in PL SQL, you can use the following syntax.
- Hits: 5591