Oracle ma'lumotlar bazasida ierarxik matnni qidirish

Stol = BLOK (kompozitsion noyob indeks, ham ustunlar mavjud)

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

Talablar:

  • Quyi bloklarga ruxsat berilmaydi. Misol uchun, 67.7.1 va 24 raqamlariga yo'l qo'yilmaydi, chunki bu 67.7 yoshli bola. Boshqacha aytganda, agar yangi IP-ning boshlang'ich qismiga mos keluvchi ma'lumotlar bazasida biron-bir IP-manzil bo'lsa, unda bu muvaffaqiyatsiz bo'lishi kerak.
  • Oracle SQL so'rovini ishlatishim mumkinmi?

Buni amalga oshirishni o'ylardim ...

  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.

Men uchun buni amalga oshiradigan Oracle so'rovi bor yoki yo'qligini ko'rish uchun qidiraman, ya'ni ma'lumotlar bazasidan taaluqli IP manzillarini qaytarib olasiz. Men Oracle ning matnli API-ni tekshirdim, ammo hali hech narsa topmadim.

0

2 javoblar

Ha, buni SQLda IP raqamlarini raqamlarga aylantirish orqali amalga oshirishingiz mumkin va keyin cidr hajmini ishlatganda xuddi shu ipnumni taqdim qiluvchi cidr kichikroq bir yozuv emasligiga ishonch hosil qiling.

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 ) )
    )

Eslatma: Mening misolingiz sizga mos keladigan jadvaldan foydalanadi:

SQL> desc ips
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 IP                                        NOT NULL VARCHAR2(16)
 BITS                                      NOT NULL NUMBER
0
qo'shib qo'ydi

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

* Tartibga solish: bu haqda o'ylash, ehtimol, natijaning 1 (mavjud IP-manzilning birinchi belgi bo'yicha boshlanadigan mumkin IP-manzillari) degan ma'noni anglatishini tekshirishingiz kerak. .

0
qo'shib qo'ydi
Bit bit soni 8 foizga teng bo'lmasa, masalan, ishlaydi. 10.224/11 , 10.240/12 kodi emas, balki 10.176/12
qo'shib qo'ydi muallif Sodved, manba
Rahmat. Ip_addressni ikkilik bit formati sifatida saqlaydigan jadvalga boshqa ustun qo'shib qo'ymayman, agar bu faqatgina bitta so'rov yordamida, bunga o'xshash narsaga qarama-qarshi bo'lgan narsalarni ishlatishimga yordam beradigan bo'lsa.
qo'shib qo'ydi muallif Sannu, manba