Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

index recommendation

kaericnJan 15 2020 — edited Jan 16 2020

Dear community,

I have the below table def and query.

The volume is 200 million plus rows.

What index would it suited to speed up the query ?

This table is mixed work load both transactional (insert and update ) and read.

Index idea:

CREATE index tbl_1 on tbl(cnt,r_creation_dt,c_id,a_action,last_modified_by);

CREATE index tbl_2 on tbl(cnt,c_status,r_creation_dt);

CREATE bitmap index tbl_3 on tbl(c_status);

tbl defintion below:

R_ID          NOT NULL VARCHAR2(30)

C_ID                     NUMBER      

N_ID                     VARCHAR2(40)

CREATED_BY         NOT NULL VARCHAR2(30)

LAST_MODIFIED_BY            VARCHAR2(30)

C_STATUS           CHAR(1)     

A_ACTION            CHAR(1)     

R_CREATION_DT          TIMESTAMP(6)

CNT                     NUMBER(38) 

SQL:

SELECT

    c_id,

    a_action,

    cnt,

    last_modified_by

FROM

    tbl

WHERE

    c_status IN(

        'N',

        'F'

    )

    AND cnt<=5

GROUP BY

    cnt,

    r_creation_dt,

    case_id,

    anonymize_action,

    last_modified_by

   

ORDER BY

    r_creation_dt

FETCH FIRST 1000 ROWS ONLY;

Comments

Tubby
Drop index un1 ;
create index un1 on xx_test (a1,a2,a3) ;
586651
I dont want to drop the index is it possible to alter?
659537
yes you can alter index with "alter index" syntax to change.
You can alter index for :-
Rebuild or coalesce an existing index
Deallocate unused space or allocate a new extent
Specify parallel execution (or not) and alter the degree of parallelism
Alter storage parameters or physical attributes
Specify LOGGING or NOLOGGING
Enable or disable key compression
Mark the index unusable
Start or stop the monitoring of index usage

-----You cannot alter an index's column structure.---------

For more details check in the documentation
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/indexes.htm#518
659537
yes you can alter index with "alter index" syntax to change.
You can alter index for :-
Rebuild or coalesce an existing index
Deallocate unused space or allocate a new extent
Specify parallel execution (or not) and alter the degree of parallelism
Alter storage parameters or physical attributes
Specify LOGGING or NOLOGGING
Enable or disable key compression
Mark the index unusable
Start or stop the monitoring of index usage

-----You cannot alter an index's column structure.---------

For more details check in the documentation
http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/indexes.htm#518
Karthick2003
[ALTER INDEX|http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_1008.htm#SQLRF00805] is beautifully documented.

Thanks,
Karthick.
1 - 5

Post Details

Added on Jan 15 2020
9 comments
1,703 views