Search

Top 60 Oracle Blogs

Recent comments

Home » Monthly archive

March 2013

The materialized view approach for implementing a table constraint

Submitted by Toon.Koppelaars on Fri, 2013-03-08 12:32

In yesterdays post I announced that I'd spent a separate post on how we can use materialized views to enforce table constraints. So here goes.

The high-level cookbook for this approach is as follows:

  1. We create a materialized view that refreshes on commit,
  2. The materialized view is defined in such a manner that it will hold no rows when the table constraint is adhered to by the current transaction trying to commit,
  3. And it is defined such that it will hold (at least) one row when the table constraint is violated by the current transaction trying to commit,
  4. We devise a construct such that on-commit refresh of the materialized view *always* fails whenever (at least) one row is materialized in the view. This can be done in two manners:
    1) we add a check constraint on the underlying table of the materialized view that always fails, or

When unused – isn’t.

Submitted by Niall Litchfield on Thu, 2013-03-07 14:44

So we recently reviewed a table with no fewer than 23 indexes on it. A combination of alter index… monitoring usage and reviewing DBMS_HIST_SQL_PLAN for plans that referenced the indexes found 8 indexes that were never used. As this table is also heavily updated we removed the indexes identified as unused. This was, fortunately, a […]

And what about table constraints?

Submitted by Toon.Koppelaars on Thu, 2013-03-07 13:21

In a previous post we've introduced a classification scheme for constraints:

  • attribute constraints
  • tuple constraints
  • table constraints
  • database constraints
  • dynamic constraints
And talked a bit about how we could implement the first two classes here. In today's post we will make a start talking about how we can implement table constraints using triggers. But before we do that we will offer some thoughts on how the ideal world with regards to this subject would look like.

MobaXterm 6.2…

Submitted by oraclebase on Wed, 2013-03-06 09:07

I just noticed a new version of MobaXterm has been released. Not totally sure what has changed in 6.2. I can’t seem to find a changelog.

Fun, fun, fun…

Cheers

Tim…

Update: Fidel pointed out the obvious changelog. Shows how observant I am. :)

Can Oracle Database Release 2 (11.2.0.3) Properly Count Cores? No. Does It Matter All That Much? Not Really..

Submitted by kevinclosson on Wed, 2013-03-06 00:45

…and with a blog post title like that who would bother to read on? Only those who find modern platforms interesting…

This is just a short, technically-light blog post to point out an oddity I noticed the other day.

This information may well be known to everyone else in the world as far as I know, but it made me scratch my head so I’ll blog it. Maybe it will help some wayward googler someday.

AWR Reports – Sockets, Cores, CPUs
I’m blogging about the Sockets/Cores/CPUs reported in the top of an Oracle AWR report.

Consider the following from a Sandy Bridge Xeon (E5-2680 to be exact) based server.

Note: These are AWR reports so I obfuscated some of the data such as hostname and instance name.

Hotsos 2013 – Presentation “Creating Structure in Unstructured Data”

Submitted by marco on Tue, 2013-03-05 15:22

Hereby, for those who want another look or for people to share, my presentation content “Creating Structure in Unstructured Data” given during the Hotsos 2013 Symposium on Monday morning. HTH Marco Hotsos 2013 – Creating Structure in Unstructured Data from Marco Gralike

TOAD Ambulance Button (and a little Google)

Submitted by doug on Tue, 2013-03-05 07:00

I've recently spent quite a while working in a performance team that supported Production performance incidents and worked with developers on application performance before the code hit Production. Whenever a developer asked me to look at the execution plan for their poorly performing statement, they would send over some evidence of the poor plan and in the majority of cases I found myself saying - "You didn't produce this using that damn Ambulance Button in TOAD, did you?"

Even since I've moved on to a new role, the last time I found myself saying that was only last week to an experienced Development Lead who knows Oracle pretty well for a Java lady. I then went on to show her DBMS_XPLAN and SQL Monitoring.

Duplicate indexes ?

Submitted by Jonathan Lewis on Mon, 2013-03-04 17:39

I don’t think this is likely to happen on a production system (until 12c) – but look what you can do if you try hard enough:

  1  select
  2     index_name, column_name from user_ind_columns
  3  where
  4     table_name = 'T1'
  5  order by
  6*    index_name , column_position
SQL> /

INDEX_NAME           COLUMN_NAME
-------------------- --------------------
T1_I1                N1
                     V1

T1_I2                N1
                     V1


4 rows selected.

That’s a straight cut-n-paste from an Oracle 11.1.0.7 SQL*Plus session. (You can tell I typed it in real time because I missed the return before the FROM, and couldn’t be bothered to go back and do it again ;) )

AUSOUG session February 2013

Submitted by connor_mc_d on Mon, 2013-03-04 10:13

Thanks to everyone that came along.

The slides can be found here