#18082: get_indexes produces wrong resuls under oracle
----------------------------------------------+--------------------
Reporter: akaariai | Owner: nobody
Type: Bug | Status: new
Component: Database layer (models, ORM) | Version: 1.4
Severity: Normal | Keywords: oracle
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+--------------------
Currently oracle/introspection.py get_indexes uses this SQL:
{{{
SELECT LOWER(all_tab_cols.column_name) AS column_name,
CASE user_constraints.constraint_type
WHEN 'P' THEN 1 ELSE 0
END AS is_primary_key,
CASE user_indexes.uniqueness
WHEN 'UNIQUE' THEN 1 ELSE 0
END AS is_unique
FROM all_tab_cols, user_cons_columns, user_constraints,
user_ind_columns, user_indexes
WHERE all_tab_cols.column_name = user_cons_columns.column_name (+)
AND all_tab_cols.table_name = user_cons_columns.table_name (+)
AND user_cons_columns.constraint_name =
user_constraints.constraint_name (+)
AND user_constraints.constraint_type (+) = 'P'
AND user_ind_columns.column_name (+) = all_tab_cols.column_name
AND user_ind_columns.table_name (+) = all_tab_cols.table_name
AND user_indexes.uniqueness (+) = 'UNIQUE'
AND user_indexes.index_name (+) = user_ind_columns.index_name
AND all_tab_cols.table_name = UPPER(%s)
}}}
This SQL has two failings: first, it LEFT JOINs instead of INNER JOINs the
user_ind_columns, so every column will show as indexed. In addition the
results are ordering dependant, for example if we check AUTH_USER by that
SQL, the result is:
{{{
id 1 1
id 0 1
username 0 1
is_staff 0 0
is_staff 0 0
last_name 0 0
is_superuser 0 0
is_superuser 0 0
last_login 0 0
date_joined 0 0
first_name 0 0
email 0 0
is_active 0 0
is_active 0 0
password 0 0
}}}
So, if ID is a primary key is purely luck-based.
In addition the SQL is really slow, it takes around 0.9 seconds for single
table on my laptop. As the test suite has nearly 1000 tables, inspectdb
tests will take half an hour just executing this SQL.
I think one could get the same results by using the three following
queries:
{{{
select * from user_ind_columns where table_name = 'AUTH_GROUP';
select * from user_constraints where constraint_type = 'P' and table_name
= 'AUTH_GROUP';
select * from user_indexes where uniqueness = 'UNIQUE' and table_name =
'AUTH_GROUP';
}}}
and then doing the joins in Python. It seems this approach would also be
at least an order of magnitude faster.
--
Ticket URL: <https://code.djangoproject.com/ticket/18082>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
