900,000 rows is a very small table.  I doubt you have zone maps on it at all unless you are running on a skimmer system perhaps.  The minimum table size to get zone maps is around 15MB * dataslice count.  So even on a TF3 that is 24*15 = 360MB.  With that minimal size you won't see much of a difference between using zone maps or not.  If you want to test out the difference you need to have a table that is in the 10s of millions of records at least and possiblity quite a bit larger if you are on system like a TF12 or one of the really big systems like a TF96 or some such.

Also a char(1) isn't really a valid test since that is a fixed width character and it is only 1 byte anyway.  The times when you'll run into issues are with something like a varchar(40) vs. an integer column (or when you can make use of zone maps).

Personally I prefer to use char(1) for values like true/false, male/female, status columns, etc... I find them much easier to read.  You won't get zone maps on them though and that can be a problem in some situations.  With Netezza 6.0 you do get zone maps on character fields if you use them as one of the columns your table organization clause, but if you use multiple columns make sure their cardinality is fairly low otherwise you won't see much if any benefit from them.

It has to be 15MB per dataslice (at least).  Use "select count(*) from _v_dslice" to determine the number of dataslices on your system.

Also you wouldn't see any performance gain unless your source table is physically ordered on the column you are joining on (for example, create table foo as select * from bar order by x) and of course you would have to have a filter of some sort.  I assume that the table you are joining to has only some of the values that are in the target table?

The easiest way to see if you have zone maps for your table is to download Aginity Workbench:

http://www.aginity.com/Workbench/tabid/237/Default.aspx

In the list of tables when you select a table you'll have a "Zone Maps" node below the table.  If you double click on "Zone Maps" it will run a query to determine the zone maps on the table.  If it doesn't find any then you don't have any good zone maps (or your table is too small).

Just a guess, but I would suspect Netezza creates zone maps regardless of the size of the table.  It doesn't make any sense that it would wait until a table reached a certain threshold because it would significantly complicate matters for the database for no real gain.

In your particular case, there is probably only one data block on each SPU, which would certainly make the zone maps 100% effective in localizing the correct data blocks.

By default Netezza creates zone maps for most numeric and date/time columns (the manual has a definitive list).  It does this regardless of how the table is created.

You say that you are joining to this column but not actually filtering on it (e.g. where value-1) - joining on the column is different from using it as a zone map - sometimes they complement each other and sometimes not. But just because you are joining does not mean the zone map is being leveraged for the scan. In fact, joining alone without filtering on the given column, would guarantee a full table scan if all the values are in both tables. The only benefit of doing it this way (joining) is if you can place a subset of the data in one table and use it as filter to another. If both tables have the same data and you simply join, you will initiate a table scan.

However, if both tables contain the same data and you join but also use where column = 1 for one or both of them, this would potentially leverage the zone maps where the filter was applied.

Bingo - A zone map assists in filtering data. Just as a distribution key assists in co-located joins. But if you don't use the distribution key in the join, the co-location will never happen, just like if you don't actually mention the zone map column in the where-clause, the zone map is not leveraged for the query. Zone maps support filtration. If you aren't filtering, what do you need a zone map for?

Zone maps are only created for tables, not in-memory cache.  It is a technique to speed up disk access.