CODEWITHSUNDEEP
sqloracle-databaseindexing
milheiros
milheiros

Reputation: 619

Tuning query using index. Best approach?

I am around a problem here. I'm using Oracle 11g and I have this query:

SELECT /*+ PARALLEL(16) */
        prdecdde,
         prdenusi,
         prdenpol,
         prdeano,
         prdedtpr
    FROM stat_pro_det
   WHERE prdeisin IS NULL AND PRDENUSI IS NOT NULL AND prdedprv = '20160114'
GROUP BY prdecdde,
         prdenusi,
         prdenpol,
         prdeano,
         prdedtpr;

I get the next execution plan:

--------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 | 53229 |  2287K|       |  3652   (4)| 00:00:01 |
|   1 |  HASH GROUP BY               |                 | 53229 |  2287K|  3368K|  3652   (4)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| STAT_PRO_DET    | 53229 |  2287K|       |  3012   (3)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | STAT_PRO_DET_08 |   214K|       |       |   626   (4)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

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

   2 - filter("PRDENUSI" IS NOT NULL AND "PRDEISIN" IS NULL)
   3 - access("PRDEDPRV"='20160114')

Note
-----
   - Degree of Parallelism is 1 because of hint

I still have a lot of CPU cost. The STAT_PRO_DET_08 index is:

CREATE INDEX STAT_PRO_DET_08 ON STAT_PRO_DET(PRDEDPRV)

I've tried to add PRDEISIN and PRDENUSI to the index, putting the most selective at first, but with worst results.

This table have 128 million records (yes...maybe we need to a PARTITION TABLE). But I can not partition the table for now.

What are the other sugestions? A different index could get better results or can not do better than this?

Thanks in advance!!!!

EDIT1: Guys.. thanks a lot for all your help. Especially @Marmite I have a next question: And adding these two queries to the subject. Create one index for each one or can I have a index that resolve my performance problem in these three queries?

  SELECT /*+ PARALLEL(16) */
        prdecdde,
         prdenuau,
         prdenpol,
         prdeano,
         prdedtpr
    FROM stat_pro_det
   WHERE prdeisin IS NULL AND PRDENUSI IS NULL AND prdedprv = '20160114'
GROUP BY prdecdde,
         prdenuau,
         prdenpol,
         prdeano,
         prdedtpr;

and

SELECT /*+ PARALLEL(16) */
        prdeisin, prdenuau
    FROM stat_pro_det, mtauto
   WHERE prdedprv = '20160114' AND prdenuau = autonuau AND autoisin IS NULL
GROUP BY prdenuau, prdeisin

Upvotes: 1

Views: 84

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

First, you might as well rewrite the query as:

SELECT /*+ PARALLEL(16) */ DISTINCT
        prdecdde, prdenusi, prdenpol, prdeano, prdedtpr
FROM stat_pro_det
WHERE prdeisin IS NULL AND PRDENUSI IS NOT NULL AND prdedprv = '20160114';

(This is shorter and makes it easier to change the list of columns you are interested in.)

The best index for this query is: stat_pro_det(prdedprv, prdeisin, prdenusi, prdecdde, prdenpol, prdeano, prdedtpr).

The first three columns are important for the WHERE clause and filtering the data. The remaining columns "cover" the query, meaning that the index itself can resolve the query without having to access data pages.

Upvotes: 3

Marmite Bomber
Marmite Bomber

Reputation: 21053

First make a following decisions:

  • you access using index or using full table scan
  • you use parallel query or no_parallel

Generall rule is index access work fine for small number of accessed records, but scale not well with a high number.

So the best way test all options and see the results.

For parallel FULL TABLE SCAN

use hint as follows (replace you table name or alias for tab)

 SELECT /*+ FULL(tab) PARALLEL(16) */

This scales better, but is not instant for small number of records.

For index access

Note that this will not be done in parallel. Check the note in your explain plan in teh question.

Defining index containing all columns (as proposed by Gordon) you will perform a (sequential) index range scan without accessing the table.

As noted above - depending of the number of accessed keys this will be quick or slow.

For parallel index access

You need to define a GLOBAL partitioned index

create index tab_idx on tab (col3,col2,col1,col4,col5)
global partition  by hash (col3,col2,col1,col4,col5) PARTITIONS 16;

Than hint 

 SELECT /*+ INDEX(tab tab_idx) PARALLEL_INDEX(tab,16)  */

You will perform the same index range scan, but this time in parallel. So there is a chance that it will respond bettwer that serial execution. If you realy can open DOP 16 depend of course of your database HW setting and configuration...

Upvotes: 2

Related Questions