Please, note. This article is entirely based on a work done by me colleague developer, who hasn't got account on this webpage and also hasn't got time for writing this article. I'm writing it for him. I will try to forward him any comments that may appear, but can't assure if/when he'll be able to answer. I'm far, far less experienced in Oracle therefore I won't probably be able to help myself.

Current situation ¶

An approach currently presented in COciSchema - i.e. basing on all_objects, ALL_TAB_COLUMNS and ALL_CONS_COLUMNS is probably correct solution, but very, very slow, when dealing with Oracle databases. Experiments proved that in some rare situations (very slow, not localhost server), schema analysing query based on above may run more than one minute (up to even twenty minutes in a very separate cases), which causes for example Gii to end up permanently with timeout error message, if PHP script execution time is to low. This situation was presented for example in this post.

This approach works very well in other RDBMS (like MySQL) as they are properly optimised for such queries. Unfortunately, fails on Oracle setup on a slow server.

Possible solution ¶

One of possible solutions is to introduce new set of tables that will hold information on tables and views used in a project. This is a kind of caching on database side.

The idea is following. We are taking original SQL query from original COciSchema, which is used to get tables schema. It is fired upon each AR use and since it is one of slowest queries in Oracle, it makes AR nearly unusable. Instead of calling this slow query every time, we are firing it only when real table schema changes. And instead of returning results, we are putting them into our set of tables, used for caching real tables schema. Then we change COciSchema so it reads tables schema from that so called technical-tables. This speeds up whole process significantly as Active Record is querying normal table, which is done very fast.

This looks pretty much like using schemaCachingDuration property, but has some advantages:

• in most situations, caching in database is faster than using caching component (see results at the end),
• using schemaCachingDuration not always works in Oracle - i.e. does not provides noticeable performance upgrade,
• on some slow servers using proposed solutions enables using Gii, while without it this tool is unusable (see here).

Building caching tables ¶

Here are an example SQL statements that builds up these tables:

[sql]
create table WWW_TAB_COLUMNS
(
  column_id    NUMBER(10) not null,
  table_name   VARCHAR2(50) not null,
  column_name  VARCHAR2(50) not null,
  data_type    VARCHAR2(100) not null,
  nullable     CHAR(1),
  data_default VARCHAR2(100),
  key          CHAR(1)
);
comment on column WWW_TAB_COLUMNS.nullable is 'Y/N';
comment on column WWW_TAB_COLUMNS.key is 'P/NULL';
alter table WWW_TAB_COLUMNS add constraint PK_WWW_TAB_COLUMNS primary
key (TABLE_NAME, COLUMN_NAME);

create table WWW_TABLES
(
  table_name VARCHAR2(50) not null
);
alter table WWW_TABLES add constraint PK_WWW_TABLES primary key (TABLE_NAME);

create table WWW_TAB_CONS
(
  table_name        VARCHAR2(50) not null,
  column_name       VARCHAR2(50) not null,
  position          NUMBER(10) not null,
  r_constraint_name VARCHAR2(50) not null,
  table_ref         VARCHAR2(50) not null,
  column_ref        VARCHAR2(200) not null
);
alter table WWW_TAB_CONS add constraint PK_WWW_TAB_CONS primary key
(TABLE_NAME, COLUMN_NAME);
alter table WWW_TAB_CONS add constraint FK_WWW_TAB_CONS foreign key
(TABLE_NAME) references WWW_TABLES (TABLE_NAME);

Feeding caching tables ¶

After creating proper tables and modifying COCiSchema class definition, you can insert any table schema into created set of tables. Here is an exemplary SQL statement for this. As it was stated eariler, it is pretty much the same as the one originally used in COciSchema.

SQL for retrieving columns data:

SELECT a.column_name, a.data_type ||
    case
        when data_precision is not null
            then '(' || a.data_precision ||
                    case when a.data_scale > 0 then ',' ||
a.data_scale else '' end
                || ')'
        when data_type = 'DATE' then ''
        else '(' || to_char(a.data_length) || ')'
    end as data_type,
    a.nullable, a.data_default,
    (   SELECT D.constraint_type
        FROM user_CONS_COLUMNS C
        inner join user_constraints D On D.constraint_name = C.constraint_name
        Where C.table_name = A.TABLE_NAME
           and C.column_name = A.column_name
           and D.constraint_type = 'P') as Key
FROM user_TAB_COLUMNS A
WHERE
    A.TABLE_NAME = 'NAZWA_TABELI'
ORDER by a.column_id

SQL for retrieving references:

SELECT D.constraint_type, C.COLUMN_NAME, C.position, D.r_constraint_name,
                E.table_name as table_ref, f.column_name as column_ref
        FROM ALL_CONS_COLUMNS C
        inner join ALL_constraints D on D.OWNER = C.OWNER and
D.constraint_name = C.constraint_name
        left join ALL_constraints E on E.OWNER = D.r_OWNER and
E.constraint_name = D.r_constraint_name
        left join ALL_cons_columns F on F.OWNER = E.OWNER and
F.constraint_name = E.constraint_name and F.position = c.position
        WHERE C.OWNER = '{$schemaName}'
           and C.table_name = '{$name}'
           and D.constraint_type = 'R'
        order by d.constraint_name, c.position

This step is essential as base of speeding up Active Record with presented solution is to change it, so it will read table schema from newly created set of tables, instead of querying Oracle each time for table schema (this is one of the slowest operations in Oracle). Therefore, any change in real table schema must be reflected in above set of tables.

Modifying COciSchema.php ¶

After filling set of tables with tables schema, you need to alter some parts of COciSchema class code, by either overwriting original one or creating own extension basing on it:

protected function findColumns($table)
{
	list($schemaName,$tableName) = $this->getSchemaTableName($table->name);
			
	
	$sql=<<<EOD
		SELECT Upper(COLUMN_NAME) as COLUMN_NAME, Upper(DATA_TYPE) as DATA_TYPE, NULLABLE, DATA_DEFAULT, KEY
		FROM www_tab_columns
		Where Upper(table_name) = Upper('{$tableName}')
		ORDER by column_id
EOD;
	
	$command=$this->getDbConnection()->createCommand($sql);

	if(($columns=$command->queryAll())===array()){			
		return false;
	}
	
	foreach($columns as $column)
	{			
		$c=$this->createColumn($column);			
		
		$table->columns[$c->name]=$c;
		if($c->isPrimaryKey)
		{
			if($table->primaryKey===null)
				$table->primaryKey=$c->name;
			else if(is_string($table->primaryKey))
				$table->primaryKey=array($table->primaryKey,$c->name);
			else
				$table->primaryKey[]=$c->name;
			/*if(strpos(strtolower($column['Extra']),'auto_increment')!==false)
				$table->sequenceName='';*/
		}
	}
	return true;
}

protected function findConstraints($table)
{
	$sql=<<<EOD
	SELECT upper(COLUMN_NAME) As COLUMN_NAME, upper(TABLE_REF) As TABLE_REF, upper(COLUMN_REF) As COLUMN_REF
	FROM WWW_TAB_CONS
     WHERE upper(TABLE_NAME) = upper('{$table->name}')
	Order By POSITION
EOD;
	$command=$this->getDbConnection()->createCommand($sql);
	foreach($command->queryAll() as $row)
	{
		$name = $row["COLUMN_NAME"];
		$table->foreignKeys[$name]=array($row["TABLE_REF"], $row["COLUMN_REF"]);
		if(isset($table->columns[$name]))
			$table->columns[$name]->isForeignKey=true;
	}
}

protected function findTableNames($schema='')
{	
	$sql='SELECT upper(table_name) as TABLE_NAME FROM www_tables';
	
	$command=$this->getDbConnection()->createCommand($sql);
	$rows=$command->queryAll();
	$names=array();
	foreach($rows as $row)
	{
		$names[]=$row['TABLE_NAME'];
	}
	return $names;
}

Final words ¶

This is one of possible solution. It was designed for our own project and therefore might not satisfy other developers needs or may need some slight changes. But, on the other hand, we were able to speed up Active Record ten times. For example selection of 300 records (160 columns) took 0,24 second, while before (on original Yii-build in COciSchema) the same query was taking around 2,45 second. Both test without using any caching component.