[SQL] ORA-00937：非單組分組函數

Error

ORA-00937：非單組分組函數


Solution

如果 select 了一個分組函數，其它也必須是分組函數，否則就出錯。這是語法規定的

ex:

select min(sal), max(sal) from emp;

* Reference
- 韓順平老師Oracle教程筆記——網摘_StackDoc

[SQL] Index

in, like 不會使用 index

[SQL] ORA-00932: 不一致的資料類型: 應該是 -, 但為 CLOB

Error Message

ORA-00932: 不一致的資料類型: 應該是 -, 但為 CLOB



Solution

當 field type is lob

可以寫

... where field like xxxx

但不能寫成

... where field = xxxx

[SQL] 分頁 performance

select * from(
  select x.*, rownum r from(
    ...
  )x
)y where y.r >= 1 and y.r <= 10


select * from (
  select row_.*, rownum rownum_ from (
    ...
  ) row_ where rownum <= 10
) where rownum_ > 0

寫法二會比寫法一快很多

而 JPA 的 Query setFirstResult(), setMaxResults() 生成的分頁 SQL 就會是寫法二

但 rownum <= 的值越大，查詢時間會越久

所以翻頁翻到越後面 頁面顯示的速度會越慢

[SQL] AND has more priority than OR

* Priority

• In SQL: AND > OR
• In Java: && > ||

SELECT count(*) FROM TABLE_A
    WHERE (X= 2 OR X = 5) AND Y = 0;
   
SELECT count(*) FROM TABLE_A
    WHERE X IN (2,5) AND Y = 0;

[SQL][Java] Date

java.sql.date:  沒有時分秒

java date: 到時分秒

[iBatis] ||

'%'||#keyword#||'%'   

• || 等同於字串所使用的 +  ->> '%keyword%' 
• 若是 '%'keyword'%' 則會變成 '%''keyword''%'

若是操作兩張 tables 沒設 join or where condition 

• 則結果會是兩張 tables 相乘

[SQL] select...from...where...

select: 決定使用的欄位(column)

from: 資料的來源(所有被列在此的來源都會包含入)

where: 決定取出的資料筆 (row)

[SQL] COALESCE and NVL

[COALESCE]

• A part of ANSI-92 standard.
• 回傳第一個不為 null 的值。

COALESCE(expression1,...n) 等於下列 CASE 運算式：
CASE
    WHEN (expression1 IS NOT NULL) THEN expression1
    WHEN (expression2 IS NOT NULL) THEN expression2
    ...
    ELSE expressionN
END


[NVL]

• NVL(expression1,expression2)
• expression1 ? expression1 : expression2
• 若 expression1 不為 null 則回傳 expression1，若為 null 則回傳 expression。
• Is Oracle specific, it was introduced in 80's before there were any standards.

In case of two values, they are synonyms. However, they are implemented differently.

* Reference
- Oracle Differences between NVL and Coalesce
- COALESCE (Transact-SQL)
- Coalesce Function
- SQL 設計小技巧--用 ISNULL 或 NVL 達到選擇性條件的下法
- PL/SQL NVL(轉換null)，如何取代Null?

[SQL] ORA-00979: 不是一個 GROUP BY 表示式

Error Message

main WARN org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:233) - SQL Error: 979, SQLState: 42000
main ERROR org.hibernate.util.JDBCExceptionReporter.logExceptions(JDBCExceptionReporter.java:234) - ORA-00979: 不是一個 GROUP BY 表示式


Solution

做為 ORDER BY 的欄位

如果有使用到 GROUP BY 的話

那些欄位也要在 GROUP BY 中

[Design] SQL condition

若 A、B、C 都要限制條件 x

可以以外層條件來寫 A.x = B.x and A.x = C.x

這樣 value 只要寫一次就可以了

......

form A

join A.x = B.x and A.x = C.x

where A.x = 'value'

[SQL] condition order

執行順序如下:

1. where
2. group by
3. having

where 會在 group by 前執行

having 在 group by 後才執行

[SQL] Join

Join 可分兩種:

• 有關係的 tables，則可使用一般見到的 join ... on ...
沒有關係的 tables，則是

select ...
from TABLENAME1, TABLENAME2
where (condition between TABLENAME1 and TABLENAME2)

[SQL] OVER, GROUP BY, PARTITION BY

* GROUP BY
是單純的對 query result 做分組。
經常和 aggregate function 一起使用 (AVG(), COUNT()...)

* PARTITION BY
analysis function，是會根據此分組去 query。

* 可見 sum()over() - 闲来之笔 - 博客频道 - CSDN.NET 範例

* OVER

FUNCTION_NAME(<argument>, <argument>...)
OVER
(<Partition-Clause><Order-by-Clause><Windowing Clause>)

* Reference
- oracle 分析函数over
- oracle partition by与group by 的区别 - 郑云飞博客 - ITeye技术网站
- oracle rank,over partition 函数 - Laughing - 博客频道 - CSDN.NET
- sum()over() - 闲来之笔 - 博客频道 - CSDN.NET
- PLSQL中over(partition by .. order by ..)的使用 - Alinaxz的专栏 - 博客频道 - CSDN.NET

[Oracle] SYS_CONNECT_BY_PATH

• SYS_CONNECT_BY_PATH is valid only in hierarchical queries.
• Provided from oracle9i.
• It returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition.
• Both column and char can be any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2.
• The string returned is of VARCHAR2 datatype and is in the same character set as column.

* 在 Oracle 中，rownum 會比 group by、order by更先執行，因此要用子查詢。

select xxx from (select xxx from xxx order by xxx) where rownum<=xx


* Reference
- SYS_CONNECT_BY_PATH
- Rownum搭配Order by - 馬小玲傳說- 點部落
- SYS_CONNECT_BY_PATH函数用法 ORACLE - XZC.Log - BlogJava

[Database] SQL note

一般而言，資料庫的語法 ( SQL ) 分為三大類別：

1. DDL ( Data Definition Language )：定義資料庫物件使用的語法，常看到的關鍵字有：

• Create：建立資料庫的物件。
• Alter：變更資料庫的物件。
• Drop：刪除資料庫的物件。

2. DCL ( Data Control Language )：控制資料庫物件使用狀況的語法，常看到的關鍵字有：

• Grant：賦予使用者使用物件的權限。
• Revoke：取消使用者使用物件的權限。
• Commit：Transaction 正常作業完成。
• Rollback：Transaction 作業異常，異動的資料回復到 Transaction 開始的狀態。

3. DML ( Data Manipulation Language )：維護資料庫資料內容的語法，常看到的關鍵字有：

• Insert：新增資料到 Table 中。
• Update：更改 Table 中的資料。
• Delete：刪除 Table 中的資料。
• Select：選取資料庫中的資料。

* 沒有指定WHERE，則所有紀錄都會被更動，請注意小心使用!!


* Reference
- SQL語法

[Oracle] Hierarchical Queries

CONNECT BY [NOCYCLE] [condition] START WITH [condition]

(or)

START WITH [condition1] CONNECT BY [condition2]

SELECT *
FROM PERSON p
CONNECT BY  p.name = PRIOR p.ID
START WITH p.ID = 0

* CONNECT BY

• Specifies the relationship between parent rows and child rows of the hierarchy.
• 指定 Hierarchical Queries 中 parent rows and child rows 之間的關係，當前資料會與對應的 parent rows 做比較。
• One expression in condition must be qualified with the PRIOR operator to refer to the parent row
• condition 中必須有一個 PRIOR，且不能包含子查詢。
• CONNECT_BY_ISLEAF

* START WITH

• Specifies the root row(s) of the hierarchy.
• 指定 Hierarchical Queries 的 root (一筆或多筆)，所有滿足 condition 的會被當作是 root rows。
• 可以省略，但若沒有指定給 root，則所有資料都會被當成 root，分別掃瞄。
• condition 可以是一個子查詢。

* PRIOR

• 指定 parent rows。
• Is most commonly used when comparing column values with the equality operator.
• ex: p.name = PRIOR p.ID，表示 id of prior row = name of current name。
• prior 放在子節點端，表示是以 START WITH 指定的節點做為跟節點，由上往下掃瞄，可能對一個或多個分支。
• prior 放在父節點端，表示是以 START WITH 指定的節點做為最低層子節點，從下往上掃瞄，直到根節點為只，這種情況只能得到一個分支。

* LEVEL

• Level of root row is 1，所以其 child rows 的 LEVEL = 2。

* SQL 執行順序

1. JOIN，無論是 JOIN ON 還是在 WHERE 中做的關連
2. CONNECT BY
3. 其它的WHERE條件

* Reference
- Oracle Connect By
- The Oracle PL/SQL PRIOR Operator
- Hierarchical Queries
- 【原】Oracle开发专题之：级联查询(Hierarchical Queries) - pengpenglin - BlogJava
- Oracle Handbook系列之一：结构化查询(Hierarchical Queries) - SnowToday - 博客园
- oracle树中prior的用法 - space6212的个人空间 - ITPUB个人空间 - powered by X-Space

[iBatis] SQL Map

* 使用SQL Map優點:

• 能夠大大減少訪問關係數據庫的代碼。
• 使用簡單的XML配置文件將Java Bean映射成SQL語句，對比其他的數據庫持續層和ORM框架（如JDO的實現，Hibernate等），SQL Map最大的優點在於它簡單易學。
• 要使用SQL Map，只要熟悉Java Bean，XML和SQL，就能使您充分發揮SQL語句的能力。

* SQL Map的概念:

• SQL Map API讓開發人員可以輕易地將Java Bean映射成PreparedStatement的輸入參數和ResultSet結果集。
• 開發SQL Map的想法很簡單：提供一個簡潔的架構，能夠用20％的代碼實現80％JDBC的功能。

* SQL Map如何工作?

• SQL Map提供了一個簡潔的框架，使用簡單的XML描述文件將Java Bean，Map實現和基本數據類型的包裝類（String，Integer等）映射成JDBC的PreparedStatement。

* 以下流程描述了SQL Maps的高層生命週期：


將一個對象作為參數（對象可以是Java Bean，Map實現和基本類型的包裝類），參數對象將為SQL修改語句和查詢語句設定參數值。

1. 執行mapped statement。這是SQL Maps最重要的步驟。 SQL Map框架將創建一個PreparedStatement實例，用參數對象為PreparedStatement實例設定參數，執行PreparedStatement並從ResultSet中創建結果對象。
2. 執行SQL的更新數據語句時，返回受影響的數據行數。執行查詢語句時，將返回一個結果對像或對象的集合。和參數對像一樣，結果對象可以是Java Bean，Map實現和基本數據類型的包裝類。

* Reference
- iBATIS SQL Map (1)_StackDoc

[Oracle] TableSpace

* Definition
是Oracle空間管理上的邏輯單位，實體上存放資料的是Tablespace裡面的檔案(Data File)；而我們所熟悉的Table就放在這 一個一個的檔案裡面。
所以TableSpace可以看成是Data File的群組，是一對多的關係。


* 通常在管理上會把使用者的資料與Oracle系統的物件以不同的Tablespace做切分 。如果一個Oracle上有多個不同的AP系統，以不同的TableSpace做切割，則可以達到管理與備份的目的。但是TableSpace的功用也不僅僅只是簡單的群組分類而已，Oracle 提供了許多功能上的參數來設定TableSpace來達到空間管理與效能增進的目的。

* 有必要提的是，TableSpace沒辦法跨資料庫，TableSpace中的Data File沒辦法跨TableSpace，Data File中的Table (Segement)可以跨 Data File，但不能跨TableSpace。
簡單的來說，一個Table裡面的資料是有可能因為Oracle的空間分配而分布在同一個TableSpace的 不同的Data File中的；因此一個Data File創出來後，是不能隨便刪除的，這將會造成嚴重的資料損毀的問題。


* SYSTEM與Non-SYSTEM TableSpace
當資料庫剛建立起來，系統會建立一個叫做SYSTEM的系統TableSpace，存放SYS、SYSTEM等User重要的系統資料(ex:資料字典與預儲程序等) 如果我們建立Oracle User時，不指定預設的TableSpace，則此User則會以SYSTEM TableSpace作為預設的TableSpace。 這將造成管理上的混亂與嚴重的效能問題，這是必須特別注意的。


* 傳統的Oracle管理概念中，傾向一個tablespace中創建多個data file，特別是在多個儲存位置時，以分散 I/O，但10g後推出了BigFile Tablespace。



* Reference
- TableSpace介紹
- Oracle Bigfile Tablespace大文件表空间 - 求道的路上 - ITPUB个人空间 - powered by X-Space

[Database] Oracle ROWNUM and ROWID

[ROWNUM]

* Definition
- 執行結果資料的列數
- Is a pseudo-column that returns a row's position in a result set.
- Is evaluated AFTER records are selected from the database and BEFORE the execution of ORDER BY clause.
- Only exists for a row once it is retrieved from a query. It represents the sequential order in which Oracle has retrieved the row. Therefore it will always exist, be at least 1, and be unique (among the rows returned by the query).


* 運作方式:

1. Oracle executes your query.
2. Oracle fetches the first row and calls it row number 1.
3. Have we gotten past row number meets the criteria? If no, then Oracle discards the row, If yes, then Oracle return the row.
4. Oracle fetches the next row and advances the row number (to 2, and then to 3, and then to 4, and so forth).
5. Go to step 3.

- 使用 ROWNUM > (condition) 是不合理的，因為在第三步時會不符合條件而不在結果內，第四步查出來的 ROWNUM 仍然是1，所以永遠也不會達成條件。
- 同樣道理，ROWNUM 如果單獨用 = ，也只有在 ROWNUM=1 時才有用。

* 因此，如果需要查詢 ROWNUM 在某區間的資料時，就必須使用子查詢的方式，但數據量大時會影響速度。
- ex: select * from(select rownum no ,id,name from student) where no>2;。
- 注意子查詢中的 rownum 必須要有别名，否則還是會查不到。因為 ROWNUM 不是真正屬於某個表的列，若沒有別名會無法判斷是子查詢還是主查詢的 ROWNUM。

* 還有，若是和 ORDER BY 同時使用的話，也必須使用子查詢，因為 ROWNUM 會比 ORDER BY 先被執行而指定，指定完列數後才會被 ORDER BY，這樣一來，最後的 ROWNUM 也亂掉了。


[ROWID]

* ROWID actually represents the physical location of the record/row in the database. That being the case, it is (according to Oracle documentation) the fastest way to retrieve a particular row. Faster than an index, even.

* Also both referred to as pseudo-columns. That is, they are not "real" columns that will show up when you DESC a table. They don't actually exist anywhere in the database. But they're available for you to use.



* Reference
- ROWNUM - Oracle FAQ
- OracleBlog: ROWNUM and ROWID
- Oracle的rownum原理和使用 - DCTM + SAP - ITeye技术网站 **