oracle 10.2.0.4執行一個樹查詢的問題
Oracle10204的一個樹查詢的問題:
建立測試環境:
-- Create table
create table TEST
(
SEQ NUMBER,
VAL VARCHAR2(6)
);
insert into test (SEQ, VAL) values (3, '001');
insert into test (SEQ, VAL) values (4, '002');
insert into test (SEQ, VAL) values (5, '003');
insert into test (SEQ, VAL) values (2, '004');
insert into test (SEQ, VAL) values (5, '005');
insert into test (SEQ, VAL) values (2, '006');
insert into test (SEQ, VAL) values (3, '007');
insert into test (SEQ, VAL) values (2, '008');
insert into test (SEQ, VAL) values (1, '009');
insert into test (SEQ, VAL) values (5, '010');
SQL> select * from test order by seq;
SEQ VAL
---------- ------
1 009
2 008
2 004
2 006
3 001
3 007
4 002
5 010
5 003
5 005
已選擇10行。
現在目的是以seq進行分組,連線每個分組下的val字串,如上面seq=3的記錄有兩個對應的val值:001和007,現在將這兩個值合併,按理應該是:"001||007”這樣的結果,但是實際的查詢情況卻出現了異常。
取seq=3的記錄進行測試,做查詢:
SQL> select aa.*, sys_connect_by_path(aa.val, '||') strval from (
2 select t.*, row_number()over(partition by t.seq order by t.seq) rn
3 from test t where t.seq = 3) aa
4 start with aa.rn = 1
5 connect by prior aa.rn = aa.rn - 1
6 /
SEQ VAL RN STRVAL
---------- ------ ---------- --------------------
3 001 1 ||001
3 001 2 ||001||001
由以上查詢可以看出並非按照前面我們所預想的結果。那假如是對應4個val的情況呢?見以下查詢:
SQL> select aa.*, sys_connect_by_path(aa.val, '||') strval from (
2 select t.*, row_number()over(partition by t.seq order by t.seq) rn
3 from test t where t.seq = 5) aa
4 start with aa.rn = 1
5 connect by prior aa.rn = aa.rn - 1
6 /
SEQ VAL RN STRVAL
---------- ------ ---------- --------------------
5 003 1 ||003
5 010 2 ||003||010
5 005 3 ||003||010||005
卻發現此時是正常的。
這個問題現在只在oracle 10.2.0.4這個版本中發現,在10.2.0.1和9i的版本中都未發現這個。按我的理解這個應該是一個bug了,可惜沒有metalink的帳號,不然可以去查檢視了。
執行計劃如下:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
Plan hash value: 1593190829
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 180 | 6 (17)| 00:00:01 |
|* 1 | CONNECT BY WITH FILTERING| | | | | |
|* 2 | VIEW | | 4 | 376 | 6 (17)| 00:00:01 |
|* 3 | WINDOW SORT PUSHED RANK| | 4 | 248 | 6 (17)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TF | 4 | 248 | 5 (0)| 00:00:01 |
|* 5 | HASH JOIN | | | | | |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------
| 6 | CONNECT BY PUMP | | | | | |
| 7 | VIEW | | 4 | 180 | 6 (17)| 00:00:01 |
| 8 | WINDOW SORT | | 4 | 128 | 6 (17)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | TF | 4 | 128 | 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("RN2"-1=PRIOR "RN2")
2 - filter("RN2"=1)
3 - filter(ROW_NUMBER() OVER ( PARTITION BY "C_CUSTNO" ORDER BY
"C_CUSTNO")<=1)
4 - filter("C_CUSTNO"='000000016643')
5 - access("RN2"-1=PRIOR "RN2")
9 - filter("C_CUSTNO"='000000016643')
可以看到Oracle首先做子查詢,然後做connect by形成一個view。然後在做一個子查詢並限制row_number的條件為<=1,然後再做rn2=1的過濾,在執行connect by操作。這段理解的不是很清楚,後面再摸索摸索。
問題原因目前還不知道,但是肯定是Oracle的執行計劃最佳化導致的,因為我如果採用了RBO提示(加了rule提示),就出現的結果就和我們所想要的結果一致了:
SQL> select /*+rule*/aa.*, sys_connect_by_path(aa.val, '||') strval from (
2 select t.*, row_number()over(partition by t.seq order by t.seq) rn
3 from test t where t.seq =3) aa
4 start with aa.rn = 1
5 connect by prior aa.rn = aa.rn - 1
6 /
SEQ VAL RN STRVAL
---------- ------ ---------- --------------------
3 007 1 ||007
3 001 2 ||007||001
檢視結果,兩個SQL除了hint不同,其他都相同,但是結果完全不同:
SQL> select /*+rule*/aa.*, sys_connect_by_path(aa.val, '||') strval from (
2 select t.*, row_number()over(partition by t.seq order by t.seq) rn
3 from test t where t.seq =3) aa
4 start with aa.rn = 1
5 connect by prior aa.rn = aa.rn - 1
6 /
SEQ VAL RN STRVAL
---------- ------ ---------- --------------------
3 007 1 ||007
3 001 2 ||007||001
SQL> select aa.*, sys_connect_by_path(aa.val, '||') strval from (
2 select t.*, row_number()over(partition by t.seq order by t.seq) rn
3 from test t where t.seq =3) aa
4 start with aa.rn = 1
5 connect by prior aa.rn = aa.rn - 1
6 /
SEQ VAL RN STRVAL
---------- ------ ---------- --------------------
3 001 1 ||001
3 001 2 ||001||001
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/12932950/viewspace-613199/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 一個使用JDBC按Date查詢查詢的問題JDBC
- 一個MySQL多表查詢的問題MySql
- Oracle中SQL語句執行效率問題的查詢與解決OracleSQL
- 一個簡單的樹查詢
- oracle 正在執行的物件查詢Oracle物件
- oracle 樹查詢Oracle
- Oracle 查詢某個session正在執行的sql語句OracleSessionSQL
- oracle樹形查詢Oracle
- 查詢Oracle正在執行的SQL語句OracleSQL
- 查詢Oracle正在執行和執行過的SQL語句OracleSQL
- Java NIO 執行緒 的一個問題Java執行緒
- 一個多執行緒的PushbackInputStream問題執行緒
- 一條查詢sql的執行之路SQL
- 一個oracle查詢引起的bugOracle
- 問一個守護執行緒問題?執行緒
- 基於UNION ALL的分頁查詢執行計劃問題
- mysql 5.7.11查詢分割槽表的一個問題MySql
- 查詢oracle正在執行的SQL和事務OracleSQL
- 並行查詢緩慢的問題分析並行
- 請教一個多執行緒的問題執行緒
- 同一欄位多個查詢條件時遇到的一個問題
- 客戶一套ORACLE 10.2.0.4 的crs 問題處理Oracle
- 基於UNION ALL的分頁查詢執行計劃問題(二)
- 一條查詢語句的執行流程
- oracle樹形選單查詢Oracle
- 如何查詢一個儲存過程是否在執行儲存過程
- 請教一個jsp查詢速度慢的問題。JS
- oracle JOB 查詢 新增 修改 刪除 執行Oracle
- MySQL函式查詢目錄樹問題記錄MySql函式
- Oracle多層級查詢相容的效能問題Oracle
- 一個問題諮詢
- oracle dbms_sql執行查詢select_dml_ddl(一)OracleSQL
- 文章主題: 在Oracle中查詢剛才執行過的SQL語句OracleSQL
- 一個“指令碼執行夯死”問題的分析指令碼
- 一個執行計劃解析的小問題分析
- 請教一個關於執行緒的問題執行緒
- 二叉查詢樹的個數
- 請教個問題執行 httprunner 遇到的問題HTTP