Query To Tune !!

[raviindbaworld]

I need all your help to tune this query though it looks simple but consuming 70
% of the CPU time. Any help on this will be highly appreciated

select /*+ INDEX (custhasproduct_AK4)*/ count(1) from custhasproduct where customer_ref ='10004459' and product_id+0 =73;

Here is the explain plan for above query

PLAN_TABLE_OUTPUT

Plan hash value: 2621521185

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| CUSTHASPRODUCT | 1 | 13 | 1 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | CUSTHASPRODUCT_AK2 | 9 | | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("PRODUCT_ID"=73)
3 - access("CUSTOMER_REF"='10004459')

Also im the whole table script and the indexes on this table CUSTHASPRODUCT - means customerhasproduct

Table script
--
-- CUSTHASPRODUCT (Table)
--
CREATE TABLE GENEVA_ADMIN.CUSTHASPRODUCT ( CUSTOMER_REF VARCHAR2(20 BYTE) NOT NULL, PRODUCT_SEQ NUMBER(9) NOT NULL, PRODUCT_ID NUMBER(9) NOT NULL, PACKAGE_SEQ NUMBER(9) NULL, PRODUCT_PACKAGE_INSTANCE NUMBER(3) NULL, PARENT_PRODUCT_SEQ NUMBER(9) NULL, SUCCESSOR_PRODUCT_SEQ NUMBER(9) NULL, CUST_ORDER_NUM VARCHAR2(20 BYTE) NULL, SUPPLIER_ORDER_NUM VARCHAR2(20 BYTE) NULL, SUPPRESS_INIT_CHARGE_BOO VARCHAR2(1 BYTE) NOT NULL, SUPPRESS_TERM_CHARGE_BOO VARCHAR2(1 BYTE) NOT NULL, SUPPRESS_EARLY_TERM_CHARGE_BOO VARCHAR2(1 BYTE) NOT NULL, EVENT_SOURCE_COUNT NUMBER(9) NOT NULL, SUBSCRIPTION_BOO VARCHAR2(1 BYTE) NOT NULL, SUBS_PRODUCT_SEQ NUMBER(9) NULL, SUBSCRIPTION_REF VARCHAR2(20 BYTE) NULL, TEMPLATE_OPTIONAL_PROD_BOO VARCHAR2(1 BYTE) NOT NULL, STRUCTURAL_INTEGER_REF NUMBER(4) NULL, TEMPLATE_SEQ NUMBER(9) NULL ) TABLESPACE CUSTOMER_TAB_TS_1 PCTUSED 0 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 300M NEXT 300M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL KEEP ) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING
/


--
-- CUSTHASPRODUCT_PK (Index)
--
CREATE UNIQUE INDEX GENEVA_ADMIN.CUSTHASPRODUCT_PK ON GENEVA_ADMIN.CUSTHASPRODUCT (CUSTOMER_REF, PRODUCT_SEQ) NOLOGGING TABLESPACE CUSTOMER_IND_TS_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 150M NEXT 150M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL KEEP ) NOPARALLEL
/


--
-- CUSTHASPRODUCT_AK1 (Index)
--
CREATE INDEX GENEVA_ADMIN.CUSTHASPRODUCT_AK1 ON GENEVA_ADMIN.CUSTHASPRODUCT (PRODUCT_ID) NOLOGGING TABLESPACE CUSTOMER_IND_TS_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 150M NEXT 150M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL
/


--
-- CUSTHASPRODUCT_AK2 (Index)
--
CREATE INDEX GENEVA_ADMIN.CUSTHASPRODUCT_AK2 ON GENEVA_ADMIN.CUSTHASPRODUCT (CUSTOMER_REF, PARENT_PRODUCT_SEQ) NOLOGGING TABLESPACE CUSTOMER_IND_TS_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 150M NEXT 150M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL
/


--
-- CUSTHASPRODUCT_AK3 (Index)
--
CREATE INDEX GENEVA_ADMIN.CUSTHASPRODUCT_AK3 ON GENEVA_ADMIN.CUSTHASPRODUCT (SUBSCRIPTION_REF, SUBSCRIPTION_BOO) NOLOGGING TABLESPACE CUSTOMER_IND_TS_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 150M NEXT 150M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL
/


--
-- CUSTHASPRODUCT_AK4 (Index)
--
CREATE INDEX GENEVA_ADMIN.CUSTHASPRODUCT_AK4 ON GENEVA_ADMIN.CUSTHASPRODUCT (CUSTOMER_REF, PACKAGE_SEQ, PRODUCT_SEQ) NOLOGGING TABLESPACE CUSTOMER_IND_TS_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 150M NEXT 150M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL
/


--
-- CUSTHASPRODUCT_AK5 (Index)
--
CREATE INDEX GENEVA_ADMIN.CUSTHASPRODUCT_AK5 ON GENEVA_ADMIN.CUSTHASPRODUCT (CUSTOMER_REF, TEMPLATE_SEQ, STRUCTURAL_INTEGER_REF) NOLOGGING TABLESPACE CUSTOMER_IND_TS_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 150M NEXT 150M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT ) NOPARALLEL
/


--
-- TRDACUSTHASPRODUCTRTC (Trigger)
--
CREATE OR REPLACE TRIGGER GENEVA_ADMIN.trdaCustHasProductRTC
after delete
ON GENEVA_ADMIN.CUSTHASPRODUCT for each row
begin
gnvIntRatingCacheQueue.enqModifyCustHasProductMsg(
'TRDACUSTHASPRODUCTRTC',
ld.customer_ref,
ld.product_seq);
end;
/
SHOW ERRORS;



--
-- TRUACUSTHASPRODUCTRTC (Trigger)
--
CREATE OR REPLACE TRIGGER GENEVA_ADMIN.truaCustHasProductRTC
after update
of product_seq, cust_order_num
ON GENEVA_ADMIN.CUSTHASPRODUCT for each row
WHEN (
old.customer_ref = new.customer_ref and
( old.product_seq <> new.product_seq
or ((old.cust_order_num is null and new.cust_order_num is not null)
or (old.cust_order_num is not null and new.cust_order_num is null)
or (old.cust_order_num <> new.cust_order_num)))
)
begin
gnvIntRatingCacheQueue.enqModifyCustHasProductMsg(
'TRUACUSTHASPRODUCTRTC',
ld.customer_ref,
ld.product_seq);
end;
/
SHOW ERRORS;



--
-- CUSTHASPRODUCT (Synonym)
--
CREATE PUBLIC SYNONYM CUSTHASPRODUCT FOR GENEVA_ADMIN.CUSTHASPRODUCT
/


--
-- CUSTHASPRODUCT (Synonym)
--
CREATE SYNONYM IPFAPPUSER.CUSTHASPRODUCT FOR GENEVA_ADMIN.CUSTHASPRODUCT
/


--
-- CUSTHASPRODUCT (Synonym)
--
CREATE SYNONYM UNIF_ADMIN.CUSTHASPRODUCT FOR GENEVA_ADMIN.CUSTHASPRODUCT
/


--
-- CUSTHASPRODUCT (Synonym)
--
CREATE SYNONYM MIG2IRB.CUSTHASPRODUCT FOR GENEVA_ADMIN.CUSTHASPRODUCT
/


--
-- Non Foreign Key Constraints for Table CUSTHASPRODUCT
--
ALTER TABLE GENEVA_ADMIN.CUSTHASPRODUCT ADD ( CONSTRAINT CUSTHASPRODUCT_PK PRIMARY KEY (CUSTOMER_REF, PRODUCT_SEQ) USING INDEX TABLESPACE CUSTOMER_IND_TS_1 PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 150M NEXT 150M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 ))
/


GRANT DELETE, INSERT, SELECT, UPDATE ON GENEVA_ADMIN.CUSTHASPRODUCT TO GENEVAADMIN
/

GRANT DELETE, INSERT, SELECT, UPDATE ON GENEVA_ADMIN.CUSTHASPRODUCT TO INFINYS_GENEVA_ADMIN
/

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ON COMMIT REFRESH, QUERY REWRITE, DEBUG, FLASHBACK ON GENEVA_ADMIN.CUSTHASPRODUCT TO MIG2IRB
/

[davey23uk]

why are you hiniting ? remove the hint and generate proper stats

[raviindbaworld]

why are you hiniting ? remove the hint and generate proper stats

Hey the Plan cost is same without the index also..can we enforce to use multiple index in this case so that data retrival will be faster. ??

[Thomasps]

select /*+ INDEX (custhasproduct_AK4)*/ count(1) from custhasproduct where customer_ref ='10004459' and product_id+0 =73;

in "product_id+0 =73;", what is the use of "+0" which may influence the optimizer

SQL> select * from b where rw=24332


Execution Plan
----------------------------------------------------------
Plan hash value: 2383670144

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    50 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| B    |     1 |    50 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_B  |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("RW"=24332)

SQL> l
  1* select * from b where rw=24332
SQL> select * from b where rw+0=24332;

Execution Plan
----------------------------------------------------------
Plan hash value: 1911541843

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    50 |    46   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| B    |     1 |    50 |    46   (3)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RW"+0=24332)

[malay_biswal]

INdex hint is incorrect. You should put /*+ index(table_name index_name) */.
That's why it's not picking the custhasproduct_AK4 index which you want to use. One suggetion though, The index being picked should be on customer_ref column (thinks cardinality is more). As somebody mentioned, what's the use of +0

[raviindbaworld]

Yah Malay thx now its picking the right index I realised my mistake as highlighted by you.

Regarding the +0 in the condition , I was told that its a kind of hint to tell the compiler that its a NUMBER comparision.I truly accept that it influences the optimizer may be in wrong direction . I will post more about it.

Thx
Ravi

[slimdave]

Regarding the +0 in the condition , I was told that its a kind of hint to tell the compiler that its a NUMBER comparision.

If product_id is a number datatype then you already have a numeric comparison. If you need to change the data type to perform a comparison then it is probably stored as the wrong type, but you ought to use an explicit conversion (to_number, to_date, Cast) if you need to do this in SQL.

Also, wherever or whoever you heard that ought to be on your list of sources to be very suspicious of in future.