Slow Query.Open in oracle environment with dozen of schemas ( owners )
Original Reporter info from Mantis: clicio
-
Reporter name: Carlos Clicio
Original Reporter info from Mantis: clicio
- Reporter name: Carlos Clicio
Description:
The routine that gets the indexes of a table in an oracle database is not optimzed when the oracle instance has several schemas in it.
The query below , used in the routine procedure TOracleConnection.UpdateIndexDefs ( line 969 ) of fpc-2.7.1/packages/fcl-db/src/sqldb/oracle/oracleconnection.pp, takes a long long time. IMHO the problem is not restricting the owner of the index.
sql.add('SELECT '+
'i.INDEX_NAME, '+
'c.COLUMN_NAME, '+
'p.CONSTRAINT_TYPE '+
'FROM ALL_INDEXES i, ALL_IND_COLUMNS c,ALL_CONSTRAINTS p '+
'WHERE '+
'i.OWNER=c.INDEX_OWNER AND '+
'i.INDEX_NAME=c.INDEX_NAME AND '+
'p.INDEX_NAME(+)=i.INDEX_NAME AND '+
'Upper(c.TABLE_NAME) = ''' + UpperCase(TableName) +''' '+
'ORDER by i.INDEX_NAME,c.COLUMN_POSITION');
I hava a sugestion to optimize it as shown bellow , that is using the table USER_ instead of ALL_ :
sql.add('SELECT '+
'i.INDEX_NAME, '+
'c.COLUMN_NAME, '+
'p.CONSTRAINT_TYPE '+
'FROM USER_INDEXES i, USER_IND_COLUMNS c,USER_CONSTRAINTS p '+
'WHERE '+
'i.INDEX_NAME=c.INDEX_NAME AND '+
'p.INDEX_NAME(+)=i.INDEX_NAME AND '+
'c.TABLE_NAME = ''' + UpperCase(TableName) +''' '+
'ORDER by i.INDEX_NAME,c.COLUMN_POSITION');
I have also removed the upper from table name , because as i can see oracle stores all names in upcase.
I Hope that I can help to make a better product.
Steps to reproduce:
create 50 schemas and in each one create 50 table with 5 index for instance.
make a simple programs as bellow:
Conn.Transaction := Tran;
Q.DataBase := Conn;
Conn.Open;
Tran.Active := true;
Q.SQL.Text := 'SELECT AFIELD FROM ATABLE';
Q.Open; // takes a long time to identify ATABLE indexes
Q.First;
while not (Q.EOF) do
begin
writeln('*****************');
for Col := 0 to Q.Fields.Count - 1 do
begin
try
writeln(Q.Fields[Col].DisplayLabel + ':');
writeln(Q.Fields[Col].AsString);
except
writeln('Error retrieving field ', Col);
end;
end;
Q.Next;
end;
Q.Close;
Mantis conversion info:
- Mantis ID: 25247
- OS: Linux Debian
- OS Build: 6
- Platform: Intel
- Version: 2.6.2
- Fixed in version: 3.0.0
- Fixed in revision: 28223 (#0ebd3318)
- Target version: 3.0.0