<!-- If the browser does not support frames put the first topic content here --> <div class="Section1"> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt; background:#DFDFDF'> <p class="MsoHeader" align="center" style= 'text-align:center;background:#DFDFDF; border:none;padding:0cm'> <b><span style='font-size:16.0pt'>LES INDEX ET LES VUES</span></b></p> </div> <p class="MsoHeader"> </p> <p class="MsoHeader"><span style='font-size:11.0pt'>La définition d'index et de vues relève du langage de définition de données (LDD) au même titre que la création de tables.</span></p> <p class="MsoHeader" style= 'margin-top:12.0pt;margin-right:0cm;margin-bottom: 0cm;margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <b><span style='font-size:16.0pt'>1.<span style= 'font:7.0pt "Times New Roman"'> </span></span></b> <b><u><span style='font-size:16.0pt'>Les index</span></u></b></p> <p class="MsoHeader" style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:0cm; margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <span style='font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Généralités</span></u></b></p> <p class="MsoHeader"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoHeader"><span style='font-size:11.0pt'>On crée des index sur les tables pour permettre un accès plus rapide à l'information. Un index concerne une ou plusieurs colonnes de la table et entraîne la création d'un espace comportant pour chaque valeur de la colonne ou du groupe de colonnes, le numéro du (ou des) tuple(s) correspondant(s).</span></p> <p class="MsoHeader"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoHeader"><b><u><span style= 'font-size:11.0pt'>Représentation schématique de la notion d'index</span></u></b></p> <p class="MsoHeader"><span style='font-size:11.0pt'>L'illustration présente une table Client avec un index sur le nom.</span></p> <p class="MsoHeader"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" align="left" style='text-align:left'><b>CLIENT (<u>Num_cli</u>, Nom_cli, Pre_Cli, Adr_cli, Cdp_cli, Vil_cli, Tel_cli)</b></p> <h2 style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:0cm;margin-left: 0cm;margin-bottom:.0001pt'> <a name="node0" id="node0"></a><span style= 'text-decoration:none'> </span> <span lang="EN-GB" style='text-decoration:none' xml:lang= "EN-GB">DONNEES INDEX</span></h2> <h3 style= 'margin-top:0cm;margin-right:0cm;margin-bottom:3.0pt;margin-left: 6.0cm;text-indent:6.8pt'> <a name="node1" id="node1"></a><span style= 'font-size:11.0pt'>Identifiant de la ligne Identifiant de la ligne</span></h3> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style='border-collapse:collapse;border:none'> <tr style='page-break-inside:avoid;height:28.55pt'> <td width="33" valign="top" style= 'width:24.8pt;border-top:1.5pt;border-left: 1.5pt;border-bottom:1.0pt;border-right:1.0pt;border-color:green;border-style: solid;padding:0cm 3.5pt 0cm 3.5pt;height:28.55pt'> <p class="MsoNormal"><span style= 'position:absolute;z-index:2;left:0px; margin-left:247px;margin-top:11px;width:205px;height:111px'> <img width="205" height="111" src= "BD_DA_Cours_Atelier2_fichiers/image001.gif" alt= "image" /></span><span style= 'position:absolute;z-index:3;left:0px;margin-left:248px;margin-top: 14px;width:216px;height:67px'><img width="216" height="67" src="BD_DA_Cours_Atelier2_fichiers/image002.gif" alt= "image" /></span><span lang="EN-GB" xml:lang="EN-GB">1</span></p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:solid green 1.5pt; border-left:none;border-bottom:solid green 1.0pt;border-right:solid green 1.0pt; padding:0cm 3.5pt 0cm 3.5pt;height:28.55pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Dupont</span></p> </td> <td width="123" valign="top" style= 'width:92.1pt;border-top:solid green 1.5pt; border-left:none;border-bottom:solid green 1.0pt;border-right:solid green 1.5pt; padding:0cm 3.5pt 0cm 3.5pt;height:28.55pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Alexandre….</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </td> <td width="119" valign="top" style= 'width:89.6pt;border-top:solid green 1.5pt; border-left:none;border-bottom:solid green 1.0pt;border-right:none; padding:0cm 3.5pt 0cm 3.5pt;height:28.55pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">A1</span></p> </td> <td width="107" valign="top" style= 'width:80.5pt;border-top:solid green 1.5pt; border-left:none;border-bottom:solid green 1.0pt;border-right:none; padding:0cm 3.5pt 0cm 3.5pt;height:28.55pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:1.5pt;border-left:1.5pt; border-bottom:1.0pt;border-right:1.0pt;border-color:green;border-style:solid; padding:0cm 3.5pt 0cm 3.5pt;height:28.55pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Armand</span></p> </td> <td width="95" valign="top" style= 'width:70.9pt;border-top:solid green 1.5pt; border-left:none;border-bottom:solid green 1.0pt;border-right:solid green 1.5pt; padding:0cm 3.5pt 0cm 3.5pt;height:28.55pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">A2</span></p> </td> </tr> <tr> <td width="33" valign="top" style= 'width:24.8pt;border-top:none;border-left:solid green 1.5pt; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'position:absolute;z-index:1;left:0px; margin-left:247px;margin-top:15px;width:217px;height:113px'> <img width="217" height="113" src= "BD_DA_Cours_Atelier2_fichiers/image003.gif" alt= "image" /></span><span lang="EN-GB" xml:lang="EN-GB">2</span></p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:none;border-left:none; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Armand</span></p> </td> <td width="123" valign="top" style= 'width:92.1pt;border-top:none;border-left: none;border-bottom:solid green 1.0pt;border-right:solid green 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Thomas….</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </td> <td width="119" valign="top" style= 'width:89.6pt;border:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">A2</span></p> </td> <td width="107" valign="top" style= 'width:80.5pt;border:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:none;border-left:solid green 1.5pt; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Armand</p> </td> <td width="95" valign="top" style= 'width:70.9pt;border-top:none;border-left:none; border-bottom:solid green 1.0pt;border-right:solid green 1.5pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">A4</p> </td> </tr> <tr> <td width="33" valign="top" style= 'width:24.8pt;border-top:none;border-left:solid green 1.5pt; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span style= 'position:absolute;z-index:4;left:0px; margin-left:248px;margin-top:2px;width:216px;height:66px'> <img width="216" height="66" src= "BD_DA_Cours_Atelier2_fichiers/image004.gif" alt= "image" /></span>3</p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:none;border-left:none; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Duval</p> </td> <td width="123" valign="top" style= 'width:92.1pt;border-top:none;border-left: none;border-bottom:solid green 1.0pt;border-right:solid green 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Chrystelle….</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </td> <td width="119" valign="top" style= 'width:89.6pt;border:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">A3</span></p> </td> <td width="107" valign="top" style= 'width:80.5pt;border:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:none;border-left:solid green 1.5pt; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Dupont</span></p> </td> <td width="95" valign="top" style= 'width:70.9pt;border-top:none;border-left:none; border-bottom:solid green 1.0pt;border-right:solid green 1.5pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">A1</span></p> </td> </tr> <tr> <td width="33" valign="top" style= 'width:24.8pt;border-top:none;border-left:solid green 1.5pt; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">4</span></p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:none;border-left:none; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Armand</span></p> </td> <td width="123" valign="top" style= 'width:92.1pt;border-top:none;border-left: none;border-bottom:solid green 1.0pt;border-right:solid green 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">Cathy….</span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </td> <td width="119" valign="top" style= 'width:89.6pt;border:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB">A4</span></p> </td> <td width="107" valign="top" style= 'width:80.5pt;border:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:none;border-left:solid green 1.5pt; border-bottom:solid green 1.0pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Duval</p> </td> <td width="95" valign="top" style= 'width:70.9pt;border-top:none;border-left:none; border-bottom:solid green 1.0pt;border-right:solid green 1.5pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">A3</p> </td> </tr> <tr> <td width="33" valign="top" style= 'width:24.8pt;border-top:none;border-left:solid green 1.5pt; border-bottom:solid green 1.5pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">…</p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:none;border-left:none; border-bottom:solid green 1.5pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> </td> <td width="123" valign="top" style= 'width:92.1pt;border-top:none;border-left: none;border-bottom:solid green 1.5pt;border-right:solid green 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> </td> <td width="119" valign="top" style= 'width:89.6pt;border:none;border-bottom:solid green 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> </td> <td width="107" valign="top" style= 'width:80.5pt;border:none;border-bottom:solid green 1.5pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> </td> <td width="76" valign="top" style= 'width:2.0cm;border-top:none;border-left:solid green 1.5pt; border-bottom:solid green 1.5pt;border-right:solid green 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">…</p> </td> <td width="95" valign="top" style= 'width:70.9pt;border-top:none;border-left:none; border-bottom:solid green 1.5pt;border-right:solid green 1.5pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"> </p> </td> </tr> </table> <p class="MsoHeader"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-top:3.0pt;margin-right:0cm;margin-bottom:0cm; margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <span style='font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <i>les données sont insérées de façon séquentielle dans la table Client au fur et à mesure des ajouts</i></p> <p class="MsoNormal" style= 'margin-top:3.0pt;margin-right:0cm;margin-bottom:0cm; margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <span style='font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <i>chaque ligne de la table est identifiée de façon unique par un identifiant dans la base de données.</i></p> <p class="MsoNormal" style= 'margin-top:3.0pt;margin-right:0cm;margin-bottom:0cm; margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <span style='font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <i>lorsqu’on crée un index sur le nom du client, les valeurs de la colonne indexée sont en permanence classées ; à chacune de ces valeurs, on fait correspondre l’identifiant de la ligne de la table.</i></p> <p class="MsoNormal"> </p> <p class="MsoHeader"><span style='font-size:11.0pt'>Un index va permettre d'éviter de balayer toute la table lors de la recherche d'une ligne répondant à un critère de sélection exprimé sur la colonne à indexer.</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoHeader" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Ex : SELECT *</span></p> <p class="MsoHeader" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> FROM Client</span></p> <p class="MsoHeader" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> WHERE Nom_cli = 'Armand';</span></p> </div> <p class="MsoNormal" style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:0cm; margin-left:35.45pt;margin-bottom:.0001pt'> <u><span style='font-size:11.0pt'>. Exécution sans index sur le nom de client :</span></u></p> <p class="MsoNormal" style='margin-left:35.45pt'><span style= 'font-size:11.0pt'>Le SGBDR scrute toute la table Client (bloc par bloc et ligne par ligne) pour retrouver les clients nommés 'Armand'.</span></p> <p class="MsoNormal" style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:0cm; margin-left:35.45pt;margin-bottom:.0001pt'> <u><span style='font-size:11.0pt'>. Exécution avec index sur le nom de client :</span></u></p> <p class="MsoNormal" style='margin-left:35.45pt'><span style= 'font-size:11.0pt'>Le SGBDR passe dans ce cas par l'index qui le renverra vers les lignes concernées dans la table. Cette seconde solution est nettement plus rapide car le nombre de blocs parcourus est beaucoup moins important.</span></p> <p class="MsoHeader" style= 'margin-top:9.0pt;margin-right:0cm;margin-bottom:3.0pt; margin-left:17.85pt;text-indent:-17.85pt'> <span style='font-size:8.0pt; font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Création d'un index</span></u></b></p> <p class="MsoNormal"><i><span style='font-size:11.0pt'>Sous Oracle ou MySql:</span></i></p> <p class="MsoNormal"><i><span style='font-size:11.0pt'>CREATE [UNIQUE] INDEX NomIndex</span></i></p> <p class="MsoNormal"></p> <table cellpadding="0" cellspacing="0" align="left"> <tr> <td width="183" height="6"></td> <td width="12"></td> <td width="26"></td> <td width="12"></td> </tr> <tr> <td height="50"></td> <td align="left" valign="top"><img width="12" height="50" src= "BD_DA_Cours_Atelier2_fichiers/image005.gif" alt="image" /></td> <td></td> <td align="left" valign="top"><img width="12" height="50" src= "BD_DA_Cours_Atelier2_fichiers/image006.gif" alt="image" /></td> </tr> </table> <i><span style='font-size:11.0pt'> </span></i><br clear= "all" /> <p class="MsoNormal" align="left" style='text-align:left'> <i><span style='font-size:11.0pt'>ON NomTable (NomColonne ASC [, NomColonne …]);</span></i></p> <p class="MsoNormal" align="left" style='text-align:left'> <i><span style= 'font-size:11.0pt'> DESC</span></i></p> <p class="MsoNormal" align="left" style='text-align:left'> <i><span style='font-size:11.0pt'> </span></i></p> <p class="MsoNormal" align="left" style='text-align:left'> <span style='font-size: 11.0pt'>UNIQUE est à préciser si les doublons ne sont pas acceptés.</span></p> <p class="MsoNormal" align="left" style='text-align:left'> <span style='font-size: 11.0pt'>Par défaut, le classement est croissant (ASC).</span></p> <p class="MsoNormal" align="left" style='text-align:left'> <span style='font-size: 11.0pt'>Un index peut être composé de une à 16 colonnes.</span></p> <p class="MsoNormal" align="left" style='text-align:left'> <span style='font-size: 8.0pt'> </span></p> <p class="MsoNormal" align="left" style='text-align:left'> <span style='font-size: 11.0pt'>Sous MySql on peut visualiser les index en écrivant : Show index From</span><span style= 'position:absolute;z-index:13;margin-left:221px; margin-top:2px;width:12px;height:2px'><img width="12" height="2" src="BD_DA_Cours_Atelier2_fichiers/image007.gif" alt= "image" /></span><span style= 'position: absolute;z-index:12;margin-left:183px;margin-top:2px;width:12px;height:2px'><img width="12" height="2" src="BD_DA_Cours_Atelier2_fichiers/image007.gif" alt= "image" /></span><i><span style= 'font-size:11.0pt'>nomTable</span></i></p> <p class="MsoNormal" align="left" style='text-align:left'> <span style='font-size: 11.0pt'> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span style= 'font-size:11.0pt'>Ex : création d'un index sur le nom du client :</span></p> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span style= 'font-size:11.0pt'>…………………………………………………………………………………………………………………</span></p> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span style= 'font-size:11.0pt'>…………………………………………………………………………………………………………………</span></p> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span></p> </div> <p class="MsoNormal" align="left" style= 'margin-top:9.0pt;margin-right:0cm; margin-bottom:3.0pt;margin-left:17.85pt;text-align:left;text-indent:-17.85pt'> <span style='font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Suppression d'un index</span></u></b></p> <p class="MsoNormal" align="left" style='text-align:left'> <i><span style='font-size:11.0pt'>DROP INDEX NomIndex;</span></i></p> <p class="MsoNormal" align="left" style= 'margin-top:9.0pt;margin-right:0cm; margin-bottom:3.0pt;margin-left:17.85pt;text-align:left;text-indent:-17.85pt'> <span style='font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Colonnes à indexer</span></u></b></p> <p class="MsoNormal" style='margin-top:3.0pt'><u><span style= 'font-size:11.0pt'>. Les clés primaires</span></u></p> <p class="MsoNormal"><span style='font-size:11.0pt'>La création d'un index sur la clé primaire est automatique.</span></p> <p class="MsoNormal" style='margin-top:3.0pt'><u><span style= 'font-size:11.0pt'>. Les clés étrangères</span></u></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Ces clés servent pour les jointures. C'est justement pour accélérer les opérations de jointure qu'un index est nécessaire sur les clés étrangères.</span></p> <p class="MsoNormal" style='margin-top:3.0pt'><u><span style= 'font-size:11.0pt'>. Les colonnes servant souvent de critère de recherche</span></u></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Ce sont les colonnes qui sont référencées dans une clause Where.</span></p> <p class="MsoNormal" style='margin-top:3.0pt'><u><span style= 'font-size:11.0pt'>. Les colonnes servant souvent de critère de tri</span></u></p> <h3 style='margin-left:18.0pt;text-indent:-18.0pt'><a name= "_Toc318647668"></a><a name="_Toc317856590"></a><a name= "_Toc317855832"><span style= 'font-size:8.0pt; font-family:Wingdings;font-weight:normal'>q<span style='font:7.0pt "Times New Roman"'> </span></span> <u><span style='font-size:14.0pt'>Les colonnes à ne pas indexer</span></u></a></h3> <p class="MsoNormal"><span style='font-size:11.0pt'>Il est recommandé de ne pas indexer :</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> . les colonnes présentant peu de valeurs distinctes,</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> . les colonnes souvent modifiées.</span></p> <h3 style='margin-left:18.0pt;text-indent:-18.0pt'><a name= "_Toc318647669"></a><a name="_Toc317856591"></a><a name= "_Toc317855833"><span style= 'font-size:8.0pt; font-family:Wingdings;font-weight:normal'>q<span style='font:7.0pt "Times New Roman"'> </span></span> <u><span style='font-size:14.0pt'>Cas où l'index n'est pas utilisé</span></u></a></h3> <p class="MsoNormal"><span style='font-size:11.0pt'>Bien qu'une colonne soit indexée, l'index ne sera pas utilisé si :</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> . la colonne indexée ne figure ni dans where, ni dans order by et ni dans group by,</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> . on applique une fonction sur la colonne indexée :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span> <span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Ex : SELECT *</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> FROM Client</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> WHERE UPPER(Nom_cli) LIKE 'P%';</span></p> </div> <p class="MsoNormal" style='margin-top:6.0pt'><span lang="EN-GB" style='font-size: 11.0pt' xml:lang= "EN-GB"> </span> <span style='font-size:11.0pt'>. on teste l'inégalité entre la colonne indexée et une valeur :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span><span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB">Ex : SELECT *</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> FROM Client</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> WHERE Nom_cli != 'Dupont';</span></p> </div> <p class="MsoNormal" style='margin-top:6.0pt'><span lang="EN-GB" style='font-size: 11.0pt' xml:lang= "EN-GB"> </span> <span style='font-size:11.0pt'>. on compare la colonne indexée avec un modèle de chaîne dont on ne connaît pas le début :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span> <span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Ex : SELECT *</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> FROM client</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> WHERE Nom_cli LIKE '_A%';</span></p> </div> <h3 style='margin-left:18.0pt;text-indent:-18.0pt'><a name= "_Toc318647670"></a><a name="_Toc317856592"></a><a name= "_Toc317855834"><span style= 'font-size:8.0pt; font-family:Wingdings;font-weight:normal'>q<span style='font:7.0pt "Times New Roman"'> </span></span> <u><span style='font-size:14.0pt'>Inconvénients des index</span></u></a></h3> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>L'insertion ou la suppression dans une table indexée de même que la modification de la valeur d'une colonne indexée va entraîner une mise à jour de l'espace d'index.</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>Les opérations DELETE et UPDATE sont très souvent précédées par une recherche. Dans ce cas, l'index fait gagner du temps lors de la recherche et en fait perdre lors de l'opération proprement dite de modification ou suppression. Donc, globalement, l'index n'est pas vraiment pénalisant.</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>L'opération INSERT ne fait pas de recherche préalable : d'éventuels index ne peuvent être que pénalisants.</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>Dans les cas de saisie massive initiale d'une table, on recommande de saisir la table d'abord et de créer les index après ; de cette façon, l'étape de saisie est plus rapide.</span></p> <p class="MsoNormal" style='margin-left:35.45pt'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:18.0pt;text-indent:-18.0pt'><b><span style= 'font-size:16.0pt'>2.<span style= 'font:7.0pt "Times New Roman"'> </span></span></b> <b><u><span style='font-size:16.0pt'>Les vues</span></u></b></p> <p class="MsoHeader"><b> </b></p> <p class="MsoNormal" style= 'margin-top:0cm;margin-right:0cm;margin-bottom:6.0pt; margin-left:17.85pt;text-indent:-17.85pt'> <span style='font-size:8.0pt; font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Définition</span></u></b></p> <p class="MsoBodyText"><span style='font-size:11.0pt'>Les vues sont des tables virtuelles créées à partir de requêtes SQL. Seule la définition de la vue (c'est à dire le texte de la requête) est stockée, l'ensemble de tuples correspondant n'est pas stocké contrairement aux tuples d'une table.</span></p> <p class="MsoBodyText"><span style='font-size:11.0pt'>Une vue dans un SGBDR est assimilable à la notion de requête sous Access mais offre plus de possibilités qu'une requête Access notamment au niveau de l'attribution des privilèges.</span></p> <p class="MsoNormal"> </p> <p class="MsoNormal" style= 'margin-top:0cm;margin-right:0cm;margin-bottom:6.0pt; margin-left:17.85pt;text-indent:-17.85pt'> <span style='font-size:8.0pt; font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Création d'une vue</span></u></b></p> <p class="MsoNormal"><i>CREATE VIEW NomVue [(NomColonne1Vue [,NomColonne2Vue…])</i> <i><span lang="EN-GB" xml:lang= "EN-GB">]</span></i></p> <p class="MsoNormal"><i><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">AS</span></i></p> <p class="MsoNormal"><i><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Requête</span></i></p> <p class="MsoNormal"><i><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">[WITH CHECK OPTION] ou sous MySql [WITH LOCAL CHECK OPTION]</span></i></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">Ex 1: CREATE VIEW V_CLI_PAR</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> AS</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> SELECT *</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> FROM Client</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> WHERE Vil_cli = 'PARIS';</span></p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>Pour utiliser la vue afin de consulter les clients parisiens, il suffit d'écrire :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span> <span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">SELECT *</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> FROM V_CLI_PAR;</span></p> </div> <p class="MsoNormal"><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> </span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> </span><span style='font-size:11.0pt'>Ex2 : CREATE VIEW V_CLI_VILLE (V_ville, V_nb)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span> <span lang="EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB">AS</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB"> SELECT Vil_cli, count(*)</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> FROM Client</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang= "EN-GB"> GROUP BY Vil_cli;</span></p> </div> <p class="MsoNormal"><b><i><u><span style= 'font-size:11.0pt'>Remarques :</span></u></i></b></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>. On n’est pas obligé de spécifier les noms des colonnes de la vue si la liste de sélection de la requête ne comporte ni expression non nommée, ni fonction non nommée, ni colonnes en double dans le select (ex : client.code et produit.code) et non renommées. Si on ne spécifie <b>pas de nom</b>, les <b>colonnes de la vue</b> héritent des <b>noms des champs indiqués dans la liste de sélection</b> (sans le nom de la table en préfixe). Il faut soit ne préciser aucun des noms de colonnes de la vue, soit les préciser tous.</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>. La clause <b>ORDER BY</b> est <b>interdite</b> dans la requête de création de la vue.</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>. La clause <b>WITH CHECK OPTION (</b>ou <b>WITH LOCAL CHECK OPTION</b> sous MySql) permet de garantir que les <b>tuples insérés ou modifiés au travers de la vue sont compatibles avec la définition de la vue</b>. Bien sûr, cette clause n'a de sens que si l'insertion ou la modification est possible.</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>. Toutes les <b>requêtes de consultation</b> sont <b>autorisées</b> sur la vue.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style= 'margin-left:18.0pt;text-indent:-18.0pt'><span style= 'font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Intérêt des vues </span></u></b></p> <p class="MsoNormal"><span style='font-size:11.0pt'>. Une vue permet d'assurer la confidentialité de certaines données : avec les vues, il est possible de mettre uniquement certaines colonnes et/ou certaines lignes à disposition de l'utilisateur.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>. Une vue peut également être utilisée pour simplifier la formulation de requêtes pour les utilisateurs (le texte de la requête complexe est défini dans la vue).</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><b><i><span style= 'font-size:11.0pt'>Ex1</span></i></b><i><span style= 'font-size:11.0pt'> : la vue V_CLI_PA2 sur la table des clients ne délivre que certaines informations (numéro de client, nom du client et numéro de téléphone) relatives aux clients parisiens.</span></i></p> <div style= 'border:solid windowtext 1.0pt;padding:0cm 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE VIEW V_CLI_PA2</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>AS</span></p> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'>. . .</span></b></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> </span></p> </div> <p class="MsoNormal"> </p> <p class="MsoNormal">TABLE CLIENT :</p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" width="647" style= 'width:485.45pt;border-collapse:collapse;border:none'> <tr> <td width="86" valign="top" style= 'width:64.7pt;border:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b>Num_cli</b></p> </td> <td width="86" valign="top" style= 'width:64.65pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b>Nom_cli</b></p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b><span lang="DE" xml:lang= "DE">Pre_cli</span></b></p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b><span lang="DE" xml:lang= "DE">Adr_cli</span></b></p> </td> <td width="64" valign="top" style= 'width:48.15pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b><span lang="DE" xml:lang= "DE">Cdp_cli</span></b></p> </td> <td width="69" valign="top" style= 'width:51.95pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b>Vil_cli</b></p> </td> <td width="91" valign="top" style= 'width:68.2pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b>Tel_cli</b></p> </td> </tr> <tr> <td width="86" valign="top" style= 'width:64.7pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1</p> </td> <td width="86" valign="top" style= 'width:64.65pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Dupont</p> </td> <td width="125" valign="top" style= 'width:93.9pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Alexandre</p> </td> <td width="125" valign="top" style= 'width:93.9pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">69 rue de Turbigo</p> </td> <td width="64" valign="top" style= 'width:48.15pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">75003</p> </td> <td width="69" valign="top" style= 'width:51.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">PARIS</p> </td> <td width="91" valign="top" style= 'width:68.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">0143211234</p> </td> </tr> <tr> <td width="86" valign="top" style= 'width:64.7pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">2</p> </td> <td width="86" valign="top" style= 'width:64.65pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Armand</p> </td> <td width="125" valign="top" style= 'width:93.9pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Thomas</p> </td> <td width="125" valign="top" style= 'width:93.9pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">05 rue Royale</p> </td> <td width="64" valign="top" style= 'width:48.15pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">75002</p> </td> <td width="69" valign="top" style= 'width:51.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">PARIS</p> </td> <td width="91" valign="top" style= 'width:68.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">0144332211</p> </td> </tr> <tr> <td width="86" valign="top" style= 'width:64.7pt;border-top:none;border-left:solid windowtext 1.0pt; border-bottom:none;border-right:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">3</p> </td> <td width="86" valign="top" style= 'width:64.65pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Duval</p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Cathy</p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">14 rue des Petits Champs</p> </td> <td width="64" valign="top" style= 'width:48.15pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">75002</p> </td> <td width="69" valign="top" style= 'width:51.95pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">PARIS</p> </td> <td width="91" valign="top" style= 'width:68.2pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">0142622222</p> </td> </tr> <tr> <td width="86" valign="top" style= 'width:64.7pt;border:solid windowtext 1.0pt; background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>4</s></p> </td> <td width="86" valign="top" style= 'width:64.65pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>Armand</s></p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>Chrystelle</s></p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>06 rue de France</s></p> </td> <td width="64" valign="top" style= 'width:48.15pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>21000</s></p> </td> <td width="69" valign="top" style= 'width:51.95pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>DIJON</s></p> </td> <td width="91" valign="top" style= 'width:68.2pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>0180421312</s></p> </td> </tr> <tr> <td width="86" valign="top" style= 'width:64.7pt;border-top:none;border-left:solid windowtext 1.0pt; border-bottom:none;border-right:solid windowtext 1.0pt;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">5</p> </td> <td width="86" valign="top" style= 'width:64.65pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Dupont</p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Jean</p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">10 rue de Conte</p> </td> <td width="64" valign="top" style= 'width:48.15pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">75003</p> </td> <td width="69" valign="top" style= 'width:51.95pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">PARIS</p> </td> <td width="91" valign="top" style= 'width:68.2pt;border:none;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">0143211235</p> </td> </tr> <tr> <td width="86" valign="top" style= 'width:64.7pt;border:solid windowtext 1.0pt; background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>6</s></p> </td> <td width="86" valign="top" style= 'width:64.65pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>Petitjean</s></p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>Serge</s></p> </td> <td width="125" valign="top" style= 'width:93.9pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>25 rue de Turin</s></p> </td> <td width="64" valign="top" style= 'width:48.15pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>80000</s></p> </td> <td width="69" valign="top" style= 'width:51.95pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>AMIENS</s></p> </td> <td width="91" valign="top" style= 'width:68.2pt;border:solid windowtext 1.0pt; border-left:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>NULL</s></p> </td> </tr> <tr> <td width="86" valign="top" style= 'width:64.7pt;border:solid windowtext 1.0pt; border-top:none;background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>7</s></p> </td> <td width="86" valign="top" style= 'width:64.65pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>Durand</s></p> </td> <td width="125" valign="top" style= 'width:93.9pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>Arthur</s></p> </td> <td width="125" valign="top" style= 'width:93.9pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>12 rue de la Paix</s></p> </td> <td width="64" valign="top" style= 'width:48.15pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>92000</s></p> </td> <td width="69" valign="top" style= 'width:51.95pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>NEUILLY</s></p> </td> <td width="91" valign="top" style= 'width:68.2pt;border-top:none;border-left:none; border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; background:#CACACA;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><s>0149999555</s></p> </td> </tr> </table> <p class="MsoNormal"><span style= 'position:absolute;z-index:11;left:0px; margin-left:268px;margin-top:0px;width:328px;height:46px'> <img width="328" height="46" src= "BD_DA_Cours_Atelier2_fichiers/image008.gif" alt= "image" /></span><span style= 'position:absolute;z-index:10;left:0px;margin-left:130px;margin-top:0px; width:12px;height:32px'><img width="12" height="32" src="BD_DA_Cours_Atelier2_fichiers/image009.gif" alt= "image" /></span><span style= 'position: absolute;z-index:9;left:0px;margin-left:34px;margin-top:0px;width:12px; height:32px'><img width="12" height="32" src="BD_DA_Cours_Atelier2_fichiers/image009.gif" alt= "image" /></span></p> <p class="MsoNormal"> </p> <p class="MsoNormal">VUE V_CLI_PA2 :</p> <table class="MsoNormalTable" border="1" cellspacing="0" cellpadding="0" style='border-collapse:collapse;border:none'> <tr> <td width="102" valign="top" style= 'width:76.75pt;border:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b>Num_cli</b></p> </td> <td width="102" valign="top" style= 'width:76.75pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b>Nom_cli</b></p> </td> <td width="102" valign="top" style= 'width:76.75pt;border:solid windowtext 1.0pt; border-left:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal"><b>Tel_cli</b></p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">1</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Dupont</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">0143211234</p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">2</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Armand</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">0144332211</p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">3</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Duval</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">0142622222</p> </td> </tr> <tr> <td width="102" valign="top" style= 'width:76.75pt;border:solid windowtext 1.0pt; border-top:none;padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">5</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">Dupont</p> </td> <td width="102" valign="top" style= 'width:76.75pt;border-top:none;border-left: none;border-bottom:solid windowtext 1.0pt;border-right:solid windowtext 1.0pt; padding:0cm 3.5pt 0cm 3.5pt'> <p class="MsoNormal">0143211235</p> </td> </tr> </table> <p class="MsoBlockText" style= 'margin-top:6.0pt;margin-right:14.2pt;margin-bottom: 0cm;margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <span style= 'font-size:11.0pt;font-family:Wingdings;font-style:normal'>ð<span style='font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>on effectue ici une projection et une sélection.</span></p> <p class="MsoBlockText" style= 'margin-top:6.0pt;margin-right:14.2pt;margin-bottom: 0cm;margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <span style= 'font-size:11.0pt;font-family:Wingdings;font-style:normal'>ð<span style='font:7.0pt "Times New Roman"'> </span></span> <span style='font-size:11.0pt'>la manipulation de la vue V_CLI_PA2 permet de masquer les lignes des clients 4, 6 et 7 et certaines colonnes pour les clients "visibles".</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style= 'margin-top:6.0pt;border:none;padding:0cm'><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">SELECT *</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">FROM V_CLI_PA2 ;</span></p> </div> <p class="MsoNormal" style='margin-top:6.0pt'><i><span style= 'font-size:11.0pt'>Résultat :</span></i></p> <p class="MsoNormal">NUM_CLI NOM_CLI TEL_CLI</p> <p class="MsoNormal">--------- -------------------- --------------------</p> <p class="MsoNormal"> 1 Dupont 0143211234</p> <p class="MsoNormal"> 2 Armand 0144332211</p> <p class="MsoNormal"> 3 Duval 0142622222</p> <p class="MsoNormal"> 5 Dupont 0143211235</p> <p class="MsoNormal"><i><span style= 'font-size:11.0pt'> </span></i></p> <p class="MsoNormal" style='margin-bottom:3.0pt'><b><i><span style= 'font-size: 11.0pt'>Ex2</span></i></b><i><span style= 'font-size:11.0pt'> : la vue V_COMM permet de simplifier la formulation d’une requête complexe pour les utilisateurs.</span></i></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Commande (<u>num_com</u>, dat_com, #num_cli)</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Article (<u>num_art</u>, des_art)</span></p> <p class="MsoNormal" style='margin-bottom:6.0pt'><span style= 'font-size:11.0pt'>Lg_comm <u>(#num_com, #num_art</u>, qte_lig)</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><b><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">CREATE VIEW V_COMM</span></b></p> <p class="MsoHeading7"><span lang="EN-GB" xml:lang= "EN-GB">AS</span></p> <p class="MsoBodyText" style='border:none;padding:0cm'> <b><span lang="EN-GB" style= 'font-size:11.0pt;text-transform:uppercase' xml:lang= "EN-GB">select</span></b> <span lang="EN-GB" style= 'font-size:11.0pt' xml:lang="EN-GB">C.num_com, C.dat_com, L.num_art, A.des_art, L.qte_lig</span></p> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span lang="EN-GB" style= 'font-size:11.0pt' xml:lang="EN-GB">FROM <b>Commande C, Lg_comm L, Article A</b></span></p> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span lang="EN-GB" style= 'font-size:11.0pt' xml:lang="EN-GB">WHERE C.num_com = L.num_com</span></p> <p class="MsoNormal" align="left" style= 'text-align:left;border:none;padding:0cm'><span style= 'font-size:11.0pt'>AND L.num_art = A.num_art ;</span></p> </div> <p class="MsoNormal" style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:0cm; margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <span style='font-size:11.0pt;font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <i><span style='font-size:11.0pt'>trois tables sont en jeu : LG_COMM, COMMANDE et ARTICLE,</span></i></p> <p class="MsoNormal" style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:0cm; margin-left:17.85pt;margin-bottom:.0001pt;text-indent:-17.85pt'> <span style='font-size:11.0pt;font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <i><span style='font-size:11.0pt'>on effectue une projection et des jointures</span></i></p> <p class="MsoNormal" style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:6.0pt; margin-left:17.85pt;text-indent:-17.85pt'> <span style='font-size:11.0pt; font-family:Wingdings'>ð<span style= 'font:7.0pt "Times New Roman"'> </span></span> <i><span style='font-size:11.0pt'>une fois la vue créée, le résultat est obtenu simplement à partir de la requête suivante :</span></i></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">SELECT *</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">FROM V_COMM ;</span></p> </div> <p class="MsoNormal"><span lang="EN-GB" xml:lang= "EN-GB"> </span></p> <p class="MsoNormal" style= 'margin-left:18.0pt;text-indent:-18.0pt'><span style= 'font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Mise à jour (modification, insertion, suppression) à travers une vue</span></u></b></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Il n'est pas possible d'effectuer des mises à jour à partir de n'importe quelle vue.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Si la définition de la vue comporte dans le SELECT de 1<sup>er</sup> niveau :</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> . plusieurs tables, </span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'>OU . une expression,</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'>OU . une fonction,</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'>OU .</span> <span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">un GROUP BY,</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'>OU . un UNION,</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>La mise à jour à travers la vue est alors interdite. On dit que la vue n'est pas modifiable.</span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Il sera également impossible de faire des insertions à travers la vue si un attribut déclaré NOT NULL dans la structure de la table, n'est pas repris dans la vue.</span></p> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>Peut-on faire des modifications à travers V_CLI_PAR ? <b>. . .</b></span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Peut-on faire des modifications à travers V_CLI_VILLE ? <b>. . .</b></span></p> <p class="MsoNormal" style= 'margin-top:6.0pt;margin-right:0cm;margin-bottom:3.0pt; margin-left:0cm'> <u><span style='font-size:11.0pt'>Ex1 : modification à travers V_CLI_PAR :</span></u></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>UPDATE V_CLI_PAR</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">SET Tel_cli = 0142123456</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">WHERE Num_cli = 1;</span></p> </div> <p class="MsoNormal" style='margin-top:6.0pt'><span style= 'font-size:11.0pt'>Si la vue V_CLI_PAR avait été définie avec la clause WITH CHECK OPTION, la modification aurait également été acceptée.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal" style='margin-bottom:3.0pt'><u><span style= 'font-size:11.0pt'>Ex2 : insertion d'un nouveau client à partir de V_CLI_PAR : </span></u></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">INSERT INTO V_CLI_PAR VALUES</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>(8, ‘Portier’, 'Nicolas', ’3 rue de Lille’, 21000, ‘DIJON’, NULL) ;</span></p> <p class="MsoNormal" style= 'text-indent:35.4pt;border:none;padding:0cm'>=> <b>1 ligne créée</b>.</p> </div> <p class="MsoNormal"><span style='font-size:11.0pt'>L'insertion est effectuée. En revanche, la nouvelle ligne ne sera pas visible à partir de V_CLI_PAR.</span></p> <p class="MsoNormal"><span style= 'font-size:11.0pt'> </span></p> <p class="MsoNormal"><span style='font-size:11.0pt'>Si la vue V_CLI_PAR avait été définie avec la clause WITH CHECK OPTION, l'insertion n'aurait pas été acceptée :</span></p> <div style= 'border:solid windowtext 1.0pt;padding:1.0pt 4.0pt 1.0pt 4.0pt'> <p class="MsoNormal" style='border:none;padding:0cm'><span lang= "EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">INSERT INTO V_CLI_PAR VALUES</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'>(8, ‘Portier’, 'Nicolas', ’3 rue de Lille’, 21000, ‘DIJON’, NULL) ;</span></p> <p class="MsoNormal" style='border:none;padding:0cm'><span style= 'font-size:11.0pt'> => <b><i>message d’erreur :</i></b></span></p> <p class="MsoNormal" style='border:none;padding:0cm'> <b><span style='font-size: 11.0pt'>ORA-01402: vue WITH CHECK OPTION - violation de clause WHERE</span></b></p> </div> <p class="MsoNormal"> </p> <p class="MsoNormal"> </p> <p class="MsoNormal" style= 'margin-left:18.0pt;text-indent:-18.0pt'><span style= 'font-size:8.0pt;font-family:Wingdings'>q<span style= 'font:7.0pt "Times New Roman"'> </span></span> <b><u><span style='font-size:14.0pt'>Suppression d'une vue</span></u></b></p> <p class="MsoNormal"> </p> <p class="MsoNormal"><span style='font-size:11.0pt'>Une vue ne peut pas être modifiée : elle doit être supprimée puis recréée.</span></p> <p class="MsoNormal"> </p> <p class="MsoNormal"><i><span lang="EN-GB" style='font-size:11.0pt' xml:lang="EN-GB">DROP VIEW NomVue ;</span></i></p> <p class="MsoHeader"><b><span lang="EN-GB" xml:lang= "EN-GB"> </span></b></p> </div>
Table des matières
Sujets
Recherche
DONNEES INDEX
Identifiant de la ligne Identifiant de la ligne
q Les colonnes à ne pas indexer
q Cas où l'index n'est pas utilisé
q Inconvénients des index
DONNEES INDEX
Identifiant de la ligne Identifiant de la ligne
q Cas où l'index n'est pas utilisé
q Inconvénients des index
q Les colonnes à ne pas indexer
Générée avec
chmProcessor
Générée avec
chmProcessor