ITPub博客

Specify DETERMINISTIC to indicate that the function returns the same result value whenever it is called with the same values for its arguments.You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that ...

1. Group-by，最简单的办法select owner from all_objects group by owner;2. 分析函数select owner from (select owner, row_number() over (partition by owner order by owner) rnfrom all_objects) where rn=1;

Oracle Arithmetic Function

model looping spreadsheet reference table ordered rule

Oracle SQL Model

Windowing rows range unbounded preceding following current_row

CTAS employs the direct path load, in other words, it skips loading data into buffer cache. PGA consumptionConsider two scenarios:a) Create table target as select * from source;The result of select statement is not saved in memory, Oracle writes it directly to di...

Time series Reporting LAG data densification Pivot Decode

Grouping_id Group_ID Oracle SQL

Oracle subtotal grand total

Ratio_to_report Percentage Performance

--Find out what objects are locked.select   c.owner,   c.object_name,   c.object_type,   b.sid,   b.serial#,   b.status,   b.osuser,   b.machinefrom   v$locked_object a ,   v$session b,&n...

SQL> show relrelease 1102000300SQL> select dept.deptno, emp.enamefrom dept left outer join emp on dept.deptno=emp.deptnoorder by deptno nulls last;    DEPTNO ENAME-------...

select replace('abc', 'a') from dual;REPLACE('abc','a')---------------------------bc 

show relrelease 1102000300desc orders;Name         Null Type         ------------ ---- ------------ ORDER_ID     &nb...

在Oracle 官方文档，semi-join是这么解释的：A semijoin returns rows that match an EXISTS subquery without duplicating rowsfrom the left side of the predicate when multiple rows on the right ...

先上语法规范：SELECT ....FROM <table-expr> PIVOT ( aggregate-function(<column>) FOR <pivot-column> IN (<value1>, <value2>,..., <valuen>) ) AS <alias>WHERE .....通过...

先上语法：LISTAGG ( column | expression, delimiter ) WITHIN GROUP (ORDER BY column | expression)这是一个聚集函数。通过一个例子来说明其用法:SELECT department_id, LISTAGG(last_name, '; ')&nbsp...