OraFAQ Forum: Performance Tuning » Query Tuning -indexing

Home » RDBMS Server » Performance Tuning » Query Tuning -indexing (Oracle 10g/Solaris)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query Tuning -indexing [message #577342]

Thu, 14 February 2013 05:30

prejib
Messages: 126
Registered: March 2009
Location: India

Senior Member

I Want to tune the attached query . I have tried by creating the normal indexes and composite indexes on the fields . I feel that , Only normal index is required for this instead of composite index . Can you please help me to clarify?

11:15:19 SQL> @slot.sql
11:16:03 SQL>
11:16:03 SQL> drop table slot purge;

Table dropped.

Elapsed: 00:00:00.05
11:16:03 SQL>
11:16:03 SQL> create table slot
11:16:03   2    (
11:16:03   3       id         varchar2 (40) not null,
11:16:03   4       ship_date  date,
11:16:03   5       type       number(1),
11:16:03   6       start_time varchar2 (20),
11:16:03   7       end_time   varchar2 (20),
11:16:03   8       store      varchar2 (20),
11:16:03   9       post       varchar2 (20),
11:16:03  10       fragment   varchar2 (20),
11:16:03  11       state      varchar2 (20),
11:16:03  12       slot_group number (10),
11:16:03  13       constraint slot_pk primary key ( id )
11:16:03  14    );

Table created.

Elapsed: 00:00:00.03
11:16:03 SQL>
11:16:03 SQL> explain plan for
11:16:03   2  select s.type,s.slot_group,s.ship_date,s.start_time,s.end_time,count(id) ord_cnt
11:16:03   3  from   slot s
11:16:03   4  where  1 = 1        and s.state in ( 'submitted' )
11:16:03   5         and s.ship_date >= to_date('20130213', 'yyyymmdd')
11:16:03   6         and s.ship_date <= to_date('20130219', 'yyyymmdd')
11:16:03   7         and s.type = '0'        and s.slot_group = '2'and s.fragment='st180l'
11:16:03   8  group  by s.type,
11:16:03   9            s.slot_group,  s.ship_date,
11:16:03  10            s.start_time, s.end_time
11:16:03  11  order  by s.ship_date, s.start_time,s.end_time;

Explained.

Elapsed: 00:00:00.00
11:16:03 SQL>
11:16:03 SQL> set echo off;

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
Plan hash value: 1682551985

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    83 |     3  (34)| 00:00:01 |
|   1 |  SORT GROUP BY     |      |     1 |    83 |     3  (34)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| SLOT |     1 |    83 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("S"."STATE"='submitted' AND "S"."TYPE"=0 AND
              "S"."SLOT_GROUP"=2 AND "S"."FRAGMENT"='st180l' AND
              "S"."SHIP_DATE">=TO_DATE(' 2013-02-13 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "S"."SHIP_DATE"<=TO_DATE(' 2013-02-19 00:00:00',
              'syyyy-mm-dd hh24:mi:ss'))

Note
-----
   - dynamic sampling used for this statement

22 rows selected.

Elapsed: 00:00:00.01
11:16:03 SQL> create index slot_idx1     on slot(fragment);

Index created.

Elapsed: 00:00:00.01
11:16:03 SQL>
11:16:03 SQL> --create index slot_idx2   on slot(fragment, ship_date);
11:16:03 SQL>
11:16:03 SQL> --create index slot_idx3   on slot(ship_date, fragment);
11:16:03 SQL>
11:16:03 SQL> explain plan for
11:16:03   2  select s.type,s.slot_group,s.ship_date,s.start_time,s.end_time,count(id) ord_cnt
11:16:03   3  from   slot s
11:16:03   4  where  1 = 1        and s.state in ( 'submitted' )
11:16:03   5         and s.ship_date >= to_date('20130213', 'yyyymmdd')
11:16:03   6         and s.ship_date <= to_date('20130219', 'yyyymmdd')
11:16:03   7         and s.type = '0'        and s.slot_group = '2'and s.fragment='st180l'
11:16:03   8  group  by s.type,
11:16:03   9            s.slot_group,  s.ship_date,
11:16:03  10            s.start_time, s.end_time
11:16:03  11  order  by s.ship_date, s.start_time,s.end_time;

Explained.

Elapsed: 00:00:00.00
11:16:03 SQL>
11:16:03 SQL>
11:16:03 SQL> set echo off;

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 2722768918

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |     1 |    83 |     2  (50)| 00:00:01 |
|   1 |  SORT GROUP BY               |           |     1 |    83 |     2  (50)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| SLOT      |     1 |    83 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | SLOT_IDX1 |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

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

   2 - filter("S"."STATE"='submitted' AND "S"."TYPE"=0 AND "S"."SLOT_GROUP"=2 AND
              "S"."SHIP_DATE">=TO_DATE(' 2013-02-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "S"."SHIP_DATE"<=TO_DATE(' 2013-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   3 - access("S"."FRAGMENT"='st180l')

Note
-----
   - dynamic sampling used for this statement

22 rows selected.

Elapsed: 00:00:00.01

Regards
Prejib

Attachment: slot.sql
(Size: 1.75KB, Downloaded 1916 times)

[Updated on: Thu, 14 February 2013 06:59] by Moderator

Report message to a moderator

Re: Query Tuning -indexing [message #577349 is a reply to message #577342]

Thu, 14 February 2013 05:55

cookiemonster
Messages: 13961
Registered: September 2008
Location: Rainy Manchester

Senior Member

I'd say that to get meaningful explain plans you have to put some data in the table first.

Report message to a moderator

Re: Query Tuning -indexing [message #577355 is a reply to message #577349]

Thu, 14 February 2013 07:03

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

And I'd add: sufficient amount of rows, you will always have a good plan if you have 10 rows, start with 1 million.

Regards
Michel

Report message to a moderator

Re: Query Tuning -indexing [message #577481 is a reply to message #577355]

Sat, 16 February 2013 22:33

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

Run this, then consider what these numbers tell you. It is only the begining of your journey.

select (select count(*) From slot) table_rows
      ,(select count(*) From slot s where s.state in ( 'submitted' )) state_rows
      ,(select count(*) From slot s where s.ship_date >= to_date('20130213', 'yyyymmdd')) ship_date_graterthan_rows
      ,(select count(*) From slot s where s.ship_date <= to_date('20130219', 'yyyymmdd')) ship_date_lessthan_rows
      ,(select count(*) From slow s where s.type = '0') type_rows
      ,(select count(*) From slow s where s.slot_group = '2') slot_group_rows
      ,(select count(*) From slow s where s.fragment='st180l') fragment_rows
from dual
/

Report message to a moderator

Re: Query Tuning -indexing [message #577823 is a reply to message #577481]

Thu, 21 February 2013 00:11

prejib
Messages: 126
Registered: March 2009
Location: India

Senior Member

Hi,
Thanks..
I have inserted some records to the table and created diferent combinations of the indexes . Please see the attachment for the output of the above query also .

Attachment: slot_plan_out.lst
(Size: 12.40KB, Downloaded 1930 times)

Report message to a moderator

Re: Query Tuning -indexing [message #577882 is a reply to message #577823]

Thu, 21 February 2013 08:24

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

So what do you think these numbers tell you about the potential usefulness of these columns and predicates towards your query?

TABLE_ROWS STATE_ROWS SHIP_DATE_GREATERTHAN_ROWS SHIP_DATE_LESSTHAN_ROWS  TYPE_ROWS SLOT_GROUP_ROWS FRAGMENT_ROWS               
---------- ---------- -------------------------- ----------------------- ---------- --------------- -------------               
   7550003      18488                    1755230                 7549988    7548147         7104930           831

What about combinations of columns?

Based on your query plan, it looks like you have a one column index on FRAGMENT. Given the numbers above, do you think you can do better than just one column?

------------------------------------------------------------------------------------------                 
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |                 
------------------------------------------------------------------------------------------                 
|   0 | SELECT STATEMENT             |           |     1 |    93 |   177   (1)| 00:00:02 |                 
|   1 |  SORT GROUP BY               |           |     1 |    93 |   177   (1)| 00:00:02 |                 
|*  2 |   TABLE ACCESS BY INDEX ROWID| SLOT      |     1 |    93 |   176   (0)| 00:00:02 |                 
|*  3 |    INDEX RANGE SCAN          | SLOT_IDX1 |   930 |       |     1   (0)| 00:00:01 |                 
------------------------------------------------------------------------------------------                 
                                  
Predicate Information (identified by operation id):                                                        
---------------------------------------------------                                                        
                                  
   2 - filter("S"."STATE"='SUBMITTED' AND "S"."TYPE"=0 AND "S"."SLOT_GROUP"=2 AND                          
              "S"."SHIP_DATE">=TO_DATE(' 2012-02-13 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND               
              "S"."SHIP_DATE"<=TO_DATE(' 2013-02-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                  
   3 - access("S"."FRAGMENT"='SS117QL')

If you do think it might be worth looking at a CONCATENATED INDEX, which columns might you add and why? How would you check ahead of time to see if they make sense?

Consider this query:

select (select count(*) From slot) table_rows
      ,(select count(*) From slot s where s.fragment='st180l') fragment_rows
      ,(select count(*) From slot s where s.fragment='st180l' and s.state in ( 'submitted' )) fragment_state_rows
      ,(select count(*) From slot s where s.fragment='st180l' and s.state in ( 'submitted' ) and s.ship_date >= to_date('20130213', 'yyyymmdd')) fragment_state_sdgt_rows
from dual
/

Kevin

[Updated on: Thu, 21 February 2013 09:04]

Report message to a moderator

Re: Query Tuning -indexing [message #577977 is a reply to message #577882]

Fri, 22 February 2013 04:28

prejib
Messages: 126
Registered: March 2009
Location: India

Senior Member

I have tried many combinations of index from the where clause and took the less cost ones.

09:53:26 SQL> select (select count(*) From slot) table_rows
09:54:58   2  ,(select count(*) From slot s where s.fragment='SS117QL') fragment_rows
09:54:58   3  ,(select count(*) From slot s where s.fragment='SS117QL' and s.state in ( 'SUBMITTED' )) fragment_state_rows
09:54:58   4  ,(select count(*) From slot s where s.fragment='SS117QL' and s.state in ( 'SUBMITTED' ) and s.ship_date >= to_date('20120213', 'yyyymmdd')) fragment_state_sdgt_rows
09:54:58   5  from dual;

TABLE_ROWS FRAGMENT_ROWS FRAGMENT_STATE_ROWS FRAGMENT_STATE_SDGT_ROWS
---------- ------------- ------------------- ------------------------
   7550003           831                  30                       30

Elapsed: 00:00:00.64

Report message to a moderator