Home » RDBMS Server » Performance Tuning » Query Tuning -indexing (Oracle 10g/Solaris)
Query Tuning -indexing [message #577342] Thu, 14 February 2013 05:30 Go to next message
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 1595 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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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.
Re: Query Tuning -indexing [message #577355 is a reply to message #577349] Thu, 14 February 2013 07:03 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Query Tuning -indexing [message #577481 is a reply to message #577355] Sat, 16 February 2013 22:33 Go to previous messageGo to next message
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
/

Re: Query Tuning -indexing [message #577823 is a reply to message #577481] Thu, 21 February 2013 00:11 Go to previous messageGo to next message
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 .
Re: Query Tuning -indexing [message #577882 is a reply to message #577823] Thu, 21 February 2013 08:24 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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
Re: Query Tuning -indexing [message #582838 is a reply to message #577977] Tue, 23 April 2013 01:00 Go to previous messageGo to next message
nlnkapardi
Messages: 26
Registered: April 2013
Location: India
Junior Member

Hello Prejib

Does Hints help you in this case,,,

Regards
kapardi
Re: Query Tuning -indexing [message #582863 is a reply to message #582838] Tue, 23 April 2013 02:59 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this a question or an affirmation?
If this is a question, please end your sentence with a question mark.

Regards
Michel
Previous Topic: Which is correct solution for ORA-01555 - Increase Undo size or Undo retention?
Next Topic: update xml eating up a lot of time
Goto Forum:
  


Current Time: Thu Mar 28 06:18:21 CDT 2024