[Django] #27323: Oracle backend cleanup
Django
----------------------------------------------+----------------------------
Reporter: felixxm | Owner: felixxm
Type: Cleanup/optimization | Status: assigned
Component: Database layer (models, ORM) | Version: master
Severity: Normal | Keywords: Oracle
| sequences
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
Little optimization. Using `USER_SEQUENCES` instead of `USER_CATALOG` in
Oracle query which verify that sequence exists or not is more optimal.
--
Ticket URL: <https://code.djangoproject.com/ticket/27323>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: felixxm
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle sequences | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* needs_better_patch: => 0
* needs_docs: => 0
* needs_tests: => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:1>
Django
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: felixxm
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by felixxm):
* has_patch: 0 => 1
* stage: Unreviewed => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:2>
Django
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: felixxm
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Simon Charette):
* stage: Ready for checkin => Accepted
Comment:
Please don't mark your own patches as RFC, see
[https://docs.djangoproject.com/en/1.10/internals/contributing/triaging-
tickets/#how-can-i-help-with-triaging our ticket triaging doc's].
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:3>
Django
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: felixxm
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle sequences | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Simon Charette):
[https://github.com/django/django/pull/7357 PR]
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:4>
Django
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: felixxm
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle sequences | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by Tim Graham):
Could you explain why it's more optimal so we have that rationale
recorded?
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:5>
Django
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: felixxm
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle sequences | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by felixxm):
Replying to [comment:5 Tim Graham]:
> Could you explain why it's more optimal so we have that rationale
recorded?
Query with `USER_CATALOG` has a cost equal **3** and it has **2**
conditions in WHERE clause.
Query with `USER_SEQUENCES` has a cost equal **2** and it has **1**
condition in WHERE clause.
`USER_CATALOG` is quite complicate, it depends on the
`_CURRENT_EDITION_OBJ` and it contains all of the user objects from which
we are choosing sequences:
{{{#!sql
CREATE OR REPLACE FORCE VIEW "SYS"."_CURRENT_EDITION_OBJ" ("OBJ#",
"DATAOBJ#", "DEFINING_OWNER#", "NAME", "NAMESPACE", "SUBNAME", "TYPE#",
"CTIME", "MTIME", "STIME", "STATUS", "REMOTEOWNER", "LINKNAME", "FLAGS",
"OID$", "SPARE1", "SPARE2", "SPARE3", "SPARE4", "SPARE5", "SPARE6",
"OWNER#", "DEFINING_EDITION") AS
select
o."OBJ#",o."DATAOBJ#",o."OWNER#",o."NAME",o."NAMESPACE",o."SUBNAME",o."TYPE#",o."CTIME",o."MTIME",o."STIME",o."STATUS",o."REMOTEOWNER",o."LINKNAME",o."FLAGS",o."OID$",o."SPARE1",o."SPARE2",o."SPARE3",o."SPARE4",o."SPARE5",o."SPARE6",
o.spare3,
case when (o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87) or
bitand(u.spare1, 16) = 0) then
null
when (u.type# = 2) then
(select eo.name from obj$ eo where eo.obj# = u.spare2)
else
'ORA$BASE'
end
from obj$ o, user$ u
where o.owner# = u.user#
and ( /* non-versionable object */
( o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87,88)
or bitand(u.spare1, 16) = 0)
/* versionable object visible in current edition */
or ( o.type# in (4,5,7,8,9,10,11,12,13,14,22,87)
and ( (u.type# <> 2 and
sys_context('userenv', 'current_edition_name') =
'ORA$BASE')
or (u.type# = 2 and
u.spare2 = sys_context('userenv',
'current_edition_id'))
or exists (select 1 from obj$ o2, user$ u2
where o2.type# = 88
and o2.dataobj# = o.obj#
and o2.owner# = u2.user#
and u2.type# = 2
and u2.spare2 =
sys_context('userenv',
'current_edition_id'))
)
)
);
}}}
`USER_SEQUENCES` is much more readable and it contains only sequences:
{{{#!sql
CREATE OR REPLACE FORCE VIEW "SYS"."USER_SEQUENCES" ("SEQUENCE_NAME",
"MIN_VALUE", "MAX_VALUE", "INCREMENT_BY", "CYCLE_FLAG", "ORDER_FLAG",
"CACHE_SIZE", "LAST_NUMBER") AS
select o.name,
s.minvalue, s.maxvalue, s.increment$,
decode (s.cycle#, 0, 'N', 1, 'Y'),
decode (s.order$, 0, 'N', 1, 'Y'),
s.cache, s.highwater
from sys.seq$ s, sys.obj$ o
where o.owner# = userenv('SCHEMAID')
and o.obj# = s.obj#;
}}}
To sum up, in the first approach we choose all objects (including
sequences), and in the second step we limit results to sequences with the
given name. In the second approach we choose sequences and next limit
results to the given name.
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:6>
Django
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: felixxm
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Shai Berger):
* stage: Accepted => Ready for checkin
Comment:
I verified that `USER_SEQUENCES` is available on Oracle 11, I don't really
care if it is as optimized there.
I left one comment on the PR, otherwise I think we're good to go.
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:7>
Django
USER_CATALOG
-------------------------------------+-------------------------------------
Reporter: felixxm | Owner: felixxm
Type: | Status: assigned
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: Oracle sequences | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:8>
Django
USER_CATALOG
-------------------------------------+-------------------------------------
Type: | Status: closed
Cleanup/optimization |
Component: Database layer | Version: master
(models, ORM) |
Keywords: Oracle sequences | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Tim Graham <timograham@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"794f866cecb7598c1537067cc1d932d95a86f439" 794f866c]:
{{{
#!CommitTicketReference repository=""
revision="794f866cecb7598c1537067cc1d932d95a86f439"
Fixed #27323 -- Optimized Oracle introspection by using USER_SEQUENCES
instead of USER_CATALOG.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/27323#comment:9>