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.
There must be a happy medium somewhere between being totally informed and blissfully unaware...
Hi,
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.
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.
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.
Explanation
---------------------------------------------------------------------------
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!
Hi,
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 )
PRIMARY INDEX COL 1, COL2 , COL 3
SECONDARY INDEX COL 1
Qry:
select *
from Table A INNER JOIN TABLE B
ON( A.COL1 = B.COL1 )
Now i want to know for COL 1 PI is used or SI is used.
Please help me .
Thanks,
Harshitha.