Zoek hiërarchische tekst in de Oracle-database

Tabel = BLOK (heeft samengestelde unieke index voor beide kolommen)

IP_ADDRESS   CIDR_SIZE
=========    ==========
10.10         16
15.0          16
67.7          16
18.0           8

Vereisten:

  • Subblok is niet toegestaan. Voor b.v. 67.7.1 en 24 is niet toegestaan ​​aangezien dit een kind van 67.7 is. Met andere woorden, als er een IP-adres in de database is dat overeenkomt met het begin van een nieuw IP-adres, dan zou het moeten falen. Kan ik dit doen met een Oracle SQL-query?

Ik dacht erover om het te doen door ...

  1. Select all records into the memory.
  2. Convert each IP into its binary bits

    10.10 = 00001010.00001010
    15.0 = 00001111.00000000
    67.7 = 01000011.00000111
    18.0 = 00010010.00000000

  3. Convert new IP into binary bit. 67.7.1 = 01000011.00000111.00000001

  4. Check to see if new IP binary bits start with existing IP binary bits.
  5. If true, then the new record exists in the database. For example, new binary bit 01000011.00000111.00000001 does start with existing ip (67.7) binary bits 01000011.00000111. Rest of records don't match.

Ik ben op zoek om te zien of er een Oracle-query is die dit voor mij kan doen, dat wil zeggen de overeenkomende IP-adressen uit de database retourneren. Ik heb de tekst-API van Oracle bekeken, maar heb nog niets gevonden.

0

2 antwoord

Ja, je kunt het in SQL doen door IP's naar getallen te converteren en dan te zorgen dat dit geen record is met een kleiner cidr -formaat dat dezelfde ipnum geeft bij gebruik van de cidr -grootte.

WITH ipv AS
(   SELECT  IP.*
        ,   NVL(REGEXP_SUBSTR( ip, '\d+', 1, 1 ),0) * 256 * 256 * 256  -- octet1
        +   NVL(REGEXP_SUBSTR( ip, '\d+', 1, 2 ),0) * 256 * 256        -- octet2
        +   NVL(REGEXP_SUBSTR( ip, '\d+', 1, 3 ),0) * 256              -- octet3
        +   NVL(REGEXP_SUBSTR( ip, '\d+', 1, 4 ),0)  AS ipnum          -- octet4
        ,   32-bits                 AS ignorebits
    FROM  ips IP
)
SELECT  IP1.ip, IP1.bits
FROM    ipv IP1
WHERE   NOT EXISTS
    (   SELECT  1
        FROM    ipv IP2
        WHERE   IP2.bits < IP1.bits
        AND     TRUNC( IP2.ipnum/POWER( 2, IP2.ignorebits ) )
              = TRUNC( IP1.ipnum/POWER( 2, IP2.ignorebits ) )
    )

Opmerking: mijn voorbeeld gebruikt de tabel gelijk aan die van u:

SQL> desc ips
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 IP                                        NOT NULL VARCHAR2(16)
 BITS                                      NOT NULL NUMBER
0
toegevoegd

Is there a reason you can't use the INSTR function? http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm#i77598

I'd do something like a NOT EXISTS clause that checks for INSTR(b_outer.IP_ADDRESS,b_inner.IP_ADDRESS) <> 1

* edit: denk erover na, je zou waarschijnlijk moeten controleren om te zien of het resultaat 1 is (wat betekent dat het potentiële IP-adres overeenkomt met het begin van het eerste karakter van een bestaand IP-adres) in plaats van een algemene substring-zoekopdracht zoals ik hem oorspronkelijk had .

0
toegevoegd
Werkt niet als het aantal bits elkaar niet overlappen met een factor 8. b.v. 10.224/11 is een ouder van 10.240/12 maar niet 10.176/12
toegevoegd de auteur Sodved, de bron
Bedankt. Ik vind het niet erg om nog een kolom aan de tabel toe te voegen die ip_adres als binair bit-formaat houdt, als dat me helpt om dit te bereiken met alleen een zoekopdracht, misschien iets dat tegengesteld is aan de LIKE-clausule.
toegevoegd de auteur Sannu, de bron