1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163
| // pour le traitement de noeuds:
import java.sql.*;
import java.util.Arrays;
import java.util.Objects;
class traitementNoeuds {
// on met les requêtes dans un tableau
public static String[] mesReq= {"SELECT sum(lo_extendedprice*lo_discount) as revenue from lineorder,"
+ " dates where lo_orderdate = d_datekey and d_year = 1997 and lo_discount "
+ "between 1 and 3 and lo_quantity < 24",
"select sum(lo_revenue), d_year, p_name from lineorder, dates, part, supplier"
+ " where lo_orderdate = d_datekey and lo_partkey = p_partkey and lo_suppkey = s_suppkey "
+ "and p_category = 'MFGR#12' and s_region = 'ASIA' group by d_year, p_name order by d_year, p_name",
"select c_city, s_city, d_year, sum(lo_revenue) as revenue "
+ "from customer, lineorder, supplier, dates where lo_custkey = c_custkey "
+ "and lo_suppkey = s_suppkey and lo_orderdate = d_datekey and (c_city='UNITED KI1' "
+ "or c_city='UNITED KI5') and (s_city='UNITED KI1' or s_city='UNITED KI5') "
+ "and d_yearmonth = 'Dec1997' group by c_city, s_city, d_year order by d_year asc, revenue desc ",
"select c_city, s_city, d_year, sum(lo_revenue) as revenue "
+ "from customer, lineorder, supplier, dates where lo_custkey = c_custkey and lo_suppkey = s_suppkey "
+ "and lo_orderdate = d_datekey and c_nation = UNITED STATES and s_nation = UNITED STATES' "
+ "and d_year >= 1992 and d_year <= 1995 "
+ "group by c_city, s_city, d_year order by d_year asc, revenue desc"};
//pour les requetes:
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
class Requete {
private String sqlReq ;
private static String[] ensembleAtt=new String []{"ID","Depth","PARENT_ID","Operation",
"Statement_ID","Cost","Bytes","Cardinality","CPU_Cost","IO_Cost","Object_Name",
"Object_alias","ACCESS_PREDICATES"};
private final String[][] planExe , tablesKey;
public Requete(String sqlReq) throws SQLException {
this.sqlReq = sqlReq;
planExe=recupInfoExecution();
tablesKey = generateTablesKey();
}
public static String[] getEnsAtt() {
return ensembleAtt ;
}
public static String attributs (){
String s ;
if(ensembleAtt.length==0) return null;
s=ensembleAtt[0];
for (int i = 1; i < ensembleAtt.length; i++) {
s+=","+ensembleAtt[i];
}
return s ;
}
public static int getColumnId(String column){
int n=ensembleAtt.length;
for (int i = 0; i <n; i++) {
if(ensembleAtt[i].equalsIgnoreCase(column)) return i ;
}
return -1 ;
}
public String[][] getPlanExe() {
return planExe;
}
public String getSqlReq() {
return sqlReq;
}
public void setSqlReq(String sqlReq) {
this.sqlReq = sqlReq;
}
public int countTables()
{ int nbr=0;
for (int i = 0; i < planExe.length; i++) {
if(planExe[i][getColumnId("Operation")].equalsIgnoreCase("TABLE ACCESS")) nbr++ ;
}
return nbr;
}
private String[][] recupInfoExecution() throws SQLException
{
ResultSetMetaData resultMeta;
ResultSet monResultat;//=(ResultSet) resultaReq();
Statement statement ;
Connection maConnexion =ConnexionDataBase.getConnection();
statement = maConnexion.createStatement();
statement.executeUpdate("explain plan for "+sqlReq);
monResultat = statement.executeQuery("select count(*) from PLAN_TABLE");
monResultat.next();
int nbrLignes = monResultat.getInt(1);
monResultat = statement.executeQuery("select "+attributs()+" from PLAN_TABLE");
//On recupere les MetaData
resultMeta = monResultat.getMetaData();
//On affiche le nom des colonnes
int nbrCol=resultMeta.getColumnCount() ;
String[][] infoReq = new String[nbrLignes][nbrCol];
int ligne =0 ;
System.out.println("nbr lignes"+nbrLignes+" nbr col "+nbrCol);
while (monResultat.next()) {
for(int i = 1; i <= nbrCol; i++)
{
infoReq [ligne][i-1]= monResultat.getString(i);
System.out.print( monResultat.getString(i)+"\t |");}
System.out.println("");
ligne ++ ;
}
monResultat.close();
statement.close();
return infoReq;
}
private String[][] generateTablesKey() throws SQLException
{ String[][] matRec=new String[countTables()][3];
int nbTable= countTables();
/* 0 : "ID" 1 : "TableName" 2 : "Alias" */
int tableName = getColumnId("Object_Name");
int alias =getColumnId("Object_Alias");
System.out.println("Table : "+tableName+"\n Alias :"+alias);
int k =0;
for(int i=0;i<planExe.length; i++)
{
if(planExe[i][getColumnId("Operation")].equalsIgnoreCase("TABLE ACCESS")){ // id = j
matRec[k][0]= Integer.toString(i);
matRec[k][1]=planExe[i][tableName];
matRec[k][2]=planExe[i][alias].split("@")[0] ;
k++;
}
}
return matRec;
}
} |
Partager