OraFAQ Forum: SQL & PL/SQL » Primary Key in another Index

Home » SQL & PL/SQL » SQL & PL/SQL » Primary Key in another Index (Oracle 11g, 11.2.0.2 Linux 64b)

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

Primary Key in another Index [message #605965]

Thu, 16 January 2014 17:51

sujitm30
Messages: 10
Registered: September 2005

Junior Member

I have a single column primary key in a table with about 10M records.
This column happens to be VARCHAR2(20). But has numeric values in it.
(Due to some historical reasons that it had characters at one point).

This table is quite wide with more than 100 columns. And there are
several indexes that I am trying to revisit and come up with a better
indexing plan.

My foremost question is:

IS THERE ANY GOOD REASON A PRIMARY KEY COLUMN BE PART OF ANOTHER INDEX?

EXAMPLE:
PK_RENTAL_KEY (RENTAL_KEY)
IDX_STORE_PICKUP (STORE_NO, PICKUP_DATE, RENTAL_KEY, STATUS)

I noticed there are several composite indexes which also have this
primary key column as part of the index. This is something I am
strongly against. I was of the opinion that if any SQL predicate
has the primary key column in it, then it will automatically go to
the PK index.

Report message to a moderator

Re: Primary Key in another Index [message #605967 is a reply to message #605965]

Thu, 16 January 2014 18:21

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

EXPLAIN PLAN will show which index, if any, is used.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Primary Key in another Index [message #605968 is a reply to message #605965]

Thu, 16 January 2014 18:30

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

sujitm30 wrote on Fri, 17 January 2014 00:51

I was of the opinion that if any SQL predicate
has the primary key column in it, then it will automatically go to
the PK index.

It depends on what you have in your predicate, I mean a predicate does not necessarily contain only the primary key. You may have both the primary key and other columns.

Ex:

SQL> set linesize 500
SQL> set autotrace traceonly
SQL>
SQL> select t1.department_id, t1.employee_id, t1.first_name, t1.salary
  2  from   hr.employees t1
  3  where  t1.employee_id = 100 or t1.first_name like 'K%';

8 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 609247258

------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |               |     7 |   126 |     1   (0)| 00:00:01 |
|   1 |  CONCATENATION                       |               |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID        | EMPLOYEES     |     1 |    18 |     0   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN                 | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |     6 |   108 |     1   (0)| 00:00:01 |
|*  5 |    INDEX SKIP SCAN                   | EMP_NAME_IX   |     6 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   3 - access("T1"."EMPLOYEE_ID"=100)
   4 - filter(LNNVL("T1"."EMPLOYEE_ID"=100))
   5 - access("T1"."FIRST_NAME" LIKE 'K%')
       filter("T1"."FIRST_NAME" LIKE 'K%')


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

SQL> set autotrace off

Here, there are two indexes which are accessed.

Report message to a moderator

Re: Primary Key in another Index [message #605977 is a reply to message #605968]

Fri, 17 January 2014 01:15

evivek
Messages: 6
Registered: December 2013
Location: mumbai

Junior Member

hi friend,

Yes,It can be good if you are using same composite index column list into your .sql query select/where clause.
But,always better to check explain plan for getting coorect idea.

Please use following sequence of steps :


Step 1: explain plan for
        <sql query>


Step 2:  select  *
         from    table(dbms_xplan.display)

Normally more indexing helps where we have Denormalized tables e.g. OLAP/Data Warehousing

Report message to a moderator

Previous Topic:	cursor loop
Next Topic:	Add a new column on first position of table in existing table.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 23:24:27 CDT 2024