| Unable to avoid Indexes (2 Merged) [message #508361] |
Fri, 20 May 2011 10:47  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
Hi
Below query is getting delayed becasue of BitMap Indexes on the table. I am trying to avoid indexes by using Hints in the query but unable to do so, please let me know how to achive the expected. Details are as follows.
explain plan for
SELECT cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
PLAN_TABLE_OUTPUT
Plan hash value: 2905810395
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 73 | 1651 (1)| 00:00:20 |
| 1 | SORT ORDER BY | | 1 | 73 | 1651 (1)| 00:00:20 |
|* 2 | FILTER | | | | | |
| 3 | HASH GROUP BY | | 1 | 73 | 1651 (1)| 00:00:20 |
|* 4 | HASH JOIN RIGHT OUTER | | 4449 | 317K| 1648 (1)| 00:00:20 |
| 5 | TABLE ACCESS FULL | SALES_VIEW_TARGET | 1 | 13 | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | LEGAL_VIEW_TARGET | 4449 | 260K| 1646 (1)| 00:00:20 |
| 7 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 8 | BITMAP AND | | | | | |
|* 9 | BITMAP INDEX SINGLE VALUE| LEGAL_VIEW_TARGET_IX11 | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE| LEGAL_VIEW_TARGET_IX10 | | | | |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUM("LEGAL"."SUBSCRIBER_CNT")>10)
4 - access("SLS"."BILLING_ACCOUNT_ID"(+)="LEGAL"."BILLING_ACCOUNT_ID")
6 - filter("LEGAL"."CBU_CID"<>'0001988048' AND "LEGAL"."CBU_CID"<>'0001379962' AND
"LEGAL"."CBU_CID"<>'0001350469')
9 - access("LEGAL"."MKTG_SUB_SEGMENT_A_NM"='TM')
10 - access("LEGAL"."BILLG_SYSTEM_ID"='TM')
The execution plan is same as above, when I tried with below hints.
Note: There are only two indexes on the table and they are on the fields billg_system_id(index- LEGAL_VIEW_TARGET_IX11
) & mktg_sub_segment_a_nm(index- LEGAL_VIEW_TARGET_IX10)
-- FULL(table)
explain plan for
SELECT /*+ FULL(daily_view) */
cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
- No_Index(table index)
Query 1:-
explain plan for
SELECT /*+ NO_INDEX(daily_view LEGAL_VIEW_TARGET_IX10 LEGAL_VIEW_TARGET_IX11) */
cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
Query 2:-
explain plan for
SELECT /*+ NO_INDEX(daily_view LEGAL_VIEW_TARGET_IX10, LEGAL_VIEW_TARGET_IX11) */
cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
- NO_INDEX_FFS
Query 1:-
explain plan for
SELECT /*+ NO_INDEX_FFS(daily_view LEGAL_VIEW_TARGET_IX10) NO_INDEX_FFS(daily_view LEGAL_VIEW_TARGET_IX11) */
cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
Query 2:-
explain plan for
SELECT /*+ NO_INDEX_RS(daily_view LEGAL_VIEW_TARGET_IX10) NO_INDEX_RS(daily_view LEGAL_VIEW_TARGET_IX11) */
cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
Query 3:-
explain plan for
SELECT /*+ NO_INDEX_SS(daily_view LEGAL_VIEW_TARGET_IX10) NO_INDEX_SS(daily_view LEGAL_VIEW_TARGET_IX11) */
cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
Note: Same Execution plan even after the execution of the query
i.e., BITMAP INDEX SINGLE VALUE| LEGAL_VIEW_TARGET_IX11
BITMAP INDEX SINGLE VALUE| LEGAL_VIEW_TARGET_IX10
Apart from above hints, I have tried with ALL_ROWS & PARALLEL.
Please let me know, how to avoid above two indexes in a query.
Thanks in Advance.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Unable to avoid Indexes (2 Merged) [message #508445 is a reply to message #508386] |
Sat, 21 May 2011 04:43  |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
As suggested, I tried below approach but execution plan did not change. i.e., no_index on the table "legal_view_target"
explain plan for
SELECT /*+ NO_INDEX(legal_view_target LEGAL_VIEW_TARGET_IX10 LEGAL_VIEW_TARGET_IX11) */
cbu_cid, cbu_cid_customer_en_nm,
COUNT (billg_acct_no) AS billg_acct_no,
SUM (subscriber_cnt) AS subscriber_cnt
FROM daily_view
WHERE (billg_system_id = 'TM' AND mktg_sub_segment_a_nm = 'TM')
AND (cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY cbu_cid, cbu_cid_customer_en_nm
HAVING SUM (subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
|
|
|
|
|
|
| Re: Unable to avoid Indexes (2 Merged) [message #508470 is a reply to message #508445] |
Sat, 21 May 2011 15:27 |
John Watson
Messages: 8929
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
To pass hint to a view, you have to use an extended hint syntax, it uses a kind-of recursive dot notation:
create view cv as select * from customers c where cust_city like 'S%';
select /*+ index(v.c cust_credit_limit_idx) */
v.cust_last_name, v.cust_credit_limit from cv v where cust_credit_limit > 5000;
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Unable to avoid Indexes (2 Merged) [message #508588 is a reply to message #508470] |
Mon, 23 May 2011 05:13 |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
There is nochange in the query execution plan. Details are as below.
> explain plan for
SELECT /*+ NO_INDEX(dv.LEGAL LEGAL_VIEW_TARGET_IX10) NO_INDEX(dv.LEGAL LEGAL_VIEW_TARGET_IX11)*/
dv.cbu_cid, dv.cbu_cid_customer_en_nm,
COUNT (dv.billg_acct_no) AS billg_acct_no,
SUM (dv.subscriber_cnt) AS subscriber_cnt
FROM daily_view dv
WHERE (dv.billg_system_id = 'TM' AND dv.mktg_sub_segment_a_nm = 'TM')
AND (dv.cbu_cid NOT IN ('0001988048', '0001379962', '0001350469'))
GROUP BY dv.cbu_cid, dv.cbu_cid_customer_en_nm
HAVING SUM (dv.subscriber_cnt) > 10
ORDER BY subscriber_cnt DESC;
> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
Plan hash value: 2905810395
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 209 | 1614 (1)| 00:00:20 |
| 1 | SORT ORDER BY | | 1 | 209 | 1614 (1)| 00:00:20 |
|* 2 | FILTER | | | | | |
| 3 | HASH GROUP BY | | 1 | 209 | 1614 (1)| 00:00:20 |
|* 4 | HASH JOIN RIGHT OUTER | | 4366 | 891K| 1612 (1)| 00:00:20 |
| 5 | TABLE ACCESS FULL | SALES_VIEW_TARGET | 1 | 13 | 2 (0)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID | LEGAL_VIEW_TARGET | 4366 | 835K| 1609 (1)| 00:00:20 |
| 7 | BITMAP CONVERSION TO ROWIDS| | | | | |
| 8 | BITMAP AND | | | | | |
|* 9 | BITMAP INDEX SINGLE VALUE| LEGAL_VIEW_TARGET_IX11 | | | | |
|* 10 | BITMAP INDEX SINGLE VALUE| LEGAL_VIEW_TARGET_IX10 | | | | |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(SUM("LEGAL"."SUBSCRIBER_CNT")>10)
4 - access("SLS"."BILLING_ACCOUNT_ID"(+)="LEGAL"."BILLING_ACCOUNT_ID")
6 - filter("LEGAL"."CBU_CID"<>'0001988048' AND "LEGAL"."CBU_CID"<>'0001379962' AND
"LEGAL"."CBU_CID"<>'0001350469')
9 - access("LEGAL"."MKTG_SUB_SEGMENT_A_NM"='TM')
10 - access("LEGAL"."BILLG_SYSTEM_ID"='TM') |
|
|
|
|
|
| Re: Unable to avoid Indexes (2 Merged) [message #508591 is a reply to message #508470] |
Mon, 23 May 2011 05:34 |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
I was able to replicate the issue in local Database and by using above extended hint syntax (it uses a kind-of recursive dot notation) I was able to avoide the Bitmap indexes, but in my client Database same approach is not helping me. Details are as follows.
SQL> create table t_1 (a number);
Table created.
SQL> create table t_2 (b number);
Table created.
SQL> insert into t_1 (a) values (1);
1 row created.
SQL> insert into t_1 (a) values (2);
1 row created.
SQL> insert into t_1 (a) values (3);
1 row created.
SQL> insert into t_1 (a) values (4);
1 row created.
SQL> commit;
Commit complete.
SQL> insert into t_2 (b) values (1);
1 row created.
SQL> insert into t_2 (b) values (2);
1 row created.
SQL> insert into t_2 (b) values (3);
1 row created.
SQL> insert into t_2 (b) values (
2 4);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_1;
A
----------
1
2
3
4
SQL> select * from t_2;
B
----------
1
2
3
4
SQL> create bitmap index x1 on t_1(a);
Index created.
SQL> create bitmap index x2 on t_2(b);
Index created.
SQL> create or replace view view_12 as select * from t_1, t_2 where a = b;
View created.
SQL> select * from view_12;
A B
---------- ----------
1 1
2 2
3 3
4 4
SQL> BEGIN
2 dbms_stats.gather_table_stats(
3 ownname => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
4 tabname => 'T_1',
5 method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4',
6 cascade => TRUE
7 );
8 END;
9 /
PL/SQL procedure successfully completed.
SQL> ED
Wrote file afiedt.buf
1 BEGIN
2 dbms_stats.gather_table_stats(
3 ownname => SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA'),
4 tabname => 'T_2',
5 method_opt => 'FOR ALL INDEXED COLUMNS SIZE 4',
6 cascade => TRUE
7 );
8* END;
SQL> /
PL/SQL procedure successfully completed.
SQL> SET AUTOTRACE TRACEONLY;
SQL> SELECT A, B FROM VIEW_12;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=4 Bytes=24)
1 0 NESTED LOOPS (Cost=1 Card=4 Bytes=24)
2 1 BITMAP CONVERSION (TO ROWIDS)
3 2 BITMAP INDEX (FAST FULL SCAN) OF 'X1'
4 1 BITMAP CONVERSION (TO ROWIDS)
5 4 BITMAP INDEX (SINGLE VALUE) OF 'X2'
Statistics
----------------------------------------------------------
7 recursive calls
2 db block gets
7 consistent gets
0 physical reads
0 redo size
570 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
SQL> SELECT /*+ NO_INDEX(V.T_1 X1) NO_INDEX(V.T_2 X2) */ V.A, V.B FROM VIEW_12 V;
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=4 Bytes=24)
1 0 HASH JOIN (Cost=3 Card=4 Bytes=24)
2 1 TABLE ACCESS (FULL) OF 'T_1' (Cost=1 Card=4 Bytes=12)
3 1 TABLE ACCESS (FULL) OF 'T_2' (Cost=1 Card=4 Bytes=12)
Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
5 consistent gets
0 physical reads
0 redo size
570 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
|
|
|
|
|
|
| Re: Unable to avoid Indexes (2 Merged) [message #508595 is a reply to message #508367] |
Mon, 23 May 2011 05:50 |
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
|
Senior Member |
|
|
oraranjangmailcom wrote on Fri, 20 May 2011 17:15In this scenario Indexes are degrading the performance.
With Index Query Execution time is 5min & without is 1min.
I confess top reading the thread pretty quickly and may have missed it BUT...
If you can prevent the use of the indexes in order to make the above assertion, why can you not use that method to solve the problem?
|
|
|
|
|
|
| Re: Unable to avoid Indexes (2 Merged) [message #508696 is a reply to message #508593] |
Tue, 24 May 2011 02:11 |
oraranjangmailcom
Messages: 67
Registered: April 2010
|
Member |
|
|
I have ran the query with the index & without the index on Daily_View (i.e., table:- legal_view_target). Observation is as below.
With the Index :- 4 to 5minutes
WithOut the Index(full table scan):- 1minutes: 30seconds.
Based on the above timelines, I am trying to avoid indexes.
Note: In this scenarios Full Table Scan is Suggested(as per the discussion with seniors in the respective application). Please let me know if I need to observe any thing else.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
