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;
}
} |
plan d'execution oracle sur java
Répondre avec citation
Partager