2 of Most Practical New Features on 23.9 – Keep Learning Keep Living

Hi,

with 23.9 I saw 2 beautiful enhancement in SQL. These are not “exceptional” things but their practical usage is a lot to me as a both developer and dba. 23.9 is already available on cloud base database systems.

First, INSERT SET is here! one of my biggest complains in plsql objects was huge insert statements. problem is it is a torture sometimes if you have 50 columns with 50 values from different variables. Your end version of sql is like:

Insert into TMP_O 
  (OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED,APPLICATION,DEFAULT_COLLATION,DUPLICATED,SHARDED,CREATED_APPID,CREATED_VSNID,MODIFIED_APPID,MODIFIED_VSNID) 
  values 
  ('SYS','TS$',null,16,6,'TABLE',to_date('2019-04-17 00:56:11','YYYY-MM-DD HH24:MI:SS'),to_date('2019-04-17 01:46:57','YYYY-MM-DD HH24:MI:SS'),'2019-04-17:00:56:11','VALID','N','N','N',1,null,'METADATA LINK',null,'Y','N','USING_NLS_COMP','N','N',null,null,null,null);

Insert into TMP_O

(OWNER,OBJECT_NAME,SUBOBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE,CREATED,LAST_DDL_TIME,TIMESTAMP,STATUS,TEMPORARY,GENERATED,SECONDARY,NAMESPACE,EDITION_NAME,SHARING,EDITIONABLE,ORACLE_MAINTAINED,APPLICATION,DEFAULT_COLLATION,DUPLICATED,SHARDED,CREATED_APPID,CREATED_VSNID,MODIFIED_APPID,MODIFIED_VSNID)

values

('SYS','TS$',null,16,6,'TABLE',to_date('2019-04-17 00:56:11','YYYY-MM-DD HH24:MI:SS'),to_date('2019-04-17 01:46:57','YYYY-MM-DD HH24:MI:SS'),'2019-04-17:00:56:11','VALID','N','N','N',1,null,'METADATA LINK',null,'Y','N','USING_NLS_COMP','N','N',null,null,null,null);

so there is a value in this insert statement “‘METADATA LINK'” so into which column it will be inserted? you need to count the orders and find it. if you count it wrong (which is quite possible) then you will be spending so much times.

Overcome this issue insert set can be used and it is so easy to use.

Insert into TMP_O set
OWNER                = 'SYS',
OBJECT_NAME          = 'TS$',
SUBOBJECT_NAME       = null,
OBJECT_ID            = 16,
DATA_OBJECT_ID       = 6,
OBJECT_TYPE          = 'TABLE',
CREATED              = to_date('2019-04-17 00:56:11','YYYY-MM-DD HH24:MI:SS'),
LAST_DDL_TIME        = to_date('2019-04-17 01:46:57','YYYY-MM-DD HH24:MI:SS'),
TIMESTAMP            = '2019-04-17:00:56:11',
STATUS               = 'VALID',
TEMPORARY            = 'N',
GENERATED            = 'N',
SECONDARY            = 'N',
NAMESPACE            = 1,
EDITION_NAME         = null,
SHARING              = 'METADATA LINK',
EDITIONABLE          = null,
ORACLE_MAINTAINED    = 'Y',
APPLICATION          = 'N',
DEFAULT_COLLATION    = 'USING_NLS_COMP',
DUPLICATED           = 'N',
SHARDED              = 'N',
CREATED_APPID        = null,
CREATED_VSNID        = null,
MODIFIED_APPID       = null,
MODIFIED_VSNID       = null ;

Insert into TMP_O set

OWNER = 'SYS',

OBJECT_NAME = 'TS$',

SUBOBJECT_NAME = null,

OBJECT_ID = 16,

DATA_OBJECT_ID = 6,

OBJECT_TYPE = 'TABLE',

CREATED = to_date('2019-04-17 00:56:11','YYYY-MM-DD HH24:MI:SS'),

LAST_DDL_TIME = to_date('2019-04-17 01:46:57','YYYY-MM-DD HH24:MI:SS'),

TIMESTAMP = '2019-04-17:00:56:11',

STATUS = 'VALID',

TEMPORARY = 'N',

GENERATED = 'N',

SECONDARY = 'N',

NAMESPACE = 1,

EDITION_NAME = null,

SHARING = 'METADATA LINK',

EDITIONABLE = null,

ORACLE_MAINTAINED = 'Y',

APPLICATION = 'N',

DEFAULT_COLLATION = 'USING_NLS_COMP',

DUPLICATED = 'N',

SHARDED = 'N',

CREATED_APPID = null,

CREATED_VSNID = null,

MODIFIED_APPID = null,

MODIFIED_VSNID = null ;

it is so practical. just like an update statement and now we know easily which value is going into which columns.

Second is “GROUP BY ANY” statement. again this is not a performance enhancement etc but it is an easy development for developers. you don’t have write all the columns in the select list into group by clause one by one. instead, you can just type ALL. if you are using large number of columns or big expressions like case when etc, copy and paste all of them into group by is just making your sql bigger and bigger.

select owner, object_type, 
       case 
         when object_type in ('TABLE', 'INDEX') then 'data segment' -- I know there are more data segments :)
         else 'other object'
       end object_kind,
       count(*)
from   dba_objects
group by all;

select owner, object_type,

case

when object_type in ('TABLE', 'INDEX') then 'data segment' -- I know there are more data segments :)

else 'other object'

end object_kind,

count(*)

from dba_objects

group by all;

that is it. I don’t have copy owner, object_type and case statement into group by anymore. So, there are 2 more reasons to upgrade 23 ai and patch to 23.9

Wish you healthy, happy days.

Leave a Reply Cancel reply