SQLite WITHOUT ROWID Tablolar

Söz dizimi ve ilk örnek

Tabloyu her zamanki gibi tanımlar, sona WITHOUT ROWID eklersiniz. Anahtar kelimelerin büyük küçük harf yazımı önemsizdir. Rowid kaldırıldığı için rowid, oid ve _rowid_ adlarıyla bir değere ulaşılamaz. Belgelerde küçük bir not daha var: diğer bağlamlarda üç ad da çalışsa da tablo seçeneği olarak yalnızca "rowid" kelimesi geçerlidir.

CREATE TABLE kelime(
  soz  TEXT PRIMARY KEY,
  adet INTEGER
) WITHOUT ROWID;

INSERT INTO kelime VALUES ('elma', 3), ('armut', 5), ('kiraz', 1);
SELECT soz, adet FROM kelime;
-- çıktı:
-- armut|5
-- elma|3
-- kiraz|1

SELECT rowid FROM kelime;
-- hata: no such column: rowid

Sonuçların birincil anahtara göre alfabetik sırada gelmesi bir garanti değil, depolama düzeninin doğal bir yan etkisidir. Sıra sizin için önemliyse her zaman ORDER BY yazın; sıralama kuralları için SELECT, WHERE, ORDER BY yazısına bakabilirsiniz.

Rowid tablosu içeride nasıl çalışır

Farkı anlamak için önce sıradan bir tablonun nasıl saklandığına bakalım. Belgelerdeki kelime sayacı örneğinde soz TEXT PRIMARY KEY içeren sıradan bir tablo aslında iki ayrı B-ağacından oluşur. Ana tabloda anahtar gizli rowid'dir; soz ve adet veri olarak durur. Birincil anahtar için ayrıca otomatik bir benzersiz indeks oluşturulur ve bu indeks soz ile rowid'i anahtar olarak tutar. Sonuç olarak her kelimenin metni diskte iki kez bulunur.

Bir kelimenin sayısını sorguladığınızda SQLite önce indekste kelimeyi bulur, oradan rowid'i alır, sonra ana tabloda ikinci bir ikili arama yaparak adet değerini okur. WITHOUT ROWID tabloda ise tek bir B-ağacı vardır; anahtar soz sütunudur ve adet aynı kayıtta durur. Metin bir kez saklanır, değer tek aramada bulunur. Belgeler bu tür durumlarda tablonun yaklaşık yarı yer kaplayıp neredeyse iki kat hızlı çalışabileceğini söyler; gerçek şemalarda ikincil indeksler ve ek kısıtlar olduğu için tablonun daha karmaşık olduğunu da ekler.

Buradan çıkan ilginç bir sonuç var: sıradan bir SQLite tablosunda PRIMARY KEY aslında bir UNIQUE indeksten ibarettir. Diskte satırı bulmak için kullanılan anahtar her zaman rowid'dir. Tek istisna, rowid'in takma adı olan INTEGER PRIMARY KEY sütunudur; o gerçek bir birincil anahtardır. INT PRIMARY KEY dahil diğer bütün birincil anahtar türleri, rowid tablosunda ayrı bir benzersiz indeks olarak yaşar. WITHOUT ROWID seçeneği, belgelerin deyişiyle birincil anahtarı kümelenmiş indeks (clustered index) olarak kullanan bir tablo üretir; yani tanımladığınız anahtar gerçekten satırın saklandığı anahtar olur.

Bu farkı sorgu planında da görebilirsiniz. Önce aynı yapıda sıradan bir tablo oluşturalım, sonra iki tablonun planını karşılaştıralım. Plan çıktısını okumanın ayrıntıları EXPLAIN QUERY PLAN yazısında.

CREATE TABLE normal(soz TEXT PRIMARY KEY, adet INTEGER);
INSERT INTO normal VALUES (NULL, 1), (NULL, 2);   -- rowid tablosu NULL kabul eder
SELECT count(*) FROM normal WHERE soz IS NULL;
-- çıktı: 2

EXPLAIN QUERY PLAN SELECT adet FROM normal WHERE soz = 'elma';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH normal USING INDEX sqlite_autoindex_normal_1 (soz=?)

EXPLAIN QUERY PLAN SELECT adet FROM kelime WHERE soz = 'elma';
-- çıktı:
-- QUERY PLAN
-- `--SEARCH kelime USING PRIMARY KEY (soz=?)

Sıradan tablo sqlite_autoindex_normal_1 adlı otomatik indeksi kullanıyor; WITHOUT ROWID tablo ise doğrudan birincil anahtarı kullanıyor. Yukarıdaki ilk kod parçası, rowid tablosunda metin birincil anahtarın NULL kabul ettiğini de gösteriyor; buna birazdan döneceğiz.

Sıradan tablolardan farkları

Çoğu durumda iki tablo türü birbirinin yerine kullanılabilir, ancak WITHOUT ROWID tablolara özgü bazı kurallar var. Belgelerdeki listeyi özetleyelim:

• PRIMARY KEY zorunludur. Birincil anahtar olmadan tablo oluşturulamaz.
• INTEGER PRIMARY KEY özel anlam taşımaz. Ortada rowid olmadığı için sütun takma ad olmaz ve otomatik numara üretilmez; sıradan bir tabloda INT PRIMARY KEY nasıl davranıyorsa öyle davranır.
• AUTOINCREMENT kullanılamaz. Bu mekanizma rowid'e dayandığı için hata verir.
• Birincil anahtar sütunlarında NOT NULL zorunlu uygulanır. SQL standardına uygun davranış budur.
• last_insert_rowid() etkilenmez. Bu tablolara yapılan eklemeler fonksiyonun döndürdüğü değeri değiştirmez.
• Artımlı BLOB okuma yazma çalışmaz. Bu C arayüzü rowid gerektirir.
• sqlite3_update_hook() geri çağrısı tetiklenmez. Geri çağrı değişen satırın rowid'ini bildirdiği için bu tablolardaki değişiklikler raporlanmaz.

Son iki madde C arayüzüyle ilgilidir; uygulamanız bir dil bağlayıcısı üzerinden değişiklik bildirimleri veya BLOB akışı kullanıyorsa bunları göz önünde bulundurun. İlk dört maddeyi komut satırında hemen deneyebilirsiniz:

CREATE TABLE eksik(a TEXT, b INT) WITHOUT ROWID;
-- hata: PRIMARY KEY missing on table eksik

CREATE TABLE ai(id INTEGER PRIMARY KEY AUTOINCREMENT, x) WITHOUT ROWID;
-- hata: AUTOINCREMENT not allowed on WITHOUT ROWID tables

INSERT INTO kelime VALUES (NULL, 2);
-- hata: NOT NULL constraint failed: kelime.soz

NULL farkı önemlidir. Rowid tablolarında birincil anahtar sütunu, eski bir hatanın uyumluluk adına korunması yüzünden NULL kabul eder. WITHOUT ROWID tablolar bu hatayı taşımaz ve NULL girişini reddeder. INTEGER PRIMARY KEY içeren bir WITHOUT ROWID tabloda ise artık otomatik numara yoktur; değer vermezseniz NULL kabul edilmediği için ekleme başarısız olur:

CREATE TABLE ipk(id INTEGER PRIMARY KEY, x) WITHOUT ROWID;
INSERT INTO ipk(x) VALUES ('deneme');
-- hata: NOT NULL constraint failed: ipk.id

INSERT INTO ipk VALUES ('12', 'metin sayı');     -- tamsayı eğilimi yine geçerli
SELECT id, typeof(id) FROM ipk;
-- çıktı: 12|integer

Metin olarak verilen '12' değerinin yine tamsayıya çevrildiğine dikkat edin. Sütun tamsayı eğilimini korur, sadece rowid takma adı olma özelliğini kaybeder. Eğilim kuralları için veri tipleri ve type affinity yazısına bakabilirsiniz.

Ne zaman WITHOUT ROWID kullanmalı

Belgelerin önerisi iki koşula dayanır: birincil anahtarın tamsayı olmaması ya da birden fazla sütundan oluşması ve satırların büyük metin veya BLOB içermemesi. Tek sütunlu INTEGER birincil anahtarı olan bir tabloda WITHOUT ROWID doğru sonuç verir ama sıradan rowid tablosu daha hızlı çalışır; bu yüzden böyle tabloları WITHOUT ROWID yapmamak iyi tasarım kabul edilir.

Satır boyutu için verilen pratik ölçüt şöyledir: ortalama satır, veritabanı sayfa boyutunun yaklaşık yirmide birinden küçük olmalı. Bu, 4 KiB sayfa boyutunda satır başına yaklaşık 200 bayt, 1 KiB sayfada yaklaşık 50 bayt eder. Daha büyük satırlarda da tablo çalışır, ancak rowid tabloları genellikle daha hızlıdır. Nedeni depolama yapısıdır: rowid tabloları bütün içeriği yaprak düğümlerde tutan B*-ağaçlarıdır, WITHOUT ROWID tablolar ise içeriği ara düğümlerde de saklayan sıradan B-ağaçlarıdır. Ara düğümlerdeki büyük kayıtlar sayfa başına sığan anahtar sayısını düşürür ve arama maliyetini artırır.

Tipik kullanım: çoktan çoğa ilişki tablosu

Yazılar ile etiketler arasındaki ilişkiyi tutan bir bağlantı tablosu, iki sütunlu birincil anahtarı ve küçük satırlarıyla bu seçenek için iyi bir adaydır. Sıradan bir tabloda bu yapı hem gizli rowid'i hem de birincil anahtar için ayrı bir indeksi gerektirirdi:

CREATE TABLE yazi_etiket(
  yazi_id INTEGER NOT NULL,
  etiket  TEXT NOT NULL,
  PRIMARY KEY (yazi_id, etiket)
) WITHOUT ROWID;

INSERT INTO yazi_etiket VALUES (2, 'sqlite'), (1, 'sql'), (1, 'sqlite'), (2, 'indeks');
SELECT yazi_id, etiket FROM yazi_etiket;
-- çıktı:
-- 1|sql
-- 1|sqlite
-- 2|indeks
-- 2|sqlite

EXPLAIN QUERY PLAN SELECT etiket FROM yazi_etiket WHERE yazi_id = 1;
-- çıktı:
-- QUERY PLAN
-- `--SEARCH yazi_etiket USING PRIMARY KEY (yazi_id=?)

INSERT INTO yazi_etiket VALUES (1, 'sql');
-- hata: UNIQUE constraint failed: yazi_etiket.yazi_id, yazi_etiket.etiket

Bileşik anahtarın ilk sütunu üzerinden yapılan sorgu da birincil anahtarı kullanabildi. Sorgularınız çoğunlukla etiket üzerinden geliyorsa ikinci bir indeks gerekir; indeks tasarımı için index oluşturma yazısına bakın.

Tipik kullanım: ayar ve anahtar değer tablosu

SQLite'ın esnek tiplemeyi savunan belgesi, uygulama ayarlarını tek bir tabloda tutmak için WITHOUT ROWID kullanan bir örnek verir. Tipi belirtilmemiş deger sütunu metin, sayı ve BLOB değerlerini olduğu gibi saklar. Değer güncellemek için UPSERT kullanmak bu kalıbı tamamlar; ayrıntılar UPSERT ve ON CONFLICT yazısında.

CREATE TABLE ayar(ad TEXT PRIMARY KEY, deger) WITHOUT ROWID;
INSERT INTO ayar VALUES ('tema', 'koyu'), ('sayfa_boyu', 25), ('oran', 1.5), ('logo', x'89504E47');

SELECT ad, typeof(deger), quote(deger) FROM ayar;
-- çıktı:
-- logo|blob|X'89504E47'
-- oran|real|1.5
-- sayfa_boyu|integer|25
-- tema|text|'koyu'

INSERT INTO ayar VALUES ('tema', 'acik')
  ON CONFLICT(ad) DO UPDATE SET deger = excluded.deger;
SELECT deger FROM ayar WHERE ad = 'tema';
-- çıktı: acik

Önce ölçün, sonra karar verin

Belgelerin en pratik önerisi, geliştirme sürecinin başında bu seçeneği dert etmemektir. İki tablo türü aynı SQL ifadelerine aynı cevapları verdiği için sonradan deneme yapmak kolaydır: tamsayı olmayan birincil anahtara sahip tablolara WITHOUT ROWID ekleyip uygulamanızı kendi iş yükünüzle test edin ve yalnızca fayda gördüğünüz tablolarda bırakın. Var olan bir tabloyu dönüştürmek için ALTER TABLE yeterli değildir; tabloyu yeni tanımla oluşturup veriyi kopyalamanız gerekir. Güvenli adımlar ALTER TABLE yazısındaki genel yöntemde anlatılıyor.

Satır boyutlarını ölçmek için belgelerde sqlite3_analyzer aracı önerilir. SQLite 3.54.0 sürüm notlarına göre bu araç kullanımdan kaldırılmış ve yerini komut satırındaki .diskused komutuna bırakmıştır. Bu komut 3.54.0 ve sonrasında bulunur; daha eski bir sürüm kullanıyorsanız ayrı aracı kullanmanız gerekir.

Bir tablonun WITHOUT ROWID olup olmadığını anlamak

PRAGMA table_info iki tablo türü için aynı sonucu verir, bu yüzden ayırt etmek için işe yaramaz. Belgelerin önerdiği yöntem PRAGMA index_info komutudur: tablo adı verildiğinde sıradan bir tablo için hiç satır dönmez, WITHOUT ROWID tablo için birincil anahtar sütunları döner. SQLite 3.37.0 ile gelen PRAGMA table_list ise wr sütununda bunu doğrudan gösterir. Şemayı sorgulamanın diğer yolları için tabloları listeleme yazısına bakabilirsiniz.

SELECT name, wr, strict FROM pragma_table_list
 WHERE schema = 'main' AND name IN ('ayar', 'kelime', 'normal') ORDER BY name;
-- çıktı:
-- ayar|1|0
-- kelime|1|0
-- normal|0|0

SELECT * FROM pragma_index_info('kelime');
-- çıktı: 0|0|soz
SELECT count(*) FROM pragma_index_info('normal');
-- çıktı: 0

STRICT ile birlikte kullanmak

Tablo seçenekleri virgülle ayrılarak birlikte yazılabilir ve sıraları önemli değildir. Katı tip denetimi isteyen bir tabloyu aynı anda WITHOUT ROWID yapabilirsiniz. Katı tablolar STRICT tablolar yazısında ayrıca ele alınıyor.

CREATE TABLE ikisi(a TEXT, b TEXT, PRIMARY KEY(a)) STRICT, WITHOUT ROWID;
SELECT name, wr, strict FROM pragma_table_list('ikisi');
-- çıktı: ikisi|1|1

Uyumluluk ve sınırlar

WITHOUT ROWID tablolar SQLite 3.8.2 (2013) ile geldi. Bu tür tablo içeren bir veritabanını daha eski bir sürümle açmaya çalışmak "malformed database schema" hatası verir. Belgelere göre bu söz dizimi yalnızca SQLite'a özgüdür; şemanızı başka bir veritabanı motoruna taşımayı planlıyorsanız bu seçeneği kaldırmanız gerekir. Son olarak, last_insert_rowid() fonksiyonunun bu tablolarda işe yaramadığını unutmayın; eklenen anahtarı zaten siz verdiğiniz için genellikle buna ihtiyaç da olmaz, gerekirse RETURNING kullanabilirsiniz.

INSERT INTO normal VALUES ('x', 1);
SELECT last_insert_rowid();
-- çıktı: 3
INSERT INTO kelime VALUES ('incir', 9);
SELECT last_insert_rowid();      -- WITHOUT ROWID eklemesi değeri değiştirmez
-- çıktı: 3

Sık yapılan hatalar

• Her tabloyu WITHOUT ROWID yapmak: Tamsayı kimlikli tablolarda fayda yoktur, rowid tablosu daha hızlıdır.
• Büyük metin veya BLOB içeren tablolarda kullanmak: Satır sayfanın yaklaşık yirmide birinden büyükse genellikle kayıp olur.
• INTEGER PRIMARY KEY ile otomatik numara beklemek: Bu tablolarda numara üretilmez, değeri sizin vermeniz gerekir.
• Ölçmeden karar vermek: Belgeler bile kesin bir kural vermez; kendi verinizle deneyin.
• Değişiklik bildirimine güvenen kodu unutmak: sqlite3_update_hook bu tablolarda tetiklenmez.

Sık Sorulan Sorular

WITHOUT ROWID tablo daha az yer kaplar mı?

Tamsayı olmayan veya bileşik birincil anahtarlı ve küçük satırlı tablolarda kaplayabilir; çünkü anahtar sütunlar iki kez saklanmaz ve ayrı bir indeks gerekmez. Ne kadar kazanç olacağı şemanıza ve verinize bağlıdır.

Var olan tabloyu WITHOUT ROWID yapabilir miyim?

Doğrudan bir ALTER TABLE seçeneği yoktur. Yeni tabloyu istediğiniz tanımla oluşturup veriyi INSERT INTO ... SELECT ile aktarmanız, ardından eski tabloyu kaldırıp yeni tabloyu yeniden adlandırmanız gerekir.

WITHOUT ROWID tabloda indeks oluşturulabilir mi?

Evet. İkincil indeksler ve UNIQUE kısıtları her iki tablo türünde de çalışır. Fark yalnızca ana verinin birincil anahtara göre saklanmasıdır.

Yabancı anahtar bu tablolara başvurabilir mi?

Yabancı anahtarların üst tablodaki adı olan sütunlara başvurması gerekir ve WITHOUT ROWID tablonun birincil anahtarı da zaten adı olan sütunlardan oluşur. PRAGMA foreign_key_check çıktısında rowid sütunu bu tablolar için NULL döner. Ayrıntılar foreign key yazısında.

Bu seçenek neden varsayılan değil?

Belgeler bunu tarihsel bir nedene bağlar: SQLite ilk tasarlandığında uygulamayı basit tutmak için satır anahtarı olarak yalnızca tamsayı rowid kullanıldı. Zamanla birincil anahtarın gerçekten satır anahtarı olduğu tablolara ihtiyaç arttı ve bu seçenek, dolaşımdaki milyarlarca veritabanıyla uyumluluğu bozmadan 2013 civarında eklendi. Varsayılanı değiştirmek eski dosyaların davranışını değiştireceği için seçenek açıkça yazılmak zorundadır.

Marka adları sahiplerine aittir. Bu yazı bağımsız bir teknik rehberdir; adı geçen kuruluşlarla bayilik veya ortaklık ilişkisi bulunduğu anlamına gelmez.

İlgili içerikler: SQLite öğrenme rehberi · PRIMARY KEY ve rowid · STRICT tablolar · Index oluşturma · EXPLAIN QUERY PLAN · Performans ayarları