Force Oracle to use an index for IS [NOT] NULL predicates.

In my previous post, I mentioned how Oracle can't keep track of NULLS in an index, and consequently, cannot use an index to satisfy the IS NULL predicates (WHERE clauses).

There is a way of making Oracle do that though....

If you are trying to make Oracle use an index-on-nullable-column, here's a nifty trick from a reader-comment at http://www.dba-oracle.com/oracle_tips_null_idx.htm :

SQL> select count(1) from t where n is null;

COUNT(1)
----------
334

Execution Plan
---------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'T' (Cost=3 Card=334 Bytes=1002)

SQL> create index tind on t(n, 1); ----> here 1 is just any arbitary value.

Index created.

SQL> exec dbms_stats.gather_table_stats(user,'t',cascade=>true);

PL/SQL procedure successfully completed.

SQL> select count(1) from t where n is null;

COUNT(1)
----------
334

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=4)
1 0 SORT (AGGREGATE)
2 1 INDEX (RANGE SCAN) OF 'TIND' (NON-UNIQUE) (Cost=2 Card=3
34 Bytes=1336)


Sandeep Redkar

Postgres can... Oracle can't (index NULLs)

Well, it was a revelation.... For almost ages, I have been made to believe that NULLs cannot be indexed; primarily because Oracle chose not to, and I had used Oracle quite a lot in the past (and worked for them too at one time).

A couple of days ago I was asked by a developer (Arie) this very question, but in context of Postgres:

Subject: Are nulls indexed?
Body:
If I have an index on column A (null-able) will null values be indexed? Basically, if not, does it mean that such a query "select * from T where A is null" will have to do a full table scan?

I blindly said no... that was until I had to test something out based on his next question. And I was in fact surprised! Postgres can use a non-unique index for 'IS NULL' and 'IS NOT NULL' predicates!

Here's a small session to demonstrate that Postgres stores index-records for NULLs and can use such indexes for IS [NOT] NULL predicates:

postgres=# create table t ( a int, b char(32) );
CREATE TABLE
postgres=# create index t_i on t( a );
CREATE INDEX
postgres=# insert into t select s, s+1 from generate_series( 1, 1000000 ) as s;
analyze t;
INSERT 0 1000000
postgres=# analyze t;
ANALYZE
postgres=# select relpages, relname from pg_class where relname like 't%';
relpages | relname
----------+--------------------------
<snip>
8334 | t
2745 | t_i
(7 rows)

postgres=# explain select * from t where a is null;
QUERY PLAN
--------------------------------------------------------------
Index Scan using t_i on t (cost=0.00..8.38 rows=1 width=37)
Index Cond: (a IS NULL)
(2 rows)

postgres=# truncate t;
TRUNCATE TABLE
postgres=# insert into t select case s%5 when 0 then null else s end, s+1 from generate_series( 1, 1000000 ) as s;
INSERT 0 1000000
postgres=# reindex index t_i;
REINDEX
postgres=# analyze t;
ANALYZE
postgres=# select relpages, relname from pg_class where relname like 't%'; relpages | relname
----------+--------------------------
<snip>
8334 | t
2745 | t_i
(7 rows)

postgres=# explain select * from t where a is null;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using t_i on t (cost=0.00..7332.37 rows=198972 width=37)
Index Cond: (a IS NULL)
(2 rows)

postgres=#

I hope you get the picture.

So, another good reason to choose Postgres (or for that matter EnterpriseDB) over Oracle... :)

PS: This works only with the B-Tree indexes though.

a NOOP WAL archiving command for PostgreSQL 8.3

Starting Postgres version 8.3, there is a little annoying feature (for the DBAs) in WAL archiving infrastructure of Postgres. In earlier versions you could change the archive_command on the fly to do different things at different times, or do nothing at all if you choose not to.

But 8.3 onwards, if you haven't switched on archive_mode to ON while starting the server, then you just can't change your archiving strategy while the server is running! So, I would recommend running a production database with this setting always turned ON. But wait, what do you set the archive_command to, if you do not want to archive?! The thing is, if archive_mode is ON and theres nothing in the archive_command you are likely to see the following WARNING in your log files:

WARNING: archive_mode enabled, yet archive_command is not set

And to worsen the situation, instead of recycling your transaction logs, it will keep them all because it is supposed to do archiving, but it is not able to since there's no command to execute... what happens? Your transaction log directory gets full!!!

And, if archive_command is set to something that Postgres can't execute, then you have a BIG problem.

So, here is a very simple solution: set the archive_command to something that does nothing and always returns 0 (zero). BUT (yeah there's always this big BUTT when handling databases :) ) you will see messages in your log files:

LOG: archived transaction log file "00000001000000000000003A"

And the problem with this that you actually didn't archive anything, and yet you see success messages; this can be confusing when you are analyzing the records later! So how do you keep a record of what actually happened? Did you actually archive a particular file!?

Here's a command:

archive_command = 'date >> dummy_archive.log && echo did dummy archiving for %f >> dummy_archive.log'

This will keep a separate log (named dummy_archive.log in your data directory) for the transaction logs that you didn't archive, and your database will run smoothly, without any warnings in the logs. And when you actually wish to start archiving, just change this command on the fly to start actual archiving. No downtimes.

So here's the final verdict:

archive_mode = on

archive_command = 'date >> dummy_archive.log && echo did dummy archiving for %f >> dummy_archive.log'

PS: I wish I could make this command's output go to the log_destination of postgres, in a generic manner! If you know of something that'll help achieve this, please comment here or write to me.

UPDATE: (Nixen (Unix like) systems only) The following archive_command lets you push these 'dummy archival messages' into Postres's server log:
archive_command = 'date >&2 && echo did dummy archiving for %f >&2 '

Postgres development notes

Tom Lane uses 'emacs + glimpse' for development (source)

GMail provides IMAP access!!

Just saw a new feature from GMail... They now provide IMAP access!!! Just go to the 'Settings' section, and look for 'Forwarding and POP/IMAP' section. If you still see just POP in the section header, you will have to wait a little more to get this feature enabled for your account.

Here are the instructions for configuring Thinderbird to use IMAP...
http://mail.google.com/support/bin/answer.py?answer=77662

Just follow the instructions above and see all your labels also being shown in your account (something that was not possible by using POP!)

This has been a long awaited feature....

Now this also makes one more much awaited features possible: Finding the bulkiest/biggest emails you have in your account, and the ability to delete them... No more need to write the cryptic search terms in GMail (has:attachment) and trying to figure out which mail to delete!!!

All you have to do now is register your GMail account using the IMAP inteface in a mail client of your choice and sort the messages by size. Go to the end of the sorted list and see if you want to delete the biggest messages...

This was not possible earlier with GMAIL+POP+Thunderbird because of a bug in Thunderbird. Thunderbird can now properly sort messages based on the apparent size of the mail (shown by the mail header) rather than having to download the message to know it's actual size.

(and it now provides 4GB if mailbox size!)

Enjoy GMail...

फर्स्ट टेस्ट ऑफ़ हिंदी ट्रांस्लिटरेशन through google गूगल

हिंदी में टाईप करना कितना आसान है!!! वाह .. मज़ा आ गया... आ जा रे ओ दिलबर जानिया... दिलबर आ रे आ

BSNL's DNS servers


I had a strange problem a couple of days ago; and it had hit me right at the time when I would have least wanted it to!!! Just till 20 minutes before the problem, I had prepared for a corporate presentation over the internet (through webex); Had installed the required component in Firefox, had a colleague walk me through how to host a presentation, etc.

But when the time came to actually log in and start the presentation, I could not connect to the site!!! I could have understood it if it had been my network failing me... but it was working fine!! I was able to browse a few sites, Google, GMail, Google docs, licofindia.com, and a few others that I cared to check.

And the strange thing was that there were a few sites I wasn't able to browse: webex.com, snapfish.com, kotaksecurities.com, and the list grew in the next 24 hours that I waited for the problem to solve itself.

The worst part was that I was totally cut-off from my corporate network!!! no mails.. even the web-interface of our mailing system vanished from the face of internet-earth! enterprisedb.com, mail01.enterprisedb.com .. everything failed!!

My colleague devised a workaround and helped me to somehow conduct the presentation, and we were able to save face.

I waited for the rest of the day for the problem to go away, and in the meantime I noticed more and more sites that were not accessible.

Next day I researched a lot about the problem. I used these keywords to search (the ping and tracert messages)

Ping request could not find host . Please check the name and try again.

Unable to resolve target system name
.

I tried quite a lot of results but nothing helped. Finally I started doubting the least suspected culprit: the DNS servers.

To cut the long story short, I changed the primary DNS server to 61.1.96.71 and moved my old primary DNS server to the 'Alternate DNS Server' box. And presto, everything was back to normal.




I guess it was some local problem; or maybe stale record on the DNS. I donno, I am no expert on this.

This is my setup:

OS: Windows XP SP2
ISP: BSNL Hyderabad
Router: Type II (from UTStarComm).


I got this DNS address from
http://www.appaji.net/get/dataone/bdah.html
; hope it helps someone else in desperate times.


BSNL DNS Servers:

61.1.96.69 (dns.server.ip.pri)
61.1.96.71 (dns.server.ip.sec)