6.1  :  FAQ Millennium

• Why have the ID columns such complex values?
The raw particle data for the Millennium Simulation was saved at 64 snapshots. Each snapshot was distributed over 512 approximately equal-sized files. At each snapshot, a friends-of-friends (FOF) groupfinder with linking length b=0.2 was run. During post-processing, each FOF group was searched for bound substructure using the SUBFIND algorithm. For each snapshot, there are 512 files that contain the results of SUBFIND. Merger trees were built based on these subhalos. The merger trees were split over 512 files, each of which contains approximately 14,000 trees. In the design of the various tables, the structure of these files is retained in the subhalo (and FOF halo) IDs as follows:
• MPAHaloTrees..MHALO::haloId = 10¹² * treeFile + 10⁶ * (rank of tree in file) + (depth-first ordering of subhalo in tree)
• MPAGalaxies..Delucia2006a/Bertone2007a::galaxyId = 10¹² * (halotreeFile) + 10⁶ * (rank of tree in file) + (depth-first ordering of galaxy in tree)
• MField..FOF::fofID = 10¹² * snapnum + 10⁸ * fileNr + (rank in file)
• MField..FOFSubHalo::subhaloId = 10¹² * snapnum + 10⁸ * fileNr + (rank in file)
• DHaloTrees..DHalo::DHaloId = 10¹² * treeFile + 10⁸ * fileNr + (rank in file)
• DGalaxies..Bower2006a::galaxyId = 10¹⁴ * treeFile + 10⁸ * (rank of tree in file) + (rank in file)
Note: the structure for the Millennium-II Simulation is different in the details!!!
• How do I work around timeouts?
A query times out most often because the database is asked to handle datasets that are so large that the required disk I/O by itself exceeds the maximum allowed query time. Very often there is a straightforward manner to work around this, at the costs of having to issue multiple queries. Here as example how to deal with queries that will require a scan of the complete galaxies table.
Suppose a user would like to run the following query (arbitrary):

   select * 
     from mpagalaxies..delucia2006a
    where blackHoleMass > coldgas
      and mag_b - mag_v between .2 and .8
   

For these constraints the database decides that a full scan of the delucia2006a table is required, which will time out. An alternative is to run the following set of queries

   select * 
     from mpagalaxies..delucia2006a
    where galaxyid between :START*1e12 and (:START+:BIN)*1e12-1
      and blackHoleMass > coldgas
      and mag_b - mag_v between .2 and .8
   

where one should substitute integer values for the variables :START and :BIN. For example with :BIN=1 and :START ranging from 0-511 one can sample the complete table in 512 separate queries, something that can be easily coded in a script, using wget for example for executing the query.
This works because the table is sorted on galaxyId and therefore the database can quickly ("in logarithmic time") find the range of galaxies given by the first constraint. As a consequence of the structure in the IDs described above this set is about as large as the the millimil..delucia2006a table. In general the :BIN size can easily be set to a higher value, 10 or possibly larger.
NB the -1 is required to ensure that consecutive bins are non overlapping: the BETWEEN clause is inclusive.

   select p.*
     from millimil..fof fof 
     ,    mmsnapshots..millimilsnapshotids id 
     ,    mmsnapshots..millimilsnapshots p 
    where fof.np between 10000 and 10010 
      and fof.snapnum=63 
      and id.fofid = fof.fofid 
      and p.id = id.particleid 
      and p.snapnum=fof.snapnum
   

select o.name as table_name
,      i.name as index_name
,      c.name as column_name
,      ic.key_ordinal as rank
  from millenniumii.sys.tables o
  ,    millenniumii.sys.indexes i
  ,    millenniumii.sys.index_columns ic
  ,    millenniumii.sys.columns c
 where i.object_id = o.object_id
   and ic.index_id = i.index_id
   and ic.column_id = c.column_id
   and ic.object_id = c.object_id
   and c.object_id = o.object_id
 order by table_name,index_name,rank
   

   select * from mpagalaxies..delucia2006a where redhsift=0
   

   select * from mpagalaxies..delucia2006a where snapnum=63