| Subject: | Non-constraint Unique index |
| Date: | Mon, 10 Aug 2015 17:21:49 +0000 |
| To: | bug-Rose-DB-Object [...] rt.cpan.org |
| From: | Konstantin Tokar <konstantin [...] tokar.ru> |
Rose::DB::Object::Metadata::Auto::Oracle generates unique indexes only
for constraints :
use constant UNIQUE_INDEX_SQL => <<'EOF';
SELECT AI.INDEX_NAME FROM ALL_INDEXES AI, ALL_CONSTRAINTS AC
WHERE AI.INDEX_NAME = AC.CONSTRAINT_NAME AND
AC.CONSTRAINT_TYPE <> 'P' AND
AI.UNIQUENESS = 'UNIQUE' AND AI.TABLE_NAME = ? AND
AI.TABLE_OWNER = ?
Conventional unique indexes are not used.
This variant used both incexes (Perhaps UNION too redundant)
# This variant allows for non-constraint and constraint indexes
use constant UNIQUE_INDEX_SQL => <<'EOF';
SELECT DISTINCT
*
FROM
(
SELECT
AI.index_name AS INDEX_NAME
FROM
ALL_INDEXES AI
WHERE
AI.UNIQUENESS = 'UNIQUE'
AND AI.TABLE_NAME = ?
AND AI.TABLE_OWNER = ?
AND ai.index_name NOT IN
(
SELECT
AI.INDEX_NAME
FROM
ALL_INDEXES AI,
ALL_CONSTRAINTS AC
WHERE
AI.INDEX_NAME = AC.CONSTRAINT_NAME
AND AC.CONSTRAINT_TYPE = 'P'
AND AI.UNIQUENESS = 'UNIQUE'
AND AI.TABLE_NAME = ?
AND AI.TABLE_OWNER = ?)
UNION
SELECT
AI.INDEX_NAME AS INDEX_NAME
FROM
ALL_INDEXES AI,
ALL_CONSTRAINTS AC
WHERE
AI.INDEX_NAME = AC.CONSTRAINT_NAME
AND AC.CONSTRAINT_TYPE <> 'P'
AND AI.UNIQUENESS = 'UNIQUE'
AND AI.TABLE_NAME = ?
AND AI.TABLE_OWNER = ? )
EOF
...
my $sth = $dbh->prepare(UNIQUE_INDEX_SQL);
$sth->execute($table, $schema,$table, $schema,$table, $schema);
$sth->bind_columns(\$key_name);