How to improve sql statement?

[nir_s]

Hi folks,

We are running benchmark and the heaviest CPU query was:

SELECT ec.tic_fk FROM t_enabled_channels ec,  t_subs s 
WHERE s.household_id = :1  AND s.sub_id = ec.sub_id;

The execution plan is:

SQL> SELECT ec.tic_fk FROM t_enabled_channels ec,  t_subs s
  2  WHERE s.household_id = 214967 and s.sub_id= ec.sub_id;

17 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5858 Card=20 Byt
          es=400)

   1    0   NESTED LOOPS (Cost=5858 Card=20 Bytes=400)
   2    1     INDEX (RANGE SCAN) OF 'T_SUB_HOUSE_SUB_IDX' (INDEX) (Cos
          t=2 Card=2 Bytes=20)

   3    1     INDEX (FULL SCAN) OF 'PK_T_ENABLE_CHAN_TIC_FK_SUB_ID' (I
          NDEX (UNIQUE)) (Cost=2928 Card=10 Bytes=100)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       5858  consistent gets
          0  physical reads
          0  redo size
        601  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         17  rows processed

More info:

SQL> desc t_enabled_channels
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 SUB_ID                                                NOT NULL NUMBER(20)
 TIC_FK                                                NOT NULL NUMBER(15)

SQL> desc t_subs
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 SUB_ID                                                NOT NULL NUMBER(20)
 USER_NAME                                                      VARCHAR2(30)
 PASSWORD                                                       VARCHAR2(100)
 F_NAME                                                NOT NULL VARCHAR2(75)
 L_NAME                                                NOT NULL VARCHAR2(75)
 OCCUPATION                                                     VARCHAR2(150)
 COMPANY_NAME                                                   VARCHAR2(105)
 GENDER                                                         VARCHAR2(30)
 EMAIL                                                          VARCHAR2(150)
 PERSONAL_ID                                                    VARCHAR2(60)
 BIRTH_DATE                                                     DATE
 TEL                                                            VARCHAR2(90)
 PORT_PHONE                                                     VARCHAR2(90)
 HOUSEHOLD_ID                                          NOT NULL NUMBER(20)
 PIN_CODE                                              NOT NULL VARCHAR2(10)
 MEMBER_TYPE                                           NOT NULL NUMBER(1)
 SMS                                                            NUMBER(1)
 POINTER                                                        NUMBER(20)
 CLUE_ANSWER                                                    VARCHAR2(300)
 CLUE_QUESTION                                                  VARCHAR2(300)

SQL> select count (*) from t_subs;

  COUNT(*)
----------
    100012

SQL> select count (*) from t_enabled_channels;

  COUNT(*)
----------
    999987

SQL> select index_name,column_name
  2  from user_ind_columns
  3  where table_name=upper('t_enabled_channels');

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------
PK_T_ENABLE_CHAN_TIC_FK_SUB_ID TIC_FK
PK_T_ENABLE_CHAN_TIC_FK_SUB_ID SUB_ID

SQL> select index_name,column_name
  2  from user_ind_columns
  3  where table_name=upper('t_subs');

INDEX_NAME                     COLUMN_NAME
------------------------------ ----------------------
PK_T_SUBS_SUB_ID               SUB_ID
IND_SUBS_HOUSEHOLD_ID          HOUSEHOLD_ID
IND_SUBS_USER_NAME             USER_NAME
T_SUB_SUB_HOUSE_IDX            SUB_ID
T_SUB_SUB_HOUSE_IDX            HOUSEHOLD_ID
T_SUB_HOUSE_SUB_IDX            HOUSEHOLD_ID
T_SUB_HOUSE_SUB_IDX            SUB_ID

7 rows selected.

Is there a way to improve this query which took 56% CPU?

Thanks in advane.
Nir

[pando]

show us

select count(distinct sub_id)
   from t_enabled_channels;

Isnt your relation is a bit dodgy? 

s.sub_id= ec.sub_id

and sub_id isnt the PK in t_enabled_channels

I suspect there is few distinct sub_id in t_enabled_channels, have you tried all_rows instead of first_rows?

[nir_s]

Hi Pando,

Thanks for your reply.
Finally,I succeeded to improve the query by adding index on T_ENABLED_CHANNELS (sub_id).
Now the optimizer uses this index instead of the PK (SUB_ID,TIC_FK ).

SQL> SELECT ec.tic_fk FROM t_enabled_channels ec,  t_subs s
  2  WHERE s.household_id = 214967 and s.sub_id= ec.sub_id;

17 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=5 Card=20 Bytes=
          400)

   1    0   NESTED LOOPS (Cost=5 Card=20 Bytes=400)
   2    1     INDEX (RANGE SCAN) OF 'T_SUB_HOUSE_SUB_IDX' (INDEX) (Cos
          t=2 Card=2 Bytes=20)

   3    1     INDEX (RANGE SCAN) OF 'UNQ_T_ENABLED_CHANNELS' (INDEX (U
          NIQUE)) (Cost=2 Card=10 Bytes=100)





Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        601  bytes sent via SQL*Net to client
        507  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         17  rows processed

Nir

[mrchrispy]

be aware that adding an index may have a detrimental effect on other statements querying that table….probably not but you never know.