Oracle direct load seems to make strange results

Question

Hi,

i have got very strange results in my Oracle 10g database since the day i have switched my loaders from direct=false to direct=true with compressed tables ..

For example, when i make a query like that after a loader :

select * from mytable where month is null

i get no answer ..

but when i query a specific line select * from mytable where id = 123 the line which is appearing has a "month" column (vharchar2(10)) which is null !! I had never saw that..

And it only happens with a few lines in my 200000+ lines table ..

Do you see what can happen ?

I have switched back to direct=false and it runs ok .. no more strange behaviour .. what can i do ? test ?

Thanks

Answer 1

Hi Fredv,

Perhaps your column is not null but contains non-printable characters. Could you do a dump on the column, ie:

SQL> select rownum, dump(a) from test;

    ROWNUM DUMP(A)
---------- ---------------------------------
         1 NULL
         2 Typ=1 Len=1: 0

Here the first row has a NULL value while the second row has a non-null character (CHR(0)).

You could run the following query on your DB to see if your column really is NULL:

select dump(month) from mytable where id = 123

Answer 2

If you are viewing the result in a tool (such as toad or sql developer) , it is possible the line has spaces/control breaks which are not being displayed correctly. Try running the following query and show the results.

select month, nvl(month,'Month is NUll'), length(month) 
     from mytable 
     where id = 123

Answer 3

Any constraints on the table, specifically a NOT NULL constraint on that column. The state of the constraint (deferred, disabled, novalidate) can create oddities.

If the constraint is set to NOVALIDATE but trusted, then the optimizer may see "Aha, that column cannot be null so I won't even bother looking".