Forum Stats

  • 3,757,939 Users
  • 2,251,294 Discussions
  • 7,869,972 Comments

Discussions

need to know which condition is better

976379
976379 Member Posts: 20
edited Apr 19, 2013 10:55AM in SQL & PL/SQL
Hi,
i have one table in which most of the records are unique.
What would be better , to create index or to continue without index.

I need faster response of the query . No of rows in table is 32 lac.

below is the output with index and without index for some sample data.

in first scenario i have two indexes.
in second scenario i have only one index
in third one i don't have any index.

here col2 is having only 2-3 distinct values while both other columns are having almost unique values ( different data )

SQL> set autotrace traceonly
SQL> SELECT COUNT(*)
FROM my_table
WHERE col1 =28582
AND col2='1'
AND col3 ='1452631' ;

Elapsed: 00:00:00.47

Execution Plan
----------------------------------------------------------
Plan hash value: 2123134521

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |16 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 |16 || |
|* 2 | INDEX RANGE SCAN| IND_my_table_1 | 1 |16 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

2 - access("col1"=28582 AND "col2"='1' AND
"col3"='1452631')


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
3882329993 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> drop index Ind_my_table_1;

Index dropped.

Elapsed: 00:00:00.66
SQL> SELECT COUNT(*)
FROM my_table
WHERE col1 =28582
AND col2='1'
AND col3 ='1452631' ;

Elapsed: 00:00:01.52

Execution Plan
----------------------------------------------------------
Plan hash value: 2881393183

-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 4(0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| my_table | 1 | 16 | 4(0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | IND_my_table_2 | 1 | | 3(0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

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

2 - filter("col2"='1' AND "col3"='1452631')
3 - access("col1"=28582)


Statistics
----------------------------------------------------------
199 recursive calls
0 db block gets
31 consistent gets
2 physical reads
0 redo size
514 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
6 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> drop index Ind_my_table_1 2;

Index dropped.

Elapsed: 00:00:00.07
SQL> SELECT COUNT(*)
FROM my_table
WHERE col1 =28582
AND col2='1'
AND col3 ='1452631' ;

Elapsed: 00:00:00.70

Execution Plan
----------------------------------------------------------
Plan hash value: 1679787526

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 16 | 6431(2)| 00:01:18 |
| 1 | SORT AGGREGATE | | 1 | 16 | | |
|* 2 | TABLE ACCESS FULL| my_table | 1 | 16 | 6431(2)| 00:01:18 |
---------------------------------------------------------------------------------------

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

2 - filter("col2"='1' AND "col1"=28582 AND
"col3"='1452631')


Statistics
----------------------------------------------------------
169 recursive calls
0 db block gets
29754 consistent gets
0 physical reads
0 redo size
514 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed


Pl suggest how should i create the index.
Tagged:

Answers

  • Frank Kulash
    Frank Kulash Member, Moderator Posts: 41,036 Red Diamond
    edited Apr 19, 2013 6:43AM
    Hi,
    KriC wrote:
    ... here col2 is having only 2-3 distinct values while both other columns are having almost unique values ( different data )

    SQL> set autotrace traceonly
    SQL> SELECT COUNT(*)
    FROM my_table
    WHERE col1 =28582
    AND col2='1'
    AND col3 ='1452631' ;
    ...
    Pl suggest how should i create the index.
    There's no substitute for actually trying things on your system, with your full data.
    You may not be able to try different ideas on your Production system, but your Test system is probably closer that anything that anyone else on this forum has.

    My best guess, for queries like the one above, would be a compound index in (col1, col3, col2) or (co3, col1,col2). Col2 is least selective (that is, fewest unique values) so it should definitely be last. There may not be any advantage to including col2 in the index.

    Assuming you do create a compound index that starts with col1: that will not help in queries where col1 is not specified, but col3 is. If you sometimes run queries like that, you might want a separate index on col3.
  • KriC wrote:
    Hi,
    i have one table in which most of the records are unique.
    What would be better , to create index or to continue without index.

    I need faster response of the query . No of rows in table is 32 lac.

    below is the output with index and without index for some sample data.

    in first scenario i have two indexes.
    in second scenario i have only one index
    in third one i don't have any index.

    . . . E t c . . .

    Pl suggest how should i create the index.
    If you yourself cannot discern the answer after you have tested the alternatives, perhaps you have chosen the wrong profession
    or have neglected studying the fine Oracle® Database Performance Tuning Guide.
    Good luck!
This discussion has been closed.