{"id":9196,"date":"2012-08-13T08:39:28","date_gmt":"2012-08-13T07:39:28","guid":{"rendered":"http:\/\/touk.pl\/blog\/?p=9196"},"modified":"2023-03-22T15:53:04","modified_gmt":"2023-03-22T14:53:04","slug":"polski-indeksy-na-kolumnach-z-wartosciami-nullowymi-cz-1","status":"publish","type":"post","link":"https:\/\/touk.pl\/blog\/2012\/08\/13\/polski-indeksy-na-kolumnach-z-wartosciami-nullowymi-cz-1\/","title":{"rendered":"Indeksy na kolumnach  z warto\u015bciami nullowymi cz. 1"},"content":{"rendered":"<p>A tak dok\u0142adnie aby zadzia\u0142a\u0142, gdy chcemy zrobi\u0107 zapytanie: <\/p>\n<p>select * from tablica where kolumna is null; <\/p>\n<p>Zak\u0142adamy wtedy indeks funkcyjny: <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\ncreate index kolumna_idx on tablica (NVL(kolumna,TO_DATE(' 4000-01-01', 'syyyy-mm-dd'))) \r\n<\/pre>\n<p>I wtedy musimy tak konstruowa\u0107 zapytanie, aby mog\u0142o wykorzysta\u0107 powy\u017cszy indeks: <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\nselect * from tablica\u00a0\u00a0 where NVL(kolumna,TO_DATE('4000-01-01', 'syyyy-mm-dd')) = TO_DATE('4000-01-01', 'syyyy-mm-dd') \r\n<\/pre>\n<p>Dla por\u00f3wnania koszt tradycyjnego zapytania: <\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\nselect count(*) from dh_monitor.events where valid_to is null\r\n<\/pre>\n<p>to 8122. Dla por\u00f3wnania, zapytanie kt\u00f3re wykorzystuje indeks ma koszt 3!!!<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"oracledb\">\r\nselect count(*) from\u00a0 events where NVL(VALID_TO,TO_DATE('4000-01-01 ', 'syyyy-mm-dd')) = TO_DATE('4000-01-01', 'syyyy-mm-dd')\r\n<\/pre>\n<p>Dla tabeli, gdzie stosunek ilo\u015bci nullowych wierszy do wszystkich w tabeli wynosi: 6.568\/5.999.617<\/p>\n","protected":false},"excerpt":{"rendered":"A tak dok\u0142adnie aby zadzia\u0142a\u0142, gdy chcemy zrobi\u0107 zapytanie: select * from tablica where kolumna is null; Zak\u0142adamy&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-9196","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\/9196","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=9196"}],"version-history":[{"count":12,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/9196\/revisions"}],"predecessor-version":[{"id":15515,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/posts\/9196\/revisions\/15515"}],"wp:attachment":[{"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/media?parent=9196"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/categories?post=9196"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/touk.pl\/blog\/wp-json\/wp\/v2\/tags?post=9196"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}