Tag: sql

Quick tip on Function Based Indexes

For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes. SQL> create table blah ( x varchar2(30)); Table created. SQL> create index blah_ix on blah ( upper(x)); Index created. SQL> select…

Median in SQL

Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial query can determine the median salary from the EMP table SQL> select avg(sal) from 2 ( select x.sal 3 from scott.EMP x,…

Views as “stored text”

You’ll often see comments that views are not copies of the data, they are merely the stored text of a query that defines. This is by and large true, but don’t forget, this is not  the same as saying we simply take whatever text…

The first matching row

I was recently asked when presented with a query along the lines: SQL> select * from 2 ( select x 3 from t1 4 where x = :b1 5 union all 6 select x 7 from t2 8 where x = :b1 9 )…

Equi-sized partitions on random data

Had an interesting AskTom question today about dynamically keeping partition sizes in check. The poster had large table, already partitioned, but since there was no logical column to do a range partition on, it was partitioned on a surrogate primary key value. Due to…

Something new learned every day

One of the reasons I leapt at the chance to be on the AskTom team when we resurrected the site, was that it’s like free training.  You get questions on topics you have not visited before, and you get new angles on things you…

On building SQL

I had a fun question on AskTom over the weekend, that of, how to display a monthly calendar for any provided date using just SQL. You can see the question and the answer here But I thought it might be worth explaining the process. …