How to improve sql statement?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: How to improve sql statement?

  1. #1
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233

    How to improve sql statement?

    Hi folks,

    We are running benchmark and the heaviest CPU query was:
    Code:
    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:
    Code:
    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:
    Code:
    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

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    show us

    Code:
    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?

  3. #3
    Join Date
    Dec 2001
    Location
    Tel-Aviv,Israel
    Posts
    233
    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 ).

    Code:
    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

  4. #4
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    be aware that adding an index may have a detrimental effect on other statements querying that table….probably not but you never know.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width