So if I didn't use a modeling tool, what did I do? I created the tables manually. There're probably more advantages using a modeling tool, so if you use it and like it, don't change because of this blog post, I just share how I typically do a project. If you have a big team and somebody is solely responsibility for modeling, I see a benefit of using a modeling tool or if you like the process of visually creating a model. As written earlier, I typically draw my entities on a whiteboard or piece of paper, after that I want to get going with real tables and data as fast as I can to verify the model. The main reason for me was, it took me so much time to add the entities, that it was too slow for my process. Oracle moved forward with SQL Developer Data Modeler.Īlthough I've been using those tools in the beginning, today I typically don't use them anymore, except for generating a visual diagram (ERD) of my tables, but that is now part of SQL Developer, so I don't have a data modeling tool installed anymore. This tool has been really popular, but today it's legacy and not maintained anymore by Oracle. If you work with Oracle for 10 or more years, you probably know Oracle Designer. Now I want to go into detail how I create the Oracle database objects like tables, foreign keys, sequences, triggers, etc.īefore I tell you what I do today, let me first walk you through my history so you understand why I'm using it.ĭata Modeling Tools Data Modeling Tools allow you to visually create your tables, relationships, etc. In a previous post we defined the ERD of the multiplication table application we're going to build.
#Apex sql data dictionary series#
with hints RESULT_CACHE USE_MERGE(F FC SC) hint 12 seconds.This post is part of a series of posts: From idea to app or how I do an Oracle APEX project anno 2017 with hints RESULT_CACHE PARALLEL USE_MERGE(F FC SC) - 3 mins 22 seconds. Then I looked in the USER_SCHEDULER_JOB_RUN_DETAILS with the following results: Job_action => 'begin DBMS_MVIEW.REFRESH(''MVBASE_REFS'') end ', SELECT /*+ RESULT_CACHE USE_MERGE(F FC SC) */ĪND F.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') with the following script, I found out that I had to remove the PARALLEL hint to get good performance, I was able to measure the execution time with help of a scheduler job to skill over the 10 seconds time limit that is enforced in the web interface. Thanks again for directing me to the hints in my queries. JOIN SYS.USER_TAB_COLS SC ON SC.TABLE_NAME = F.TABLE_NAME AND SC.COLUMN_NAME = FC.COLUMN_NAME AND SC.HIDDEN_COLUMN = 'NO'ĪND F.OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA')
JOIN SYS.USER_CONS_COLUMNS FC ON F.OWNER = FC.OWNER AND F.CONSTRAINT_NAME = FC.CONSTRAINT_NAME AND F.TABLE_NAME = FC.TABLE_NAME SC.COLUMN_ID, SC.NULLABLE, F.DELETE_RULE, F.DEFERRABLE, F.DEFERRED,į.STATUS, F.VALIDATED, F.R_CONSTRAINT_NAME, F.R_OWNER SELECT /*+ RESULT_CACHE PARALLEL USE_MERGE(F FC SC) */į.TABLE_NAME, F.OWNER, F.CONSTRAINT_NAME, FC.COLUMN_NAME, FC.POSITION, What can I do to make them run fast again? Those views were tuned to run fast in my local Oracle Virtual Box with DB Version 19.3.0.0.0 and in a autonomous DB Version 19.5.0.0.0 environment.īut on the refresh in a scheduler job takes ca. What works is creating materialized views on queries that access the data dictionary views. I have a performance problem specific to where I have installed the 'HR data' sample schemaĪnd then try to execute a simple select on a data dictionary view in SQL Commands:Įxplain returns ORA-01039: insufficient privileges on underlying objects of the view