All Forums General
harshita45 11 posts Joined 08/12
17 Aug 2012
how to Check in Explain Plan SI is used or nor


I have a table which is having PI (Combination of columns)  and SI as the same column. I want to know whether SI is used by the query or not. How can i know from Explain plan.

Table A (Col 1 )







select *


ON( A.COL1  =  B.COL1 )


Now i want to know for COL 1 PI is used or SI is used.


Please help me .





VandeBergB 182 posts Joined 09/06
17 Aug 2012

If you run an explain on the query, the use of the SI will show up in the explain plan with verbiage similar to "by way of index # 4".  Your secondary and join indices will increment by four if you have more than one on a particular table.

NUSI usage by the optimizer can be problematic as they must be very selective, 85%+ has been bandied about in some conversations.  If the NUSI doesn't reach that or several other conditions, the optimizer won't use it because the cost of using the NUSI exceeds the cost of a full table scan.


Some drink from the fountain of knowledge, others just gargle.

harshita45 11 posts Joined 08/12
18 Aug 2012


Thank u for the reply. I saw the explain plan. i am not able to understand whether it is using my SI or not. 

Here my SI is L3_SALES and it is the PI of the same table but in Combination.


We do an all-AMPs JOIN step from Spool 3 (Last Use) by way of a RowHash match scan, which is joined to Spool 4 (Last Use) by way of a RowHash match scan.  Spool 3 and Spool 4 are joined using a merge join, with a join condition of ( "(L2_SALES = L2_SALES) AND (L3_SALES = L3_SALES)").

The result goes into Spool 6 (all_amps) (compressed columns allowed), which is redistributed by the hash code of (     ETLDB.EL_REV.L3_SALES,SUPPORTDB_PRF.Q2.ISO_COUNTRY,     SUPPORTDB_PRF.Q2.L3_SALES,  SUPPORTDB_PRF.Q2.SALES_COVERAGE) to all AMPs.



ToddAWalter 316 posts Joined 10/11
18 Aug 2012

This is not the step that would tell you. This says it is joining two spools which means it has already accessed and spooled both tables. The use of the index would come when it is accessing the table, not the derived spool file. In a case such as the query you describe, the index will never be used. You are not specifying any qualification against the indexed column which would allow the index to have value in selecting rows. And you are asking for all columns from the table so there is no opportunity to cover the query from the index. For this query it is always more efficient to just access the base table directly.

malli 10 posts Joined 08/12
21 Aug 2012

explain sel c.* ,o.* from customer c join ordertbl o on c.c_custkey=o.o_cus
tkey and o.o_custkey=100;

 *** Help information returned. 21 rows.
 *** Total elapsed time was 1 second.

  1) First, we lock a distinct TPCH."pseudo table" for read on a
     RowHash to prevent global deadlock for TPCH.o.
  2) Next, we lock TPCH.o for read.
  3) We do an all-AMPs RETRIEVE step from TPCH.o by way of index # 4
     "TPCH.o.O_CUSTKEY = 100" with no residual conditions into Spool 2
     (one-amp), which is redistributed by the hash code of (100) to all
     AMPs.  Then we do a SORT to order Spool 2 by row hash.  The size
     of Spool 2 is estimated with low confidence to be 16 rows (1,552

sivaji.gv2 2 posts Joined 08/12
24 Aug 2012

If DBQL is enabled at object level, then DBQLOBJ table would record whenever a query accesses an index. Please dig into DBQL and see if it helps!

Sivaji GV
Teradata DBA

You must sign in to leave a comment.