PL/SQL & CIDR?

David Andersen dga at cs.cmu.edu
Sat Mar 13 02:46:53 UTC 2010


On Mar 12, 2010, at 9:36 PM, Matthew Kaufman wrote:

> Seth Mattinen wrote:
>> On 3/12/2010 09:13, J.D. Falk wrote:
>>  
>>> Does anyone know of a library, sample code, etc. to help Oracle PL/SQL do CIDR math?
>>> 
>>>    
>> 
>> 
>> Not exactly sample code, but: I do that with MySQL by storing the IP as
>> its integer value and using simple comparisons to see if that stored
>> value is within the range of values that a given CIDR mask represents.
>> Works great for IPv4 and IPv6 addresses.
>> 
>> ~Seth
>> 
>>  
> I do it in MySQL by storing the IP as an integer and the mask as an integer and using bitwise operators in the SELECT.
> 
> Just something to think about...

To expand upon this, we do this in pure SQL as Matthew suggested by generating the sql automatically.  e.g., to find all routes in a BGP table that equal or contain a particular prefix:

SELECT * from table 
WHERE (prefix = x AND mask=32)
   OR (prefix = x & 0xfffffffe AND mask=31)
   OR ...

(we store BGP entries as prefix, mask).

You can write a user defined function to do this for you in many languages.

We chose that expansion because it worked well with the indexes defined on prefix/mask.  You can also express it as CIDR math as bitwise operators, though we've found that doing so tends to destroy any indexing you've created:

SELECT mask, prefix from t1
  WHERE (search_prefix & ((!0) << (32 - t1.mask)) = t1.prefix)
  ...

I can probably cough up a few more examples from our codebase if you want them.

  -Dave





More information about the NANOG mailing list