Home » RDBMS Server » Performance Tuning » On which columns to apply index (Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit)
On which columns to apply index [message #546198] Mon, 05 March 2012 12:46 Go to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

I have two tables like below-

CREATE TABLE prod_vendor_record (
  vendor_record_seq_no   NUMBER       NOT NULL,
  study_seq_no           NUMBER       NOT NULL,
  vendor_subject_seq_no  NUMBER       NULL,
  control_dataset_seq_no NUMBER       NOT NULL,
  checksum               NUMBER       NOT NULL,
  processing_flag        VARCHAR2(1)  NULL,
  transaction_user       VARCHAR2(30) NOT NULL,
  transaction_src        VARCHAR2(30) NOT NULL,
  transaction_dt         DATE         NOT NULL,
  transaction_type       VARCHAR2(1)  NOT NULL,
  key_hash               NUMBER(10,0) NULL,
  key_col_val            CLOB         NULL
)


CREATE TABLE prod_temp_vendor_record_20000 (
  rownumber            NUMBER         NULL,
  vendor_record_seq_no NUMBER         NULL,
  checksum             NUMBER         NULL,
  transaction_type     VARCHAR2(1)    NULL,
  error_flag           VARCHAR2(1)    NULL,
  error_code           VARCHAR2(256)  NULL,
  recordstring         VARCHAR2(4000) NULL,
  key_hash             NUMBER(10,0)   NULL,
  key_col_val          CLOB           NULL
)
/


and executing below query on those tables-

insert into prod_temp_vendor(vendor_record_seq_no,checksum,rownumber,transaction_type,iu_flag)
select vr.vendor_record_seq_no, tvr.checksum, tvr.rownumber, tvr.transaction_type, 'U' from
prod_vendor_record vr, prod_temp_vendor_record_20000 tvr
where vr.study_seq_no=25707 
and vr.control_dataset_seq_no=3910
and vr.key_hash=tvr.key_hash
and dbms_lob.compare(vr.key_col_val, tvr.key_col_val) = 0
and tvr.error_flag is null;


Please let me know on which columns of PROD_VENDOR_RECORD table to apply index to make processing faster. As I tried to build index like below-

CREATE INDEX idx_prod_vendor_record
  ON prod_vendor_record (
    study_seq_no,
    control_dataset_seq_no,
    key_hash
  )
/


But it is not being used by above query (see execution plan)

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name                          | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT        |                               |       |       |  5168 (100)|          |        |      |            |
|   1 |  PX COORDINATOR         |                               |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002                      |   212 |   445K|  5168   (1)| 00:01:03 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN BUFFERED   |                               |   212 |   445K|  5168   (1)| 00:01:03 |  Q1,02 | PCWP |            |
|   4 |     BUFFER SORT         |                               |       |       |            |          |  Q1,02 | PCWC |            |
|   5 |      PX RECEIVE         |                               | 21168 |    41M|   278   (1)| 00:00:04 |  Q1,02 | PCWP |            |
|   6 |       PX SEND HASH      | :TQ10000                      | 21168 |    41M|   278   (1)| 00:00:04 |        | S->P | HASH       |
|*  7 |        TABLE ACCESS FULL| PROD_TEMP_VENDOR_RECORD_20000 | 21168 |    41M|   278   (1)| 00:00:04 |        |      |            |
|   8 |     PX RECEIVE          |                               |  1899K|   197M|  4886   (1)| 00:00:59 |  Q1,02 | PCWP |            |
|   9 |      PX SEND HASH       | :TQ10001                      |  1899K|   197M|  4886   (1)| 00:00:59 |  Q1,01 | P->P | HASH       |
|  10 |       PX BLOCK ITERATOR |                               |  1899K|   197M|  4886   (1)| 00:00:59 |  Q1,01 | PCWC |            |
|* 11 |        TABLE ACCESS FULL| PROD_VENDOR_RECORD            |  1899K|   197M|  4886   (1)| 00:00:59 |  Q1,01 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("VR"."KEY_HASH"="TVR"."KEY_HASH")
       filter("DBMS_LOB"."COMPARE"("VR"."KEY_COL_VAL","TVR"."KEY_COL_VAL")=0)
   7 - filter("TVR"."ERROR_FLAG" IS NULL)
  11 - access(:Z>=:Z AND :Z<=:Z)
       filter(("VR"."STUDY_SEQ_NO"=25707 AND "VR"."CONTROL_DATASET_SEQ_NO"=3910))
 
Note
-----
   - dynamic sampling used for this statement 


Thanks,
Manu
Re: On which columns to apply index [message #546199 is a reply to message #546198] Mon, 05 March 2012 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
when all else fails Read The Fine Manual

http://docs.oracle.com/cd/E11882_01/server.112/e16638/data_acc.htm#i17778
Re: On which columns to apply index [message #546200 is a reply to message #546199] Mon, 05 March 2012 13:25 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

Am I not clear with my question?

Regards,
Manu
Re: On which columns to apply index [message #546232 is a reply to message #546200] Mon, 05 March 2012 17:01 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
Was I unclear with the answer?
Re: On which columns to apply index [message #546250 is a reply to message #546232] Mon, 05 March 2012 23:40 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

Thanks for all your help!!

Regards,
Manu

[Updated on: Mon, 05 March 2012 23:58]

Report message to a moderator

Re: On which columns to apply index [message #546267 is a reply to message #546250] Tue, 06 March 2012 01:14 Go to previous messageGo to next message
Michel Cadot
Messages: 59291
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And so the solution was?

Regards
Michel
Re: On which columns to apply index [message #546386 is a reply to message #546267] Tue, 06 March 2012 07:33 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

I created some tables for testing purpose, in which I have dumped selected data from production. Later I realized, index is not being used, as on the columns index was created has only one distinct value, I didn't get time to insert more different data in those columns, let me do that and I will update whether the index is being used or not.
Re: On which columns to apply index [message #546396 is a reply to message #546386] Tue, 06 March 2012 08:47 Go to previous messageGo to next message
Roachcoach
Messages: 1215
Registered: May 2010
Location: UK
Senior Member
Something is not being disclosed somewhere. That plan doesnt match the given information.

If the whole story is not given, you can't expect accurate assistance.
Re: On which columns to apply index [message #546404 is a reply to message #546396] Tue, 06 March 2012 09:53 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

Ahhh, fault from my side only. First I also didn't know about that as creating dump data is made by some other person.

Well, thanks for assistance.
Re: On which columns to apply index [message #546405 is a reply to message #546404] Tue, 06 March 2012 09:56 Go to previous messageGo to next message
Roachcoach
Messages: 1215
Registered: May 2010
Location: UK
Senior Member
Well your reply doesn't make a lot of sense to me so I'll just mention that sufficient parallel can result in the optimiser eschewing index access in favour of FTS.

Something to keep in mind. Given you have a parallel plan.
Re: On which columns to apply index [message #548059 is a reply to message #546405] Tue, 20 March 2012 01:54 Go to previous messageGo to next message
manishag
Messages: 6
Registered: March 2012
Junior Member
Did you figure out why the index is not being used?
You could try using HINT also
Re: On which columns to apply index [message #548540 is a reply to message #548059] Thu, 22 March 2012 13:46 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

I didn't find any solution for the same. Just got time to look into it again.

I have inserted other distinct data into the table and modified my query in the way below:

INSERT /*+ NOPARALLEL(prod_temp_vendor) */ INTO prod_temp_vendor 
                                                (vendor_record_seq_no, 
                                                 checksum, 
                                                 rownumber, 
                                                 transaction_type, 
                                                 iu_flag) 
SELECT /*+ NOPARALLEL(vr) NOPARALLEL(tvr) */ vr.vendor_record_seq_no, 
                                             tvr.checksum, 
                                             tvr.rownumber, 
                                             tvr.transaction_type, 
                                             'U' 
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910 
       AND vr.key_hash = tvr.key_hash 
       AND dbms_lob.Compare(vr.key_col_val, tvr.key_col_val) = 0 
       AND tvr.error_flag IS NULL; 


In execution plan, I am still not able to see the usage of index.

Please suggest where I am doing wrong.

Thanks,
Manu

* BlackSwan posted formatted code

[Updated on: Thu, 22 March 2012 14:05] by Moderator

Report message to a moderator

Re: On which columns to apply index [message #548541 is a reply to message #548540] Thu, 22 March 2012 14:07 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
>AND tvr.error_flag IS NULL;
precludes use of index since NULL never included in INDEX
Re: On which columns to apply index [message #548542 is a reply to message #548541] Thu, 22 March 2012 14:29 Go to previous messageGo to next message
manubatham20
Messages: 468
Registered: September 2010
Location: Champaign, IL
Senior Member

And what if I am excluding condition >AND tvr.error_flag IS NULL; its still not using the index.

Thanks,
Manu
Re: On which columns to apply index [message #548544 is a reply to message #548542] Thu, 22 March 2012 14:46 Go to previous messageGo to next message
BlackSwan
Messages: 22845
Registered: January 2009
Senior Member
post results from following SQL
SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910 
       AND vr.key_hash = tvr.key_hash 
       AND dbms_lob.Compare(vr.key_col_val, tvr.key_col_val) = 0 
       AND tvr.error_flag IS NULL; 

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910 
       AND dbms_lob.Compare(vr.key_col_val, tvr.key_col_val) = 0 
       AND tvr.error_flag IS NULL;

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910 
       AND dbms_lob.Compare(vr.key_col_val, tvr.key_col_val) = 0 ;

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 
       AND vr.control_dataset_seq_no = 3910;

SELECT COUNT(*)
FROM   prod_vendor_record vr, 
       prod_temp_vendor_record_20000 tvr 
WHERE  vr.study_seq_no = 25707 ;


SELECT COUNT(*)
FROM   prod_vendor_record vr;

SELECT COUNT(*)
FROM       prod_temp_vendor_record_20000 tvr ;

Re: On which columns to apply index [message #548593 is a reply to message #548542] Fri, 23 March 2012 05:48 Go to previous message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

And what if I am excluding condition >AND tvr.error_flag IS NULL; its still not using the index.

What is the reason for you to think that it should use the Index? Is it because the current query is taking a longer time to execute and you want to make the query to run faster ? If so, define how slow it is and why do you consider it is slow. If you have been given the task (instead of the oracle optimizer) to optimize the query which path you would have adopted to make it run faster and why would adopt that path ?

Thanks

Raj
Previous Topic: Order of steps in execution path and order of predicates in the Plan
Next Topic: very slow on insert
Goto Forum:
  


Current Time: Wed Oct 01 21:05:28 CDT 2014

Total time taken to generate the page: 0.13053 seconds