User login

Aggregator

Please use the contact form to include your blog here, suggest a feed, or give feedback.

If your blog is aggregated on OraFAQ, you may want to display this image on your blog:

Search

[ Advanced ] [ Help ]

Feed aggregator

The Leadership Development Carnival – Back to Football Edition is Up!

TalentedApps - 7 hours 4 min ago

Dan McCarthy, the founder of the Leadership Development Carnival over at Great Leadership, has posted the September Leadership Development Carnival – Back to Football Edition. Dan has assembled over 35 contributed posts on a huge range of Leadership topics and has also written a helpful, brief intro to each, so check it out.

Some notable posts include:

Erin Schreyer and Mike Henry’s What Really Makes You a Leader?
Lois Melbourne’s Be an Accountable Leader and Get to Lunch First!
Sharlyn Lauby’s The Business Case for Managing Ourselves
Kevin W. Grossman’s Influential Leadership Can Trump Gender Bias
David Burkus’ My Buffalo Wild Wings Rant

This Leadership Development Carnival offers a terrific opportunity for you to get a great sample of a variety of perspectives, thinking, and just plain good writing. Who knows? Maybe you’ll discover a great blog you never heard of before.

Not NULL

Jonathan Lewis - 7 hours 42 min ago

Here’s a little detail that I’ve known for years – but keep forgetting until I embarrass myself by rediscovering it (usually in front of a client). I’ll start with a demonstration of a useful little feature of mandatory columns:


drop table t1 purge;

create table t1
as
select
	*
from
	all_objects
where
	rownum <= 10000
;

execute dbms_stats.gather_table_stats(user,'t1')

create index t1_i1 on t1(object_name);

set autotrace traceonly explain

select count(*) from t1;

/*

--------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Cost (%CPU)| Time
--------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    13   (0)| 00:00:0
|   1 |  SORT AGGREGATE       |       |     1 |            |
|   2 |   INDEX FAST FULL SCAN| T1_I1 | 10000 |    13   (0)| 00:00:0
--------------------------------------------------------------------

*/

Oracle can use the index on column object_name to count the number of rows in the table because the column has been declared NOT NULL, so every row in the table also has to appear in the index. Let’s just demonstrate that by changing the column definition:


alter table t1 modify object_name null;
select count(*) from t1;

/*

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    40   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 10000 |    40   (0)| 00:00:01 |
-------------------------------------------------------------------

*/

Now let’s make the column mandatory again – by adding a constraint:


alter table t1 add constraint t1_ck_on_nn check(object_name is not null);
select count(*) from t1;

What do you think the execution path is going to be ?

Oracle uses a full table scan – even if you supply the optimizer with an index hint.

The special NOT NULL optimisation only applies if the column is declared not null – an ordinary constraint isn’t just good enough.

You may remember a short note I wrote a few days ago about adding a not null constraint to a table with 200 million rows without causing the system to stop for a few minutes. I did it with the intent of giving the optimizer a few extra options for exection plans – but now I need to go back and see whether or not the constraint has any effect at all on any of the plans we generate.