JSON Duality View in Oracle 23 ai.

 

Oracle 23ai introduced "JSON-Relational Duality" Views. 

With this feature , the existing relational structure can be retrieved 

as a JSON document and the data can be stored as JSON documents.

With this approach , the application can use "elastic schema" .

Let us jump into a simple use case / example. 

Here is the script for table / record creation.

CREATE TABLE dept (

    depid    INT PRIMARY KEY,

    deptname VARCHAR2(50) NOT NULL

);

CREATE TABLE emp (

    empid   INT PRIMARY KEY,

    empname VARCHAR2(50) NOT NULL,

    depid   INT,

    CONSTRAINT dep_fk FOREIGN KEY ( depid )

        REFERENCES dept ( depid )

);

insert into dept values ( 1 , 'HR');

insert into dept values ( 2 , 'DEVOPS');

insert into dept values ( 3 , 'RETAIL');

Insert into emp values ( 101 , 'Zahir Mohideen' ,1 );

Insert into emp values ( 201 , 'John' , 2);

Insert into emp values ( 202 , 'Jane' , 2);

Let us create a  simple JSON document from the "emp" table. 

Secondly , we can create another JSON document by joining the "dept" and "emp" table.  The following JSON document is "schema flexible" or "elastic schema" to suit the application's needs .  That is , in the example , "HR" has one employee and "DEVOPS" have two employees and the "RETAIL" has none.

 

When  the above SQL is used to create  JSON duality view , Oracle greets us with the error.

According to the oracle documentation ( https://docs.oracle.com/en/database/oracle/oracle-database/23/jsnvu/document-identifier-field-duality-views.html#GUID-A1445407-623E-4898-BE32-5789A11E2EBD) , duality view always include "document identifier" field , "_id" ...  So , let us rewrite our view as below.

Yikes . ... We can't use alias . 

So , let us try it for the 3rd time . 

Now , we got it . 

Comments Welcome!

DB_DEVELOPER_ROLE in Oracle 23 ai

Oracle 23 ai introduced a new role called 'DB_DEVELOPER_ROLE' . 

At first glance , it looks like , it is easier way to grant privileges . 

With ease , it comes with security risk. This is seemingly powerful role that encompasses other predefined roles RESOURCE , CTXAPP. 

We were discouraged to use the standard role and grant the necessary ( least possible) privileges to do the job. 

With RESOURCE , we can create lot of DB objects. ( See the output from Listing 2) 

SQLcl: Release 24.1 Production on Thu Jun 20 19:27:21 2024

Copyright (c) 1982, 2024, Oracle.  All rights reserved.

Last Successful login time: Thu Jun 20 2024 19:27:22 +00:00

Connected to:

Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free

Version 23.4.0.24.05

SQL> create user appdevuser identified by XXXXXXX;

User APPDEVUSER created.

SQL> grant db_developer_role to appdevuser;

Grant succeeded.

Listing 1 

----------

SQL> select 'SYS PRIVS' as Priv_Type  , privilege from dba_sys_privs where grantee = 'DB_DEVELOPER_ROLE'

  2  union all

  3  select 'ROLES' as Priv_Type  , granted_role from dba_role_privs where grantee = 'DB_DEVELOPER_ROLE'

  4  union all

  5  select 'TAB PRIVS' as Priv_Type  , table_name from dba_tab_privs where grantee = 'DB_DEVELOPER_ROLE'

  6* ;

PRIV_TYPE    PRIVILEGE                    

____________ ____________________________ 

SYS PRIVS    CREATE DOMAIN                

SYS PRIVS    CREATE MLE                   

SYS PRIVS    EXECUTE DYNAMIC MLE          

SYS PRIVS    CREATE CUBE BUILD PROCESS    

SYS PRIVS    CREATE CUBE                  

SYS PRIVS    CREATE CUBE DIMENSION        

SYS PRIVS    CREATE MINING MODEL          

SYS PRIVS    CREATE JOB                   

SYS PRIVS    DEBUG CONNECT SESSION        

SYS PRIVS    ON COMMIT REFRESH            

SYS PRIVS    CREATE DIMENSION             

SYS PRIVS    FORCE TRANSACTION            

SYS PRIVS    CREATE SESSION               

ROLES        CTXAPP                       

ROLES        RESOURCE                     

TAB PRIVS    JAVASCRIPT                   

TAB PRIVS    DBA_PENDING_TRANSACTIONS     

TAB PRIVS    DBMS_RLS                     

TAB PRIVS    DBMS_REDACT                  

TAB PRIVS    DBMS_TSDP_MANAGE             

TAB PRIVS    DBMS_TSDP_PROTECT            

TAB PRIVS    V_$STATNAME                  

TAB PRIVS    V_$PARAMETER                 

23 rows selected. 

Listing 2 

----------

SQL> select   privilege 

  2  from dba_sys_privs 

  3* where grantee in (  'RESOURCE' , 'CTXAPP');

PRIVILEGE                     

_____________________________ 

CREATE TABLE                  

CREATE CLUSTER                

CREATE SYNONYM                

CREATE VIEW                   

CREATE SEQUENCE               

CREATE PROCEDURE              

CREATE TRIGGER                

CREATE MATERIALIZED VIEW      

CREATE TYPE                   

CREATE OPERATOR               

CREATE INDEXTYPE              

CREATE ATTRIBUTE DIMENSION    

CREATE HIERARCHY              

CREATE ANALYTIC VIEW          

CREATE PROPERTY GRAPH         

CREATE SEQUENCE               

16 rows selected. 

As I learned from Tom Kyte , I usually follow  / advise the developers of the API based approach. 

With this approach , 

a) one schema will have only base tables 

b) second schema will have the views / packages 

c) third schema will have access to the objects created in the second schema.

d) application will be using only the third schema to interact with the database. 

"DB_DEVELOPER_ROLE" seems to be going in other direction .Please be sure to proceed with caution . 

Switching Power Bi Report Connections ( On Premises Data Sources to Semantic Models)

 

There are few situations / use cases , where we would want to change the power bi report's data source from RDBMS / other data sources to Power BI data sources.

 a) When we transition from Power Bi Report Server ( On Premises ) to Power BI Service ( Cloud)

 b) We start small and build the report . At this time , there is one to mapping with the report and the data source. Later on , you want to divide the responsibilities of maintaining the data model and maintaining the reports / dashboards.

I found out this hack , where we change the data sources and maintain the same visuals , without recreating it.

I wish , I knew this method , when I transitioned from PBI report server to PBI Service.

Here are the steps to switch from RDBMS / other on-premises data source .

Let us say , we create the Power Bi Report connecting to SQL Server table “zipcodes” and this is called zipcode.pbix

1. Duplicate the PBI file

        Copy the file “ZipCodes.pbix” to “ZipCodesPBService.pbix”

2. Publish / Upload the original PBIX  file to the  Power Bi Service

 

 

 

3. Open the duplicated file ( ZipCodesPBService.pbix) in Power BI Desktop

 

4. Click on “Transform Data” in the ribbon.

 

 

5. Delete all the queries associated with the report.

          In our example , delete the “zipcodes” query

 

 

6. Click “Close & Apply” to exit the Power Query Editor

         Ignore the error message for now. This error is only transient.

 

 

7.  Click on “Get Data” and select “Power BI Semantic Models” .

 

 

 

 

8. Choose the PBI Semantic model ( ZipCodes) that was uploaded in Step 1 and click “Connect”

 

 

 

9. Once the PBI Semantic model gets refreshed , the error disappears and visuals comeback .

 

 

 If there are any other easier methods, please feel free to comment on this post. 

 

Oracle 23c New Feature - Grouping by an alias

Probably , this is my shortest blog post till now. 

In Oracle 23c , we can group by the alias name .

This simple enhancement  improves readability in the SQL statements. 

SQL> select banner from v$version ;  

BANNER           

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release    

SQL> create table t as select * from all_objects;

Table T created.

SQL> select to_char(created,'MON') as created_month , count(*) from t

  2* group by created_month;

CREATED_MONTH       COUNT(*) 

________________ ___________ 

MAR                    57620 

APR                     1014 

MAY                        1 

Maria DB 

This feature has been in MySQL / MariaDB for quite sometime. Here is an example from MariaDB

MariaDB [(none)]> create database testdb ;

Query OK, 1 row affected (0.008 sec)

MariaDB [(none)]> use testdb;

Database changed

MariaDB [testdb]> Create table t as select * from information_schema.tables;

Query OK, 324 rows affected (0.129 sec)

Records: 324  Duplicates: 0  Warnings: 0

MariaDB [testdb]> select

    ->  to_char(create_time,

    ->  'MON') as created_month ,

    ->  count(*)

    -> from

    ->  t

    -> group by

    ->  created_month;

+---------------+----------+

| created_month | count(*) |

+---------------+----------+

| NULL          |      184 |

| May           |      140 |

+---------------+----------+

2 rows in set (0.007 sec)

 

Naming Window Clause in SQL Server 2022 Window / Oracle 23c Analytical Functions

One of my favorites in database development is "Analytical Functions" ( Oracle ) and "Window Functions" ( SQL Server / MariaDB).

I have written about these functions in the below mentioned posts. 

https://zahirmohideen.blogspot.com/2009/09/sql-treasures.html

https://zahirmohideen.blogspot.com/2009/10/analytics-20.html

https://zahirmohideen.blogspot.com/2009/11/analytics-20-part-ii.html

https://zahirmohideen.blogspot.com/2012/09/difference-between-percentilecont-and.html

https://zahirmohideen.blogspot.com/2013/01/book-sql-server-window-functions.html

https://zahirmohideen.blogspot.com/2018/10/windowing-clause-why-it-matters.html

In MS SQL Server 2022 , now we have the option of "labelling" or "naming" the Window.

This improved readability , especially , when the same windowing clause is referenced in the query multiple times. 

Here is the sample script to create a table and populate few records.

CREATE TABLE 

    emp

    ( 

        eid        INT ,

        lastname   VARCHAR(50),

        firstname  VARCHAR(50),

        department VARCHAR(50),

        salary     NUMERIC(18,2) ,

        hiredate   DATE 

    );

INSERT INTO 

    emp VALUES 

    ( 

        101 , 

        'Doe' , 

        'Jane', 

        'Research', 

        10000 , 

        '1971-08-16' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        102 , 

        'Doe' , 

        'John', 

        'HR', 

        7500 , 

        '1975-03-09' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        103 , 

        'Amin' , 

        'Al', 

        'Research', 

        10300 , 

        '1985-05-14' 

    );

INSERT INTO 

    emp VALUES 

    ( 

        105 , 

        'Kumar' , 

        'Palani', 

        'Faculty Development', 

        9175 , 

        '1985-05-14' 

    );

Here is the sample table "emp" with 4 records in it. 

Here is the sample table "emp" with 4 records in it. 

In the following example , I am naming each window clause a name and reusing that window wherever possible. 

This simple enhancement leads to readability , easier to understand. 

SELECT 

    e.* ,

    MIN(e.salary) OVER win_dep   AS Lowest_Salary_Department ,

    MIN(e.salary) OVER win_all   AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER win_dep   AS Higher_Salary_Department ,

    MAX(e.salary) OVER win_all   AS Higher_Salary_OverAll,

    first_value(e.hiredate) 

                 OVER win_emp AS First_Hired_Date_Department

FROM 

    emp e 

    WINDOW 

    win_emp AS (PARTITION BY department ORDER BY hiredate ASC),

    win_dep AS (PARTITION BY department ),

    win_All AS ( PARTITION BY (1) );

This is equivalent to 

SELECT 

    e.* ,

    MIN(e.salary) OVER ( 

                    PARTITION BY 

                        department ) AS Lowest_Salary_Department ,

    MIN(e.salary) OVER ( 

                    PARTITION BY 

                        (1) ) AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER ( 

                    PARTITION BY 

                        department 

                    ORDER BY 

                        hiredate ASC) AS Higher_Salary_Department ,

    MAX(e.salary) OVER ( 

                    PARTITION BY 

                        (1) ) AS Higher_Salary_OverAll,

    first_value(e.hiredate) OVER ( 

                              PARTITION BY 

                                  department 

                              ORDER BY 

                                  hiredate ASC) AS First_Hired_Date_Department

FROM 

    emp e;

Updated on 04/18/2024

By looking at the 23c new features , learn that this feature is available in Oracle 23c as well.

Here is the script ( identical to SQL Server 2023) . 

SQL> select banner from v$version ;

BANNER

--------------------------------------------------------------------------------

Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-Release

SQL> desc emp;

 Name    Null?    Type

 ----------------------------------------- -------- ----------------------------

 EID     NUMBER(38)

 LASTNAME     VARCHAR2(50)

 FIRSTNAME     VARCHAR2(50)

 DEPARTMENT     VARCHAR2(50)

 SALARY     NUMBER(18,2)

 HIREDATE     DATE

SQL> select * from emp;

       EID LASTNAME    FIRSTNAME    DEPARTMENT    SALARY HIREDATE

---------- --------------- --------------- -------------------- --------- ---------

       101 Doe    Jane    Research 10000.00 16-AUG-71

       102 Doe    John    HR   7500.00 03-SEP-75

       103 Amin    Al    Research 10300.00 14-MAY-85

       105 Kumar    Palani    Faculty Development   9175.00 14-MAY-85

SQL> SELECT 

    e.* ,

    MIN(e.salary) OVER win_dep   AS Lowest_Salary_Department ,

    MIN(e.salary) OVER win_all   AS Lowest_Salary_OverAll,

    MAX(e.salary) OVER win_dep   AS Higher_Salary_Department ,

    MAX(e.salary) OVER win_all   AS Higher_Salary_OverAll,

    first_value(e.hiredate) 

     OVER win_emp AS First_Hired_Date_Department

FROM 

    emp e 

    WINDOW 

    win_emp AS (PARTITION BY department ORDER BY hiredate ASC),

    win_dep AS (PARTITION BY department ),

    win_All AS ( PARTITION BY (1) );  

       EID LASTNAME    FIRSTNAME    DEPARTMENT    SALARY HIREDATE  LOWEST_SALARY_DEPARTMENT LOWEST_SALARY_OVERALL HIGHER_SALARY_DEPARTMENT HIGHER_SALARY_OVERALL FIRST_HIR

---------- --------------- --------------- -------------------- --------- --------- ------------------------ --------------------- ------------------------ --------------------- ---------

       105 Kumar    Palani    Faculty Development   9175.00 14-MAY-85 9175       7500        9175     10300 14-MAY-85

       102 Doe    John    HR   7500.00 03-SEP-75 7500       7500        7500     10300 03-SEP-75

       101 Doe    Jane    Research 10000.00 16-AUG-71        10000       7500       10300     10300 16-AUG-71

       103 Amin    Al    Research 10300.00 14-MAY-85        10000       7500       10300     10300 16-AUG-71

Comments Welcome!

My Five Productivity Habits

 

So, how many weeks do we have in our lifetime?  

Guess …  

Before you squeeze your brain too long or ask Google for help, that average number isn’t a million or even tens of thousands... assuming you live a long, healthy life, you can expect after near 90 orbits around the sun to have enjoyed roughly 4,680 weeks on Earth.  

Breaking that down a bit, by the time you are age 25, you will have used 1,300 of those weeks. Many of us are in the middle of our careers and lives. We have family, commitments, professions, and hobbies with more weeks under our collective belts than you did at 25 years... and there is no guarantee you will get to 90, as much as many of us hope to get there safely, securely and happily.  

From the perspective of limited time, how do we make each weeklong snapshot of our story mean the most to us? How do we make those individual periods the most productive they can be, which in turn allows us to feel engaged and happy in our day-to-day moments.  

In this digital era, we need to change our mindset since we are the only ones we can ultimately manage or control. We can use technology as a tool, instead of being a tool to the technology. We have become digiphernia, which is lost to digital information overload, splitting ourselves too much into too many different roles. In this mode, we cannot enjoy any one experience as we look and worry about the next. We have lost focus and priority for even simple tasks at hand, ultimately making ourselves feel burned out rather than accomplished. 

As I have gone through my journey, as a son, father, DBA, man of faith, man of habit, I have found these few habits can enhance productivity and they allow me to feel more focused in my daily routines. 

 

 

#1 Batch Processing of Emails.  

Most of us keep our emails open all the time. We have anxiety to process them and look to each individual interaction to achieve minor tasks with often minimal reward. According to a study done at the university of British Columbia checking email less frequently on a scheduled basis reduces stress and anxiety. 

https://www.sciencedirect.com/science/article/abs/pii/S0747563214005810?via%3Dihub 

Email has become a business focused tool, running a wide array of major project updates, critical tasks, but more often smaller, rapid things that can pull from the bigger picture of your day. By creating scheduled email response periods, where that is your sole focus, you allow yourself the freedom to work on major projects and issues, which in turn could be part of your various electronic responses. Additionally, we still have other tools for urgent issues such as Teams, Slack, mobile phones and tried and true in-person conversation. Not all emails need to be responded to urgently, and the reality is we should all break the habit of urgent communication going over email.  

Putting a schedule around this type of activity could also have the auxiliary benefit of being more engaged with your colleagues, because the reality is sometimes a quick conversation can negate misunderstandings or even long, drawn out threads where people are often being added without really understanding what their purpose to the discussion is... so thinking of new ways to engage email, and in fact the way you communicate overall, could return valuable time and focus.  

 

#2 Write it down. 

When you are coming up with a plan or a need for next steps, sit down with a pen and paper and start to write.

The purpose isn’t to create a list of tasks to do, or even next steps. You may find it may end up that way, but the real goal at the beginning is to allow your mind to freely form ideas that otherwise could be lost. One idea can beget another, and freeflowing thought you’ve jotted down allows you to come back later, which can sometimes open doors to ideas or opportunities you may not have thought of at the time.  

 

#3 Turn off Notifications. 

Distraction is #1 killer for productivity. We may be working through an issue or writing down stream of conscious thought... and suddenly Facebook wants your attention. We can easily turn off or pause notifications in our smart phone or computers. Airplane mode is your friend for this step of the habit.  

Those dings and beeps from that device send a signal to the rewards center in our hypothalamus and consequently create a dopamine rush that leads to less favorable workplace productivity, and from there being less productive overall. See the previous step of the journey regarding in-person or other styles of communication for urgent needs. This is often the same effect; the notification is often a service or non-urgent event triggering you to engage it. You have the power to take control back, realizing if there is something urgent that involves your insight or presence, the sender shouldn’t expect your attention to solely be attainable through a cell phone ding.  

Prof.Robert Lustig has summarized the difference between Dopamine and Serotonin in his book “The hacking of American Mind". I have learned a lot from Dr.Lustig ( https://robertlustig.com/). 

 

 

 

#4 Defensive Calendaring  

By blocking certain segments of your calendar, you can plan and focus on an activity (NOTE: Singular Activity!). Human beings for all our abilities and talents and self-assessment, and truly not very good at multitasking, although many of us think we are. Context switching from task-to-task results in less productivity because we often rush to reach the end goal, doing each task as quickly as possible to go on to the next. On average, it takes 52 seconds to refocus from one task to the next, not counting what you may have eventually done with the original train of thought. 

As the saying from John Beckley goes “People don’t plan to fail; but they fail to plan”. Like corralling email into specific blocks of time, you can extend the concept to other aspects of your daily routines. Do you want to exercise? Then every day, you plan to at 5pm, or 5am. The time isn’t as relevant as the commitment to the schedule. That allows you to be clear and authentic with yourself as well as your colleagues that at specific periods or times, you may have commitments that matter to you, and you are both ensuring the time is available to meet your obligations. At the same time, guarding against other infringements that may creep randomly into your day.  

 

#5 Automate repetitive tasks. 

If there are any repetitive tasks you do throughout your week, they are the most likely automation candidates. This allows you to set routines throughout your day and even your week that repeat reliable, consistent results. 

This is where technology and habit can synthesize into a powerhouse of productivity for you. As an example, if you are responsible for generating a summary report for event registrations, and you can predict the timing of when the data is available, you can then use tools at your disposal such as Power Query to automate the output if the output is always the same. Additionally, this can extend out more even if the output needs superficially change, allowing the bulk of the processing to be done and then you simply modify the output. This is something AI will ultimately be beneficial and assistive with as we march into the future, but for the time being it is important to take stock of things you often repeat and do over with similar outcomes each time. Not only will you maybe save some time for your week-to-week schedule, but you’ll also have a predetermined candidate list of concepts and tasks that will be ready when AI becomes more ubiquitous around us.