A cool thing with EXCHANGE PARTITION (part 1)

“Common sense” would tell us that if I am running a query against a table, and then all of a sudden I rip out a giant chunk of that table with a DDL command, then either

  • the query should crash, or
  • the DDL command should not be permitted to run.

But in fact, with Oracle, we can even go one better in many situations.  We can allow the DDL and still have the query (more...)

Those pesky dates as strings

You might be thinking “Nothing is more frustrating that encountering a string column that is full of dates”.

But there is something worse than that…and that is, when that string column is full of potential dates, and your job is to sift out the good data from the bad data. For example, if your table looks like this:


SQL> select * from T;

DATE_STR
--------------------
qwe
01/01/2000
31/02/2000
12-jan-14
20001212
Jan 14, 2016

6 rows  (more...)

OTN Yathra– scenes from Bangalore and Hyderbad

It’s sad that I could not capture in pictures the amazing sights and sounds from both inside the conference and outside in the streets of these amazing places.

I was too busy just absorbing it myself and neglected to take enough pictures Sad smile

But again, a truly wonderful couple of days.


OTN Yathra Chennai

Rather than try to convey in words the first couple of days in India, I thought I’d share some of the images from the first days here in India, covering the Chennai conference. Enjoy

 


Subtle variations in optimizer stats

Subtle variances in the way you work with statistics can have a significant impact on how your optimizer plans work out…so you need to take care.

Let’s look at the following example


SQL> create table T (
  2    x varchar2(20) , y varchar2(100));

Table created.

SQL> insert into T
  2  select 'x' , rpad('z',100) from all_objects;

94117 rows created.

SQL> insert into T
  2  select 'X' , rpad('z',100) from all_objects;

94117 rows created.

SQL> exec  (more...)

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 column_name from user_ind_columns
  2  where index_name = 'BLAH_IX'
  3  /

COLUMN_NAME
------------------------------
SYS_NC00002$

Don’t panic. Simply take a look at USER_IND_EXPRESSIONS to find the function that you used.


SQL> select column_expression  (more...)

A little known RI clause

Most people are aware of this standard referential integrity control, namely you can’t delete the parent if children exist


SQL> drop table PAR cascade constraints purge;

Table dropped.

SQL> create table PAR ( p int primary key, data int);

Table created.

SQL> insert into PAR
  2  select rownum, rownum
  3  from dual connect by level <= 7;

7 rows created.

SQL>
SQL> drop table CHD purge;

Table dropped.

SQL> create table CHD ( c int  (more...)

Active and Inactive Sessions

Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc).

However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. The name suggests the time since the last call, but the documentation provides a more useful description:

 

If the session STATUS is currently ACTIVE, then the value represents the elapsed (more...)

Why PLSQL ?

With Collaborate 2016 under way, there seems no better time to reflect on why PL/SQL is the natural choice for anyone who loves to code, and loves their data


Technology debates

As always happens from time to time, we had the following request on AskTom today:

Could you list down 2 reasons why sql server is better than oracle?

Then 2 counter reasons as to why why oracle is better than sql server

 

And I thought I’d reproduce my response here

 

I’ll answer it slightly differently…

Q: When is SQL Server better then Oracle ?
A: When you have good, intelligent, knowledgeable SQL Server people (more...)