{"id":9204,"date":"2012-08-13T08:44:53","date_gmt":"2012-08-13T07:44:53","guid":{"rendered":"http:\/\/touk.pl\/blog\/?p=9204"},"modified":"2023-03-22T15:57:12","modified_gmt":"2023-03-22T14:57:12","slug":"polski-indeksy-na-kolumnach-z-wartosciami-nullowymi-cz-2","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2012\/08\/13\/polski-indeksy-na-kolumnach-z-wartosciami-nullowymi-cz-2\/","title":{"rendered":"Indeksy na kolumnach z warto\u015bciami nullowymi cz. 2"},"content":{"rendered":"<p>Jest jeszcze jeden spos\u00f3b indeksowania, kt\u00f3ry pozwala nam na bardzo wygodne korzystanie z indeks\u00f3w i wyszukiwanie warto\u015bci nullowych. Mianowicie indeks jest prostym indeksem sk\u0142adaj\u0105cym si\u0119 z dw\u00f3ch kolumn &#8211; tej po kt\u00f3rej chcemy wyszukiwa\u0107 i warto\u015bci sta\u0142ej. Np: <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\ncreate index kolumna_IDX on tabela (kolumna, TO_DATE('4000-01-01', 'yyyy-mm-dd')); \r\n<\/pre>\n<p>i wtedy mo\u017cemy pisa\u0107 zapytania: <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\nselect * from tabela where kolumna is null \r\n<\/pre>\n<p>i indeks zadzia\u0142a! Zrobi\u0142am te\u017c por\u00f3wnanie dla tabeli, gdzie stosunek ilo\u015bci nullowych wierszy do wszystkich w tabeli wynosi: 1.002\/131.002 I za\u0142o\u017cy\u0142am indeks <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\ncreate index AA2_IDX on AAAAA2_TEST (NVL(MODIFY_DATE,TO_DATE('4000-01-01', 'yyyy-mm-dd'))) \r\n<\/pre>\n<p>Koszt zapytania: <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\nselect COUNT(*) from aaaaa2_test WHERE NVL(modify_DATE,TO_DATE('4000-01-01', 'syyyy-mm-dd')) = TO_DATE('4000-01-01', 'syyyy-mm-dd') \r\n<\/pre>\n<p>wyni\u00f3s\u0142 <strong>38<\/strong> za\u015b dla indeksu: <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\ncreate index AA_IDX on AAAAA2_TEST (FROM_DATE, TO_DATE('4000-01-01', 'yyyy-mm-dd')) \r\n<\/pre>\n<p>i zapytania: <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\nselect COUNT(*) from aaaaa2_test WHERE FROM_DATE IS NULL\r\n<\/pre>\n<p>wyni\u00f3s\u0142 <strong>28<\/strong>. Dla wi\u0119kszej ilo\u015bci nullowych rekord\u00f3w tzn. 11002\/131.002\u00a0stosunek koszt\u00f3w wyni\u00f3s\u0142 43\/28, a wi\u0119c zn\u00f3w zdecydowanie na korzy\u015b\u0107 indeksu dwukolumnowego.<\/p>\n","protected":false},"excerpt":{"rendered":"Jest jeszcze jeden spos\u00f3b indeksowania, kt\u00f3ry pozwala nam na bardzo wygodne korzystanie z indeks\u00f3w i wyszukiwanie warto\u015bci nullowych.&hellip;\n","protected":false},"author":39,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[11],"tags":[687,13],"class_list":{"0":"post-9204","1":"post","2":"type-post","3":"status-publish","4":"format-standard","6":"category-development-design","7":"tag-db","8":"tag-oracle"},"_links":{"self":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/9204","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/users\/39"}],"replies":[{"embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/comments?post=9204"}],"version-history":[{"count":13,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/9204\/revisions"}],"predecessor-version":[{"id":15520,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/9204\/revisions\/15520"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=9204"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=9204"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=9204"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}