On 10g R2 we want a hierarchical query to return only the branches which at least have one not NULL leaf ;
hierarchical sql-how to get only branches with at least one not null leaves
2197504
But its performance is not as required, we plan to implement the filtering on the client side, any comments are welcomed for database side query;
set linesize 1500
set timing on
set autotrace traceonly
-- returns data with filter
SELECT /*+ first_rows */
nayar_id,
nparent_id,
vayar_menu_desc,
vayar_desc
FROM (SELECT *
FROM tcihaz_ayar
WHERE (ncihaz_id = 12605 OR ncihaz_id = 0)
AND ndil_id = 10)
WHERE nayar_id NOT IN
(SELECT /*+ first_rows */
nayar_id
FROM (SELECT /*+ first_rows */
b.*, SUM(resulting) over(PARTITION BY root) sumresulting
FROM (SELECT /*+ first_rows */
connect_by_root a.nayar_id root,
decode(connect_by_root a.nayar_id,
nayar_id,
1,
0) isroot,
connect_by_isleaf isleaf,
nvl2(vayar_desc,
1,
0) notnull,
decode(connect_by_root a.nayar_id,
nayar_id,
0,
1) * connect_by_isleaf *
nvl2(vayar_desc,
1,
0) resulting,
LEVEL lev,
a.*
FROM (SELECT *
FROM tcihaz_ayar
WHERE (ncihaz_id = 12605 OR ncihaz_id = 0)
AND ndil_id = 10) a
CONNECT BY PRIOR nayar_id = nparent_id) b
WHERE b.isleaf <> b.lev) c
WHERE c.sumresulting = 0)
CONNECT BY PRIOR nayar_id = nparent_id
START WITH nayar_id IN (SELECT /*+ first_rows */
nayar_id
FROM (SELECT /*+ first_rows */
*
FROM tcihaz_ayar
WHERE (ncihaz_id = 12605 OR ncihaz_id = 0)
AND ndil_id = 10)
WHERE nparent_id = 0)
ORDER BY nayar_id,
nparent_id,
vayar_menu_desc,
vayar_desc ;
286 rows selected.
Elapsed: 00:01:33.12
Execution Plan
----------------------------------------------------------
Plan hash value: 1062547021
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 43 | 5 (20)| 00:00:01 |
| 1 | SORT ORDER BY | | 1 | 43 | 5 (20)| 00:00:01 |
|* 2 | FILTER | | | | | |
|* 3 | CONNECT BY WITH FILTERING | | | | | |
|* 4 | TABLE ACCESS BY INDEX ROWID | TCIHAZ_AYAR | | | | |
| 5 | NESTED LOOPS | | 1 | 31 | 5 (20)| 00:00:01 |
|* 6 | VIEW | index$_join$_012 | 1 | 18 | 4 (25)| 00:00:01 |
|* 7 | HASH JOIN | | | | | |
|* 8 | INDEX RANGE SCAN | NUI_TCIHAZ_AYAR_NPARENT_ID | 1 | 18 | 3 (0)| 00:00:01 |
| 9 | INLIST ITERATOR | | | | | |
|* 10 | INDEX RANGE SCAN | NUI_CHZ_AYR_CHZ_DIL_AYR | 1 | 18 | 10 (20)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID | TCIHAZ_AYAR | 1 | 13 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PK_CIHAZ_AYAR | 1 | | 0 (0)| 00:00:01 |
| 13 | NESTED LOOPS | | | | | |
| 14 | BUFFER SORT | | | | | |
| 15 | CONNECT BY PUMP | | | | | |
|* 16 | TABLE ACCESS BY INDEX ROWID | TCIHAZ_AYAR | 1 | 43 | 4 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | NUI_TCIHAZ_AYAR_NPARENT_ID | 2 | | 3 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | TCIHAZ_AYAR | 1 | 43 | 4 (0)| 00:00:01 |
|* 19 | VIEW | | 412 | 10712 | 2753 (1)| 00:00:34 |
| 20 | WINDOW SORT | | 412 | 26780 | 2753 (1)| 00:00:34 |
|* 21 | VIEW | | 412 | 26780 | 2752 (1)| 00:00:34 |
|* 22 | CONNECT BY WITHOUT FILTERING| | | | | |
|* 23 | TABLE ACCESS BY INDEX ROWID| TCIHAZ_AYAR | 412 | 12360 | 2752 (1)| 00:00:34 |
|* 24 | INDEX RANGE SCAN | REF5573 | 133K| | 270 (2)| 00:00:04 |
----------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ FIRST_ROWS */ 0 FROM (SELECT /*+ FIRST_ROWS */ "B"."ROOT"
"ROOT","B"."ISROOT" "ISROOT","B"."ISLEAF" "ISLEAF","B"."NOTNULL" "NOTNULL","B"."RESULTING"
"RESULTING","B"."LEV" "LEV","B"."NAYAR_ID" "NAYAR_ID","B"."NDIL_ID" "NDIL_ID","B"."VAYAR_DESC"
"VAYAR_DESC","B"."NAYAR_CESIDI_ID" "NAYAR_CESIDI_ID","B"."DTANIM_TARIHI"
"DTANIM_TARIHI","B"."VTANIMLAYAN" "VTANIMLAYAN","B"."NCIHAZ_ID" "NCIHAZ_ID","B"."VAYAR_MENU_DESC"
"VAYAR_MENU_DESC","B"."NPARENT_ID" "NPARENT_ID",SUM("RESULTING") OVER ( PARTITION BY "ROOT")
"SUMRESULTING" FROM (SELECT /*+ FIRST_ROWS */ CONNECT_BY_ROOT "TCIHAZ_AYAR"."NAYAR_ID"
"ROOT",DECODE(CONNECT_BY_ROOT "TCIHAZ_AYAR"."NAYAR_ID","TCIHAZ_AYAR"."NAYAR_ID",1,0)
"ISROOT",CONNECT_BY_ISLEAF "ISLEAF",NVL2("TCIHAZ_AYAR"."VAYAR_DESC",1,0)
"NOTNULL",DECODE(CONNECT_BY_ROOT "TCIHAZ_AYAR"."NAYAR_ID","TCIHAZ_AYAR"."NAYAR_ID",0,1)*CONNECT_BY_ISLEA
F*NVL2("TCIHAZ_AYAR"."VAYAR_DESC",1,0) "RESULTING",LEVEL "LEV","TCIHAZ_AYAR"."NAYAR_ID"
"NAYAR_ID","TCIHAZ_AYAR"."NDIL_ID" "NDIL_ID","TCIHAZ_AYAR"."VAYAR_DESC"
"VAYAR_DESC","TCIHAZ_AYAR"."NAYAR_CESIDI_ID" "NAYAR_CESIDI_ID","TCIHAZ_AYAR"."DTANIM_TARIHI"
"DTANIM_TARIHI","TCIHAZ_AYAR"."VTANIMLAYAN" "VTANIMLAYAN","TCIHAZ_AYAR"."NCIHAZ_ID"
"NCIHAZ_ID","TCIHAZ_AYAR"."VAYAR_MENU_DESC" "VAYAR_MENU_DESC","TCIHAZ_AYAR"."NPARENT_ID" "NPARENT_ID"
FROM "TCIHAZ_AYAR" "TCIHAZ_AYAR" WHERE "NDIL_ID"=10 AND ("NCIHAZ_ID"=12605 OR "NCIHAZ_ID"=0)) "B" WHERE
"B"."ISLEAF"<>"B"."LEV") "C" WHERE "NAYAR_ID"=:B1 AND "C"."SUMRESULTING"=0))
3 - access("TCIHAZ_AYAR"."NPARENT_ID"=PRIOR "TCIHAZ_AYAR"."NAYAR_ID")
4 - filter(("NCIHAZ_ID"=12605 OR "NCIHAZ_ID"=0) AND "NDIL_ID"=10)
6 - filter("TCIHAZ_AYAR"."NPARENT_ID"=0 AND "NDIL_ID"=10 AND ("NCIHAZ_ID"=0 OR "NCIHAZ_ID"=12605))
7 - access(ROWID=ROWID)
8 - access("TCIHAZ_AYAR"."NPARENT_ID"=0)
10 - access("NCIHAZ_ID"=0 OR "NCIHAZ_ID"=12605)
11 - filter("NDIL_ID"=10 AND ("NCIHAZ_ID"=0 OR "NCIHAZ_ID"=12605))
12 - access("TCIHAZ_AYAR"."NAYAR_ID"="TCIHAZ_AYAR"."NAYAR_ID")
16 - filter("NDIL_ID"=10 AND ("NCIHAZ_ID"=12605 OR "NCIHAZ_ID"=0))
17 - access("TCIHAZ_AYAR"."NPARENT_ID"=PRIOR "TCIHAZ_AYAR"."NAYAR_ID")
18 - access("TCIHAZ_AYAR"."NPARENT_ID"=PRIOR "TCIHAZ_AYAR"."NAYAR_ID")
filter(("NCIHAZ_ID"=12605 OR "NCIHAZ_ID"=0) AND "NDIL_ID"=10)
19 - filter("NAYAR_ID"=:B1 AND "C"."SUMRESULTING"=0)
21 - filter("B"."ISLEAF"<>"B"."LEV")
22 - access("TCIHAZ_AYAR"."NPARENT_ID"=PRIOR "TCIHAZ_AYAR"."NAYAR_ID")
23 - filter("NCIHAZ_ID"=12605 OR "NCIHAZ_ID"=0)
24 - access("NDIL_ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1039791 consistent gets
0 physical reads
0 redo size
12619 bytes sent via SQL*Net to client
622 bytes received via SQL*Net from client
21 SQL*Net roundtrips to/from client
759 sorts (memory)
0 sorts (disk)
286 rows processed
-- returns all data without filter
SELECT nayar_id, nparent_id, vayar_menu_desc, vayar_desc
FROM tcihaz_ayar
WHERE (ncihaz_id = 12605 OR ncihaz_id = 0)
AND ndil_id = 10
ORDER BY nayar_id,
nparent_id,
vayar_menu_desc,
vayar_desc ;
364 rows selected.
Elapsed: 00:00:00.07
Execution Plan
----------------------------------------------------------
Plan hash value: 1328134547
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 409 | 17587| 80 (2)| 00:00:01 |
| 1 | SORT ORDER BY | | 409 | 17587| 80 (2)| 00:00:01 |
| 2 | INLIST ITERATOR | | || | |
| 3 | TABLE ACCESS BY INDEX ROWID| TCIHAZ_AYAR | 409 | 17587| 79 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | NUI_CHZ_AYR_CHZ_DIL_AYR | 409 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(("NCIHAZ_ID"=0 OR "NCIHAZ_ID"=12605) AND "NDIL_ID"=10)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
83 consistent gets
0 physical reads
0 redo size
15026 bytes sent via SQL*Net to client
407 bytes received via SQL*Net from client
26 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
364 rows processed
Message was edited by:
FENERBAHCE