Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
22 octobre 2013 2 22 /10 /octobre /2013 15:02

J'avais lu, il y a très longtemps de cela, une astuce de l'oncle Tom pour indexer uniquement quelques valeurs très sélectives d'une colonne.

Typiquement un flag ou statut de traitement.

 

1) FBI is watching you

La vieille méthode avant 11G : 

 

CREATE TABLE test_ind AS

SELECT level n, CASE WHEN level <= 100 then 1 ELSE 0 END flag

FROM DUAL

CONNECT BY LEVEL <= 1000000;

 

CREATE INDEX test_ind_ix ON test_ind(flag);

CREATE INDEX test_ind_ix2 ON test_ind(nullif(flag, 0));

 

exec dbms_stats.gather_table_stats(null, 'TEST_IND', cascade => true)

 

La mécanique se base sur le fait que lorsque toutes les valeurs de la clef à indexer sont nulles, l'entrée n'est pas stockée dans l'index.

Créer un Function Based Index avec NULLIF permet donc d'exclure les valeurs non désirables de l'index.

 

Regardons maintenant quelques différences entre l'index "normal" et l'index custom : 

 

SELECT index_name, blevel, leaf_blocks, index_type

FROM dba_indexes

WHERE table_name = 'TEST_IND';

 

INDEX_NAME                         BLEVEL LEAF_BLOCKS INDEX_TYPE

------------------------------ ---------- ----------- ---------------------------

TEST_IND_IX                             2        1812 NORMAL

TEST_IND_IX2                            0           1 FUNCTION-BASED NORMAL

 

- Très peu de blocs pour l'index custom, donc très rapide à scanner et optimal en consommation de ressources

- B-tree level à 0 pour l'index custom, donc deux lectures d'économisées à chaque accès

- Index_type = FUNCTION-BASED NORMAL pour l'index custom

 

Pour utiliser l'index, il faut par contre requêter la colonne après application de la fonction qui définit l'index : 

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE nullif(flag, 0) = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

---------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |   100 |   800 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IND     |   100 |   800 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | TEST_IND_IX2 |   100 |       |     1   (0)|

---------------------------------------------------------------------------------

 

Côté plan d'exécution, on constate que ça torche sévère, et qu'en plus la cardinalité est estimée correctement même sans histogramme !

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE flag = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

------------------------------------------------------------------------------

| Id  | Operation         | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |          |   500K|  3906K|   472   (3)| 00:00:06 |

|*  1 |  TABLE ACCESS FULL| TEST_IND |   500K|  3906K|   472   (3)| 00:00:06 |

------------------------------------------------------------------------------

 

... ce qui n'est pas le cas de l'index classique. Il faudrait calculer un histogramme, mais même dans ce cas, il peut y avoir des suprises. (Je montrerais un de ces jours un exemple de prod que j'ai recontré il y a peu)

 

Côté dictionnaire, on constate qu'Oracle a créé une colonne virtuelle cachée, pour stocker la valeur de la fonction, et l'indexer : 

 

SELECT column_name, hidden_column, virtual_column, data_default

FROM dba_tab_cols

WHERE table_name = 'TEST_IND';

 

COLUMN_NAME               HID VIR DATA_DEFAULT

------------------------- --- --- --------------------------------------------

SYS_NC00003$              YES YES CASE "FLAG" WHEN 0 THEN NULL ELSE "FLAG" END

FLAG                      NO  NO

N                         NO  NO

 

Au passage, c'est la majeure différence entre DBA_TAB_COLUMNS et DBA_TAB_COLS : 

l'affichage des colonnes cachées. 

(Pour ma part, je requête évidemment toujours dba_tab_cols parce que c'est plus court à taper...)


2) 11g et virtual column

 

L'autre jour, l'ami Waldar faisait remarquer que c'est une pratique obscure, et qu'on pouvait, à partir de 11g (si je ne me trompe pas), créer une colonne virtuelle et l'indexer.

"Big up" à lui, comme diraient les jeunes aujourd'hui : 

 

ALTER TABLE test_ind ADD ghost_flag NUMBER GENERATED ALWAYS AS (nullif(flag, 0));

 

ALTER TABLE test_ind ADD ghost_flag NUMBER GENERATED ALWAYS AS (nullif(flag, 0))

                         *

ERROR at line 1:

ORA-54015: Duplicate column expression was specified

 

Paf. Oui, au passage, on ne peut créer deux colonnes virtuelles de même définition.

 

DROP INDEX test_ind_ix2;

ALTER TABLE test_ind ADD ghost_flag NUMBER AS (nullif(flag, 0));

CREATE INDEX test_ind_ix3 ON test_ind(ghost_flag);

exec dbms_stats.gather_table_stats(null, 'TEST_IND', cascade => true)

 

EXPLAIN PLAN FOR

SELECT *

FROM test_ind

WHERE ghost_flag = 1;

 

SELECT *

FROM TABLE(dbms_xplan.display);

 

---------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)|

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |   100 |   800 |     2   (0)|

|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_IND     |   100 |   800 |     2   (0)|

|*  2 |   INDEX RANGE SCAN          | TEST_IND_IX3 |   100 |       |     1   (0)|

---------------------------------------------------------------------------------

 

Effectivement, cela donne le même résultat.

Si ce n'est que la colonne n'est plus cachée bien que virtuelle : 

 

SELECT column_name, hidden_column, virtual_column, data_default

FROM dba_tab_cols 

 WHERE table_name = 'TEST_IND';

 

 

COLUMN_NAME                    HID VIR DATA_DEFAULT

------------------------------ --- --- -------------------------

GHOST_FLAG                     NO  YES CASE "FLAG" WHEN 0 THEN NULL ELSE "FLAG" END

FLAG                           NO  NO

N                              NO  NO

Partager cet article

Published by Pacman - dans SGBD
commenter cet article

commentaires

Houri 25/10/2013 17:13

Bonjour pacman

Il existe une situation où tu créés un index sur la colonne virtuelle et lorsque tu utilises un requête non pas sur la colonne virtuelle mais sur la colonne réelle sur laquelle tu t'es basé pour
créer la colonne virtuelle, alors l'index est utilisé. Pour plus de détails voir ceci

http://hourim.wordpress.com/2013/10/25/index-on-a-virtual-column-would-it-help-others/

Par contre pour l'instant ceci n'est valable que pour la fonction trunc. Et je ne sais pas pourquoi. Si tu as un idée elle sera la bienvenue

Bien Cordialement

Pacman 29/10/2013 15:07



Salut Mohammed, 


 


C'est magique, bien joué ! :)


Je n'ai pas trouvé plus d'infos sur le sujet. Par contre, je trouve que le test est un tout petit peu plus parlant en ne prenant pas une constante égale à son image par la fonction : 


Oracle applique une première fois le filtre au niveau de l'index en appliquant la fonction, puis une deuxième fois lors de l'access by rowid : 


 


SQL> select * from t1 where ord_date =to_date('02/08/2011','dd/mm/yyyy')+ 1/4;


 


no rows selected


 


Elapsed: 00:00:00.01


 


Execution Plan


----------------------------------------------------------


Plan hash value: 4006924800


 


---------------------------------------------------------------------------------------------


| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |


---------------------------------------------------------------------------------------------


|   0 | SELECT STATEMENT            |               |     2 |    80 |     3   (0)|
00:00:01 |


|*  1 |  TABLE ACCESS BY INDEX ROWID| T1            |     2 |    80 |     3   (0)| 00:00:01 |


|*  2 |   INDEX RANGE SCAN          | IND_VIRT_DATE |     2 |       |     1   (0)| 00:00:01 |


---------------------------------------------------------------------------------------------


 


Predicate Information (identified by operation id):


---------------------------------------------------


 


   1 - filter("ORD_DATE"=TO_DATE(' 2011-08-02 06:00:00', 'syyyy-mm-dd hh24:mi:ss'))


   2 - access("T1"."VIRT_DATE"=TRUNC(TO_DATE(' 2011-08-02 06:00:00', 'syyyy-mm-dd


              hh24:mi:ss')))


 


Bref, tiens moi au courant si tu découvres d'autres choses :)