Boneist's Oracle Blog

Today saw me trundling off to dear old Birmingham for the second time in 2 weeks (the first time being for the UKOUG Tech17 conference) and doing a fair impression of a nervous wreck.

That’s because I was heading to take both the SQL (1z0-061) and PL/SQL (1z0-144) Oracle certification exams.

I don’t like doing exams, mainly because I’m rubbish at revising (seriously; the most revision I did throughout all my university career consisted of 10-15 minutes before each exam. Thankfully, the Maths degree I did (and got!) involved practising stuff as part of the coursework, so I learnt by doing, rather than rote memorisation).

I was very worried that I’d fail these exams because the questions in the exam (certainly in the practice exams) don’t necessarily reflect anything like what you’d do in real life.

For example, I would never consider using NATURAL JOINs in any of my code, and I’d certainly flag it up if I ever saw it in someone else’s meant-for-production code! Why guess at what join conditions you want, rather than specifying them explicitly? Especially when that opens you up to a potential bug (what if someone adds a new column that ends up being included in the join condition?). Even the JOIN … USING form is not something I’d recommend, because then you will end up aliasing some columns but not the columns in the join; why make life hard for yourself when you can use JOIN … ON and simply alias all the columns?

Anyway, I digress. I passed both exams relatively convincingly, fortunately. It would have been embarrassing if I’d failed them, given how long I’ve worked with SQL and PL/SQL!

The main reason I’m writing this post is because Tim Hall basically asked me for an interview, and I thought I would humour him (because he definitely didn’t have a good idea. Nope! No siree! *{;-) ). And also, it’s been waaay too long since I last updated this blog!

On with the interview:

You’re a very experienced Oracle developer. What made you decide to do some Oracle certification exams at this point in your career?

I can’t take the credit for this; basically my department was allocated some budget to get everyone certified to either Associate (most of the developers) or Professional (the team leads) in 2017.

Before you say “didn’t you leave it a bit late?” I had planned on doing it at the beginning of October, but emergency surgery (I’m fine now!) the week before I was due to do it meant I had to postpone it. Today was the day!

What materials (books, blogs, training, practise questions) did you use to prepare for the exams?

The books I used were Roopesh Ramklass’s OCA Oracle Database 12c SQL Fundamentals I Exam Guide and Matthew Morris’s Study Guide for 1Z0-144: Oracle Database 11g: Program with PL/SQL: Oracle Certification Prep.

Roopesh’s book included a CD with some practice questions, so I worked my way through those. Matthew Morris’s book had a link (and discount) to a test you had to pay for, which I took and failed (this did *not* help my nerves one little bit!) and had to retake.

Plus, of course, there was the official Oracle training course for the PL/SQL exam (although I only managed the first 3 days of the online course!), plus the official practice exams provided by Transcender.

How long did you spend revising specifically for the exams?

I’ve been working with SQL for nearly 19 years and PL/SQL for at least 13 years, so you could say that I’ve been working towards the exams for a while now.

However, in terms of actually boning up for the SQL and PL/SQL exams? I’d already done a bit before life interfered – I’d read the first 3 chapters of Roopesh’s SQL book and done 3/5ths of the official PL/SQL training (I ran out of time on that one; I can’t blame the surgery for that!) plus the official practice exams.

Then I had a break of about two-and-a-bit-months, until last Monday (four days before the exams) when work very kindly gave me four days off for revision before the exams. I finished reading the SQL book over Monday and Tuesday (finishing up with the practice exams from the CD), and then read the PL/SQL book over Wednesday and Thursday (finishing up with the practice exams from the website linked to the book).

As revision goes, I couldn’t recommend my technique to anyone. I should have actually practised things more (e.g. actually creating triggers on tables, etc) but that would have taken far longer than the four days I had. Also, the SQL book didn’t cover some topics, such as LISTAGG (which I knew about) and the new FETCH FIRST… etc functionality (which I hadn’t realised would be tested), so perhaps a wider reading selection would have helped cover these extra topics.

Now you’ve passed the exams, how do you feel about the certification process?

I feel relieved, to be honest! These exams have been hanging over me for at least the past 6 months, and now they are done!

More seriously, I think they’re probably a good way to get beginners to learn a broad subset of SQL and PL/SQL, but like most text books, the examples given/questions in the exams are terribly contrived and are nothing like what you’d actually do in real life.

I think for our department (which consists of lots of PL/SQL developers, not all of whom are that familiar with SQL and/or PL/SQL) it was a good idea – at least everyone should now be at a similar level of knowledge.

I think some of the questions on the actual exams were very ambiguous. I had to spend time analysing the requirement and trying to work out what the heck they meant. Did they want employees earning over X salary that were hired in the last N months that have a manager, or was it employees with a manager plus employees hired within N months who earn over X salary? Or something else? *scratches head* I’m still not sure on that one, but I’m bloomin’ sure I wouldn’t provide an answer using set operations, which is what all the suggested answers used!

I’m convinced at least one of the questions in each exam didn’t have an answer. *That’s* not helpful. I think someone really needs to go through the exam and point out all these inconsistencies to Oracle.

Also I’m confused about why regexp and analytic functions were completely missing from the SQL exam and prep; there was one question where I could have ticked all four boxes, except it wouldn’t allow me to. I left the box that would have been covered by regexp_count unticked since that wasn’t in the training gumph.

Are you likely to do any more certifications?

Part of me is like “Noooooo, are you kidding? Why would I put myself through this again?!” but the completist side of me thinks that maybe I should go the whole hog and try for the OCP. Whether work would have enough budget for that (seeing as I’m not one of the team leads), I don’t know. I’m still debating whether I should ask or not.

Do you think you’ve benefited from the process?

I get an extra three letters to add after my name? I guess that’s a benefit.

I don’t think I learnt much of anything new, or at least nothing that I couldn’t google as part of my day-job (I mean, who remembers the syntax for creating a DDL trigger, if you only ever do it once in a blue moon?). Possibly the only thing I learnt was about PLSQL_OPTIMIZE_LEVEL, which I may bring up at work on Monday, if we aren’t already set at the highest level!

Personally, I don’t feel the need to “prove my worth” by having a piece of paper stamped with “OCA”, since I have other means of proving my capabilities, but I do think this might be of use to new SQL and PL/SQL developers. It’s not something that should be taken to indicate the person is an expert, though – experience is needed long before you can call yourself that! To be fair, I did see that mentioned on the Transcender practice exam page.

In a recent release that I’ve been working on, we had to drop and recreate some of the unique/primary key constraints (due to columns being added into the constraint) on some of our tables.

Prior to working in this team, I had only ever come across explicitly creating the indexes for a table, similar to:

create table test_explicit_index (col1 number,
                                  col2 varchar2(30),
                                  col3 date);

create unique index col1_exp_pk on test_explicit_index (col1);

We can see the index exists:

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_EXPLICIT_INDEX';

INDEX_NAME   TABLE_NAME           UNIQUENESS STATUS  
------------ -------------------- ---------- --------
COL1_EXP_PK  TEST_EXPLICIT_INDEX  UNIQUE     VALID   

If we drop the constraint, we can see that the index still exists:

alter table test_explicit_index drop constraint col1_exp_pk;

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_EXPLICIT_INDEX';

INDEX_NAME   TABLE_NAME           UNIQUENESS STATUS  
------------ -------------------- ---------- --------
COL1_EXP_PK  TEST_EXPLICIT_INDEX  UNIQUE     VALID   

Therefore, in my release scripts, I would always run a drop index statement after dropping a constraint supported by an index.

All was well with the world, until I moved to a team who created constraints that implicitly created the index, something like:

create table test_implicit_index (col1 number,
                                  col2 varchar2(30),
                                  col3 date);

alter table test_implicit_index add constraint col1_imp_pk primary key (col1) using index;

Once again, we can see that the index exists:

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_IMPLICIT_INDEX';

INDEX_NAME   TABLE_NAME           UNIQUENESS STATUS  
------------ -------------------- ---------- --------
COL1_IMP_PK  TEST_IMPLICIT_INDEX  UNIQUE     VALID 

But what happens when we drop the constraint?

alter table test_implicit_index drop constraint col1_imp_pk;

col index_name format a12;
col table_name format a20;

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_IMPLICIT_INDEX';

no rows selected.

The index is automatically dropped!

Again, all was well in the world – I no longer needed to explicitly drop the index first… great!

That is, until I encountered “ORA-00955: name is already used by an existing object” when trying to recreate the constraint with the extra columns. After researching, I discovered that although the constraint wasn’t present, the index still was. Oh no! Inconsistently created objects!

It was at this point, I remembered that Neil Chandler had written a blog entry about how to tell if the index is going to be dropped when dropping the corresponding constraint. Turns out, there isn’t an easy way – you have to use the sys.ind$ table which, of course, I don’t have access to!

“Ah well,” I thought, “I’ll simply have to code a bit of PL/SQL to drop the index if it exists, something along the lines of:

begin
  execute immediate 'drop index col1_exp_pk';
exception
  when others then null;
end;
/

However, I wasn’t really happy with this solution; it seemed… awkward. After doing some additional research, it turns out that Oracle have catered for dropping the index at the same time as the constraint, as mentioned in the documentation!

So now, in my release scripts, instead of a clunky PL/SQL procedure, I can just encapsulate the index drop in one command:

create table test_explicit_index2 (col1 number,
                                   col2 varchar2(30),
                                   col3 date);

create unique index col1_exp2_pk on test_explicit_index2 (col1);

alter table test_explicit_index2 add constraint col1_exp2_pk primary key (col1);

-- **** Ensure the index is dropped along with the constraint: ****
alter table test_explicit_index2 drop constraint col1_exp2_pk drop index;

select index_name, table_name, uniqueness, status
from   user_indexes where table_name = 'TEST_EXPLICIT_INDEX2';

no rows selected.

Problem solved!

Most people who are new to working with databases tend to think procedurally. That is: I have a table that I need to update, so:

• Find the first row
• Update the relevant columns with the relevant values
• Find the next row
• Update the relevant columns with the relevant values
• …
• Find the last row
• Update the relevant columns with the relevant values

People who are familiar with databases and set-based thinking would simply do:

• Update all the rows in the table, setting the relevant columns to the relevant values

I think you’ll agree that this set-based (i.e. taking the whole set of data and then applying the changes in one go) is a much neater approach than the procedural, step-by-step approach! Also, it will be faster, since you don’t have to spend time context switching between – in the case of Oracle – the PL/SQL and SQL “engines”.

Having helped out on the SQL and PL/SQL Oracle forum, I’ve seen lots of different people ask questions around how to fix a syntax error in their horrendously procedural, row-by-row (aka slow-by-slow, or in SQL Server land, rbar (row-by-agonising-row) code, only for people (myself included!) to say “don’t do that, do it like this: <set-based code>”.

I remember having difficulty with the concept of set-based when I first started working with PL/SQL way back when, so I thought I’d put together a bit of a guide as to how to spot when you can use set-based processing and, more importantly, how to convert from the procedural code.

Remember, as the great Tom Kyte says, “you should do [the work] in a single SQL statement if at all possible”.

Spotting Procedural Code

In PL/SQL, the number one flag for procedural code that can be converted to set-based code is: code looping through a cursor (yes, even if you’re taking advantage of bulk processing (BULK COLLECT/FORALL)!).

It’s not always possible to convert procedural code to set-based, of course – if the work you’re doing against each row is not DML on another table (e.g. you’re sending a mail, or writing out to a file), then there’s nothing you can do to make it set-based. (There are other options that may be available to you in this scenario, but that’s not the subject of this post.)

Ok, so I’ve found some procedural code… what next?

Let’s take an example that popped up recently on the forum. Rewriting to remove the poster’s syntax and logic errors, here’s the procedure they intended to end up with:

create or replace procedure valid_proc (start_date in date,
                                        end_date in date)  
is  
  cursor c1 is   
    select *
    from emp  
    where hiredate between start_date and end_date;
begin
  for c_row in c1
  loop
    insert into emp_back (empno,
                          ename,
                          job,
                          mgr,
                          hiredate,
                          sal,
                          comm,
                          deptno)  
    values (c_row.empno,
            c_row.ename,
            c_row.job,
            c_row.hiredate,
            c_row.sal,
            c_row.comm,
            c_row.deptno);  
  end loop;
  commit;
end;  
/

The poster started off by asking what was wrong with his code, and immediately, what leapt to my mind was: “You’re using row-by-row processing! Why not do it in a single insert?” If you look through the post to reply #16, you can see that BluShadow had the same thought and has provided the solution, which I present, slightly reformatted, here:

create or replace procedure valid_proc(start_date in date,
                                       end_date in date) as
begin
  insert into emp_back (empno,
                        ename,
                        job,
                        mgr,
                        hiredate,
                        sal,
                        comm,
                        deptno)
  select empno,
         ename,
         job,
         mgr,
         hiredate,
         sal,
         comm,
         deptno
  from  emp
  where  hiredate between start_date and end_date;

  commit; -- if appropriate to business/transaction logic
end; 
/

The steps to get from the original, row-by-row procedure to the set-based procedure are:

1. Look at the cursor
2. Look at the insert … values () statement
3. Think “all the rows being selected in the cursor are being inserted by the insert statement – this calls for an insert-as-select statement!”
4. Make sure the select statement in the cursor lists the columns being inserted by the insert statement
5. Remove the values() clause of the insert statement and replace with the amended select statement from the cursor

For those who are new to set-based thinking, I believe that step 3 is the hardest to grasp – part of it is familiarity with INSERT / UPDATE / DELETE / MERGE statements, but part of it is experience (which following the SQL and PL/SQL forum will help you get!)

But my procedure is much more complicated than that! I laugh at your single loop and give you my code with nested cursor loops!

First off, congratulations for reinventing the wheel – you have succeeded in replicating the NESTED LOOP join. Did you really intend to do that? If so, you’ve successfully hobbled Oracle’s optimizer to the possible detriment of your code (what if one of the other types of join condition was more efficient?). Whoops…!

Let’s look at a simple example of this type of processing:

declare 
  cursor c1 is select col_id, col_name from temp1; 
  cursor c2 is select col_id, col_name from temp2; 
begin 
  for i in c1 loop 
    for j in c2 loop 
      if i.col_name != j.col_name then 
        insert into temp1 values(j.col_id,j.col_name); 
      end if; 
    end loop; 
  end loop; 
  commit;
end;
/

The above code can be read as:

1. For each row in cursor c1, find all rows in cursor c2 where the col_name in c1 doesn’t equal the col_name in c2
2. Then insert the col_id and col_name from c2 into the temp1 table

Step 1 is basically describing a join between the two cursors, and we know that a join can be written in a single SQL statement:

select c1.col_id c1_col_id,
       c1.col_name c1_col_name,
       c2.col_id c2_col_id,
       c2.col_name c2_col_name
from   temp1 c1,
       temp2 c2
where  c1.col_name != c2.col_name;

Step 2 just inserts all the rows that we just selected, so the final statement becomes:

insert into temp1 (col_id, col_name)
select c2.col_id c2_col_id,
       c2.col_name c2_col_name
from   temp1 c1,
       temp2 c2
where  c1.col_name != c2.col_name;

If you need to, you can then slot the above statement into a PL/SQL procedure.

In reality, most of the code that I’ve seen involving nested cursor loops is not as simple as the above example, but with a bit of patience, it’s possible to untangle the nested cursors and, at the very least, turn it into a single cursor. If you can’t merge the cursor and the code that’s doing the work into a single SQL statement (e.g. sending a mail), at least you will have improved the performance by reducing the code to a single cursor loop!

Okay, so now I know a bit about refactoring existing procedure, what about if I have to write my own set-based procedure from scratch?

The very first step that I do is to try and build a SQL statement that matches the requirements. Once you have that (which you can build up in stages, depending on how complex the logic is), you can then use it to plug into an INSERT, UPDATE, MERGE or DELETE statement. You may have to modify the SELECT to fit in with the final statement, but once you have that base statement, you’re 90% of the way there.

The trick is to work out how to write that initial SELECT statement, which may involve aggregated queries, joins, analytic functions, etc. If you’re not very comfortable with SQL, then I suggest you invest some time in learning more about the various things you can do in SQL – it’s a very powerful language that will be faster at getting results from the database than anything you could write yourself in PL/SQL.

It’s a bit late arriving, but this year I couldn’t just do a copy and paste job that would put it into a similar table format as last years, so I’ve had to edit it by hand. *yawn*

Anyway, here it is:

There are some tough clashes in there! Those UKOUG schedulers sure know how to torture people! *shakes fist* *{;-)

The other day, I was investigating an issue in our process that takes a list of users and (effectively) merges them into the database. As part of that, I was trying to understand how the whole process worked.

This is the basic structure of the table:

Name          Null?    Type                        
------------- -------- ----------------------------
ID            NOT NULL NUMBER(12)                  
LOGIN_NAME             VARCHAR2(25)                
CREATED                DATE                        
LAST_UPDATED           DATE        

I could see data in the table:

   ID LOGIN_NAME CREATED             LAST_UPDATED       
----- ---------- ------------------- -------------------
 1451 user_1451  06/08/2008 00:10:54 10/10/2008 01:01:34
 1452 user_1452  07/08/2008 00:11:18 23/02/2009 15:23:53
 1453 user_1453  12/08/2008 00:09:51 04/07/2012 03:09:08
 1454 user_1454  14/08/2008 00:09:58 10/06/2009 05:28:24

And this is the code that actually inserts the user information (I’ve removed the updates from this, as they’re not relevant to my mystery!):

PROCEDURE SynchroniseUsers (pUserList user_info_tab)
IS
BEGIN
  INSERT INTO USERS (ID, LOGIN_NAME)
  SELECT ID_SEQ.Nextval, theUsers.column_value
  FROM   THE (SELECT CAST(l_userTable AS VARCHARTAB) FROM dual) theUsers
  WHERE  NOT EXISTS (SELECT /*+ index(U USERS_LOGIN) */ 1
                     FROM USERS U
                     WHERE U.login_name = theUsers.column_value);
END;
/

“Hmm,” I thought, “I can’t see where the CREATED or LAST_UPDATED columns are being updated.” So, I hunted through the rest of the code in the package – there was an UPDATE of the LAST_UPDATED column elsewhere, so I wasn’t concerned about that, but I could not find any mention of where the CREATED column was being updated.

“Aha! Must be in a trigger!” … alas, no.

I was absolutely stumped; by now, I’ve spent the best part of 30 mins trying to work out where this column gets updated (the code involved is not the best code on the planet, as I’m sure you can judge from the above procedure!). Eventually, after staring at it some more and scratching my head, it hit me: default column values *smacks forehead*

Sure enough, when I go into the table structure:

CREATE TABLE USERS
(
  ID NUMBER(12) NOT NULL,
  LOGIN_NAME VARCHAR2(25 BYTE),
  CREATED DATE DEFAULT SYSDATE, -- here is the culprit!
  LAST_UPDATED DATE
);

If whoever had designed this pile of… code had included the CREATED column in the INSERT statement in the first place, I wouldn’t have had to waste my time trying to work out what the heck was going on! That is why you should always be explicit in your coding habits – sure, have default values so that things don’t slip through the cracks, but make sure the table is well designed and that any code doing inserts, updates, etc has ALL the necessary columns included.

Don’t rely on the side effects of doing things automagically, it just leads to maintenance headaches!

When I first started working with Oracle, as a lowly system tester/support person, I had no idea there was such a thing as an Oracle community, beyond the developers and DBAs that I had contact with. In fact, it wasn’t until I started my current job (4 years ago) that I realised there was even a hint of a community! That’s probably a combination of how I became an Oracle developer and the structure of the company I worked for previously.

I consider myself a part of several Oracle communities:

• OTN Forums
• Work chat channels
• the informal London pub Oracle massive*
• the Oracle Blogging community
• Twitter

and each one adds to my knowledge in different ways. I count myself very, very lucky that I work at a company which encourages communities via the IM chat program that we use – there are lots of channels dedicated to various areas, not just Oracle-based (eg. Unix, java, c# ….).

I think these channels are not only a vital source of help and knowledge, they’re a great way of networking across the company. I now have a little network of like-minded DBAs and devs, spanning continents, that I can be cheeky and ask for help from (and vice versa, of course!), which has been very helpful when I’ve been stuck, etc!

Pooling knowledge and forging ties is at the heart of every community and by participating, you can’t help but continue to keep learning new things or new ways to apply things you already know.

I would definitely encourage new (and old!) Oracle developers/DBAs to participate in at least one Oracle community; it’ll be well worth their while to do so!

* OMG! I sat next to Jonathan Lewis the last time there was a pub outing. O. M. G!! (This is not me name dropping, unlike a certain Doug I know *{;-) – this is me being totally amazed at the company I found myself in, even though I’ve met JL several times before now! Someone pinch me….)

Yesterday, I came across an extremely odd problem with a view that I had created. The problem I had was that I was joining the view to another table, based on the primary key of the main table in the view, yet it was doing a FTS of that table and then doing the filtering.

This made the query take ~18 minutes, yet I was convinced that it ought to be able to push the filtering inside the view. Nothing I tried would get the filtering to take place in the view, not push_pred or no_merge or no_unnest hint; nothing.

It was actually a conversation with Doug Burns about the trace file I generated that spat out a clue – thanks Doug!

I’ve managed to recreate the issue, so here goes:

create table t1 as 
select level col1,
       dbms_random.string('A', 5) col2,
       mod(level, 20) col3
from   dual
connect by level <= 10000;

alter table t1 add constraint t1_pk primary key (col1);

create table t2 as
select level col1,
       ceil(level/2) col2,
       dbms_random.string('A', 5) col3
from   dual
connect by level <= 20000;

alter table t2 add constraint t2_pk primary key (col1);

alter table t2 add constraint t2_t1_fk foreign key (col2) references t1 (col1);

create table t3 as
select level col1,
       level * 2 col2
from   dual
connect by level <= 19;

alter table t3 add constraint t3_pk primary key (col1);

begin
  dbms_stats.gather_table_stats(user, 'T1');
  dbms_stats.gather_table_stats(user, 'T2');
  dbms_stats.gather_table_stats(user, 'T3');
end;

create view t1_t3_view
as
with t3a as (select col1,
                    decode(col2, 4, 90, 8, 45, col2) col2
             from   t3)
select t1.col1,
       t1.col2,
       t1.col3,
       t3a.col2 t3_col2
from   t1,
       t3a
where  t1.col3 = t3a.col1 (+);

The following query does push the filtering into the view (see the use of the T1_PK index):

select *
from   t1_t3_view
where  col1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  ca9pw03r3tua1, child number 0
-------------------------------------
select * from   t1_t3_view where  col1 in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
Plan hash value: 1448310819             
---------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |       |       |    10 (100)|          |
|   1 |  VIEW                          | T1_T3_VIEW |    10 | 20410 |    10   (0)| 00:00:01 |
|*  2 |   HASH JOIN OUTER              |            |    10 |   180 |    10   (0)| 00:00:01 |
|   3 |    INLIST ITERATOR             |            |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| T1         |    10 |   120 |     7   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | T1_PK      |    10 |       |     6   (0)| 00:00:01 |
|   6 |    TABLE ACCESS FULL           | T3         |    19 |   114 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
   2 - access("T1"."COL3"="COL1")
   5 - access(("T1"."COL1"=1 OR "T1"."COL1"=2 OR "T1"."COL1"=3 OR "T1"."COL1"=4 OR
              "T1"."COL1"=5 OR "T1"."COL1"=6 OR "T1"."COL1"=7 OR "T1"."COL1"=8 OR "T1"."COL1"=9 OR
              "T1"."COL1"=10)

whereas the following query does not (note the FTS on T1):

select *
from   t1_t3_view
where  col1 in (select col2 from t2
                where col1 <= 10);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
SQL_ID  95rxxkfy26rg3, child number 0
-------------------------------------
select * from   t1_t3_view where  col1 in (select col2 from t2
         where col1 <= 10)
Plan hash value: 637444478
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |       |       |    15 (100)|          |
|*  1 |  HASH JOIN RIGHT SEMI        |            |    10 | 20500 |    15   (7)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| T2         |    10 |    90 |     3   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | T2_PK      |    10 |       |     2   (0)| 00:00:01 |
|   4 |   VIEW                       | T1_T3_VIEW | 10000 |    19M|    12   (9)| 00:00:01 |
|*  5 |    HASH JOIN RIGHT OUTER     |            | 10000 |   175K|    12   (9)| 00:00:01 |
|   6 |     TABLE ACCESS FULL        | T3         |    19 |   114 |     3   (0)| 00:00:01 |
|   7 |     TABLE ACCESS FULL        | T1         | 10000 |   117K|     8   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
   1 - access("COL1"="COL2")
   3 - access("COL1"<=10)
   5 - access("T1"."COL3"="COL1")

After much scratching of my head (and not a small amount of swearing!) I eventually worked out how to do a 10053 trace on the query. The following is what Doug picked up on (and to be fair, so had I, only I hadn't got round to doing anything about it aside from making a mental note of it):

**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in SEL$1 (#0).
PM:   Checking validity of predicate move-around in SEL$1 (#0).
CBQT: copy not possible because linked to with clause
 in SEL$3 (#0)
CBQT: copy not possible because view
 in SEL$1 (#0)
CBQT bypassed for SEL$1 (#0): Cannot copy query block.
CBQT: Validity checks failed for 95rxxkfy26rg3.

If I change the view so that the subquery is an in-line view, bingo!:

create view t1_t3_view2
as
select t1.col1,
       t1.col2,
       t1.col3,
       t3a.col2 t3_col2
from   t1,
       (select col1,
               decode(col2, 4, 90, 8, 45, col2) col2
        from   t3) t3a
where  t1.col3 = t3a.col1 (+);

PLAN_TABLE_OUTPUT                             
----------------------------------------------------------------------------------------------
SQL_ID  9jj0atfw7050d, child number 0
-------------------------------------
select * from   t1_t3_view2 where  col1 in (select col2 from t2
where col1 <= 10)
Plan hash value: 2918082440
----------------------------------------------------------------------------------------
| Id  | Operation                      | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |       |       |       |    13 (100)|          |
|*  1 |  HASH JOIN OUTER               |       |    10 |   270 |    13  (16)| 00:00:01 |
|   2 |   NESTED LOOPS                 |       |    10 |   210 |     9  (12)| 00:00:01 |
|   3 |    SORT UNIQUE                 |       |    10 |    90 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T2    |    10 |    90 |     3   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | T2_PK |    10 |       |     2   (0)| 00:00:01 |
|   6 |    TABLE ACCESS BY INDEX ROWID | T1    |     1 |    12 |     1   (0)| 00:00:01 |
|*  7 |     INDEX UNIQUE SCAN          | T1_PK |     1 |       |     0   (0)|          |
|   8 |   TABLE ACCESS FULL            | T3    |    19 |   114 |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T1"."COL3"="COL1")
   5 - access("COL1"<=10)
   7 - access("T1"."COL1"="COL2")

So one place where a WITH clause may cause problems and, much as I love it, I will now steer clear of WITH clauses in views until I know the issue has been fixed! (Not that I work with views that often; I'm only doing so here due to the fact we're on 10g, there's a join I need to add into a dynamic sql and the 32767 character limit EXECUTE IMMEDIATE has. I can't wait for 11g where EXECUTE IMMEDIATE works with CLOBs!)

Judging from the amount of forum posts around the subject of dates, you would dates in Oracle are difficult, confusing and just plain awkward to use.

However, far from being difficult, I think dates in Oracle are really, really easy.

The DATE datatype in Oracle holds date and time (to the nearest second) information and stores it in its own internal format. Everyone knows that date+time consists of year, month, day, hours, minutes and seconds, so Oracle gives you a method of allowing you to specify which bits are the year, which are the month, etc. What could be simpler than that?

It’s the TO_DATE() function that allows you to pass in a string and have Oracle read it as a date. Similarly, it’s the TO_CHAR() function that allows you to take an Oracle DATE value and convert it back into a string that you and I can understand.

Let’s look at an example:

to_date('01/01/2011', 'dd/mm/yyyy')
to_char(sysdate, 'dd Month yyyy hh24:mi')

Granted, it’s a wee bit of typing, but it’s not difficult. The list of available format masks can be found here. It’s worth nothing that you can also use these format masks with TRUNC and ROUND, for example if you need to find the quarter start date of a given date etc.

People abuse dates in many ways in Oracle, such as only using 2-digit years (why, I’m not sure. It’s as if Y2K never happened!) or worse, when they want to compare dates, they first convert them to strings.

This is a bad idea in many ways – for one, if you don’t organise your date-as-a-string correctly, comparisons are meaningless – eg. ‘2 Feb 1999’ is greater than ‘1 Jan 2011’, because ‘1’ < '2' in the string comparison. For another, when you convert the DATE into a string (or number), you remove vital information from the optimizer. This can lead to incorrect cardinality estimates which could throw your execution path off. Why? Well, how many days are there between 1st January 2011 and 31st December 2010? Of course, the answer is 1. But if you were converting those to numbers, the question becomes "What is the difference between 20110101 and 20101231" to which the answer is, of course, 8870. 1 vs 8870 - that's a fair amount of difference! By converting dates to strings or numbers, you leave the optimizer no option than to do the equivalent of sticking its finger in the air to guess how many rows it's going to retrieve! Don't be afraid of Oracle's DATE format, it's simple to use and can help you do some very powerful things without much work from you - that's got to be a bonus! (Grouping results into quarters? No problem! Pivoting by month? Not a problem either! etc etc)