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 )
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.
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.
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
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!