{"action":"closed","issue":{"url":"https://api.github.com/repos/yiisoft/yii2/issues/11428","repository_url":"https://api.github.com/repos/yiisoft/yii2","labels_url":"https://api.github.com/repos/yiisoft/yii2/issues/11428/labels{/name}","comments_url":"https://api.github.com/repos/yiisoft/yii2/issues/11428/comments","events_url":"https://api.github.com/repos/yiisoft/yii2/issues/11428/events","html_url":"https://github.com/yiisoft/yii2/issues/11428","id":151307144,"number":11428,"title":"Oracle schema.php / function findColumns","user":{"login":"SSiwek","id":1586351,"avatar_url":"https://avatars.githubusercontent.com/u/1586351?v=3","gravatar_id":"","url":"https://api.github.com/users/SSiwek","html_url":"https://github.com/SSiwek","followers_url":"https://api.github.com/users/SSiwek/followers","following_url":"https://api.github.com/users/SSiwek/following{/other_user}","gists_url":"https://api.github.com/users/SSiwek/gists{/gist_id}","starred_url":"https://api.github.com/users/SSiwek/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SSiwek/subscriptions","organizations_url":"https://api.github.com/users/SSiwek/orgs","repos_url":"https://api.github.com/users/SSiwek/repos","events_url":"https://api.github.com/users/SSiwek/events{/privacy}","received_events_url":"https://api.github.com/users/SSiwek/received_events","type":"User","site_admin":false},"labels":[{"url":"https://api.github.com/repos/yiisoft/yii2/labels/feature:db","name":"feature:db","color":"bfd4f2"},{"url":"https://api.github.com/repos/yiisoft/yii2/labels/Oracle","name":"Oracle","color":"006b75"},{"url":"https://api.github.com/repos/yiisoft/yii2/labels/type:enhancement","name":"type:enhancement","color":"d7e102"}],"state":"closed","locked":false,"assignee":{"login":"SilverFire","id":4499203,"avatar_url":"https://avatars.githubusercontent.com/u/4499203?v=3","gravatar_id":"","url":"https://api.github.com/users/SilverFire","html_url":"https://github.com/SilverFire","followers_url":"https://api.github.com/users/SilverFire/followers","following_url":"https://api.github.com/users/SilverFire/following{/other_user}","gists_url":"https://api.github.com/users/SilverFire/gists{/gist_id}","starred_url":"https://api.github.com/users/SilverFire/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/SilverFire/subscriptions","organizations_url":"https://api.github.com/users/SilverFire/orgs","repos_url":"https://api.github.com/users/SilverFire/repos","events_url":"https://api.github.com/users/SilverFire/events{/privacy}","received_events_url":"https://api.github.com/users/SilverFire/received_events","type":"User","site_admin":false},"milestone":{"url":"https://api.github.com/repos/yiisoft/yii2/milestones/17","html_url":"https://github.com/yiisoft/yii2/milestones/2.0.9","labels_url":"https://api.github.com/repos/yiisoft/yii2/milestones/17/labels","id":1580065,"number":17,"title":"2.0.9","description":"","creator":{"login":"cebe","id":189796,"avatar_url":"https://avatars.githubusercontent.com/u/189796?v=3","gravatar_id":"","url":"https://api.github.com/users/cebe","html_url":"https://github.com/cebe","followers_url":"https://api.github.com/users/cebe/followers","following_url":"https://api.github.com/users/cebe/following{/other_user}","gists_url":"https://api.github.com/users/cebe/gists{/gist_id}","starred_url":"https://api.github.com/users/cebe/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/cebe/subscriptions","organizations_url":"https://api.github.com/users/cebe/orgs","repos_url":"https://api.github.com/users/cebe/repos","events_url":"https://api.github.com/users/cebe/events{/privacy}","received_events_url":"https://api.github.com/users/cebe/received_events","type":"User","site_admin":false},"open_issues":102,"closed_issues":18,"state":"open","created_at":"2016-02-12T16:35:34Z","updated_at":"2016-05-01T12:54:09Z","due_on":"2016-06-25T21:00:00Z","closed_at":null},"comments":1,"created_at":"2016-04-27T06:14:21Z","updated_at":"2016-05-01T12:54:09Z","closed_at":"2016-05-01T12:54:09Z","body":"Hi,\r\nagain please be lenient with my English.\r\n\r\nThe select used in this function is rather slow  on the DB's I can connect to, \r\n\r\nSo I looked at it and eventually I found at last a little speed up.\r\nThe main timeconsumer in this select is the inner select to retrieve attribute \"Key\".\r\nMaybe one should get this attribut in an extra select.\r\n\r\nFor now I modified the select as followed( speed up by factor 10, if repeatedly called )\r\n\r\n```sql\r\n        $sql = <<<SQL\r\nSELECT a.column_name, a.data_type, a.data_precision, a.data_scale, a.data_length,\r\n    a.nullable, a.data_default,\r\n    (   SELECT D.constraint_type\r\n        FROM ALL_CONS_COLUMNS C\r\n        inner join ALL_constraints D on D.OWNER = C.OWNER  and d.TABLE_NAME=c.TABLE_NAME and D.constraint_name = C.constraint_name\r\n        WHERE C.OWNER = A.OWNER\r\n           and C.table_name = A.table_name\r\n           and C.column_name = A.column_name\r\n           and D.constraint_type = 'P') as Key,\r\n    com.comments as column_comment\r\nFROM ALL_TAB_COLUMNS A\r\ninner join ALL_OBJECTS B ON b.owner = a.owner and ltrim(B.OBJECT_NAME) = ltrim(A.TABLE_NAME)\r\nLEFT JOIN all_col_comments com ON (A.owner = com.owner AND A.table_name = com.table_name AND A.column_name = com.column_name)\r\nWHERE\r\n    a.owner = :schemaName\r\n    and b.object_type IN ('TABLE', 'VIEW', 'MATERIALIZED VIEW')\r\n    and b.object_name = :tableName\r\nORDER by a.column_id\r\nSQL;\r\n```\r\n\r\nSven"}}