sql

Stupid unique index tricks...

Submitted by dave on Thu, 05/30/2013 - 17:59
I have a case where I need to enforce uniqueness on a combination of columns but only when a flag is set (ACTIVE_FLAG=1). The table also holds history and may have multiple inactive records mixed in but I don't want to keep those from being added to the table. This can be done by exploiting the fact that null index expressions are not indexed. DECODE and function based index to the rescue!
[node:read-more:link]
Tags: 
sql

DBDeployTool.pl

Submitted by dave on Fri, 08/03/2012 - 00:31

In a previous life I got to spend at least one night a week deploying developer's scripts to production databases... Running scripts one by one got old and was error prone. I created DBDeployTool.pl to read a list of scripts and deploy the ones that hadn't been deployed yet. Basically it was a way to "catch up" schemas to a new version.

Since it worked incrementally it was as useful in dev/test as it was in prod. We could add scripts to be deployed, crank up the tool to get us to the new version.

Tags: 
sql
deploy
GoldenGate

V$OBJECT_MONITORING gotcha...

Submitted by dave on Wed, 07/25/2012 - 13:03

I have been checking out some usage patterns on a database I am migrating to Exadata. I won't go into the 'drop all of the indexes' herd mentality... but I would like to know what is really being used on this system so we can make some educated guesses about what is really needed. Luckily it is a DW / ETL type of application that has a comprehensive nightly refresh. Yes, a nice way of saying flush and fill. Well at least I know they touch _every_ important object _every_ night.

[node:read-more:link]
Tags: 
oracle
sql
monitoring
indexing

This is why we can't have nice things...

Submitted by dave on Tue, 07/03/2012 - 10:34
This is probably old hat for most of my audience but I needed to get this down on paper (or bits actually) for posterity... The granularity of Oracle privileges leaves a lot to be desired...
[node:read-more:link]
Tags: 
sql
privileges

Quick filter for Application Express Reports

Submitted by dave on Mon, 12/05/2011 - 12:44
I normally get a lot of mileage out of Application Express interactive reports. Unfortunately there are some restrictions on the SELECT statements that can be used with them. Here is a demonstration of a technique I use when I want to add some interactivity to a plain old Apex report. I will create a Select List dropdown that submits and refreshes the report data automagically. The value in the select list will be applied as a filter to the report when it refreshes.

1) Create the Select List

• Items -> New Item -> Select List • Choose "Select List with Submit" • Next
[node:read-more:link]
Tags: 
sql
Application Express

HAVING & Bind Variables

Submitted by dave on Tue, 11/22/2011 - 17:34
Had an odd one come across my desk today. I have seen Bind variables used in the WHERE clause thousands of times. I have seen Binds in the SELECT lists hundreds of times. I have seen it used in the HAVING clause zero times... until today when it turns out it was causing a performance issue. With the Bind variable in the HAVING clause we were seeing an execution plan with lots of Full Table Scans and 12+ second runtimes. When editing the query and using a literal in the HAVING clause we saw 100ms or less performance and very efficient Execution Plans.
[node:read-more:link]
Tags: 
sql
bind variables

SQLDeveloper User Defined Reports Pack

Submitted by dave on Thu, 11/10/2011 - 00:37

I have started a project to collect useful SQL Developer User Defined Reports. More info to follow after I complete a few check-ins.

https://github.com/dmann99/SQLDevUDRepPack

-Dave

Tags: 
sql
SQLDeveloper
reports

New Project: trc2sql.pl - Extract SQL from an Oracle Trace...

Submitted by dave on Fri, 10/14/2011 - 17:18

I started a new project for a utility I started writing. It has some basic functionality now but will probably increase in utility int he future.

It is a Perl script that extracts the SQL statements from an Oracle 10046 trace file. This is useful if you need to re-run SQL that was captured during a trace. It also helps reduce the amount of junk you have to slog through if you are interested in

trc2sql.pl - Extract SQL Statements From Oracle 10046 Trace File

-Dave

[node:read-more:link]
Tags: 
oracle
perl
sql

SQL Tuning Advisor - what profile am I accepting?

Submitted by dave on Thu, 10/13/2011 - 13:35
When working on a SQL Tuning issue I often kick off a SQL Tuning Advisor job on the SQL while I gather info about statistics and other things I like to check when tuning. After I get an idea of where performance might be suffering I use the SQL Tuning Advisor results for a second opion. Unfortunately more often than not the SQL Tuning Advisor reports it can dramatically improve the performance of a SQL Statement by using a SQL Profile to force a different plan.
[node:read-more:link]
Tags: 
sql
oem
tuning

A SQL_ID by any other name... Same SQL / Different Tools / Different SQL_ID

Submitted by dave on Tue, 10/11/2011 - 23:33
I have been trying to chase down the same SQL being submitted against the same 10.2.0.4 Solaris database from different query tools used in our organization. I have been striking out when trying to match up with SQL IDs. I connected with 5 different tools (including SQLPLUS on 2 platforms) and submitted the following 4 lines of SQL: 
SELECT *
  FROM EMP, 
       DEPT 
 WHERE EMP.DEPTNO=DEPT.DEPTNO
[node:read-more:link]
Tags: 
sql

Pages

Subscribe to sql