| Subject: | statistics_info() missing |
Not often used, but in portable DBI scripts the only way to
get more information about keys/indexes.
Attached is an implementation for statistics_info().
There is room for improvements, e.g. for INDEX TYPE,
but the beginning has been made.
| Subject: | Oracle.pm.diff |
--- Oracle.166 Fri Aug 23 18:59:32 2013
+++ Oracle.pm Mon Aug 26 10:57:58 2013
@@ -784,6 +784,61 @@
$sth;
}
+ sub statistics_info {
+ my($dbh, $catalog, $schema, $table, $unique_only, $quick) = @_;
+ if (ref $catalog eq 'HASH') {
+ ($schema, $table) = @$catalog{'TABLE_SCHEM','TABLE_NAME'};
+ $catalog = undef;
+ }
+ my $SQL = <<'SQL';
+SELECT *
+ FROM
+(
+ SELECT NULL TABLE_CAT
+ , t.OWNER TABLE_SCHEM
+ , t.TABLE_NAME TABLE_NAME
+ , to_number( NULL ) NON_UNIQUE
+ , NULL INDEX_QUALIFIER
+ , NULL INDEX_NAME
+ ,'table' TYPE
+ , to_number( NULL ) ORDINAL_POSITION
+ , NULL COLUMN_NAME
+ , NULL ASC_OR_DESC
+ , t.NUM_ROWS CARDINALITY
+ , t.BLOCKS PAGES
+ , NULL FILTER_CONDITION
+ FROM ALL_TABLES t
+ UNION
+ SELECT NULL TABLE_CAT
+ , t.OWNER TABLE_SCHEM
+ , t.TABLE_NAME TABLE_NAME
+ , decode( t.UNIQUENESS,'UNIQUE', 0, 1 ) NON_UNIQUE
+ , c.INDEX_OWNER INDEX_QUALIFIER
+ , c.INDEX_NAME INDEX_NAME
+ , decode( t.INDEX_TYPE,'NORMAL','btree','CLUSTER','clustered','other') TYPE
+ , c.COLUMN_POSITION ORDINAL_POSITION
+ , c.COLUMN_NAME COLUMN_NAME
+ , decode( c.DESCEND,'ASC','A','DESC','D') ASC_OR_DESC
+ , t.DISTINCT_KEYS CARDINALITY
+ , t.LEAF_BLOCKS PAGES
+ , NULL FILTER_CONDITION
+ FROM ALL_INDEXES t
+ , ALL_IND_COLUMNS c
+ WHERE t.OWNER = c.INDEX_OWNER
+ AND t.INDEX_NAME = c.INDEX_NAME
+ AND t.TABLE_OWNER = c.TABLE_OWNER
+ AND t.TABLE_NAME = c.TABLE_NAME
+ AND t.UNIQUENESS LIKE :3
+)
+ WHERE TABLE_SCHEM = :1
+ AND TABLE_NAME = :2
+ ORDER BY NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, ORDINAL_POSITION
+SQL
+ my $sth = $dbh->prepare($SQL) or return undef;
+ $sth->execute($schema, $table, $unique_only ?'UNIQUE':'%') or return undef;
+ $sth;
+ }
+
sub type_info_all {
my ($dbh) = @_;
my $version = ( ora_server_version($dbh)->[0] < DBD::Oracle::ORA_OCI() )
@@ -2567,6 +2622,40 @@
alter table bloggind add (Bla_BLA NUMBER)
any case can be used to get info on the column.
+
+=head2 B<statistics_info()>
+
+Oracle does not support catalogues so TABLE_CAT is ignored as
+selection criterion.
+The TABLE_CAT field of a fetched row is always NULL (undef).
+See L</table_info()> for more detailed information.
+
+The INDEX_QUALIFIER field of a fetched row is always NULL (undef),
+for the same reason as for TABLE_CAT.
+
+If an index was created without an identifier
+(e.g. in the course of a PK creation),
+INDEX_NAME contains a system generated name with the form SYS_.
+
+COLUMN_NAME may contain a system generated name
+(e.g. for function-based indexes).
+
+For the TYPE column, a simple mapping is used:
+
+ NORMAL btree
+ CLUSTER clustered
+ ... other
+
+The C<$quick> parameter is currently ignored.
+The method uses the dictionary with the gathered statistics,
+thus cannot ensure that the values for CARDINALITY and PAGES are current.
+
+The result set is ordered by
+NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, ORDINAL_POSITION.
+
+An identifier is passed I<as is>, i.e. as the user provides or
+Oracle returns it.
+See L</table_info()> for more detailed information.
=head2 B<selectrow_array>