Opened 8 years ago
Closed 8 years ago
#27323 closed Cleanup/optimization (fixed)
Optimize Oracle introspection by using USER_SEQUENCES instead of USER_CATALOG
Reported by: | Mariusz Felisiak | Owned by: | Mariusz Felisiak |
---|---|---|---|
Component: | Database layer (models, ORM) | Version: | dev |
Severity: | Normal | Keywords: | Oracle sequences |
Cc: | Triage Stage: | Ready for checkin | |
Has patch: | yes | Needs documentation: | no |
Needs tests: | no | Patch needs improvement: | no |
Easy pickings: | no | UI/UX: | no |
Description
Little optimization. Using USER_SEQUENCES
instead of USER_CATALOG
in Oracle query which verify that sequence exists or not is more optimal.
Change History (9)
comment:1 by , 8 years ago
Summary: | Oracle backend cleanup → Using USER_SEQUENCES instead of USER_CATALOG |
---|
comment:2 by , 8 years ago
Has patch: | set |
---|---|
Triage Stage: | Unreviewed → Ready for checkin |
comment:3 by , 8 years ago
Triage Stage: | Ready for checkin → Accepted |
---|
follow-up: 6 comment:5 by , 8 years ago
Could you explain why it's more optimal so we have that rationale recorded?
comment:6 by , 8 years ago
Replying to 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:
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:
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.
comment:7 by , 8 years ago
Triage Stage: | Accepted → Ready for checkin |
---|
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.
comment:8 by , 8 years ago
Summary: | Using USER_SEQUENCES instead of USER_CATALOG → Optimize Oracle introspection by using USER_SEQUENCES instead of USER_CATALOG |
---|
Please don't mark your own patches as RFC, see our ticket triaging doc's.