Supports group by with constants，means that it is possible to perform aggregation based on grouping by constants, where the grouping fields can include one or more constant fields, as well as a mixture of constant fields, variables, and expressions. see Aggregate Functions.

For example:

lightdb@oracle=# select 0,
lightdb@oracle-#         count(*) as rowcount ,
lightdb@oracle-#         sum(sal) as sum_salary,
lightdb@oracle-#         sum(comm) as sum_comm
lightdb@oracle-# from emp group by -100;
 ?column? | rowcount | sum_salary | sum_comm 
----------+----------+------------+----------
        0 |       14 |      29025 |     2200
(1 row)
 
lightdb@oracle=# select 0,
        count(*) as rowcount ,
        sum(sal) as sum_salary,
        sum(comm) as sum_comm,deptno
from emp group by -100,deptno,1,23,'aa';
 ?column? | rowcount | sum_salary | sum_comm | deptno 
----------+----------+------------+----------+--------
        0 |        3 |       8750 |          |     10
        0 |        6 |       9400 |     2200 |     30
        0 |        5 |      10875 |          |     20
(3 rows)
 
lightdb@oracle=# select mgr,count(*) from emp group by 100,-11111111111.23232423543545657,mgr;
 mgr  | count 
------+-------
      |     1
 7566 |     2
 7782 |     1
 7902 |     1
 7788 |     1
 7839 |     3
 7698 |     5
(7 rows)
              

Supports the features of merge partitions，including list and range partitioning，but does not support hash partitioning. see ALTER TABLE.

Range Partitioning Management Example:：

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate) (
	PARTITION p0 VALUES LESS THAN (TO_DATE('2020-01-01', 'yyyy-mm-dd')),
	PARTITION p1 VALUES LESS THAN (TO_DATE('2020-02-01', 'yyyy-mm-dd')),
	PARTITION p2 VALUES LESS THAN (TO_DATE('2020-03-01', 'yyyy-mm-dd')),
	PARTITION p3 VALUES LESS THAN (TO_DATE('2020-04-01', 'yyyy-mm-dd')),
	PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
CREATE INDEX idx_measurement_logdate ON measurement (logdate);
insert into measurement values(0, to_date('2019-01-01', 'yyyy-mm-dd'), 0, 0);
insert into measurement values(1, to_date('2020-01-01', 'yyyy-mm-dd'), 1, 1);
insert into measurement values(2, to_date('2020-02-01', 'yyyy-mm-dd'), 2, 2);
insert into measurement values(3, to_date('2020-03-01', 'yyyy-mm-dd'), 3, 3);
insert into measurement values(4, to_date('2020-04-01', 'yyyy-mm-dd'), 4, 4);

--The name of the merged partition can be the same as the name of one of the original partitions.
alter table measurement merge partitions p0, p1 into partition p0;
insert into measurement values (6, to_date('2020-01-08', 'yyyy-mm-dd'), 6, 6);
select * from pg_indexes where tablename = 'measurement$p0';
              

List Partitioning Management Example:

CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    total_amount NUMBER(10, 2)
)
PARTITION BY LIST (customer_id)
(
    PARTITION p1 VALUES (1001, 1002, 1003),
    PARTITION p2 VALUES (2001, 2002, 2003),
    PARTITION p3 VALUES (3001, 3002, 3003),
    PARTITION p4 VALUES (4001, 4002, 4003),
    PARTITION p5 VALUES (DEFAULT)
);
\d+ orders
insert into orders values (1, 999, 1);
insert into orders values (2, 1001, 2);
insert into orders values (3, 2002, 3);
insert into orders values (4, 3003, 4);
insert into orders values (5, 4002, 5);

--A list partition can have a default partition.
alter table orders merge partitions p1, p2, p3 into partition p1;
--= 3
select count(*) from orders$p1;

alter table orders merge partitions p4, p5 into partition p2;
--= 2
select count(*) from orders$p2;
			  

Support for setting session-level date format via nls_date_format. see orafce.

For example:

alter session set NLS_DATE_FORMAT to "yyyymmdd hh24";
select sysdate;
alter session set NLS_DATE_FORMAT to "yyyy-mm-dd hh24";
select to_date('2024-01-20 10:30:00', 'YYYY-MM-DD HH24:MI:SS');      
              

Support for UNION with NULL type matching. see UNION.

For example:

lightdb@oracle_test=# select null l_zqlbmx2 from dual
lightdb@oracle_test-# union all
lightdb@oracle_test-# select null l_zqlbmx2 from dual
lightdb@oracle_test-# union all
lightdb@oracle_test-# select 0 l_zqlbmx from dual;
 l_zqlbmx2 
-----------
 
 
 0
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select 5.55::numeric l_zqlbmx from dual;
 l_zqlbmx2 
-----------
 
 
 5.55
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select sysdate l_zqlbmx from dual;
      l_zqlbmx2      
---------------------
 
 
 2024-01-26 08:23:42
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select current_timestamp l_zqlbmx from dual;
           l_zqlbmx2           
-------------------------------
 
 
 2024-01-26 16:28:07.278097+08
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

lightdb@oracle_test=# select null l_zqlbmx2 from dual
union all
select null l_zqlbmx2 from dual
union all
select 'hello' l_zqlbmx from dual;
 l_zqlbmx2 
-----------
 
 
 hello
(3 rows)

lightdb@oracle_test=# \gdesc
  Column   | Type 
-----------+------
 l_zqlbmx2 | text
(1 row)

              

Support for direct concatenation using || and - without space syntax parsing. see CREATE OPERATOR.

For example:

lightdb@oracle=# select 1 || -1 from dual;
 ?column? 
----------
 1-1
(1 row)

lightdb@oracle=# select 1||-'1'from dual;
 ?column? 
----------
 1-1
(1 row)
              

Support for ROWNUM on the left side of an expression. see ROWNUM.

For example:

SELECT rownum FROM table WHERE id <= 10;
SELECT * FROM table WHERE rownum <= 10;
SELECT * FROM table WHERE rownum <= 10 order by id;
SELECT rownum + 1, 1 + rownum FROM table WHERE rownum <= 10; 
              

Support for using order by with aggregate functions，allowing the order by clause to appear when there is no GROUP BY clause but there are aggregate functions. see syntax-compatible-type.

For example:

--With a GROUP BY clause
create table student(id integer primary key, score integer, classid integer);
insert into student values(1,90,1);
insert into student values(2,100,1);
insert into student values(2,100,2);
select count(score) from student group by classid;
select avg(score) from student group by classid order by classid;
select avg(score)from student group by classid order by score; 

--Without a GROUP BY clause
create table student(id integer primary key, score integer, classid integer);
insert into student values(1,90,1);
insert into student values(2,100,1);
insert into student values(2,100,2);
select count(*) from student order by classid; 
select count(*)+1 from stduent order by classid; 
              

Support for operators >=、<=、!=、<> with spaces between the characters in the syntax.

For example:

select count(*) from dual where 1 >  = 1;
select count(*) from dual where 1 <   =  1;
select count(*) from dual where 1 !     =   2;
select count(*) from dual where 1 <       >   1;
              

Support for the connect by rownum syntax. see SELECT.

For example:

-- Generate sequence
select
  rownum
from
  dual CONNECT BY rownum <= 6;
 rownum 
--------
      1
      2
      3
      4
      5
      6
(6 rows)
-- ROWNUM expression
select
  rownum
from
  duals CONNECT BY rownum + 2 - 1 <= 2 + 2;
 rownum 
--------
      1
      2
      3
(3 rows)
  
SELECT empno,ename,mgr,LEVEL FROM emp_ CONNECT BY rownum + empno < 3;
ERROR:  connect by rownum does not support rownum Op column
LINE 7:   emp_ CONNECT BY rownum + empno < 3;
                                         
select rownum from duals CONNECT BY 6 > rownum * 2;
ERROR:  connect by rownum does not support Multiplication or division operation
LINE 4:   duals CONNECT BY 6 > rownum * 2;
                             
-- Generate a sequence and add Non-SPJ operations to the sequence
select rownum from dual CONNECT BY rownum <= 6 ORDER BY 1 DESC LIMIT 2;
 rownum 
--------
      6
      5
(2 rows)
  
-- Bind variable
PREPARE my_query (VARCHAR) AS select rownum from duals CONNECT BY rownum <= $1;
EXECUTE my_query(6);
 rownum 
--------
      1
      2
      3
      4
      5
      6
(6 rows)
  
-- Not support for multiple tables
SELECT empno,ename,mgr,LEVEL FROM emp_,emp_2 where emp_.empno = emp_2.mgr CONNECT BY rownum <= 6;
ERROR:  connect by rownum not support multi tables
  
SELECT empno,ename,mgr,LEVEL FROM emp_ join emp_2 on emp_.empno = emp_2.mgr CONNECT BY rownum <= 6;
ERROR:  connect by rownum not support multi tables
  
-- Function
CREATE OR REPLACE FUNCTION generate_rownums(limit_value BIGINT)
RETURNS SETOF BIGINT AS $$ BEGIN RETURN QUERY
select
  rownum
from
  duals CONNECT BY rownum <= limit_value;
  
END;
$$ LANGUAGE plpgsql;
select generate_rownums(6);
 generate_rownums 
------------------
                1
                2
                3
                4
                5
                6
(6 rows)
          

When rownum is used as a constraint condition, the "COUNT STOPKEY" query optimization technique is supported. The principle is that when scanning a table, the scanning will stop after the number of records satisfying the rownum rownum < condition reaches the specified value, rather than continuing to scan the entire table.

For example:

-- By default, parallel processing was used, and the execution time was 568.314 ms.
explain analyze 
 select rownum, a from test where b < 10 and rownum < 100;
                                                              QUERY PLAN
 ------------------------------------------------------------------------------------------------------------------------------------
  Count StopKey  (cost=1000.00..9455.15 rows=99 width=12) (actual time=0.284..568.213 rows=4 loops=1)
    ->  Gather  (cost=1000.00..34820.60 rows=396 width=12) (actual time=0.283..568.210 rows=4 loops=1)
          Workers Planned: 4
          Workers Launched: 4
          -> Parallel Count StopKey  (cost=0.00..33781.00 rows=99 width=12) (actual time=442.731..555.289 rows=1 loops=5)
                ->  Parallel Seq Scan on test  (cost=0.00..85305.55 rows=250 width=12) (actual time=442.726..555.282 rows=1 loops=5)
                      Filter: (b < 10)
                      Rows Removed by Filter: 1999999
  Planning Time: 0.139 ms
  Execution Time: 568.314 ms
 (10 rows)
  
  
 -- Forced to use non-parallel processing, the execution time was 1618.765 ms.
 SET min_parallel_table_scan_size = 99999;
  
 explain analyze select rownum, a from test where b < 10 and rownum < 100;
                                                    QUERY PLAN
 ----------------------------------------------------------------------------------------------------------------
  Count StopKey  (cost=0.00..17726.66 rows=99 width=12) (actual time=0.016..1618.701 rows=4 loops=1)
    ->  Seq Scan on test  (cost=0.00..179057.19 rows=1000 width=12) (actual time=0.015..1618.699 rows=4 loops=1)
          Filter: (b < 10)
          Rows Removed by Filter: 9999996
  Planning Time: 0.189 ms
  Execution Time: 1618.765 ms
 (6 rows)
              

Support for single element queries in the WHERE IN clause after SELECT without parentheses, allowing direct querying.

For example:

create table test (a int,b varchar(100),c varchar2(10),d date,m timestamp(6),n ROWID);

--Numeric type
select * from test where a in 2;

--String type
select * from test where b in 'abc';
select * from test where c in 'abc';

--Date type
select * from test where d in to_date('2022-01-01','yyyy-mm-dd');
select * from test where m in to_timestamp('2022-01-01','yyyy-mm-dd');

--Rowid type
select * from test where n in '(0,1)'::tid;

--Addition and Subtraction Operations
	1.Arithmetic operations of addition and subtraction for numeric types：
select * from test where a in 2+1;
select * from test where a in 2-1;
select * from test where a in 2*1;
select * from test where a in 2/1;

	2.Arithmetic operations on table column values
select * from test t where a in t.a/1;
select * from test t where a in t.a+1;
select * from test t where a in t.a-1;
select * from test t where a in t.a*1;
select * from test t where a in t.a/1;
select * from test t where a in t.a+t.a;
select * from test t where a in t.a-t.a;
select * from test t where a in t.a*t.a;

	3.Arithmetic operations with functions
select a from test where a in TO_NUMBER(2)+2;
select a from test where a in TO_NUMBER(2)+ TO_NUMBER(2)

--Function 
SELECT * FROM test WHERE a IN CAST ('1' AS INTEGER);
select * from test where a in TO_NUMBER(2);
select * from test where  m in TIMESTAMP '2022-01-01 00:00:00' AT TIME ZONE 'Asia/Shanghai';
              

Support for the timestamp_to_scn(timestamp) function，which returns an SCN (System Change Number) based on a timestamp. see orafce.

For example:

set orafce.timezone = 'Asia/Shanghai';
select timestamp_to_scn(sysdate) from dual;
 timestamp_to_scn
------------------
          5288085
(1 row)

create table foo(a int);
insert into foo values (1);
select ora_rowscn, xmin from foo;
 ora_rowscn |  xmin
------------+---------
    5288454 | 5288454
(1 row)
              

Support for the Oracle bit_and、bit_or、bit_xor、bit_complement functions to perform bitwise operations on RAW data types. see orafce.

For example:

--The bit_and function performs an AND operation, or bitwise AND, on each bit in the raw data type.
--= 0A0B
SELECT UTL_RAW.BIT_AND(HEXTORAW('0A0B'), HEXTORAW('0F')) AS result FROM DUAL;

--The bit_or function performs an OR operation on each bit in the raw data type.
--= BBFDEF
SELECT UTL_RAW.BIT_OR(HEXTORAW('ABCDEF'), HEXTORAW('1234')) FROM dual;

--The bit_xor function performs an XOR operation on each bit in the raw data type.
--= B9F9EF
SELECT UTL_RAW.BIT_XOR(HEXTORAW('ABCDEF'), HEXTORAW('1234')) FROM dual;

--The bit_complement function performs a bitwise complement operation on each bit in the raw data type.
--= 543210
select UTL_RAW.BIT_COMPLEMENT(HEXTORAW('ABCDEF')) from dual;
              

Support for omitting the NAME keyword in the XMLELEMENT function，without affecting its functionality.XMLELEMENT is a function that formats XML tags to contain one or more expression values. see functions.

For example:

lightdb@oracle_test=# SELECT xmlelement(foo);
 xmlelement 
------------
 <foo/>
(1 row)

lightdb@oracle_test=# SELECT xmlelement(foo, xmlattributes('xyz' as bar));
    xmlelement    
------------------
 <foo bar="xyz"/>
(1 row)

lightdb@oracle_test=# SELECT xmlelement(foo, xmlattributes(current_date as bar), 'cont', 'ent');
             xmlelement              
-------------------------------------
 <foo bar="2024-03-06">content</foo>
(1 row)

lightdb@oracle_test=# 
              

When creating a function or stored procedure with a parameter of type date，it should be compatible with Oracle's date type. see orafce.

For example:

--Example of Using a Stored Procedure
create or replace procedure ptest(x date) AS
begin
	DBMS_OUTPUT.PUT_LINE('tmp: ' || x);
end;
/

call ptest(sysdate);

--Example of Using a Function
create or replace function ftest(x date)  return date AS
begin
	DBMS_OUTPUT.PUT_LINE('tmp: ' || x);
	return x;
end;
/

select ftest(sysdate);

--Example of Using a Package
create or replace package pack is
	procedure ptest(x date);
	function ftest(x date) return date;
end;
/

create or replace package body pack is
	procedure ptest(x date) as
tmp varchar(20);
	begin
		tmp := 'hello world';
DBMS_OUTPUT.PUT_LINE('tmp: ' || tmp);

	end;

	function ftest(x date) return date as
tmp varchar(20);

	begin
		tmp := 'hello world';
DBMS_OUTPUT.PUT_LINE('tmp: ' || tmp);

		return x;
	end;
end;
/

\df pack.ptest
\df pack.ftest

call pack.ptest(sysdate);
select pack.ftest(sysdate);
          

Support for the to_date function, with default date formats consistent with Oracle; supports scenarios where the delimiter between the first and second parameters does not match. see orafce.

For example:

select to_date('170458','hh24:mi:ss') FROM dual;
select to_date('10-12 17:04:58','mm-dd hh24:mi:ss') FROM dual; 
              

Enhance the TO_TIMESTAMP function to automatically match the date conversion format. 1.The delimiter in the format string supports any non-alphanumeric visible ASCII characters； 2.In the case where the input date and time do not have delimiters while the format string does, parsing should be done based on the length of the format string； 3.The format string template supports FF with a precision up to six decimal places. see functions.

For example:

lightdb@lightdb=# set datestyle to iso;
SET
lightdb@lightdb=# select to_timestamp('20231201 170000','yyyy-mm-dd hh24:mi:ss.ff');
      to_timestamp
------------------------
 2023-12-01 17:00:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('20231201 170000','yyyy/mm/dd hh24miss.ff');
      to_timestamp
------------------------
 2023-12-01 17:00:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00','yyyy-mm-dd hh24:mi:ss');
      to_timestamp
------------------------
 2024-01-20 10:30:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('2023-02-14 10:11:12.123','yyyy-mm-dd hh:mi:ss.ff');
        to_timestamp
----------------------------
 2023-02-14 10:11:12.123+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00','yyyy-mm-dd hh24:mi:ss');
      to_timestamp
------------------------
 2024-01-20 10:30:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-01-20 10:30:00.123456','yyyy-mm-dd hh24:mi:ss.us');
         to_timestamp
-------------------------------
 2024-01-20 10:30:00.123456+08
(1 row)

lightdb@lightdb=# select to_timestamp('20-jan-24','dd-mon-yy');
      to_timestamp
------------------------
 2024-01-20 00:00:00+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy-mm-dd hh:mi:ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm/dd hh:mi:ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm/dd hh/mi/ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy/mm-dd hh\mi\ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy:mm:dd hh\mi\ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2024-03-18 9:34:56','yyyy:mm:dd hh@mi@ss');
      to_timestamp
------------------------
 2024-03-18 09:34:56+08
(1 row)

lightdb@lightdb=# select to_timestamp('2011-09-14 12:52:42.123456789', 'yyyy-mm-dd hh24:mi:ss.ff');
         to_timestamp
-------------------------------
 2011-09-14 12:52:42.123456+08
(1 row)

              

Support for setting a minvalue that is less than INT64_MIN in CREATE SEQUENCE. see CREATE SEQUENCE.

For example:

lightdb@oracle_test=# show lightdb_dblevel_syntax_compatible_type ;
 lightdb_dblevel_syntax_compatible_type 
----------------------------------------
 Oracle
(1 row)

lightdb@oracle_test=# create sequence s1 minvalue -1000000000000000000000000000;
NOTICE:  minvalue out of range, set sequence min value to -9223372036854775808
CREATE SEQUENCE
lightdb@oracle_test=# \d+ s1
                                           Sequence "public.s1"
  Type  |        Start         |       Minimum        |       Maximum       | Increment | Cycles? | Cache 
--------+----------------------+----------------------+---------------------+-----------+---------+-------
 bigint | -9223372036854775808 | -9223372036854775808 | 9223372036854775807 |         1 | no      |     1

lightdb@oracle_test=# 
              

Support for user-defined functions without parameters to be executed without parentheses.

For example:

create function fn_noparam RETURN int
as
begin
  return 1;
end;
/

--= 1, simple expr
select fn_noparam from dual;
--= 2
select fn_noparam + 1 from dual;
--= 1
select 1 from dual where fn_noparam = 1;

--= 1, composed expr
select least(fn_noparam, 2) from dual;
              

Support compatibility with Oracle's view mechanism. Specifically, if the objects that a view depends on are damaged and affect the definition of the view, the view will automatically be in an incorrect state. Once the corresponding objects are restored, the view will automatically be recovered. see CREATE VIEW. see DROP VIEW.

For example: Among them, modifications to the tables or views that the view depends on in 1, 2, and 3 will invalidate the view. If subsequent operations restore the corresponding objects to the state that the view depends on, then the corresponding view will also be automatically restored.

1.Create table, view
lightdb@oradb=# create table t(a int);
CREATE TABLE
lightdb@oradb=# create view v as select a from t;
CREATE VIEW
lightdb@oradb=# insert into t(a) values(1);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---
 1
(1 row)

2.Delete table, rebuild table
lightdb@oradb=# drop table t;
DROP TABLE
lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# create table t(a int);
CREATE TABLE
lightdb@oradb=# insert into t(a) values(2);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---
 2
(1 row)

3.Modify column name, column type
lightdb@oradb=# alter table t rename a to aa;
ALTER TABLE
lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# alter table t add column a int;
ALTER TABLE
lightdb@oradb=# select * from v;
 a
---
 
(1 row)
 
lightdb@oradb=# alter table t modify a numeric;
ALTER TABLE
lightdb@oradb=# select * from v;
 a
---
 
(1 row)
 
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 a      | numeric |           |          |         | main    |
View definition:
 SELECT t.a
   FROM t;

4.Drop a column that a view depends on
lightdb@oradb=# alter table t drop a;
ALTER TABLE
lightdb@oradb=# \d+ t
                                     Table "public.t"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
 aa     | integer |           |          |         | plain   |              |
Access method: heap
 
lightdb@oradb=# select * from v;
ERROR:  view broken: public.v
lightdb@oradb=# alter table t add a int;
ALTER TABLE
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 a      | integer |           |          |         | plain   |
View definition:
 SELECT t.a
   FROM t;
 
lightdb@oradb=# insert into t(a) values(1);
INSERT 0 1
lightdb@oradb=# select * from v;
 a
---
 
 1
(2 rows)

5.Redefine the view
lightdb@oradb=# alter table t add b int;
ALTER TABLE
lightdb@oradb=# alter table t add c int;
ALTER TABLE
lightdb@oradb=# create or replace view v as select b from t;
CREATE VIEW
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 b      | integer |           |          |         | plain   |
View definition:
 SELECT t.b
   FROM t;
 
lightdb@oradb=# create or replace view v as select b,c from t;
CREATE VIEW
 
lightdb@oradb=# \d+ v
                              View "public.v"
 Column |  Type   | Collation | Nullable | Default | Storage | Description
--------+---------+-----------+----------+---------+---------+-------------
 b      | integer |           |          |         | plain   |
 c      | integer |           |          |         | plain   |
View definition:
 SELECT t.b,
    t.c
   FROM t;
              

Support for GLOBAL PARTITION BY hash syntax. see CREATE INDEX.

For example:

1.Create a partitioned table
CREATE TABLE ora_ph_t(a int,b int,c int) PARTITION BY HASH(a) partitions 4;

2.Create a partitioned index
CREATE INDEX t_global_ph_idx ON ora_ph_t(a) GLOBAL PARTITION BY hash(a) partitions 2;

3.use tablespace
\! mkdir /tmp/tbs_test_path
create tablespace tbs_test location '/tmp/tbs_test_path';
CREATE INDEX t_global_ph_idx_with_tbs ON ora_ph_t(a ASC ,b DESC) TABLESPACE tbs_test GLOBAL PARTITION BY hash(a) partitions 2;
              

Support insertion using nested table elements as the data source. see orafce.

Example 1: Create a nested table based on a table type and perform an insert operation using nested table elements as the data source.

drop table if exists t;
create table t(a int,b float,c number);
insert into t values(1,10,100),(2,20,200);
select * from t;
 
lightdb@oracle=# select * from t;
 a | b  |  c  
---+----+-----
 1 | 10 | 100
 2 | 20 | 200
(2 rows)
 
 
create or replace procedure p1 is 
  TYPE array_table IS TABLE OF t%rowtype;
  a_table array_table := array_table();
BEGIN
 
  SELECT t.* BULK COLLECT INTO a_table FROM t;
  execute immediate 'truncate table t';
 
  FOR i IN a_table.first..a_table.last LOOP
	a_table(i).a := a_table(i).a * 100;
	a_table(i).b := a_table(i).b * 100;
	a_table(i).c := a_table(i).c * 100;
    INSERT INTO t VALUES a_table(i); --Support insertion using nested table elements as the data source
  END LOOP;
 
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;
/
 
begin
  p1();
end;
/
 
select * from t;
  a  |  b   |   c   
-----+------+-------
 100 | 1000 | 10000
 200 | 2000 | 20000
(2 rows)
          

Example 2: Create a nested table based on a global type and perform an insert operation using nested table elements as the data source.

drop table if exists t;
create table t(a int,b float,c number);
insert into t values(1,10,100),(2,20,200);
select * from t;
 
lightdb@oracle=# select * from t;
 a | b  |  c  
---+----+-----
 1 | 10 | 100
 2 | 20 | 200
(2 rows)
 
create type type1 as (a int,b float,c number);
create or replace procedure p1 is 
  TYPE array_table IS TABLE OF type1;	--Create a nested table based on type
  a_table array_table := array_table();
BEGIN
  execute immediate 'truncate table t';
  a_table.extend();
  a_table.extend;
  a_table(1).a := 111;
  a_table(1).b := 111.222;
  a_table(1).c := 222.222;
  INSERT INTO t VALUES a_table(1);
  
  a_table(2).a := 100;
  a_table(2).b := 100.202;
  a_table(2).c := 222.123456789;
  INSERT INTO t VALUES a_table(2);
EXCEPTION
   WHEN OTHERS THEN
      ROLLBACK;
END;
/
				

Add an optimizer hint "no_expand hint" to cancel the OR-expansion optimization and prevent it from being performed. see lt_hint_plan.

For example:

create table t_no_expand1(key1 int, key2 int);
create table t_no_expand2(key1 int, key2 int);

lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand*/ * from t_no_expand1 where exists (select * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#0"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select * from t_no_expand1 where exists (select /*+no_expand*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#1"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2 @"lt#0"
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)

lightdb@postgres=# EXPLAIN (COSTS false) select /*+no_expand(@qb)*/ * from t_no_expand1 where exists (select/*+qb_name(qb)*/ * from t_no_expand2 where key1= 1 or key2=1) or key1 =10;
LOG:  lt_hint_plan:
used hint:
no_expand(@qb)
not used hint:
duplication hint:
error hint:

                  QUERY PLAN                  
----------------------------------------------
 Seq Scan on t_no_expand1 @"lt#0"
   Filter: ($0 OR (key1 = 10))
   InitPlan 1 (returns $0)
     ->  Seq Scan on t_no_expand2 @qb
           Filter: ((key1 = 1) OR (key2 = 1))
(5 rows)
        

Add 5 new features （ECPG(Oracle Pro*c compatible)）. see ECPG(Oracle Pro*c compatible).

oracle_fdw supports enhanced join pushdown. see oracle_fdw.

oracle_fdw Supports operator and function pushdown. see oracle_fdw.

oracle_fdw oracle.date/varchar2 type. see oracle_fdw.

Support the string data type，allowing users to define this data type in stored procedures. see Data Type.

Support the long data type, allowing users to define this data type in stored procedures. The long type has similar functional characteristics as the clob type. see Data Type.

Support the pls_integer data type, allowing users to define this data type in stored procedures. see Data Type.

Support nested tables with RECORD type specification. see orafce.

Support the use of varchar2 type for associative array index columns in stored procedures and packages. see orafce.

Support the usage of default values for Oracle functions/stored procedures, where parameters with default values can be followed by parameters without default values. see CREATE FUNCTION. see CREATE PROCEDURE.

Support the mod operator in stored procedures, maintaining consistency with Oracle's behavior. see functions.

Support the DBMS_DATAPUMP package to achieve high-performance data import and export. see orafce.

EXECUTE IMMEDIATE supports the bulk collect clause to specify where the rows returned by the SQL command should be allocated. see orafce.

Cursors without parameter declarations are supported in stored procedures, and can be invoked with parentheses when opened. see orafce.

Storage procedure creation supports different record types containing the same field names. see orafce.

Storage procedure creation supports inner and outer references to variables see orafce.

Add feature to directly assign values to nested table elements using the assignment symbol :=. see orafce.

Support for PL/SQL custom exception functionality, allowing the throwing and handling of custom exceptions to extend business-specific exception handling and enrich PL/SQL behavior. see orafce.

Support users to explicitly use the ROLLBACK statement in stored procedures, functions, and anonymous blocks, greatly enhancing the flexibility of transaction management. see orafce.

Support the increase of the maximum number of parameters that users can create in functions from 100 to 200. see GUC Parameter Settings.

Support for Oracle's DBMS_SQL package. see orafce.

Support for Oracle's UTL_FILE package. see orafce.

Support nested usage of the WITH clause when cursors are implicitly declared. see orafce.

LightDB-x supports K8S deployment on the ARM platform.

LightDB-x supports the openEuler operating system.

Adjust the level of the 'serial' keyword. see SQLkeyword.

LightDB-x supports the synonym feature. see CREATE DATABASE.

Support for pushdown optimization features in multi-table hierarchical queries.

A new GUC parameter, lightdb_analyze_function_bodies, has been added. When this parameter is set to 'on', it will enable the checking of the existence of tables and their columns.

LightDB-x supports the impdb import command.

LightDB-x supports the CREATE/DROP DIRECTORY features. see CREATE DIRECTORY. see DROP DIRECTORY.

LightDB-x supports the CREATE/DROP/ALTER TRIGGER features. see CREATE TRIGGER. see DROP TRIGGER. see ALTER TRIGGER.

Support for parsing CREATE TYPE BODY syntax.

Support for converting VARCHAR to BOOLEAN.

Support for implicit data type conversion in Oracle mode.

Enhanced security and anti-SQL injection capabilities.